University of Manitoba Asper School of Business 3500

University of Manitoba Asper School of Business 3500 DBMS Bob Travica Chapter 5 Advanced Querying Based on G. Post, DBMS: Designing & Building Business Applications Updated 2021 1

D B S Y S T E M S Use of Advanced Queries ² Complex query – more tables used (tables’ joins) ² Query with Subquery ² Clauses In, Not In ² INNER JOIN vs. LEFT JOIN, RIGHT JOIN ² UNION, INTERSECT & other Boolean operations on tables ² CASE function ² Download Sally’s Pet Store 2010 (queries work with it) 2 of 22

D B S Y S T E M S Harder Questions (Numbering continues from last lecture) ² 11) List last names of customers who bought something between 4/1/2010 and 5/31/2010. ² 15) Which animals sold for more than the average price of animals in their category? ² 16) Which animals have not been sold? ² 12) List the Last Name and Phone of anyone who bought a registered ² 17) Which animals have been sold? white cat between 6/1/2010 and 12/31/2010. ² 18) Which customers, who bought something at least once, did not buy ² 13) How many cats are “in-stock” on anything between 11/1/2010 and 10/1/2010? 12/31/2010? ² 14) Which cats sold for more than ² 19) Which dog buyer also bought the average price of cats? products for cats (at any time)? 3 of 22

D B Know your data! Sally’s Pet Store Schema (2010) S Y S T E M S 4 of 22

D B S Y S T E M S Joining Two Tables ² Query #11: List alphabetically Last. Names of Customers who bought something between 4/1/2010 and 5/31/2010. SELECT DISTINCT Sale. Customer. ID, Customer. Last. Name FROM Customer INNER JOIN Sale ON Customer. ID = Sale. Customer. ID WHERE Sale. Date BETWEEN #4/1/2010# AND #5/31/2010# ORDER BY Customer. Last. Name; Output: 19 rows This is a 2 tables join. 5 of 22

D B S Y S T E M S SQL Inner Join Syntax SELECT column(s) FROM table 1 Primary Key Foreign Key INNER JOIN table 2 ON table 1. column. A = table 2. column. A WHERE constraints • Inner Join is most frequently used • Selects rows from related tables based on matching PKs and FKs 6 of 22

D B S Y S T E M S Joining More than Two Tables Query #12: List the Last Name and Phone of customers who bought a registered white cat between 6/1/2010 and 12/31/2010. Add the filtering attributes for checking purposes. SELECT Customer. Last. Name, Customer. Phone, Animal. Category, Animal. Color, Animal. Registered, Sale. Date FROM Customer INNER JOIN (Sale INNER JOIN Animal ON Sale. ID=Animal. Sale. ID) ON Customer. ID=Sale. Customer. ID WHERE Animal. Category="Cat" AND Animal. Color LIKE "*White*" AND Animal. Registered IS NOT Null AND Sale. Date Between #6/1/2010# And #12/31/2010# ; Output: Last. Name Phone Category Registered Color Bell (717) 2443484 Cat CFA White 12/22/2010 Hinton (606) 2413182 Cat CFA White 8/16/2010 Hooks (618) 1099250 Cat CFA White/Black 12/3/2010 Kidd (207) 5857583 Cat CFA White 12/9/2010 See Footnote! Sale. Date Check 7 of 22

D B S Y S T E M S Query #16 simplified To avoid complications with parentheses, you can write the same query by suing an older SQL syntax: the WHERE line joins the tables in addition to setting the constraints. SELECT Customer. Last. Name, Customer. Phone, Animal. Category, Animal. Color, Animal. Registered, Sale. Date FROM Customer, Sale, Animal WHERE Sale. ID=Animal. Sale. ID AND Customer. ID=Sale. Customer. ID AND Animal. Category="Cat" AND Animal. Color LIKE "*White*" AND Animal. Registered IS NOT Null AND Sale. Date Between #6/1/2010# And #12/31/2010# ; Disadvantages: the mix-up in the WHERE line (FKs/PKs and constraints); errors are costly. * Still may save time for query writing when used carefully. 8 of 22

