Database Management Systems

1. Database Management Systems Chapter 5 Advanced Queries

2. Objectives • How can SQL be used to answer more complex questions? • Why are some business questions more difficult than others? • How do you find something that did not happen? • How is a subquery used for IN and NOT IN conditions? • What are the common uses for subqueries? • What are correlated subqueries? • What tricky problems arise and how do you handle them in SQL? • What are the SQL data definition commands? • What SQL commands alter the data stored in tables? • How do you know if your query is correct?

3. Tables

4. Organization • Harder Questions • Not In, LEFT JOIN • Subqueries • UNION, Multiple JOIN columns, Recursive JOIN • Other SQL Commands • DDL: Data Definition Language • DML: Data Manipulation Language • OLAP • Microsoft SQL Server • Oracle • Microsoft Access Crosstab

5. Harder Questions Which items have not been sold? Which items were not sold in July 2013? Which cat merchandise sold for more than the average sale price of cat merchandise? Compute the merchandise sales by category in terms of percentage of total sales. List all of the customers who bought something in March and who bought something in May. (Two tests on the same data!). List dog merchandise with a list price greater than the sale price of the cheapest cat product. Has one salesperson made all of the sales on a particular day? Use Not Exists to list customers who have not bought anything. Which merchandise has a list price greater than the average sale price of merchandise within that category? List all the managers and their direct reports. Convert age ranges into categories. Classify payments by number of days late. Which employees sold merchandise from every category? List customers who adopted dogs and also bought cat products.

6. LEFT JOIN Problem Which merchandise items have not been sold? INNER JOIN is a filter that returns ONLY rows that exist in both tables. But SaleItem includes ONLY merchandise that HAS been sold. SaleItem Merchandise SaleIDItemID 4 1 4 36 6 20 6 21 7 5 7 19 7 40 8 11 8 16 8 36 10 23 10 25 10 26 10 27 ItemID Description 1 Dog Kennel-Small 2 Dog Kennel-Medium 3 Dog Kennel-Large 4 Dog Kennel-Extra Large 5 Cat Bed-Small 6 Cat Bed-Medium 7 Dog Toy 8 Cat Toy 9 Dog Food-Dry-10 pound 10 Dog Food-Dry-25 pound 11 Dog Food-Dry-50 pound 12 Cat Food-Dry-5 pound 13 Cat Food-Dry-10 pound 14 Cat Food-Dry-25 pound 15 Dog Food-Can-Regular

7. LEFT JOIN Answer Query05_Fig03 Which merchandise items have not been sold? LEFT JOIN includes ALL rows from the table on the SQL left side and matching rows from the right-side table. RIGHT JOIN is similar. SaleItem Merchandise SaleIDItemID 4 1 4 36 6 20 6 21 7 5 7 19 7 40 ItemID Description 1 Dog Kennel-Small 10 Dog Food-Dry-25 pound 11 Dog Food-Dry-50 pound 12 Cat Food-Dry-5 pound 13 Cat Food-Dry-10 pound 14 Cat Food-Dry-25 pound 15 Dog Food-Can-Regular SELECT Merchandise.ItemID, Merchandise.Description, SaleItem.SaleID FROM Merchandise LEFT JOIN SaleItem ON Merchandise.ItemID = SaleItem.ItemID WHERE SaleItem.SaleIDIs Null;

8. LEFT JOIN Query Note: LEFT/RIGHT depends on the SQL statement (Merchandise LEFT JOIN SaleItem). It is NOT set by the order of the tables in the display.

9. LEFT JOIN Result

10. LEFT JOIN: Old Syntax SELECT * (SQL Server) FROM Merchandise, SaleItem WHERE Merchandise.ItemID*= SaleItemID.ItemID And SaleItem.SaleID Is Null SELECT * (Oracle) FROM Merchandise, SaleItem WHERE Merchandise.ItemID = SaleItemID.ItemID(+) And SaleItem.SaleID Is Null Note that Oracle’s plus sign is on the opposite side from what you would expect. You should not use this syntax for new queries. It is hard to read. But you will likely encounter older queries in your work that use this syntax, so you need to recognize it and understand it is a LEFT join.

