1 / 36

Using Relational Databases and SQL

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. SELECT Clause Subqueries.

jewell
Download Presentation

Using Relational Databases and SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


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

More Related