# 5.5 Exercises (See Section A.1 for the solution to each exercise.)

1. Diagram the following query:

```SELECT ...
FROM Customers C, ZIP_Codes Z, ZIP_Demographics D, Regions R
WHERE C.ZIP_Code=Z.ZIP_Code
AND Z.Demographic_ID=D.Demographic_ID
AND Z.Region_ID=R.Region_ID
AND C.Active_Flag='Y'
AND C.Profiled_Flag='N'
AND R.Name='SOUTHWEST'
AND D.Name IN ('YUPPIE', 'OLDMONEY');```

Make the usual assumptions about primary-key names, except that the primary key of ZIP_Codes is simply ZIP_Code, and note that the Name columns of both REGIONS and ZIP_Demographics are also uniquely indexed. You have 5,000,000 rows in Customers, 250,000 rows in Zip_Codes, 20 rows in ZIP_Demographics, and 5 rows in Regions. Assume all foreign keys are never null and always point to valid primary keys. The following query returns 2,000,000 rows:

`SELECT COUNT(*) FROM Customers C WHERE Active_Flag='Y' AND Profiled_Flag='N';`
2. Diagram the following query:

```SELECT ...
FROM Regions R, Zip_Codes Z, Customers C, Customer_Mailings CM,
Mailings M, Catalogs Cat, Brands B
WHERE R.Region_ID(+)=Z.Region_ID
AND Z.ZIP_Code(+)=C.ZIP_Code
AND C.Customer_ID=CM.Customer_ID
AND CM.Mailing_ID=M.Mailing_ID
AND M.Catalog_ID=Cat.Catalog_ID
AND Cat.Brand_ID=B.Brand_ID
AND B.Name='OhSoGreen'
AND M.Mailing_Date >= SYSDATE-365
GROUP BY... ORDER BY ...```

Start with the same assumptions and statistics as in Exercise 1. Customer_Mailings contains 30,000,000 rows. Mailings contains 40,000 rows. Catalogs contains 200 rows. Brands contains 12 rows and has an alternate unique key on Name. The following query returns 16,000 rows:

`SELECT COUNT(*) FROM Mailings M WHERE Mailing_Date >= SYSDATE-365;`
3. Diagram the following query:

```SELECT ...
FROM Code_Translations SPCT, Code_Translations TRCT, Code_Translations CTCT,
Products P, Product_Lines PL, Inventory_Values IV, Brands B,
Product_Locations Loc, Warehouses W, Regions R,
Inventory_Taxing_Entities ITx, Inventory_Tax_Rates ITxR, Consignees C
WHERE W.Region_ID=R.Region_ID
AND Loc.Warehouse_ID=W.Warehouse_ID
AND W.Inventory_Taxing_Entity_ID=ITx.Inventory_Taxing_Entity_ID
AND ITx.Inventory_Taxing_Entity_ID= ITxR.Inventory_Taxing_Entity_ID
AND ITxR.Effective_Start_Date <= SYSDATE
AND ITxR.Effective_End_Date > SYSDATE
AND ITxR.Rate>0
AND P.Product_ID=Loc.Product_ID
AND Loc.Quantity>0
AND P.Product_Line_ID=PL.Product_Line_ID(+)
AND P.Product_ID=IV.Product_ID
AND P.Taxable_Inventory_Flag='Y'
AND P.Consignee_ID=C.Consignee_ID(+)
AND P.Strategic_Product_Code=SPCT.Code
AND SPCT.Code_Type='STRATEGIC_PRODUCT'
AND P.Turnover_Rate_Code=TRCT.Code
AND TRCT.Code_Type='TURNOVER_RATE'
AND P.Consignment_Type_Code=CTCT.CODE
AND CTCT.Code_Type='CONSIGNMENT_TYPE'
AND IV.Effective_Start_Date <= SYSDATE
AND IV.Effective_End_Date > SYSDATE
AND IV.Unit_Value>0
AND P.Brand_ID=B.Brand_ID
AND B.Name='2Much\$'
AND ITX.Tax_Day_Of_Year='DEC31'
GROUP BY... ORDER BY ...```