11. IN Function Query05_Fig06a Query05_Fig06b SELECT * FROM Customer WHERE FirstName=N’Tim’ Or FirstName=N’David’ Or FirstName=N’Dale’; SELECT * FROM Customer WHERE FirstNameIN(N’Tim’, N’David’, N’Dale’); The IN function compares a column to a set of values. IN is easier to write. Items are joined with an “Or” condition.

12. Query Sets (IN) Query05_Fig07a SELECT * FROM Merchandise WHERE ItemID IN (1,2,3,4,5,6,7,8,9,10,11,14,15); Merchandise ItemID Description QuantityOnHand ListPrice List all merchandise with the ItemIDs of (1,2,3,4,5,6,7,8,9,10,11,14,15).

13. IN Condition as a JOIN Query05_Fig07b SELECT * FROM Merchandise WHERE ItemIDIN (SELECT ItemIDFROM SaleItem); Match ItemID values in the Merchandise table to those that were sold or listed in the SaleItem table.

14. NOT IN: Things that did not happen Query05_Fig08 Which merchandise items have not been sold? Merchandise SELECT * FROM Merchandise WHERE ItemID NOT IN (SELECT ItemIDFROM SaleItem); ItemID Description 1 Dog Kennel-Small 2 Dog Kennel-Medium 3 Dog Kennel-Large 4 Dog Kennel-Extra Large 5 Cat Bed-Small 6 Cat Bed-Medium 7 Dog Toy 8 Cat Toy 9 Dog Food-Dry-10 pound 10 Dog Food-Dry-25 pound 11 Dog Food-Dry-50 pound 12 Cat Food-Dry-5 pound 13 Cat Food-Dry-10 pound 14 Cat Food-Dry-25 pound 15 Dog Food-Can-Regular Think of taking the main list (Merchandise) and subtracting the items from the second list (SaleItem). Then display the ones that are left.

15. Not Sold Conditions (Date Subquery) Query05_Fig09 Which items were not sold in July 2013? SELECT * FROM Merchandise WHERE ItemID IN (SELECT ItemID FROM SaleItem INNER JOIN Sale ON Sale.SaleID=SaleItem.SaleID WHERE SaleDate BETWEEN ’01-JUL-2013’ AND ’31-JUL-2013’ );

16. Not Sold Conditions (Date LEFT JOIN--bad) Query05_Fig10 Which items were not sold in July 2013? SELECT Merchandise.* FROM Sale INNER JOIN (Merchandise LEFT JOIN SaleItem ON Merchandise.ItemID = SaleItem.ItemID) ON Sale.SaleID = SaleItem.SaleID WHERE SaleDate BETWEEN ’01-JUL-2013’ AND ’31-JUL-2013’; Probably will not run and might not return desired results. To work, the query must filter the SaleItem rows FIRST, Then apply the LEFT JOIN.

17. Not Sold Conditions (Date LEFT JOIN--good) JulyItems and Query05_Fig11 Which items were not sold in July 2013? CREATE VIEW JulyItemsAS SELECT Sale.SaleID, ItemID FROM Sale INNER JOIN SaleItem ON Sale.SaleID=SaleItem.SaleID WHERE SaleDate BETWEEN ’01-JUL-2013’ AND ’31-JUL-2013’; SELECT Merchandise.* FROM Merchandise LEFT JOIN JulyItems ON Merchandise.ItemID=JulyItems.ItemID WHERE JulyItems.Sale Is Null; The saved view forces the selection of July sale items to occur first. Then the LEFT JOIN applies those rows to the Merchandise table.

18. Subquery: Calculations 1 Query05_Fig12a Which cat merchandise sold for more than the average sale price of cat merchandise? SELECT Merchandise.ItemID, Merchandise.Description, Merchandise.Category, SaleItem.SalePrice FROM Merchandise INNER JOIN SaleItem ON Merchandise.ItemID = SaleItem.ItemID WHERE Merchandise.Category=N’Cat’ AND SaleItem.SalePrice > 9; If you know (guess) that the average price of cat merchandise is 9; then the query is easy. So write the easy part first.

