Using Relational Databases and SQL

1. Steven Emory Department of Computer Science California State University, Los Angeles Using Relational Databases and SQL Lecture 8: Correlated Subqueries and the Data Manipulation Language

2. SELECT Clause Subqueries • A SELECT clause subquery must return a single value (not a list or table)‏ • Examples: • SELECT (SELECT 1) + (SELECT 2); -- 3 • SELECT (SELECT COUNT(*) FROM Movies); -- 6 • SELECT (SELECT * FROM Movies); -- ERROR!!!

3. SELECT Clause Subqueries • SELECT clause subqueries are good for single-value calculations, such as percentages • Example: • -- What percent of accounts are male?

4. SELECT Clause Subqueries • Example • -- OUTER QUERYSELECT 100*(X)/(Y); • -- INNER QUERY X = number of male accountsSELECT COUNT(*) FROM AccountsWHERE Gender = 'M'; • -- INNER QUERY Y = number of total accountsSELECT COUNT(*) FROM Accounts; • -- SOLUTION SELECT 100*(SELECT COUNT(*) FROM Accounts WHERE Gender = 'M')/(SELECT COUNT(*) FROM Accounts);

5. SELECT Clause Subqueries • A SELECT clause subquery is even more useful when the outer query and inner query are correlated (the inner query is dependent on data from the outer query)‏

6. Correlated Subqueries • Previous subqueries have been non-correlated. • non-correlated means ‘no dependencies’ • which means you can run the inner query separately • Correlated subqueries are inner queries that are ‘dependent’ on data from outer queries. • correlated means ‘with dependencies’ • which means you can’t run the inner query separately • the result of the inner query ‘depends on’ data given to it from the outer query

7. Correlated Subqueries • Some FROM clause subquery problems can be rewritten using correlated subqueries in the SELECT clause. • Let’s try an example: • List each movie title along with the number of ratings and the number of genres for that movie. • +--------------------------------+---------+--------+| Title | Ratings | Genres |+--------------------------------+---------+--------+| Star Trek: Generations | 10 | 4 || X-Men | 12 | 4 || X-Men: The Last Stand | 12 | 4 || Things We Lost in the Fire | 9 | 1 || The X Files | 12 | 5 || The X Files: I Want to Believe | 11 | 3 |+--------------------------------+---------+--------+

8. Correlated Subqueries • Non-correlated solution: • SELECT Title, X.Ratings, Y.GenresFROM Movies M LEFTJOIN (SELECT MovieID, COUNT(Rating) AS Ratings FROM Ratings GROUPBY MovieID) X ON M.MovieID = X.MovieID LEFTJOIN (SELECT MovieID, COUNT(Genre) AS Genres FROM XRefGenresMovies GROUPBY MovieID) Y ON M.MovieID = Y.MovieID; • Take out the inner queries and try running them. Both run because they are independent of the outer query!

9. Correlated Subqueries • Correlated setup: • -- OUTER QUERYSELECT Title, (X) AS Ratings, (Y) AS GenresFROM Movie M; • -- INNER QUERY XSELECT COUNT(Rating)FROM RatingsWHERE MovieID = M.MovieID; • -- INNER QUERY YSELECT COUNT(Genre)FROM XRefGenresMoviesWHERE MovieID = M.MovieID;

10. Correlated Subqueries • Correlated solution (dependencies are underlined): • SELECT Title, (SELECT COUNT(Rating)FROM Ratings WHERE MovieID = M.MovieID) AS Ratings, (SELECT COUNT(Genre) FROM XRefGenresMovies WHERE MovieID = M.MovieID) AS Genres FROMMovies M; • Take out the inner queries and try running them. They won’t run because they are dependent on the MovieID attribute from the outer query.

11. Correlated Subqueries • So which one do you choose? Subqueries in the FROM clause or correlated subqueries in the SELECT clause? • Whichever one runs faster! • Our database is too small to do any real testing. • Notice the correlated version is shorter and looks nicer.

12. Data Manipulation Language • DML for short • Contains commands for modifying table data • Insertion commands (INSERT INTO) • Deletion commands (DELETE) • Update commands (UPDATE) • Not a query • Queries extract data from the database • Commands do not extract data from the database

13. Before We Start • When modifying the database data, you are going to mess up because nobody is perfect • If you mess up there are two ways to restore the original database: • Remove and restore the tables • Use transactions (use BEGIN and ROLLBACK)‏ • I prefer using BEGIN and ROLLBACK • Use BEGIN before entering DML commands • Use ROLLBACK to undo all changes • USE COMMIT to accept all changes

14. Transactions • ACID • Atomicity • Consistency • Isolation • Durability • SQL Keywords • BEGIN/STARTTRANSACTION • COMMIT • ROLLBACK

15. Inserting Records • Two syntaxes: • INSERT INTO • Insert one record at a time • INSERT SELECT • Insert one or more records at a time

16. Inserting Records • INSERT INTO Syntax • -- Form #1: Insert whole record.INSERT INTO tablenameVALUES(value1, value2, ..., valuen); • -- Form #2: Insert partial record. Non-specified fieldnames are assigned default values.INSERT INTO tablename(field1, field2, ..., fieldn)VALUES(value1, value2, ..., valuen); • IGNORE • You can use the IGNORE keyword between INSERT and INTO to suppress duplicate error messages.

17. Inserting Records • INSERT SELECT Syntax • -- Form #1: Insert whole record.INSERT INTO destination_tableSELECT field1, field2, ..., fieldnFROM source_tablesWHERE conditions; • -- Form #2: Insert partial record. Non-specified fieldnames are assigned default values. INSERT INTO destination_table(df1, df2, ..., dfn)SELECT sf1, sf2, ..., sfnFROM source_tablesWHERE conditions;

