Outline • Unions, intersections, differences (6.2.5, 6.4.2) Lecture 03: SQL • Subqueries (6.3) • Aggregations (6.4.3 – 6.4.6) Friday, October 4, 2002 Hint for reading the textbook: read the entire chapter 6 ! Reading assignment from “SQL for Nerds”: chapter 4, “More complex queries” (you will find it very useful for subqueries) 1 2 First Unintuitive SQLism Renaming Columns Product PName Price Category Manufacturer SELECT DISTINCT R.A SELECT DISTINCT R.A Gizmo $19.99 Gadgets GizmoWorks FROM R, S, T FROM R, S, T Powergizmo $29.99 Gadgets GizmoWorks WHERE R.A=S.A OR R.A=T.A WHERE R.A=S.A OR R.A=T.A SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi SELECT Pname AS prodName, Price AS askPrice SELECT Pname AS prodName, Price AS askPrice Looking for R ∩ (S ∪ T) FROM Product FROM Product WHERE Price > 100 WHERE Price > 100 prodName askPrice But what happens if T is empty? Query with SingleTouch $149.99 MultiTouch $203.99 renaming 3 4 Union, Intersection, Difference (SELECT name (SELECT DISTINCT R.A (SELECT name (SELECT DISTINCT R.A FROM Person FROM R) FROM Person FROM R) WHERE City=“Seattle”) INTERSECT ( (SELECT S.A FROM S) WHERE City=“Seattle”) INTERSECT ( (SELECT S.A FROM S) UNION UNION UNION (SELECT T.A FROM T)) UNION (SELECT T.A FROM T)) (SELECT name (SELECT name FROM Person, Purchase FROM Person, Purchase WHERE buyer=name AND store=“The Bon”) WHERE buyer=name AND store=“The Bon”) Similarly, you can use INTERSECT and EXCEPT. You must have the same attribute names (otherwise: rename). 5 6 1
Conserving Duplicates Subqueries A subquery producing a single value: (SELECT name (SELECT name SELECT Purchase.product FROM Person SELECT Purchase.product FROM Person FROM Purchase FROM Purchase WHERE City=“Seattle”) WHERE City=“Seattle”) WHERE buyer = WHERE buyer = (SELECT name (SELECT name UNION ALL UNION ALL FROM Person FROM Person WHERE ssn = ‘123456789‘); WHERE ssn = ‘123456789‘); In this case, the subquery returns one value. (SELECT name (SELECT name FROM Person, Purchase FROM Person, Purchase If it returns more, it’s a run-time error. WHERE buyer=name AND store=“The Bon”) WHERE buyer=name AND store=“The Bon”) 7 8 Subqueries Returning Relations Can say the same thing without a subquery: Find companies who manufacture products bought by Joe Blow. SELECT Company.name SELECT Purchase.product SELECT Company.name SELECT Purchase.product FROM Company, Product FROM Purchase, Person FROM Company, Product FROM Purchase, Person WHERE Company.name=Product.maker WHERE buyer = name AND ssn = ‘123456789‘ WHERE Company.name=Product.maker WHERE buyer = name AND ssn = ‘123456789‘ AND Product.name IN AND Product.name IN (SELECT Purchase.product (SELECT Purchase.product This is equivalent to the previous one when the ssn is a key FROM Purchase FROM Purchase and ‘123456789’ exists in the database; WHERE Purchase .buyer = ‘Joe Blow‘); WHERE Purchase .buyer = ‘Joe Blow‘); otherwise they are different. Here the subquery returns a set of values: no more runtime errors. 9 10 Subqueries Returning Relations Removing Duplicates ← Multiple copies SELECT Company.name SELECT Company.name Equivalent to: FROM Company, Product, Purchase FROM Company, Product, Purchase WHERE Company.name= Product.maker SELECT Company.name WHERE Company.name= Product.maker SELECT Company.name AND Product.name = Purchase.product AND Product.name = Purchase.product FROM Company, Product, Purchase FROM Company, Product, Purchase AND Purchase.buyer = ‘Joe Blow’ AND Purchase.buyer = ‘Joe Blow’ WHERE Company.name= Product.maker WHERE Company.name= Product.maker AND Product.name = Purchase.product AND Product.name = Purchase.product AND Purchase.buyer = ‘Joe Blow’ ← Single copies AND Purchase.buyer = ‘Joe Blow’ SELECT DISTINCT Company.name SELECT DISTINCT Company.name FROM Company, Product, Purchase FROM Company, Product, Purchase WHERE Company.name= Product.maker Is this query equivalent to the previous one ? WHERE Company.name= Product.maker AND Product.name = Purchase.product AND Product.name = Purchase.product AND Purchase.buyer = ‘Joe Blow’ Beware of duplicates ! AND Purchase.buyer = ‘Joe Blow’ 11 12 2
Removing Duplicates Subqueries Returning Relations SELECT DISTINCT Company.name SELECT DISTINCT Company.name You can also use: s > ALL R FROM Company, Product FROM Company, Product s > ANY R WHERE Company.name= Product.maker WHERE Company.name= Product.maker EXISTS R AND Product.name IN AND Product.name IN (SELECT Purchase.product (SELECT Purchase.product Product ( pname, price, category, maker) FROM Purchase Find products that are more expensive than all those produced FROM Purchase WHERE Purchase.buyer = ‘Joe Blow’) WHERE Purchase.buyer = ‘Joe Blow’) By “Gizmo-Works” SELECT name SELECT DISTINCT Company.name SELECT name SELECT DISTINCT Company.name Now FROM Product FROM Company, Product, Purchase FROM Product FROM Company, Product, Purchase they are WHERE price > ALL (SELECT price WHERE Company.name= Product.maker WHERE price > ALL (SELECT price WHERE Company.name= Product.maker equivalent FROM Purchase AND Product.name = Purchase.product FROM Purchase AND Product.name = Purchase.product WHERE maker=‘Gizmo-Works’) AND Purchase.buyer = ‘Joe Blow’ WHERE maker=‘Gizmo-Works’) AND Purchase.buyer = ‘Joe Blow’ 13 14 Question for Database Fans Conditions on Tuples and their Friends • Can we express this query as a single SELECT- SELECT DISTINCT Company.name SELECT DISTINCT Company.name FROM-WHERE query, without subqueries ? FROM Company, Product FROM Company, Product WHERE Company.name= Product.maker WHERE Company.name= Product.maker AND (Product.name,price) IN AND (Product.name,price) IN (SELECT Purchase.product, Purchase.price) (SELECT Purchase.product, Purchase.price) • Hint: show that all SFW queries are monotone FROM Purchase FROM Purchase (figure out what this means). A query with ALL WHERE Purchase.buyer = “Joe Blow”); WHERE Purchase.buyer = “Joe Blow”); is not monotone May not work in SQL server... 15 16 Correlated Queries Complex Correlated Query Movie (title, year, director, length) Product ( pname, price, category, maker, year) Find movies whose title appears more than once. • Find products (and their manufacturers) that are more expensive correlation than all products made by the same manufacturer before 1972 SELECT DISTINCT title SELECT DISTINCT title SELECT DISTINCT pname, maker SELECT DISTINCT pname, maker FROM Movie AS x FROM Movie AS x FROM Product AS x FROM Product AS x WHERE year <> ANY WHERE price > ALL (SELECT price WHERE year <> ANY WHERE price > ALL (SELECT price FROM Product AS y (SELECT year FROM Product AS y (SELECT year WHERE x.maker = y.maker AND y.year < 1972); WHERE x.maker = y.maker AND y.year < 1972); FROM Movie FROM Movie WHERE title = x.title); WHERE title = x.title); Powerful, but much harder to optimize ! Note (1) scope of variables (2) this can still be expressed as single SFW 17 18 3
Aggregation Aggregation: Count SELECT Avg(price) SELECT Avg(price) SELECT Count(*) SELECT Count(*) FROM Product FROM Product FROM Product FROM Product WHERE maker=“Toyota” WHERE maker=“Toyota” WHERE year > 1995 WHERE year > 1995 SQL supports several aggregation operations: SUM, MIN, MAX, AVG, COUNT Except COUNT, all aggregations apply to a single attribute 19 20 Aggregation: Count Simple Aggregation Purchase(product, date, price, quantity) COUNT applies to duplicates, unless otherwise stated: Example 1: find total sales for the entire database SELECT Count(category) same as Count(*) FROM Product SELECT Sum(price * quantity) WHERE year > 1995 FROM Purchase Better: Example 1’: find total sales of bagels SELECT Count(DISTINCT category) SELECT Sum(price * quantity) FROM Product FROM Purchase WHERE year > 1995 WHERE product = ‘bagel’ 21 22 Simple Aggregations Grouping and Aggregation Purchase Usually, we want aggregations on certain parts of the relation. Product Date Price Quantity Purchase(product, date, price, quantity) Bagel 10/21 0.85 15 Example 2: find total sales after 9/1 per product. Banana 10/22 0.52 7 SELECT product, Sum(price*quantity) AS TotalSales SELECT product, Sum(price*quantity) AS TotalSales Banana 10/19 0.52 17 FROM Purchase FROM Purchase WHERE date > “9/1” WHERE date > “9/1” Bagel 10/20 0.85 20 GROUPBY product GROUPBY product Let’s see what this means… 23 24 4
Recommend
More recommend