19. Subquery: Calculations 2 Query05_Fig12b Which cat merchandise sold for more than the average sale price of cat merchandise? SELECT Merchandise.ItemID, Merchandise.Description, Merchandise.Category, SaleItem.SalePrice FROM Merchandise INNER JOIN SaleItem ON Merchandise.ItemID = SaleItem.ItemID WHERE Merchandise.Category=N’Cat’ AND SaleItem.SalePrice > 9; SELECT Merchandise.ItemID, Merchandise.Description, Merchandise.Category, SaleItem.SalePrice FROM Merchandise INNER JOIN SaleItem ON Merchandise.ItemID = SaleItem.ItemID WHERE Merchandise.Category=N’Cat’ AND SaleItem.SalePrice > (SELECT Avg(SaleItem.SalePrice) AS AvgOfSalePrice FROM Merchandise INNER JOIN SaleItem ON Merchandise.ItemID = SaleItem.ItemID WHERE Merchandise.Category=N’Cat’) ;

20. Subquery Calculation Notes When building subqueries that use calculations: Write the query using a simple number. Write the subquery separately to compute the desired number. Test it! Isolate the original estimated number in the first query by putting it on a separate line. Delete the number and add parentheses ( ). Paste the subquery inside the parentheses.

21. Subquery for Percentages Query05_Fig13 Compute the merchandise sales by category in terms of percentage of total sales. SELECT Merchandise.Category, Sum([Quantity]*[SalePrice]) AS [Value], Sum([Quantity]*[SalePrice])/(SELECT Sum([Quantity]*[SalePrice]) FROM SaleItem) As [Pct] FROM Merchandise INNER JOIN SaleItem ON Merchandise.ItemID = SaleItem.ItemID GROUP BY Merchandise.Category; Category Value Percentage Bird $631.50 7.45063292035315E-02 Cat $1,293.30 0.152587546411603 Dog $4,863.49 0.573809638983505 Fish $1,597.50 0.188478006179955 Mammal $90.00 1.06184792214059E-02

22. Percentages with JOIN using Views Query05_Fig14 CREATE VIEW CategorySubtotalsAS SELECT Category, Sum(Quantity*SalePrice) AS Value FROM Merchandise INNER JOIN SaleItem ON Merchandise.ItemID = SaleItem.ItemID GROUP BY Merchandise.Category; CREATE VIEW TotalItemSales AS SELECT Sum(Value) AS MainTotal FROM CategorySubtotals; SELECT Category, Value, Value/MainTotal AS Percentage FROM CategorySubtotals, TotalItemSales; Save the first view that computes subtotals. Create a second view to compute totals. Compute the percentages in a third query using a cross join.

23. Sets of Data Query05_Fig15 List all of the customers who bought something in March and in May. Try answering this question the “easy” but wrong way. SELECT Customer.CustomerID, Customer.Phone, Customer.LastName, Sale.SaleDate FROM Customer INNER JOIN Sale ON Customer.CustomerID = Sale.CustomerID WHERE Sale.SaleDate Between ’01-MAR-2013’ And ’31-MAR-2013’ AND Sale.SaleDate Between ’01-MAY-2013’ And ’31-MAY-2013’; The WHERE clause checks the date on each row to see if it fall in March AND in May. But no date can be in two months!

24. Two Sets using Subquery Query05_Fig16 List all of the customers who bought something in March and in May. SELECT Customer.LastName, Customer.FirstName FROM Customer INNER JOIN Sale ON Customer.CustomerID = Sale.CustomerID WHERE (SaleDate Between ’01-MAR-2013’ And ‘31-MAR-2013’) AND Customer.CustomerID IN (SELECT CustomerID FROM Sale WHERE (SaleDate Between ‘01-MAY-2013’ And ’31-MAY-2013’) ); Customers in March and customers in May are two separate sets of data. Two separate SELECT statements are needed to answer the question. This query combines those sets using a subquery.