18. INSERT INTO Example • Example: • Add Kung Fu Panda into the database.INSERTINTO MoviesVALUES(7, 'Kung Fu Panda', '2008-06-06', 'G', 92, 'USA', 'English', (SELECT CompanyID FROM Companies WHERE Name = 'Dreamworks Pictures'));

19. INSERT INTO and Subqueries • As in the previous example, subqueries in the INSERT command work, but only if the update table and the subquery table are different. • This rule only applies to MySQL, other database management systems may behave differently.

20. INSERT SELECT Example • Example: • Associate all movies that have ‘The X Files’ anywhere in the title with the romance genre. INSERTIGNOREINTO XRefGenresMoviesSELECT MovieID, 'Romance'FROM MoviesWHERE Title LIKE'%The X Files%';

21. Deleting Records • Deletes one or more rows from a table • Deletes all rows without WHERE condition • Two syntaxes • Single-Table DELETE Syntax • Multi-Table DELETE Syntax

22. Single-Table DELETE Syntax • Deletes one or more rows from a table • Deletes all rows without WHERE condition • Syntax: • DELETEFROM tablenameWHERE conditions;

23. Single-Table DELETE Syntax • Examples: • Delete all ratings. • Delete all ratings by semory.

24. Single-Table DELETE Syntax • Solutions: • -- Delete all ratings.DELETEFROM Ratings; • -- Delete all ratings by semory.DELETEFROM RatingsWHERE AccountID = (SELECT AccountID FROM Accounts WHERE Username = 'semory');

25. Multi-Table DELETE Syntax • Deletes rows from multiple tables • You must be very cautious or else you may delete something you didn’t want to delete • Syntax: • DELETE T1, T2, ..., TnFROM T1 JOIN T2 JOIN ... JOIN TnWHERE conditions; • Note: If you use table alias in the FROM clause, you must use the alias in the DELETE clause as well (see examples later on).

26. Multi-Table DELETE Syntax • Examples: • -- Delete all ratings by semory (use multi-table delete syntax instead of using single-table delete syntax with a subquery). • -- Delete all directors from the database (from both the People and XRefDirectorsMovies tables).

27. Multi-Table DELETE Syntax • Examples: • -- Delete all ratings by semory.DELETE Ratings FROM Accounts JOIN Ratings USING(AccountID) WHERE Username = 'semory'; • -- Delete all ratings by semory (alternate).DELETE RFROM Accounts A JOIN Ratings RUSING(AccountID) WHERE Username = 'semory'; • -- Delete all directors from the database.DELETE P, DFROM People P JOIN XRefDirectorsMovies DON P.PersonID = D.DirectorID;

28. Multi-Table DELETE Syntax • There is a big problem in the last example. • Multi-table delete can lead to orphaned records if misused (a foreign key with no primary key). • DELETE P, DFROM People P JOIN XRefDirectorsMovies DON P.PersonID = D.DirectorID; • Jonathan Frakes is both an actor and a director. • If we delete him from the People and XRefDirectorsMovies tables, we orphan him in the XRefActorsMovies and Spouses table!

29. Multi-Table DELETE Syntax • Therefore, be careful what you delete! • If you delete a record, and that record is referenced somewhere else, you have an orphaned record! • No real good solution exists in MySQL. • Best solution is to only delete records from the XRefDirectorsMovies table and leave the People table alone since these people may be referenced somewhere else. • DELETE FROM XRefDirectorsMovies;

30. Updating Records • To update existing records, you may use one of the following syntaxes: • Single-table syntax. • Multi-table equi-join syntax. • Only equi-join is supported. • You may not use any other join syntax (JOIN ON, JOIN USING, etc.)

31. Updating Records • To update existing records: • -- Single-table syntax.UPDATE [IGNORE] tablenameSET field1 = value1, field2 = value2, ...WHERE conditions; • -- Multi-table equi-join syntax.UPDATE [IGNORE] tablename1, tablename2, ...SET field1 = value1, field2 = value2, ...WHERE conditions; • -- Multi-table subquery syntax.UPDATE [IGNORE] tablenameSET field1 = subquery1, field2 = subquery2, ...WHERE conditions;

32. Updating Records • You may use the IGNORE keyword immediately after UPDATE to ignore errors when an update produces duplicate primary keys. • Example: • -- ERROR! BEEP!UPDATE MoviesSET MovieID = 1WHERE MovieID = 2; • -- Error will be ignored!UPDATEIGNORE MoviesSET MovieID = 1WHERE MovieID = 2;

33. Updating Records • Examples: • -- The user ojisan has decided to change his username to uncle_steve. Update the database to reflect this change. • -- The user colderstone has decided to change his username and password to jackstone and slsev0812z3, respectively. Update the database to reflect this change. • -- Dreamworks Pictures went bankrupt and was bought out by Paramount Pictures. Modify all CompanyIDs in the Movies table to reflect this change.

34. Updating Records • Examples: • -- The user ojisan has decided to change his username to uncle_steve. Update the database to reflect this change.UPDATE AccountsSET Username = 'uncle_steve'WHERE Username = 'ojisan';

35. Updating Records • Examples: • -- The user colderstone has decided to change his username and password to jackstone and slsev0812z3, respectively. Update the database to reflect this change.UPDATE AccountsSET Username = 'jackstone', Password = 'slsev0812z3' WHERE Username = 'colderstone';

36. Updating Records • Examples: • -- Dreamworks Pictures went bankrupt and was bought out by Paramount Pictures. Modify all CompanyIDs in the Movies table to reflect this change.UPDATE MoviesSET CompanyID = (SELECT CompanyID FROM Companies WHERE Name = 'Paramount Pictures')WHERE CompanyID = (SELECT CompanyID FROM Companies WHERE Name = 'Dreamworks Pictures');