Lecture'03:'SQL'Friday,'April'2nd,'2010'Dan Suciu -- 444 Spring 2010 1Announcements'• New'IMDB'database:''use'imdb_new'instead'of'imdb'• Up'to'date,'and'much'larger'!'• Make'following'change'to'Project'1'/'QuesMon'5:'consider'only'movies'made'in'2010'Dan Suciu -- 444 Spring 2010 23 Outline • Aggregations (6.4.3 – 6.4.6) • Examples, examples, examples… • Nulls (6.1.6) • Outer joins (6.3.8)4 Aggregation SELECT count(*) FROM Product WHERE year > 1995 Except count, all aggregations apply to a single attribute SELECT avg(price) FROM Product WHERE maker=‘Toyota’ SQL supports several aggregation operations: sum, count, min, max, avg5 COUNT applies to duplicates, unless otherwise stated: SELECT Count(category) FROM Product WHERE year > 1995 same as Count(*) We probably want: SELECT Count(DISTINCT category) FROM Product WHERE year > 1995 Aggregation: Count6 Purchase(product, date, price, quantity) More Examples SELECT Sum(price * quantity) FROM Purchase SELECT Sum(price * quantity) FROM Purchase WHERE product = ‘bagel’ What do they mean ?7 Simple Aggregations Purchase SELECT Sum(price * quantity) FROM Purchase WHERE product = ‘Bagel’ 90 (= 60+30) Product Price Quantity Bagel 3 20 Bagel 1.50 20 Banana 0.5 50 Banana 2 10 Banana 4 108 Grouping and Aggregation Purchase(product, price, quantity) SELECT product, Sum(quantity) AS TotalSales FROM Purchase WHERE price > 1 GROUP BY product Let’s see what this means… Find total quantities for all sales over $1, by product.9 Grouping and Aggregation 1. Compute the FROM and WHERE clauses. 2. Group by the attributes in the GROUPBY 3. Compute the SELECT clause: grouped attributes and aggregates.10 1&2. FROM-WHERE-GROUPBY Product Price Quantity Bagel 3 20 Bagel 1.50 20 Banana 0.5 50 Banana 2 10 Banana 4 1011 3. SELECT SELECT product, Sum(quantity) AS TotalSales FROM Purchase WHERE price > 1 GROUP BY product Product TotalSales Bagel 40 Banana 20 Product Price Quantity Bagel 3 20 Bagel 1.50 20 Banana 0.5 50 Banana 2 10 Banana 4 1012 GROUP BY v.s. Nested Quereis SELECT product, Sum(quantity) AS TotalSales FROM Purchase WHERE price > 1 GROUP BY product SELECT DISTINCT x.product, (SELECT Sum(y.quantity) FROM Purchase y WHERE x.product = y.product AND price > 1) AS TotalSales FROM Purchase x WHERE price > 1 Why twice ?13 Another Example SELECT product, sum(quantity) AS SumSales max(price) AS MaxQuantity FROM Purchase GROUP BY product What does it mean ?14 HAVING Clause SELECT product, Sum(quantity) FROM Purchase WHERE price > 1 GROUP BY product HAVING Sum(quantity) > 30 Same query, except that we consider only products that had at least 100 buyers. HAVING clause contains conditions on aggregates.15 General form of Grouping and Aggregation SELECT S FROM R1,…,Rn WHERE C1 GROUP BY a1,…,ak HAVING C2 S = may contain attributes a1,…,ak and/or any aggregates but NO OTHER ATTRIBUTES C1 = is any condition on the attributes in R1,…,Rn C2 = is any condition on aggregate expressions Why ?16 General form of Grouping and Aggregation Evaluation steps: 1. Evaluate FROM-WHERE, apply condition C1 2. Group by the attributes a1,…,ak 3. Apply condition C2 to each group (may have aggregates) 4. Compute aggregates in S and return the result SELECT S FROM R1,…,Rn WHERE C1 GROUP BY a1,…,ak HAVING C217 Advanced SQLizing 1. Getting around INTERSECT and EXCEPT 2. Unnesting Aggregates 3. Finding witnesses18 INTERSECT and EXCEPT: (SELECT R.A, R.B FROM R) INTERSECT (SELECT S.A, S.B FROM S) SELECT R.A, R.B FROM R WHERE EXISTS(SELECT * FROM S WHERE R.A=S.A and R.B=S.B) (SELECT R.A, R.B FROM R) EXCEPT (SELECT S.A, S.B FROM S) SELECT R.A, R.B FROM R WHERE NOT EXISTS(SELECT * FROM S WHERE R.A=S.A and R.B=S.B) Can unnest. How ? INTERSECT and EXCEPT: not in some DBMS19 Unnesting Aggregates Product ( pname, price, company) Company(cname, city) Find the number of companies in each city SELECT DISTINCT city, (SELECT count(*) FROM Company Y WHERE X.city = Y.city) FROM Company X SELECT city, count(*) FROM Company GROUP BY city Equivalent queries Note: no need for DISTINCT (DISTINCT is the same as GROUP BY)20 Unnesting Aggregates Product ( pname, price, company) Company(cname, city) Find the number of products made in each city SELECT DISTINCT X.city, (SELECT count(*) FROM Product Y, Company Z WHERE Y.cname=Z.company AND Z.city = X.city) FROM Company X SELECT X.city, count(*) FROM Company X, Product Y WHERE X.cname=Y.company GROUP BY X.city They are NOT equivalent ! (WHY?)21 More Unnesting • Find authors who wrote ≥ 10 documents: • Attempt 1: with nested queries SELECT DISTINCT Author.name FROM Author WHERE count(SELECT Wrote.url FROM Wrote WHERE Author.login=Wrote.login) > 10 This is SQL by a novice Author(login,name) Wrote(login,url)22 More Unnesting • Find all authors who wrote at least 10 documents: • Attempt 2: SQL style (with GROUP BY) SELECT Author.name FROM Author, Wrote WHERE Author.login=Wrote.login GROUP BY Author.name HAVING count(wrote.url) > 10 This is SQL by an expert23 Finding Witnesses Store(sid, sname) Product(pid, pname, price, sid) For each store, find its most expensive products24 Finding Witnesses SELECT Store.sid, max(Product.price) FROM Store, Product WHERE Store.sid = Product.sid GROUP BY Store.sid Finding the maximum price is easy… But we need the witnesses, i.e. the products with max price25 Finding Witnesses SELECT Store.sname, Product.pname FROM Store, Product, (SELECT Store.sid AS sid, max(Product.price) AS p FROM Store, Product WHERE Store.sid = Product.sid GROUP BY Store.sid, Store.sname) X WHERE Store.sid = Product.sid and Store.sid = X.sid and Product.price = X.p To find the witnesses, compute the maximum price in a subquery26 Finding Witnesses There is a more concise solution here: SELECT Store.sname, x.pname FROM Store, Product x WHERE Store.sid = x.sid and x.price >= ALL (SELECT y.price FROM Product y WHERE Store.sid = y.sid)27 NULLS in SQL • Whenever we don’t have a value, we
View Full Document
Unlocking...