CSE 103 Students Review INNER and OUTER JOINs

CSE 103 Students: Review INNER and OUTER JOINs, Subqueries. Others: Please save your work and log out by 10: 10. http: //www. cse. msu. edu/~cse 103 U: /msu/course/cse/103 Day 13, Slide

Day 12 Review What kinds of questions can we solve with subqueries we couldn't otherwise? What kinds of questions can we solve with self-JOINs? How can we use the same table more than once in the same query? http: //www. cse. msu. edu/~cse 103 U: /msu/course/cse/103 Day 13, Slide

Nested SELECTs in My. SQL does not (currently) allow views (saved queries) In effect, using a saved query is the same as embedding that entire SELECT statement in the parent (calling) query All RDBMS support this idea of using nested SELECTs (sometimes called subqueries) http: //www. cse. msu. edu/~cse 103 U: /msu/course/cse/103 Day 13, Slide

Nested SELECT example We can use nested queries as an alternate to JOINs: – SELECT Birth. Date FROM tbl_Births WHERE Person. ID = (SELECT Person. ID FROM tbl_People WHERE First. Name = "Charlton" AND Last. Name = "Heston") – Same as: SELECT Birth. Date FROM tbl_Births AS b INNER JOIN tbl_People AS p ON b. Person. ID = p. Person. ID WHERE First. Name = "Charlton" AND Last. Name = "Heston" Subqueries must be completely enclosed in parantheses () http: //www. cse. msu. edu/~cse 103 U: /msu/course/cse/103 Day 13, Slide

More on nested queries If the subquery returns exactly one row and one field, you can test with =, or >, or <, etc. (for example finding people of "above average height") If the subquery returns multiple rows and/or columns, it is best to use a virtual table and a JOIN (more on this later) http: //www. cse. msu. edu/~cse 103 U: /msu/course/cse/103 Day 13, Slide

Basic nested queries exercises Use nested queries to find these answers: – Find all people who have above average height [8946] – Find the name of the person or people who have the earliest birthday in 1970 [6] – Find all movies made in the same year as "Harold and Maude" [4106] http: //www. cse. msu. edu/~cse 103 U: /msu/course/cse/103 Day 13, Slide

Subqueries to make virtual tables: Using SELECT in the FROM clause Recall: music DB - we found albums with total runtimes greater than average – First had to find total runtimes, then average – Used stored queries We need a calculation based on another calculation, so we nest queries We have to SELECT … FROM a virtual table we create with a subquery (see next) http: //www. cse. msu. edu/~cse 103 U: /msu/course/cse/103 Day 13, Slide

Nested queries in FROM example Problem: find people who are older than the average of all those in our db – Have to find ages, then average ages, then… – SELECT First. Name, Last. Name FROM tbl_People AS p INNER JOIN tbl_Births AS b ON p. Person. ID = b. Person. ID WHERE (YEAR(NOW()) - YEAR(Birth. Date)) > (SELECT AVG(Age) FROM (SELECT YEAR(NOW()) - YEAR(Birth. Date) AS Age FROM tbl_Births) AS vr_tbl_1 ) http: //www. cse. msu. edu/~cse 103 U: /msu/course/cse/103 Day 13, Slide

Nested queries in FROM clause Example analysis Yellow text (outermost query): selects final fields for display and restriction: ages have to be greater than average – Don't know average yet; write subqueries to find it and compare to that – Could test with say, > 50 to verify syntax Blue text (1 st subquery): selects average for comparison w/outer query – It returns AVG(Age) but we have no field called Age – AVG() only works on a field; not on something like an entire table (even if the table has only one field, we have to specify the field) – Its FROM line has a subquery… Green text (second subquery): selects everyone's Ages – We create calculated field called Age for use by outer query – In a FROM line, the subquery returns a (virtual) data table to be used – All tables in a FROM line need a name… Red text: Provides the virtual table with a name, just like any alias – The alias goes outside the () around the subquery – Alias name only matters if you would need to JOIN, etc. with it http: //www. cse. msu. edu/~cse 103 U: /msu/course/cse/103 Day 13, Slide

Nicolas Cage Example Who has costarred with Mr. Cage? – Step 1: Find Cage's Actor. ID – Step 2: Use that Actor. ID to find the Movie. IDs he's been in – Step 3: Use those Movie. IDs to find the Actor. IDs in those movies – Step 4: Use those Actor. IDs to find the names of the other actors Can solve using self-JOINs and/or subqueries – Mix & match as you like; Some solutions will be faster SELF-JOINs generally are very slow – Use subqueries instead of repeating a table, where possible http: //www. cse. msu. edu/~cse 103 U: /msu/course/cse/103 Day 13, Slide

Nicolas Cage, continued One possible solution (there are many): – SELECT p 1. First. Name, p 1. Last. Name FROM ((tbl_People AS p 1 INNER JOIN tbl_Roles AS r 1 ON p 1. Person. ID = r 1. Actor. ID) INNER JOIN (SELECT Actor. ID, Movie. ID FROM tbl_Roles AS r 2 INNER JOIN tbl_People AS p 2 ON r 2. Actor. ID = p 2. Person. ID WHERE p 2. First. Name = 'Nicolas' AND p 2. Last. Name = 'Cage') AS vr_tbl_1 ON r 1. Movie. ID = vr_tbl_1. Movie. ID ORDER BY Last. Name, First. Name Blue text: subquery to find NC's Movies – See the use of INNER JOIN with the virtual table produced by the subquery (red text) For further thought: without restricting another firstname/lastname, make Nicolas Cage not star with himself. http: //www. cse. msu. edu/~cse 103 U: /msu/course/cse/103 Day 13, Slide

Nested queries in FROM exercise Find all the genres which had a higherthan-average number of movies made in 2003 (compared only to the average per genre for 2003 movies). Show the Genre. Name and that Number. – E. g. , if the average Genre in '03 has 300 movies made, and Action has 500 while Comedy has 200, your query should return Action, 500 http: //www. cse. msu. edu/~cse 103 U: /msu/course/cse/103 Day 13, Slide

Upcoming Bridge Task Everyone should come; get next BT you haven't passed – This is 5 th opportunity out of 12 – If you take & pass the 1. 5, you have 7 more attempts to pass 3 BTs, but if you fail the 1. 5, you have only 7 attempts for 4 BTs (less than 2 tries each) Review text, Web and homework – Get help from helprooms (best if you missed entire days), office hours (good for specific questions), email (specific questions after hours) If taking 2. 0, must have C&U database from passed 1. 5 Bring any books, notes, back-up disk, etc. & a photo ID Last BT opportunity before Spring Break! http: //www. cse. msu. edu/~cse 103 U: /msu/course/cse/103 Day 13, Slide

Bridge Task 2. 0 Covers materials primarily from Days 10 -13 – Advanced queries of all sorts – Functions (Day 10), Aggregates (Day 11), Stored queries/views (Day 12), [Nested] Subqueries (Day 13) – Also be sure you understand INNER/OUTER JOIN and Calculated fields, as many advanced problems rely on those more basic concepts Review notes, homework, classwork – Notes are on the Web pages http: //www. cse. msu. edu/~cse 103 U: /msu/course/cse/103 Day 13, Slide