1 / 22

Chapter 5 Advanced Querying

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 2014. 1. Use of Advanced Queries. Harder questions calling for joining tables Subqueries In, Not In clauses

patricia-arden
Download Presentation

Chapter 5 Advanced Querying

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. University of ManitobaAsper School of Business3500 DBMSBob Travica Chapter 5 Advanced Querying Based on G. Post, DBMS: Designing & Building Business Applications Updated 2014 1

  2. Use of Advanced Queries • Harder questions calling for joining tables • Subqueries • In, Not In clauses • INNER JOIN, LEFT JOIN, RIGHT JOIN • UNION, INTERSECT & other support to Boolean logic, CASE • Download Sally’s Pet Store 2007 (queries work with it)

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

  4. Animal Animal AnimalOrder AnimalID OrderItem SaleAnimal Name OrderID * * * OrderID Category OrderDate * SaleID AnimalID Breed ReceiveDate AnimalID * Breed Cost * DateBorn SupplierID * SalePrice Gender ShippingCost Category Registered EmployeeID * Breed Color Employee ListPrice Supplier Customer EmployeeID Photo SupplierID LastName CustomerID City Sale Name FirstName Phone ContactName Phone FirstName CityID SaleID Phone Address LastName ZipCode SaleDate Address ZipCode * Address City EmployeeID ZipCode * CityID ZipCode State CustomerID * * CityID TaxPayerID CityID AreaCode SalesTax Category * DateHired Population1990 DateReleased Population1980 Category Country Registration SaleItem Latitude * Merchandise Longitude SaleID OrderItem ItemID * ItemID PONumber * Quantity Merchandise * Description ItemID SalePrice Order QuantityOnHand Quantity PONumber ListPrice Cost OrderDate Category ReceiveDate * SupplierID * EmployeeID ShippingCost * Know Thy data! Sally’s Pet Store Schema

  5. Joining Two Tables • Query #11: List LastNames of Customers who bought something between 4/1/2007 and 5/31/2007. Output: 19 rows SELECT DISTINCT Sale.CustomerID, Customer.LastName FROM Customer INNER JOIN Sale ON Customer.CustomerID = Sale.CustomerID WHERE Sale.SaleDate BETWEEN #4/1/2007# AND #5/31/2007# ORDER BY Customer.LastName;

  6. SQL Inner Join Syntax SELECT column(s) FROM table1 INNER JOIN table2 ON table1.columnA = table2.columnA WHERE constraints Foreign Key Primary Key • Inner Join is most frequently used • Matches rows from different tables based on matching values of PKs and FKs

  7. Joining More than Two Tables Query #12: List the Last Name and Phone of customers who bought a registered white cat between 6/1/2007 and 12/31/2007. SELECT Customer.LastName, Customer.Phone FROM Customer INNER JOIN (Sale INNER JOIN (Animal INNER JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID) ON Sale.SaleID = SaleAnimal.SaleID) ON Customer.CustomerID = Sale.CustomerID WHERE ((Animal.Category="Cat") AND (Animal.Registered Is Not Null) AND (Animal.Color Like "*White*") AND (Sale.SaleDate Between #6/1/2007# And #12/31/2007#)); Output: 4 rows See Note!

  8. More on 4-table querying: Query #16 simplified - To avoid complications with parentheses, you can write the same query by suing an older SQL statement: the WHERE line both joins the tables and sets the constraints. SELECT Customer.LastName, Customer.Phone FROM Customer, Sale, SaleAnimal, Animal WHERE Customer.CustomerID=Sale.CustomerID AND Sale.SaleID=SaleAnimal.SaleID AND SaleAnimal.AnimalID=Animal.AnimalID AND Animal.Category="Cat" AND Animal.Registered Is NOT null AND Animal.Color LIKE "*White*" AND Sale.SaleDate BETWEEN #6/1/2007# and #12/31/2007#; Disadvantages: the mix-up in the WHERE line (FKs/PKs and constraints). Still may save time for query writing.

  9. Building a Query • Query #12: List the Last Name and Phone of anyone who bought a registered white cat between 6/1/2007 and 12/31/2007. • Identify the tables involved. • Look at the columns you want to see. • LastName, Phone: Customer • Look at the columns used in the constraints. • Registered, Color, Category: Animal • SaleDate: Sale • Find linking tables. • To link Animal with Sale: SaleAnimal • Select the desired columns (may be computed as well) and test the query. • Enter the constraints.

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

  11. Query #14: Which cats sold for more than the average sale price of cats? Sub-query for Calculation We can find the answer in 2 steps: 1. Find what the average price for cats is ($171.86). SELECT AVG(SalePrice) FROM Animal INNER JOIN SaleAnimal ON Animal.AnimalID=SaleAnimal.AnimalID WHERE Animal.Category=“Cat”; Output: $171.86 2. Use the average price as a constraint in another query: SELECT SaleAnimal.AnimalID, Animal.Category, SaleAnimal.SalePrice FROM Animal INNER JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID WHERE ((Animal.Category="Cat") AND (SaleAnimal.SalePrice>171.86)); Output: 24 rows See Note…

  12. Sub-query for Calculation • A more automated solution that executes in 1 step, sub-query below in brown: SELECT SaleAnimal.AnimalID, Animal.Category, SaleAnimal.SalePrice FROM Animal INNER JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID WHERE ( Animal.Category=‘Cat’ AND SaleAnimal.SalePrice > ( SELECT AVG(SalePrice) FROM Animal INNER JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID WHERE Animal.Category=‘Cat’ ) ); Output: 24 rows

  13. Query: List names of customers who purchased one of the following items: 1, 2, 30, 32, 33. Query Sets (IN) SELECT Customer.LastName, Customer.FirstName, SaleItem.ItemID FROM (Customer INNER JOIN Sale ON Customer.CustomerID = Sale.CustomerID) INNER JOIN SaleItem ON Sale.SaleID = SaleItem.SaleID WHERE SaleItem.ItemID In (1,2,30,32,33) ORDER BY Customer.LastName, Customer.FirstName; Output: 39 rows

  14. Query: List all customers who bought items for cats. Using IN with a Sub-query SELECT Customer.CustomerID, Customer.LastName, Customer.FirstName, SaleItem.ItemID FROM (Customer INNER JOIN Sale ON Customer.CustomerID = Sale.CustomerID) INNER JOIN SaleItem ON Sale.SaleID = SaleItem.SaleID WHERE (SaleItem.ItemIDIn (SELECT ItemID FROM Merchandise WHERE Category="Cat")) ORDER BY Customer.LastNameAsc; 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.CustomerID, Customer.LastName, Customer.FirstName, SaleItem.ItemID, Merchandise.Description FROM Customer, Sale, SaleItem, Merchandise WHERE (Customer.CustomerID = Sale.CustomerID) AND (Sale.SaleID = SaleItem.SaleID) AND (SaleItem.ItemID = Merchandise.ItemID) AND (Merchandise.Category="Cat") ORDER BY Customer.LastName Asc;

  15. Query #16: Which animals have not been sold? Start with list of all animals. Subtract out list of those that were sold. Animal AnimalID SaleAnimal Name * * Category SaleID Breed AnimalID * DateBorn * SalePrice Gender Registered Color ListPrice Photo Sale SubQuery: NOT IN (Subtract) Output: 8 rows SELECT Animal.AnimalID, Animal.Name, Animal.Category FROM Animal WHERE (Animal.AnimalID NOT In (SELECT AnimalID From SaleAnimal));

  16. SQL Mnemonic (again) Someone - SELECT From - FROM Ireland - INNER JOIN Will - WHERE Grow - GROUP BY Horseradish & - HAVING Onions - ORDER BY Stop!

  17. Query #16: “Which animals have not been sold?” with LEFT JOIN Output building: Outer Join: Left Outer Join SELECT * FROM Animal LEFT JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID; LEFT JOIN creates a transient table with: • All rows from left table Animal pasted with matching rows in SaleAnimal • Blank attributes (null values) in SaleAnimal for the rows in Animal that were not macthed.** To get just the non-matched rows, add the WHERE line: SELECT Animal.AnimalID, Animal.Name, Animal.Category FROM Animal LEFT JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID WHERE(SaleAnimal.AnimalID Is Null);

  18. Outer Join: Right Outer Join • Query #17: Which animals have been sold? SELECT Animal.AnimalID, Animal.Name, Animal.Category FROM Animal RIGHT JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID; Actually, in this case equivalent to: SELECT Animal.AnimalID, Name, Category FROM SaleAnimal INNER JOIN Animal ON SaleAnimal.AnimalID=Animal.AnimalID; … because RIGHT JOIN selects all the records from the right table (SaleAnimal) and matching records from the left table (Animal). There can be no blank fields, because Animal provides FK for SaleAnimal—all AnimalIDs in SaleAnimal are matched in Animal.

  19. Types of Join - Summary + Inner Join = + Left Join = Right Join + = Note: RIGHT JOIN can be presented as LEFT JOIN by reversing order of tables.

  20. 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. UNION Operator SELECT EID, Name, Phone, Salary, ‘East’ AS Office FROM EmployeeEast UNION SELECT EID, Name, Phone, Salary, ‘West’ AS Office FROM EmployeeWest EID Name Phone Salary Office 352 Jones 3352 45,000 East 876 Inez 8736 47,000 East 372 Stoiko 7632 38,000 East 890 Smythe 9803 62,000 West 361 Kim 7736 73,000 West

  21. List the name of any employee who has worked for both the East and West regions: SELECT EID, Name FROM EmployeeEast INTERSECT SELECT EID, Name FROM EmployeeWest UNION, INTERSECT, EXCEPT(Boolean Logic) A AC C T1 T2

  22. 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 CASE Function ( SQL Server, Oracle) Select AnimalID, CASE WHEN Date()-DateBorn < 90 Then “Baby” WHEN Date()-DateBorn >= 90 AND Date()-DateBorn < 270 Then “Young” WHEN Date()-DateBorn >= 270 AND Date()-DateBorn < 365 Then “Grown” ELSE “Experienced” END FROM Animal;

More Related