-- Tutorial 4 -- -- Run Tutorial 3 before this one! -- -- Section 1: INSERT -- -- Syntactical Template: "INSERT INTO VALUES " INSERT INTO EMAIL VALUES('abc@123.com'); INSERT INTO PERSONS VALUES('Violet', 'abc@123.com', '001-001-0001', 'FEMALE', 21, '1996-01-24'); -- Recall that in PERSONS we have a composite key, so the following will work: INSERT INTO PERSONS VALUES('Violet', 'abc@123.com', '905-905-9050', 'FEMALE', 21, '1996-01-24'); -- But the following will not work, as we violate a foreign key constraint [there's no email called "huh@123.com"] -- INSERT INTO PERSONS VALUES('Violet', 'huh@123.com', '001-001-0001', 'FEMALE', 21, '1996-11-24'); -- But, if we do... INSERT INTO EMAIL VALUES('huh@123.com'); -- The line will work. -- Section 2: UPDATE -- -- Syntactical Template: UPDATE SET WHERE UPDATE PERSONS SET AGE = 22 WHERE Name = 'Violet'; UPDATE PERSONS SET Name = 'V' WHERE Name = 'Violet'; -- Always observe Foreign Keys. UPDATE PERSONS SET EMAIL = 'nah@nah.nah' will not work- "nah@nah.nah" is not in parent table EMAIL. -- Similarly, UPDATE EMAIL SET EADDR = 'nah@nah.com' WHERE EADDR = 'huh@123.com' will not work- child table PERSONS will throw a conflict. -- On the other hand, the following: INSERT INTO EMAIL VALUES('waste@email.com'); UPDATE EMAIL SET EADDR = 'throwaway@email.com' WHERE EADDR = 'waste@email.com'; -- Will run just fine. There are no foreign dependencies on that data so we can freely update it. -- To circumvent these update problems, you can use "ON UPDATE CASCADE" in your table design to ensure that, once a parent/child row has been updated, its related parent/child rows are too [i.e. the update cascades over the rest of the DB]. -- Section 3: DELETE -- -- Syntactical Template: DELETE FROM WHERE -- Very similar to UPDATE. It might even be useful to think of it as an "UPDATE" that sets rows to nothing. -- Main difference is that you can now delete rows from a foreign key relation's child tables at ease. DELETE FROM PERSONS WHERE NAME = 'V'; -- Deleting from a parent table will cause issues though- that will mean the data in the child is pointing to nothing. -- So DELETE FROM EMAIL WHERE EADDR = 'farmerbusinessmanrapperinspace@gmail.com'; will not work. But the following... DELETE FROM EMAIL WHERE EADDR = 'throwaway@email.com'; -- ... works just fine, as there are no foreign key dependencies. -- Again, you can use "ON DELETE CASCADE" in your table design to ensure that, once a parent row has been deleted, its corresponding child rows are too [i.e. the delete cascades over the rest of the DB]. -- Section 4: SQL -- -- We had some difficult problems due to the lecture mishaps. However, here are some of the SELECT statements we did work on: -- List everything from Email and Persons. SELECT * FROM EMAIL; SELECT * FROM PERSONS; -- List everything about people whose ages are between 10 and 90 non inclusive. SELECT * FROM PERSONS WHERE age > 10 AND age < 90; -- List every different name for every Person. SELECT DISTINCT NAME FROM PERSONS; -- List the names of every person older than the oldest person under 10 years old. SELECT NAME FROM PERSONS WHERE age >= ALL(SELECT age FROM PERSONS WHERE AGE < 10);