25. Two Sets of Data Using JOIN CREATE VIEW MarchCustomers AS SELECT CustomerID FROM Sale WHERE (SaleDate Between ‘01-MAR-2013’ And ‘31-MAR-2013’); CREATE VIEW MayCustomers AS SELECT CustomerID FROM Sale WHERE (SaleDate Between ‘1-MAY-2013’ And ‘31-MAY-2013’); SELECT Customer.LastName, Customer.FirstName FROM Customer INNER JOIN MarchCustomers ON Customer.CustomerID=MarchCustomers.CustomerID INNER JOIN MayCustomers ON MarchCustomers.CustomerID=MayCustomers.CustomerID;

26. Subquery: Any Query05_Fig18 List dog merchandise with a list price greater than the sale price of the cheapest cat product. SELECT Merchandise.ItemID, Merchandise.Description, Merchandise.Category, Merchandise.ListPrice FROM Merchandise WHERE Category=N'Dog‘ AND ListPrice > ANY (SELECT SalePrice FROM Merchandise INNER JOIN SaleItem ON Merchandise.ItemID=SaleItem.ItemID WHERE Merchandise.Category=N'Cat') ; Any: value is compared to each item in the list. If it is True for any of the items, the statement is evaluated to True. Probably easier to use MIN function in the subquery. All: value is compared to each item in the list. If it is True for every item in the list, the statement is evaluated to True (much more restrictive than any.