D B S Y S T E M S Building a Query ² Query #12: List the Last Name and Phone of anyone who bought a registered white cat between 6/1/2010 and 12/31/2010. ² Identify the columns sought (the SELECT line) ª Look at the columns you want to see. © Last. Name, Phone: Customer ª Look at the columns used in the constraints. © Registered, Color, Category: Animal © Sale. Date: Sale ª Columns may be computed as well ² Identify names of the tables involved (the FROM line). ² Determine the tables’ joins (look for PK-FK links). ² Write the constraints (the WHERE line). 9 of 22

D B S Y S T E M S SQL Mnemonic (Remember!) Someone SELECT From FROM Ireland (Italy, India…) INNER JOIN Will WHERE Grow GROUP BY Horseradish & HAVING Onions ORDER BY SQL is picky about putting the commands in the proper sequence. For memorizing the sequence, use this mnemonic. 10 of 22

D B S Y S T E M S Levels of automation: Sub-query 1/2 ² Query #14: List the cats that drew a donation bigger than the average. We can find the answer in 2 steps: 1. Find what the average price for cats is ($171. 82). SELECT Category, AVG(Donation) As [Average Donation] FROM Animal WHERE Category="Cat“ Output: GROUP BY Category ; Average Donation Category Cat $171. 82 2. Use the average price as a constraint in another query: SELECT Animal. ID, Animal. Category, Animal. Breed, Animal. Donation FROM Animal WHERE (Animal. Category="Cat") AND (Animal. Donation>171. 82); See Note… 11 of 22

D B S Y S T E M S Levels of automation: Sub-query 2/2 ² A more automated solution that executes in 1 step. Uses query 2 on top and query 1 in the sub-query. SELECT Animal. ID, Animal. Category, Animal. Breed, Animal. Donation FROM Animal WHERE ( (Animal. Category="Cat") AND ( Animal. Donation > (SELECT AVG(Donation) FROM Animal WHERE Animal. Category="Cat") ) ) ORDER BY Animal. Donation; Output: 23 rows 12 of 22

D B S Y S T E M S Querying Set of Rows with operator IN ² Query: List names of customers who purchased one of the following items: 1, 2, 30, 32, 33. SELECT Customer. Last. Name, Customer. First. Name, Sale. Item. ID FROM (Customer INNER JOIN Sale ON Customer. ID = Sale. Customer. ID) INNER JOIN Sale. Item ON Sale. ID = Sale. Item. Sale. ID WHERE Sale. Item. ID In (1, 2, 30, 32, 33) ORDER BY Customer. Last. Name, Customer. First. Name; Output: 39 rows 13 of 22

D B S Y S T E M S Using IN with a Sub-query ² Query: List all customers who bought items for cats. SELECT Customer. ID, Customer. Last. Name, Customer. First. Name, Sale. Item. ID FROM (Customer INNER JOIN Sale ON Customer. ID = Sale. Customer. ID) INNER JOIN Sale. Item ON Sale. ID = Sale. Item. Sale. ID WHERE (Sale. Item. ID In (SELECT Merchandise. Item. ID FROM Merchandise WHERE Category="Cat") ) ORDER BY Customer. Last. Name Asc; Note: 3 tables joined and 4 tables used. Output: 79 rows. • An alternative simplified using older SQL syntax and 4 tables to show description of merchandise: SELECT Customer. ID, Customer. Last. Name, Customer. First. Name, Sale. Item. ID, Merchandise. Description FROM Customer, Sale. Item, Merchandise WHERE (Customer. ID = Sale. Customer. ID) AND (Sale. ID = Sale. Item. Sale. ID) AND (Sale. Item. ID = Merchandise. Item. ID) AND (Merchandise. Category="Cat") ORDER BY Customer. Last. Name Asc; 14 of 22

