06-01 Insert data into tables

01

Order matter when inserting the tables, If tables have foreign keys, the row values of the foreign keys need to be intiliazed with NULL

For example in the first table created in the previous lesson, the last two columns of the employee table are forieng keys from the branch table 

INSERT INTO employee VALUES(100,'David','Wallace','1967-11-17','M',250000, NULL, NULL);

So insert NULL values for the the primary key super_id and branch_id because those values haven't been filled yet

02

Create the first branch row values, the branch_id can be updated to employee after being inserted, so foreign branch_id key is connected to employee 

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;

-- place the next employee who works for that branch in the employee table
INSERT INTO employee VALUES(101,'Jan','Levinson','1961-05-11','F',110000,100,1);

SELECT * FROM employee;

Insert the other employee who works for that branch

03

Do this for the other branches, 

INSERT INTO employee VALUES(102,'Michael','Scott','1964-03-15','M',75000,100,NULL);

Remember to place NULLinside branch_id for employee value if value is not set in the branch table 

INSERT INTO branch VALUES(2,'Scranton',102,'1992-04-06');

Then update the employee, and add other employees to the employee table who work for that branch 

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);

SELECT * FROM employee;

We have to do it this way. Employess can't get the branch_id or super_id when no values have been inserted in client_table,

04

employee & branch(last insert)
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 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);
SELECT * FROM employee;

05

branch_supplier

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');
SELECT * FROM branch_supplier

05

client

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);
SELECT * FROM client;

05

work_with

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, 15000);
INSERT INTO works_with VALUES(105, 406, 130000);
SELECT * FROM works_with;