Start with the same assumptions and statistics as in Exercises 1 and 2, except that W.Inventory_Taxing_Entity_ID points to a valid taxing entity only when it is not null, which is just 5% of the time. The counts for table rows are as follows:

Products=8,500
Product_Lines=120
Inventory_Values=34,000
Brands=12
Product_Locations=176,000
Warehouses=80
Regions=5
Inventory_Taxing_Entities=4
Inventory_Tax_Rates=7
Consignees=14

Code_Translations has a two-part primary key: Code_Type, Code.

Inventory_Values and Inventory_Tax_Rates have a time-dependent primary key consisting of an ID and an effective date range, such that any given date falls in a single date range for any value of the key ID. Specifically, the join conditions to each of these tables are guaranteed to be unique by the Effective_Start_Date and Effective_End_Date conditions, which are part of the joins, not separate filters. (Unfortunately, there is no convenient way to enforce that uniqueness through an index; it is a condition created by the application.) The following queries return the rowcounts shown in the lines that follow each query:

```Q1:  SELECT COUNT(*) A1 FROM Inventory_Taxing_Entities ITx
WHERE ITx.Tax_Day_Of_Year='DEC31'
A1: 2

Q2:  SELECT COUNT(*) A2 FROM Inventory_Values IV
WHERE IV.Unit_Value>0
AND IV.Effective_Start_Date <= SYSDATE
AND IV.Effective_End_Date > SYSDATE
A2: 7,400

Q3:  SELECT COUNT(*) A3 FROM Products P
WHERE P.Taxable_Inventory_Flag='Y'
A3: 8,300

Q4:  SELECT COUNT(*) A4 FROM Product_Locations Loc
WHERE Loc.Quantity>0
A4: 123,000

Q5:  SELECT COUNT(*) A5 FROM Inventory_Tax_Rates ITxR
WHERE ITxR.RATE>0
AND ITxR.Effective_Start_Date <= SYSDATE
AND ITxR.Effective_End_Date > SYSDATE
A5: 4

Q6:  SELECT COUNT(*) A6 FROM Inventory_Values IV
WHERE IV.Effective_Start_Date <= SYSDATE
AND IV.Effective_End_Date > SYSDATE
A6: 8,500

Q7:  SELECT COUNT(*) A7 FROM INVENTORY_TAX_RATES ITxR
WHERE ITxR.Effective_Start_Date <= SYSDATE
AND ITxR.Effective_End_Date > SYSDATE
A7: 4

Q8:  SELECT COUNT(*) A8 FROM Code_Translations SPCT
WHERE Code_Type = 'STRATEGIC_PRODUCT'
A8: 3

Q9:  SELECT COUNT(*) A9 FROM Code_Translations TRCT
WHERE Code_Type = 'TURNOVER_RATE'
A9: 2

Q10: SELECT COUNT(*) A10 FROM CTCT
WHERE Code_Type = 'CONSIGNMENT_TYPE'
A10: 3```
4. Fully simplify the query diagram for Exercise 1. Try starting from the queries and the query statistics, rather than from the full query diagrams. Then, compare your result with what you get when you start from the full query diagrams that you already did.

5. Fully simplify the query diagram for Exercise 2, following the guidelines in Exercise 4.

6. Fully simplify the query diagram for Exercise 3, following the guidelines in Exercise 4.

 Preface
 Chapter 1. Introduction
 Chapter 2. Data-Access Basics
 Chapter 3. Viewing and Interpreting Execution Plans
 Chapter 4. Controlling Execution Plans
 Chapter 6. Deducing the Best Execution Plan
 Chapter 7. Diagramming and Tuning Complex SQL Queries
 Chapter 8. Why the Diagramming Method Works
 Chapter 9. Special Cases
 Chapter 10. Outside-the-Box Solutions to Seemingly Unsolvable Problems
 Appendix A. Exercise Solutions
 Appendix B. The Full Process, End to End
 Glossary