Section 8 - Manipulating Data

1. Section 8 - Manipulating Data • The INSERT statement adds rows of data to the database • The UPDATE statement changes columns of existing data • The DELETE statement deletes rows of data from the database

2. Add, Change, or Remove Data • Data Modification Statements • INSERT - adds rows to a table • UPDATE - changes information in a table • DELETE - deletes rows from a table • You can modify only ONE table per SQL statement • May be based on data from more than one table • You can use a SQL subquery

3. INSERT Statement • Adds Rows to a Table • There are two ways to use an insert statement... • the VALUES keyword • the Subquery [SELECT statement]

4. INSERT Syntax • INSERT INTO table_name [ (column_1, column_2, etc.) ]VALUES (constant_1, constant_2, etc.) • In embedded SQL, you may use variables instead of constants

5. INSERT Example 1 • If you are adding values to every column defined for a table you don't have to specify the column names • INSERT INTO publishersVALUES ( '1662', 'Jardin, Inc.', '55th Avenue', 'Camden', 'NJ');

6. INSERT Example 2 • If you add a row using less than all of the columns defined for a table you must specify the column names • INSERT INTO publishers ( (pub_id, pub_name)VALUES ('1756', 'Healthtext'); • Each column must match a value in the list • The columns do not have to be listed in CREATE TABLE statement order

7. INSERT Example 3 • Using the SELECT as a subquery... • INSERT INTO authors(au_id, au_lname, au_fname) SELECT ed_id, ed_lname, ed_fname FROM editors WHERE ed_lname = 'SMITH'; • This adds all the editors with the last name of Smith to the authors table

8. Compatible Structures • When Inserting from one table to another... • The matching columns must be of the same datatype • Or they must convert to the correct datatype(e.g. a character datatype column may be loaded into a numeric datatype column if all the data in the character column is numeric)

9. Exercise • Write a statement to add a row into the Titleauthors table

10. Discussion • If we want to add all the columns to the titleauthors table we can skip listing the column names... as long as we follow the CREATE TABLE column order • INSERT INTO titleauthors('BU1234', '149-48-9082', 1, 1.00);

11. Exercise • Write a statement to add a row into the Titles table

12. Discussion • If wanted to add a row to the titles table with the minimum required columns specified… Example: here's how we might add a new book by Bill Gates...INSERT INTO titles(title_id, title, contract)VALUES('BU1234', 'The Geek Shall Inherit the Earth", 1);

13. Exercise • Write a statement to make the author Albert Ringer an editor as well

14. Discussion • Here we must use a Subquery • First write the Select statementSELECT au_id, au_lname, au_fnameFROM authorsWHERE au_lname = 'Ringer'AND au_fname = 'Albert';

15. Discussion - cont. • Now place the correct INSERT statement above the SELECT INSERT INTO editors(ed_id, ed_lname, ed_fname)SELECT au_id, au_lname, au_fnameFROM authorsWHERE au_lname = 'Ringer'AND au_fname = 'Albert';

16. UPDATE Statement • Changes existing data for... • A Single row • A Group of rows • All rows in a table • Updates can come from constants, variables(in Embedded SQL) or other tables

17. UPDATE Syntax • UPDATE table_name SET column_name1 = expression1, column_name2 = expression2...[ WHERE search_conditions ] • Expressions must be of the same datatype as the column you're updating (or convert to the same datatype)

18. UPDATE Examples • Change the price on all the books in the titles table to $17.99UPDATE titles SET price = 17.99;

19. Update Example 2 • Double the price and add $1,000 to the advance for all books that have sold more than 10,000 copiesUPDATE title SET price = price * 2, advance = advance + 1000 WHERE ytd_sales > 10000;

20. Exercise • Reduce the price of all books by 20% if they have sold under a thousand books this year.

21. Discussion • UPDATE titlesSET price = price * .80WHERE ytd_sales < 1000;This sets the column price to 80% of itself(20% off) where the year-to-sales is less than 1000 books sold.

22. Exercise • The publisher "New Age Books" has been relocated to Seattle, Washington. All of the editors have moved to a Seattle commune at 123 Flower Street. Make the appropriate changes to the editors table.

23. Discussion • This seems like a simple request, change the city and state to Seattle, WA if the editor is working for New Age Books, but let's look at the SQL required to do this.

24. Discussion • We know we want to update the editors tableUPDATE editorsSET city = 'Seattle', state = 'WA'...but only for New Age Book editors...

25. Discussion • Look at the chart on Page 43 of your book • First we must find out which books the editors are editing by looking at the titleditors table • Then we need to find out which publisher is is publishing that book by looking at the titles table • The we need to find out which of these publishers are named 'New Age Books'

26. Discussion • UPDATE editorsSET city = 'SEATTLE', state = 'WA'WHERE ed_id IN (SELECT ed_id FROM titleditors WHERE title_id IN (SELECT title_id FROM titles WHERE pub_id = (SELECT pub_id FROM publishers WHERE pub_name = "New Age Books");

27. Discussion • Read the statement from the bottom up... • The pub_id is found for 'New Age Books' and passed up to the next SELECT statement • The title_ids for that pub_id are found and passed up to the next SELECT statement • The ed_ids associated with those books are found and passed up to the WHERE clause of the update statement. • Now the Update statement changes the city and state for the appropriate list of editors

28. Removing Data • Use the DELETE statement to remove data from a table • SYNTAX: • DELETE FROM table_name[WHERE search_conditions];

29. Examples • DELETE FROM titlesWHERE price = 2.99; • This deletes all rows from the titles tablewhere the price of the book is $2.99 • DELETE FROM titles; • This deletes all rows from the titles table, but does not delete the titles table itself

30. Example: Delete with Select • DELETE from authorsWHERE au_lname IN (SELECT ed_lname FROM editors WHERE ed_pos = 'Managing');This deletes all authors who have the same last name as any Managing Editor

31. Exercise • Delete all editors that live in Seattle, WA.

32. Discussion • DELETE FROM editorsWHERE city = 'Seattle'AND state = 'WA';

33. Exercise • 'New Age Books' has gone out of business. Remove all relevant information from the database

34. Discussion • What's wrong with this?DELETE FROM publishersWHERE pub_name = 'New Age Books';

35. Discussion • There is nothing wrong with the statement, this will remove the 'New Age Books' publisher from the database, but what about all the books they were selling? What about the authors? • If we remove the publisher first we will lose the information we need to reference the other data that needs to be deleted

36. Discussion • We can find out the books published by matching with pub_id on the titles table, but look at how many places the title_id is referenced: the titleauthors table, the titleditors table, and the salesdetail table. • Do we remove the editors that were working on books for New Age Books? • Do we remove the authors who wrote books published by New Age Books? What if they've written for another publisher? • Do we still want to keep track of sales history information?

37. Constraints • Many DBMS's offer an extention to SQL called CONSTRAINTS • These enforce rules which preserve the referential integrity in a database. For example, you can specify that a publisher cannot be deleted, unless all the books that reference it have been deleted first.

38. Last Slide Section 8 • Please complete Assignment 7