Creating some complex tables with sql (employee, branch, works_with, supplier)

tables.sql

--***********************EMPLOYEE TABLE **************************
--This creates the employee table but the super_id and branch_id are going to be forgein keys later. 
CREATE TABLE employee(
    emp_id INT PRIMARY kEY AUTO_INCREMENT,
    first_name VARCHAR(40),
    last_name VARCHAR(40),
    birth_date DATE,
    sex VARCHAR(1),
    salary INT,
    super_id INT,
    branch_id INT
);
--add employees to the table but the foreign keys are null bc the branch table doesnt have anything yet
INSERT INTO employee VALUES(100, 'David', 'Wallace', '1967-11-17', 'M', 250000, NULL, NULL);
INSERT INTO employee VALUES(101, 'Jane', 'Levinson', '1961-05-17', 'F', 150000, NULL, 1);
INSERT INTO employee VALUES(102, 'Michael', 'Scott', '1964-03-15', 'M', 75000, NULL, NULL);
INSERT INTO employee VALUES(103, 'Angela', 'Martin', '1971-06-25', 'F', 65000, NULL, 2);
INSERT INTO employee VALUES(104, 'Kelly', 'Kappor', '1967-10-12', 'M', 55000, NULL, 2);
INSERT INTO employee VALUES(105, 'Standley', 'Hudson', '1964-02-19', 'M', 60000, NULL, 2);
INSERT INTO employee VALUES(106, 'Josh', 'Pointer', '1969-09-05', 'M', 78000, NULL, NULL);
INSERT INTO employee VALUES(107, 'Andy', 'Bernard', '1973-07-22', 'M', 65000, NULL, 3);
INSERT INTO employee VALUES(108, 'Jim', 'Halbert', '1978-10-01', 'M', 71000, NULL, 3);
--now update the david wallace employee to reflect the correct foreign key
UPDATE employee
SET branch_id = 3
WHERE emp_id = 106;
--show that the employees were addded correctly
SELECT * FROM employee;
--change the first table to have a forgein key branch_id instead of an INT
--This will link the branch_id to the branch table and each branch_id
ALTER TABLE employee
ADD FOREIGN KEY(branch_id)
REFERENCES branch(branch_id)
ON DELETE SET NULL;
--This will link the super_id of the employee table to the same table but points to the id of another employee
ALTER TABLE employee
ADD FOREIGN KEY(super_id)
REFERENCES employee(emp_id)
ON DELETE SET NULL;



--**************************** BRANCH TABLE **********************************
--This creates a branch table that will allow us to connect the employee and the branch tables
--this will make the emp_id of the employee table related to this tables mrg_id
CREATE TABLE branch(
    branch_id INT PRIMARY KEY,
    branch_name VARCHAR(20),
    mrg_id INT, 
    mgr_start_date DATE,
    FOREIGN key(mrg_id) REFERENCES employee(emp_id) ON DELETE SET NULL
);
INSERT INTO branch VALUES(3, 'Standford', 106, '1962-02-13');
--check that the branches were added correctly
SELECT * FROM branch;


--*************************** CLIENT TABLE ****************************
--This will create the client table
CREATE TABLE client(
    client_id INT PRIMARY KEY,
    client_name VARCHAR(20),
    branch_id INT,
    FOREIGN key(branch_id) REFERENCES branch(branch_id) ON DELETE SET NULL
);
--add clients to the table just created
INSERT INTO client VALUES(400, 'Dunmore Highschool',2);
INSERT INTO client VALUES(401, 'Lackawana County', 2);
INSERT INTO client VALUES(402, 'FedEx', 2);
INSERT INTO client VALUES(403, 'John Daily', 3);
INSERT INTO client VALUES(404, 'Scraton White Pages', 3);
INSERT INTO client VALUES(405, 'Time Newspaper', 3);
INSERT INTO client VALUES(406, 'FedEx', 2);
--show the clients were added
SELECT * FROM client;


--******************* BRANCH SUPPLIER *******************
--This will create the client table
CREATE TABLE branch_supplier(
    branch_id INT,
    supplier_name VARCHAR(20),
    supply_type VARCHAR(20),
    PRIMARY KEY(branch_id, supplier_name),
    FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE CASCADE
);
--add suppliers to the branch_supplier table
INSERT INTO branch_supplier VALUE(2, 'Hammer Mill', "Paper");
INSERT INTO branch_supplier VALUE(2, 'Uni-ball', "Writing Utensils");
INSERT INTO branch_supplier VALUE(3, 'Patriot Paper', "Paper");
INSERT INTO branch_supplier VALUE(2, 'J.T. Forms & Labels', "Custom Labels");
INSERT INTO branch_supplier VALUE(3, 'Uni-ball', "Writing Utensils");
INSERT INTO branch_supplier VALUE(3, 'Hammer Mill', "Paper");
INSERT INTO branch_supplier VALUE(3, 'Stamford Labels', "Custom Labels");
--show that the branch suppliers were added
SELECT * FROM branch_supplier;


--**********************WORKS WITH *************************
--This will make a works with table that has a COMPOSITE KEY
CREATE TABLE 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
);
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);
INSERT INTO works_with VALUES(108, 403, 12000);
INSERT INTO works_with VALUES(105, 404, 33000);
INSERT INTO works_with VALUES(107, 405, 26000);
INSERT INTO works_with VALUES(102, 406, 16000);
INSERT INTO works_with VALUES(105, 406, 20000);
--checks that the works with table was added correctly
SELECT * FROM works_with;