27. Subquery: All Query05_Fig19 Has one salesperson made all of the sales on a particular day (Mar 28)? SELECT Employee.EmployeeID, Employee.LastName FROM Employee WHERE EmployeeID= ALL (SELECT EmployeeID FROM Sale WHERE SaleDate = #28-MAR-2013#) ; ID LastName 2 Gibson Returns a match only if the employee made all of the sales on the specified date. (Or if there were no sales—all null values—on that date.)

28. Subquery: Exists Query05_Fig20 Use Not Exists to list customers who have not bought anything. SELECT Customer.CustomerID, Customer.Phone, Customer.LastName FROM Customer WHERE NOT EXISTS (SELECT SaleID, SaleDate FROM Sale WHERE Sale.CustomerID=Customer.CustomerID); EXISTS tests for the existence of rows in the subquery. The subquery can contain multiple columns because none of the returned values matter—only whether any values match the WHERE clause. This example is better if you use a JOIN command, but it works and illustrates the Exists term.

29. Correlated Subquery Query05_Fig21 Which merchandise has a list price greater than the average sale price of merchandise within that category? SELECT Merchandise1.ItemID, Merchandise1.Description, Merchandise1.Category, Merchandise1.ListPrice FROM Merchandise AS Merchandise1 WHERE Merchandise1.ListPrice> ( SELECT Avg(SaleItem.SalePrice) AS AvgOfSalePrice FROM Merchandise As Merchandise2 INNER JOIN SaleItem ON Merchandise2.ItemID = SaleItem.ItemID WHERE Merchandise2.Category=Merchandise1.Category ); The WHERE clause in the subquery depends on values in the outer query. The tables require aliases to tell them apart.

30. Correlated Subquery Potential Problem Assume small query 100,000 rows 5 categories of 20,000 rows 100,000 * 20,000 = 1 billion rows to read! Merchandise MerchID Category ListPrice Compute Avg: $23.32 1 Dog$45.00 2 Dog$65.00 3 Dog$85.00 4 Dog$110.00 5 Cat$25.00 6 Cat$35.00 7 Dog$4.00 8 Cat$3.00 9 Dog$7.50 Compute Avg: $23.32 Compute Avg: $23.32 Compute Avg: $23.32 Compute Avg: $8.99 Recompute average for every row in the main query!

31. More Efficient Solution: 2 queries Compute the averages once and save query JOIN saved query to main query Two passes through table: 1 billion / 200,000 => 10,000 Query05_Fig23a Merchandise Saved Query MerchID Category ListPrice Category AvgOfSalePrice 1 Dog$45.00 2 Dog$65.00 3 Dog$85.00 4 Dog$110.00 5 Cat$25.00 6 Cat$35.00 7 Dog$4.00 8 Cat$3.00 9 Dog$7.50 Bird $37.60 Cat $8.99 Dog $23.32 Fish $38.18 Mammal $9.00 JOIN Merchandise.Category= Query05_Fig23a.Category

32. Uncorrelated Queries Query05_Fig23b SELECT Merchandise.Category, Avg(SaleItem.SalePrice) AS AvgOfSalePrice FROM Merchandise INNER JOIN SaleItem ON Merchandise.ItemID = SaleItem.ItemID GROUP BY Merchandise.Category; SELECT Merchandise.ItemID, Merchandise.Category, Merchandise.ListPrice, Query05_Fig23a.AvgOfSalePrice FROM Query05_Fig23a INNER JOIN Merchandise ON Query05_Fig23a.Category = Merchandise.Category WHERE Merchandise.ListPrice>[Query05_Fig23a].[AvgOfSalePrice];

33. 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 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.

34. UNION, INTERSECT, EXCEPT List the name of any employee who has worked for both the East and West regions. A B C SELECT EID, Name FROM EmployeeEast INTERSECT SELECT EID, Name FROM EmployeeWest T1 T2 Microsoft Access supports only UNION. SQL Server supports all three.

35. Multiple JOIN Columns Query05_Fig26 Animal AnimalID Name Category Breed DateBorn Gender . . . Breed Category Breed SELECT * FROM Breed INNER JOIN Animal ON Breed.Category = Animal.Category AND Breed.Breed = Animal.Breed Sometimes need to JOIN tables on more than one column. PetStore: Category and Breed.

36. Reflexive Join Query05_Fig20 Employee EID Name . . . Manager 1Reeves11 2Gibson1 3Reasoner1 4Hopkins3 SQL SELECT Employee.EmployeeID, Employee.LastName, Employee.ManagerID, E2.LastName FROM Employee INNER JOIN Employee AS E2 ON Employee.ManagerID = E2.EmployeeID Result EID Name Manager Name 1Reeves11Smith 2Gibson 1Reeves 3Reasoner 1Reeves Need to connect a table to itself. Common example: Employee(EID, LastName, . . ., ManagerID) A manager is also an employee. Use a second copy of the table and an alias.

37. Recursive Joins (SQL 99 and 2003) Not available in Microsoft Access. Variations are in SQL Server and Oracle. WITH RECURSIVE EmployeeList (EmployeeID, Title, Salary) AS ( SELECT EmployeeID, Title, 0.00 FROM Manages WHERE Title = N‘CEO’ -- starting level UNION ALL SELECT Manages.EmployeeID, Manages.Title, Manages.Salary FROM EmployeeList INNER JOIN Manages ON EmployeeList.EmployeeID = Manages.ManagerID ) SELECT EmployeeID, Count(Title), Sum(Salary) FROM EmployeeList GROUP BY EmployeEID ; List all of the managers and list everyone who reports to them. Available in higher-end systems (SQL Server, Oracle, DB2, etc.), but each vendor uses a different, proprietary syntax. See the Workbooks. It provides tree spanning capabilities.

38. Recursive JOIN: SQL Server WITH DirectReports(EmployeeID, LastName, ManagerID, Title, Level) AS ( --Root/anchor member (find employee with no manager) SELECT EmployeeID, LastName, ManagerID, Title, 0 As Level FROM Employee WHERE ManagerID=0 -- starting level UNION ALL -- Recursive members SELECT Employee.EmployeeID, Employee.LastName, Employee.ManagerID, Employee.Title, Level +1 FROM Employee INNER JOIN DirectReports ON Employee.ManagerID = DirectReports.EmployeeID ) -- Now execute the common table expression SELECT ManagerID, EmployeeID, LastName, Title, Level FROM DirectReports ORDER BY Level, ManagerID, LastName

39. Recursive Query Results

40. CASE Function Not available in Microsoft Access. It is in SQL Server and Oracle. Convert age ranges into categories. 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; 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

41. Inequality Join Classify payments by number of days late. AR(TransactionID, CustomerID, Amount, DateDue) LateCategory(Category, MinDays, MaxDays, Charge, …) Month 30 90 3% Quarter 90 120 5% Overdue 120 9999 10% SELECT * FROM AR INNER JOIN LateCategory ON ((Date() - AR.DateDue) >= LateCategory.MinDays) AND ((Date() - AR.DateDue) < LateCategory.MaxDays) AccountsReceivable Categorize by Days Late 30, 90, 120+ Three queries? New table for business rules

42. Queries with “Every” Need EXISTS List the employees who have sold animals from every category. By hand: List the employees and the categories. Go through the SaleAnimal list and check off the animals they have sold.

43. Query With EXISTS Query05_Fig31 List the Animal categories where merchandise has notbeen sold by an employee (#5). SELECT Category FROM Category WHERE (Category <> 'Other') And Category NOT IN (SELECT Merchandise.Category FROM Merchandise INNER JOIN (Sale INNER JOIN SaleItem ON Sale.SaleID = SaleItem.SaleID) ON Merchandise.ItemID = SaleItem.ItemID WHERE Sale.EmployeeID = 5) If this query returns any rows, then the employee has not sold every animal. So list all the employees for whom the above query returns no rows: SELECT EmployeeID, LastName FROM Employee WHERE NOT EXISTS (above query slightly modified.)

44. Query for Every Query05_Fig32 Which employees have merchandise sales from every category? SELECT Employee.EmployeeID, Employee.LastName FROM Employee WHERE Not Exists (SELECT Category FROM Category WHERE (Category NOT IN (‘Other’, ‘Reptile’, ‘Spider’) And Category NOT IN (SELECT Merchandise.Category FROM Merchandise INNER JOIN (Sale INNER JOIN SaleItem ON Sale.SaleID = SaleItem.SaleID) ON Merchandise.ItemID = SaleItem.ItemID WHERE Sale.EmployeeID = Employee.EmployeeID) ); EmployeeIDLastName 2 Gibson 3 Reasoner 5 James 7 Farris

45. Simpler Query for Every Query05_Fig33 Sometimes it is easier to use Crosstab and the Count function. But some systems do not have Crosstab, and sometimes the lists would be too long. So you need to know both techniques.

46. SQL SELECT SELECT DISTINCT Table.Column {AS alias} , . . . FROM Table/Query {INNER or LEFT} JOIN Table/Query ON T1.ColA = T2.ColB WHERE (condition) GROUP BY Column HAVING (group condition) ORDER BY Table.Column { Union second select }

47. SQL Mnemonic Someone From Ireland Will Grow Horseradish and Onions SELECT FROM INNER JOIN WHERE GROUP BY HAVING ORDER BY SQL is picky about putting the commands in the proper sequence. If you have to memorize the sequence, this mnemonic may be helpful.

48. SQL Data Definition • Create Schema Authorization dbName password • Create Table TableName (Column Type, . . .) • Alter Table Table {Add, Column, Constraint, Drop} • Drop {Table Table | Index Index On table} • Create Index IndexName ON Table (Column {ASC|DESC})

49. Syntax Examples CREATE TABLE Customer (CustomerID INTEGER NOT NULL, LastName CHAR (10), more columns ); ALTER TABLE Customer DROP COLUMN ZipCode; ALTER TABLE Customer ADD COLUMN CellPhone CHAR(15);

50. SQL: Foreign Key CREATE TABLE Order (OrderID INTEGER NOT NULL, OrderDate DATE, CustomerID INTEGER CONSTRAINT pkorder PRIMARY KEY (OrderID), CONSTRAINT fkorder FOREIGN KEY (CustomerID) REFERENCES Customer (CustomerID) ); Order Customer OrderID OrderDate CustomerID CustomerID LastName FirstName Address … *