chapter 5 advanced querying 1 based on g. post, dbms: designing & building business applications...
TRANSCRIPT
Chapter 5
Advanced Querying
1
Based on G. Post, DBMS: Designing & Building Business Applications
University of ManitobaAsper School of Business
3500 DBMSBob Travica
Updated 2015
DDBB
SSYYSSTTEEMMSS
2 of 22
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 2010 (queries work with it)
DDBB
SSYYSSTTEEMMSS
3 of 22
Harder Questions
11) List last names of customers who bought something between 4/1/2010 and 5/31/2010.
12) List the Last Name and Phone of anyone who bought a registered white cat between 6/1/1998 and 12/31/2010.
13) How many cats are “in-stock” on 10/1/2010?
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/2010 and 12/31/2010?
18) Which customers who bought dogs also bought products for cats (at any time)?
(Numbering continues from last lecture)
DDBB
SSYYSSTTEEMMSS
4 of 22
SupplierIDNameContactNamePhoneAddressZipCodeCityID
Supplier
PONumberOrderDateReceiveDateSupplierIDEmployeeIDShippingCost
MerchandiseOrder
OrderIDOrderDateReceiveDateSupplierIDShippingCostEmployeeID
AnimalOrder
OrderIDAnimalIDCost
AnimalOrderItem
CityIDZipCodeCityStateAreaCodePopulation1990Population1980CountryLatitudeLongitude
City
EmployeeIDLastNameFirstNamePhoneAddressZipCodeCityIDTaxPayerIDDateHiredDateReleased
Employee
PONumberItemIDQuantityCost
OrderItem
CategoryRegistration
Category
CategoryBreed
Breed
AnimalIDNameCategoryBreedDateBornGenderRegisteredColorListPricePhoto
Animal
SaleIDSaleDateEmployeeIDCustomerIDSalesTax
Sale
SaleIDItemIDQuantitySalePrice
SaleItem
ItemIDDescriptionQuantityOnHandListPriceCategory
Merchandise
SaleIDAnimalIDSalePrice
SaleAnimal
CustomerIDPhoneFirstNameLastNameAddressZipCodeCityID
Customer
*
*
*
*
*
*
*
*
*
**
*
*
*
**
*
**
*
Know your data! Sally’s Pet Store Schema(2007 version)
DDBB
SSYYSSTTEEMMSS
5 of 22
Joining Two Tables Query #11: List LastNames of Customers who bought something
between 4/1/2010 and 5/31/2010.
SELECT DISTINCT Sale.CustomerID, Customer.LastNameFROM CustomerINNER JOIN Sale ON Customer.CustomerID = Sale.CustomerIDWHERE Sale.SaleDate BETWEEN #4/1/2010# AND #5/31/2010#ORDER BY Customer.LastName;
Output:20 rows
DDBB
SSYYSSTTEEMMSS
6 of 22
SQL Inner Join Syntax
SELECT column(s)
FROM table1
INNER JOIN table2 ON table1.columnA = table2.columnA
WHERE constraints
• Inner Join is most frequently used
• Matches rows from different tables based on
matching values of PKs and FKs
Primary Key Foreign Key
DDBB
SSYYSSTTEEMMSS
7 of 22
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.
SELECT Customer.LastName, Customer.Phone, Animal.Category, Animal.Color, Animal.Registered, Sale.SaleDateFROM CustomerINNER JOIN (Sale INNER JOIN Animal ON Sale.SaleID=Animal.SaleID) ON Customer.CustomerID=Sale.CustomerIDWHERE Animal.Category="Cat" AND Animal.Color LIKE "*White*" AND Animal.Registered IS NOT Null AND Sale.SaleDate Between #6/1/2010# And #12/31/2010# ;
See Footnote!
Output: 4 rowsLastName Phone Category Color Registered SaleDate
Bell (717) 244-3484 Cat White CFA 22/12/2010
Kidd (207) 585-7583 Cat White CFA 09/12/2010
Hooks (618) 109-9250 Cat White/Black CFA 03/12/2010
Hinton (606) 241-3182 Cat White CFA 16/08/2010
DDBB
SSYYSSTTEEMMSS
8 of 22
SELECT Customer.LastName, Customer.Phone, Animal.Category, Animal.Color, Animal.Registered, Sale.SaleDate
FROM Customer, Sale, Animal
WHERE Sale.SaleID=Animal.SaleID
AND Customer.CustomerID=Sale.CustomerID
AND
Animal.Category="Cat" AND Animal.Color LIKE "*White*" AND Animal.Registered IS NOT Null AND Sale.SaleDate Between #6/1/2010# And #12/31/2010# ;
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.
Disadvantages: the mix-up in the WHERE line (FKs/PKs and constraints).
Still may save time for query writing.
DDBB
SSYYSSTTEEMMSS
9 of 22
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 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.
DDBB
SSYYSSTTEEMMSS
10 of 22
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.
DDBB
SSYYSSTTEEMMSS
11 of 22
Levels of automation: Sub-query 1/2
Query #14: List the cats that drew a donation bigger than the average.
SELECT Animal.AnimalID, Animal.Category, Animal.Breed, Animal.DonationFROM AnimalWHERE (Animal.Category="Cat") AND (Animal.Donation>171.82);
SELECT Category, AVG(Donation) As [Average Donation] FROM Animal WHERE Category="Cat“ ;
We can find the answer in 2 steps:1. Find what the average price for cats is ($171.82).
2. Use the average price as a constraint in another query:
See Note…
Output:
Category Average Donation
Cat $171.82
DDBB
SSYYSSTTEEMMSS
12 of 22
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.AnimalID, 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
DDBB
SSYYSSTTEEMMSS
13 of 22
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.LastName, Customer.FirstName, SaleItem.ItemIDFROM (Customer INNER JOIN Sale ON Customer.CustomerID = Sale.CustomerID) INNER JOIN SaleItem ON Sale.SaleID = SaleItem.SaleIDWHERE SaleItem.ItemID In (1,2,30,32,33)ORDER BY Customer.LastName, Customer.FirstName;
Output: 39 rows
DDBB
SSYYSSTTEEMMSS
14 of 22
Using IN with a Sub-query Query: List all customers who bought items for cats.
SELECT Customer.CustomerID, Customer.LastName, Customer.FirstName, SaleItem.ItemIDFROM
(Customer INNER JOIN Sale ON Customer.CustomerID = Sale.CustomerID)INNER JOIN SaleItem ON Sale.SaleID = SaleItem.SaleID
WHERE (SaleItem.ItemID In(SELECT Merchandise.ItemID FROM Merchandise WHERE Category="Cat") )
ORDER BY Customer.LastName 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.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;
DDBB
SSYYSSTTEEMMSS
15 of 22
SubQuery: 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.
SELECT Animal.AnimalID, Animal.Name, Animal.Category, Animal.SaleID
FROM Animal
WHERE Animal.AnimalID NOT IN (SELECT AnimalID FROM Animal INNER JOIN Sale ON Animal.SaleID=Sale.SaleID);
Output: 10 rowsAnimalID Name Category SaleID
2 Fish
4 Simon Dog
12 Leisha Dog
19 Gene Dog
25 Vivian Dog
34 Rhonda Dog
88 Brandy Dog
175 Donald Cat
181 Fish
201 Mammal
See Note for optional query.
DDBB
SSYYSSTTEEMMSS
16 of 22
SQL Mnemonic (again)
Someone - SELECT
From - FROM
Ireland - INNER JOIN
Will - WHERE
Grow - GROUP BY
Horseradish & - HAVING
Onions - ORDER BY
DDBB
SSYYSSTTEEMMSS
17 of 22
Outer Join: Left Outer Join
Query #16: “Which animals have not been sold?” with LEFT JOIN Output building:
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.**
SELECT Animal.AnimalID, Animal.Name, Animal.CategoryFROM Animal LEFT JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalIDWHERE (SaleAnimal.AnimalID Is Null);
To get just the non-matched rows, add the WHERE line:
DDBB
SSYYSSTTEEMMSS
18 of 22
Query #17: Which animals have been sold?
Outer Join: Right Outer Join
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.
DDBB
SSYYSSTTEEMMSS
19 of 22
Types of Join - Summary
Inner Join + =
Left Join +
+
=
Right Join =
Note: RIGHT JOIN can be presented as LEFT JOIN by reversingorder of tables.
DDBB
SSYYSSTTEEMMSS
20 of 22
UNION Operator
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.
SELECT EID, Name, Phone, Salary, ‘East’ AS OfficeFROM EmployeeEastUNIONSELECT EID, Name, Phone, Salary, ‘West’ AS OfficeFROM EmployeeWest
EID Name Phone Salary Office352 Jones 3352 45,000 East876 Inez 8736 47,000 East372 Stoiko 7632 38,000 East
890 Smythe 9803 62,000 West361 Kim 7736 73,000 West
DDBB
SSYYSSTTEEMMSS
21 of 22
UNION, INTERSECT, EXCEPT(Boolean Logic)
T1 T2
A AC CSELECT EID, NameFROM EmployeeEastINTERSECTSELECT EID, NameFROM EmployeeWest
List the name of any employee who has worked for both the East and West regions:
DDBB
SSYYSSTTEEMMSS
22 of 22
CASE Function
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
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”
ENDFROM Animal;
( SQL Server, Oracle)