D B S Y S T E M S Sub. Query: NOT IN (Subtract) ² Query #16: Which animals have not been sold (adopted)? ª Start with list of all animals. ª Select those whose IDs are not is sales records. Output: 10 rows Animal. ID SELECT Animal. ID, Animal. Name, Animal. Category, Animal. Sale. ID FROM Animal WHERE Animal. ID NOT IN (SELECT Animal. ID FROM Animal INNER JOIN Sale ON Animal. Sale. ID=Sale. ID); Name Category Fish 4 Simon Dog 12 Leisha Dog 19 Gene Dog 25 Vivian Dog 34 Rhonda Dog 88 Brandy Dog 175 Donald Cat 2 181 Fish 201 Mammal Sale. ID See Note for optional query. 15 of 22

D B S Y S T E M S SQL Mnemonic (again) Someone - SELECT From - FROM Ireland - INNER JOIN Will - WHERE Grow - GROUP BY Horseradish & - HAVING Onions - ORDER BY 16 of 22

D B S Y S T E M S Outer Join: Left Outer Join ² Query #16: “Which animals have not been sold? ” (Note: Table Animal ² is an inventory containing current and historic data; when an animal is sold, it is not deleted from the table. ) SELECT * FROM Animal LEFT JOIN Sale ON Animal. Sale. ID = Sale. ID; Output: 10 rows, Animal. IDs: 2, 4, 12, 19, 25, 34, 88, 175, 181, 201. LEFT JOIN creates a transient (temporary, only in RAM) table with: ªAll rows from left table Animal pasted with matching rows in table Sale AND ªRows from Animal with blanks (null values) for FK Sale. ID. These indicate animals not yet sold. To get just the non-matched rows, add the WHERE line write the query below. This is also a check for the query above. SELECT * FROM Animal LEFT JOIN Sale ON Animal. Sale. ID = Sale. ID WHERE Animal. Sale. ID Is Null ; 17 of 22

D B Outer Join: Right Outer Join ² Query #17: Which animals have been sold? S Y S T E M S SELECT * FROM Animal RIGHT JOIN Sale ON Animal. Sale. ID = Sale. ID; The output is similar to the inner join query: SELECT * FROM Sale INNER JOIN Animal ON Sale. Animal. ID=Animal. ID; 18 of 22

D B S Y S T E M S Types of Join - Summary Inner Join + Left Join + Right Join + = = = Note: RIGHT JOIN can be presented as LEFT JOIN by reversing order of tables. 19 of 22

D B S Y S T E M S UNION Operator SELECT EID, Name, Phone, Salary, ‘East’ AS Office FROM Employee. East UNION SELECT EID, Name, Phone, Salary, ‘West’ AS Office FROM Employee. West ² ² EID 352 876 372 Name Jones Inez Stoiko Phone 3352 8736 7632 Salary 45, 000 47, 000 38, 000 Office East 890 361 Smythe Kim 9803 7736 62, 000 73, 000 West Offices in Los Angeles and New York. Each has an Employee table (East and West). Need to search data from both tables. Columns in the two SELECT lines must match. 20 of 22

D B S Y S T E M S UNION, INTERSECT, EXCEPT (Boolean Logic) A AC T 1 C T 2 List the name of any employee who has worked for both the East and West regions: SELECT EID, Name FROM Employee. East INTERSECT SELECT EID, Name FROM Employee. West 21 of 22

D B S Y S T E M S CASE Function ( SQL Server, Oracle) Select Animal. ID, CASE WHEN Date()-Date. Born < 90 Then “Baby” WHEN Date()-Date. Born >= 90 AND Date()-Date. Born < 270 Then “Young” WHEN Date()-Date. Born >= 270 AND Date()-Date. Born < 365 Then “Grown” ELSE “Experienced” END FROM Animal; ² Used for grouping objects, complex decisions. ² Example: Define age categories for the animals. ª Less than 3 months ª Between 3 months and 9 months ª Between 9 months and 1 year ª Over 1 year 22 of 22