dunder_mifflin_queries.sql

CREATE DATABASE dunder_mifflin;
USE dunder_mifflin;
CREATE TABLE IF NOT EXISTS employee (
    emp_id INT PRIMARY KEY,
    first_name VARCHAR(40),
    last_name VARCHAR(40),
    birth_day DATE,
    sex VARCHAR(1),
    salary INT,
    super_id INT,
    branch_id INT
);
CREATE TABLE IF NOT EXISTS branch (
    branch_id INT PRIMARY KEY,
    branch_name VARCHAR(40),
    mgr_id INT,
    mgr_start_date DATE,
    FOREIGN KEY(mgr_id) REFERENCES employee(emp_id) ON DELETE
    SET NULL
);
ALTER TABLE employee
ADD FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE
SET NULL;
ALTER TABLE employee
ADD FOREIGN KEY(super_id) REFERENCES employee(emp_id) ON DELETE
SET NULL;
CREATE TABLE IF NOT EXISTS client (
    client_id INT PRIMARY KEY,
    client_name VARCHAR(40),
    branch_id INT,
    FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE
    SET NULL
);
CREATE TABLE IF NOT EXISTS works_with (
    emp_id INT,
    client_id INT,
    total_sales INT,
    PRIMARY KEY(emp_id, client_id),
    FOREIGN KEY(emp_id) REFERENCES employee(emp_id) ON DELETE CASCADE,
    FOREIGN KEY(client_id) REFERENCES client(client_id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS branch_supplier (
    branch_id INT,
    supplier_name VARCHAR(40),
    supply_type VARCHAR(40),
    PRIMARY KEY(branch_id, supplier_name),
    FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE CASCADE
);
INSERT INTO employee
VALUES(
        100,
        'David',
        'Wallace',
        '1967-11-17',
        'M',
        250000,
        NULL,
        NULL
    );
INSERT INTO branch
VALUES(1, 'Corporate', 100, '2006-02-09');
UPDATE employee
SET branch_id = 1
WHERE emp_id = 100;
INSERT INTO employee
VALUES(
        101,
        'Jan',
        'Levinson',
        '1961-05-11',
        'F',
        110000,
        100,
        1
    );
INSERT INTO employee
VALUES(
        102,
        'Michael',
        'Scott',
        '1964-03-15',
        'M',
        75000,
        100,
        NULL
    );
INSERT INTO branch
VALUES(2, 'Scranton', 102, '1992-04-06');
UPDATE employee
SET branch_id = 2
WHERE emp_id = 102;
INSERT INTO employee
VALUES(
        103,
        'Angela',
        'Martin',
        '1971-06-25',
        'F',
        63000,
        102,
        2
    );
INSERT INTO employee
VALUES(
        104,
        'Kelly',
        'Kapoor',
        '1980-02-05',
        'F',
        55000,
        102,
        2
    );
INSERT INTO employee
VALUES(
        105,
        'Stanley',
        'Hudson',
        '1958-02-19',
        'M',
        69000,
        102,
        2
    );
INSERT INTO employee
VALUES(
        106,
        'Josh',
        'Porter',
        '1969-09-05',
        'M',
        78000,
        100,
        NULL
    );
INSERT INTO branch
VALUES(3, 'Stamford', 106, '1998-02-13');
UPDATE employee
SET branch_id = 3
WHERE emp_id = 106;
INSERT INTO branch
VALUES(4, 'Buffalo', NULL, NULL);
INSERT INTO employee
VALUES(
        107,
        'Andy',
        'Bernard',
        '1973-07-22',
        'M',
        65000,
        106,
        3
    );
INSERT INTO employee
VALUES(
        108,
        'Jim',
        'Halpert',
        '1978-10-01',
        'M',
        71000,
        106,
        3
    );
INSERT INTO employee
VALUES(
        109,
        'Oscar',
        'Martinez',
        '1968-02-19',
        'M',
        69000,
        106,
        3
    );
INSERT INTO employee
VALUES(
        110,
        'Kevin',
        'Malone',
        '1978-02-19',
        'M',
        69000,
        106,
        3
    );
INSERT INTO employee
VALUES(
        111,
        'Pam',
        'Beesly',
        '1988-02-19',
        'F',
        69000,
        106,
        3
    );
INSERT INTO branch_supplier
VALUES(2, 'Hammer Mill', 'Paper');
INSERT INTO branch_supplier
VALUES(2, 'Uni-ball', 'Writing Utensils');
INSERT INTO branch_supplier
VALUES(3, 'Patriot Paper', 'Paper');
INSERT INTO branch_supplier
VALUES(2, 'J.T. Forms & Labels', 'Custom Forms');
INSERT INTO branch_supplier
VALUES(3, 'Uni-ball', 'Writing Utensils');
INSERT INTO branch_supplier
VALUES(3, 'Hammer Mill', 'Paper');
INSERT INTO branch_supplier
VALUES(3, 'Stamford Lables', 'Custom Forms');
INSERT INTO client
VALUES(400, 'Dunmore Highschool', 2);
INSERT INTO client
VALUES(401, 'Lackawana Country', 2);
INSERT INTO client
VALUES(402, 'FedEx', 3);
INSERT INTO client
VALUES(403, 'John Daly Law, LLC', 3);
INSERT INTO client
VALUES(404, 'Scranton Whitepages', 2);
INSERT INTO client
VALUES(405, 'Times Newspaper', 3);
INSERT INTO client
VALUES(406, 'FedEx', 2);
INSERT INTO works_with
VALUES(105, 400, 55000);
INSERT INTO works_with
VALUES(102, 401, 267000);
INSERT INTO works_with
VALUES(108, 402, 22500);
INSERT INTO works_with
VALUES(107, 403, 5000);