1Lecture 03: SQLMonday, October 2nd, 20062Outline• Subqueries (6.3)• Aggregations (6.4.3 – 6.4.6)• Examples, examples, examples…Read the entire chapter 6 !3AggregationSELECT count(*)FROM ProductWHERE year > 1995SELECT count(*)FROM ProductWHERE year > 1995Except count, all aggregations apply to a single attributeSELECT avg(price)FROM ProductWHERE maker=“Toyota”SELECT avg(price)FROM ProductWHERE maker=“Toyota”SQL supports several aggregation operations:sum, count, min, max, avg4COUNT applies to duplicates, unless otherwise stated:SELECT Count(category)FROM ProductWHERE year > 1995SELECT Count(category)FROM ProductWHERE year > 1995same as Count(*)We probably want:SELECT Count(DISTINCT category)FROM ProductWHERE year > 1995SELECT Count(DISTINCT category)FROM ProductWHERE year > 1995Aggregation: Count5Purchase(product, date, price, quantity)More ExamplesSELECT Sum(price * quantity)FROM PurchaseSELECT Sum(price * quantity)FROM PurchaseSELECT Sum(price * quantity)FROM PurchaseWHERE product = ‘bagel’SELECT Sum(price * quantity)FROM PurchaseWHERE product = ‘bagel’What dothey mean ?6Simple AggregationsPurchase201.5010/25Bagel10110/10Banana100.510/3Banana20110/21BagelQuantityPriceDateProductSELECT Sum(price * quantity)FROM PurchaseWHERE product = ‘bagel’SELECT Sum(price * quantity)FROM PurchaseWHERE product = ‘bagel’50 (= 20+30)7Grouping and AggregationPurchase(product, date, price, quantity)SELECT product, Sum(price*quantity) AS TotalSalesFROM PurchaseWHERE date > ‘10/1/2005’GROUP BY productSELECT product, Sum(price*quantity) AS TotalSalesFROM PurchaseWHERE date > ‘10/1/2005’GROUP BY productLet’s see what this means…Find total sales after 10/1/2005 per product.8Grouping and Aggregation1. Compute the FROM and WHERE clauses.2. Group by the attributes in the GROUPBY3. Compute the SELECT clause: grouped attributes and aggregates.91&2. FROM-WHERE-GROUPBY201.5010/25Bagel10110/10Banana100.510/3Banana20110/21BagelQuantityPriceDateProduct103. SELECTSELECT product, Sum(price*quantity) AS TotalSalesFROM PurchaseWHERE date > ‘10/1/2005’GROUP BY productSELECT product, Sum(price*quantity) AS TotalSalesFROM PurchaseWHERE date > ‘10/1/2005’GROUP BY product201.5010/25Bagel10110/10Banana100.510/3Banana20110/21BagelQuantityPriceDateProduct15Banana50BagelTotalSalesProduct11GROUP BY v.s. Nested QuereisSELECT product, Sum(price*quantity) AS TotalSalesFROM PurchaseWHERE date > ‘10/1/2005’GROUP BY productSELECT product, Sum(price*quantity) AS TotalSalesFROM PurchaseWHERE date > ‘10/1/2005’GROUP BY productSELECT DISTINCT x.product, (SELECT Sum(y.price*y.quantity)FROM Purchase yWHERE x.product = y.product AND y.date > ‘10/1/2005’)AS TotalSalesFROM Purchase xWHERE x.date > ‘10/1/2005’SELECT DISTINCT x.product, (SELECT Sum(y.price*y.quantity)FROM Purchase yWHERE x.product = y.product AND y.date > ‘10/1/2005’)AS TotalSalesFROM Purchase xWHERE x.date > ‘10/1/2005’12Another ExampleSELECT product,sum(price * quantity) AS SumSalesmax(quantity) AS MaxQuantityFROM PurchaseGROUP BY productSELECT product,sum(price * quantity) AS SumSalesmax(quantity) AS MaxQuantityFROM PurchaseGROUP BY productWhat doesit mean ?13HAVING ClauseSELECT product, Sum(price * quantity)FROM PurchaseWHERE date > ‘10/1/2005’GROUP BY productHAVING Sum(quantity) > 30SELECT product, Sum(price * quantity)FROM PurchaseWHERE date > ‘10/1/2005’GROUP BY productHAVING Sum(quantity) > 30Same query, except that we consider only products that hadat least 100 buyers.HAVING clause contains conditions on aggregates.14General form of Grouping and AggregationSELECT SFROM R1,…,RnWHERE C1GROUP BY a1,…,akHAVING C2S = may contain attributes a1,…,akand/or any aggregates but NO OTHER ATTRIBUTESC1 = is any condition on the attributes in R1,…,RnC2 = is any condition on aggregate expressionsWhy ?15General form of Grouping and AggregationEvaluation steps:1. Evaluate FROM-WHERE, apply condition C12. Group by the attributes a1,…,ak3. Apply condition C2 to each group (may have aggregates)4. Compute aggregates in S and return the resultSELECT SFROM R1,…,RnWHERE C1GROUP BY a1,…,akHAVING C2SELECT SFROM R1,…,RnWHERE C1GROUP BY a1,…,akHAVING C216Advanced SQLizing1. Getting around INTERSECT and EXCEPT2. Quantifiers3. Aggregation v.s. subqueries4. Two examples (study at home)171. INTERSECT and EXCEPT:(SELECT R.A, R.BFROM R)INTERSECT(SELECT S.A, S.BFROM S)(SELECT R.A, R.BFROM R)INTERSECT(SELECT S.A, S.BFROM S)SELECT R.A, R.BFROM RWHEREEXISTS(SELECT *FROM SWHERE R.A=S.A and R.B=S.B)SELECT R.A, R.BFROM RWHEREEXISTS(SELECT *FROM SWHERE R.A=S.A and R.B=S.B)(SELECT R.A, R.BFROM R)EXCEPT(SELECT S.A, S.BFROM S)(SELECT R.A, R.BFROM R)EXCEPT(SELECT S.A, S.BFROM S)SELECT R.A, R.BFROM RWHERENOT EXISTS(SELECT *FROM SWHERE R.A=S.A and R.B=S.B)SELECT R.A, R.BFROM RWHERENOT EXISTS(SELECT *FROM SWHERE R.A=S.A and R.B=S.B)If R, S have noduplicates, then canwrite withoutsubqueries(HOW ?)INTERSECT and EXCEPT: not in SQL Server182. QuantifiersProduct ( pname, price, company)Company( cname, city)Find all companies that make some products with price < 100SELECT DISTINCT Company.cnameFROM Company, ProductWHERE Company.cname = Product.company and Product.price < 100SELECT DISTINCT Company.cnameFROM Company, ProductWHERE Company.cname = Product.company and Product.price < 100Existential: easy ! ☺192. QuantifiersProduct ( pname, price, company)Company( cname, city)Find all companies s.t. all of their products have price < 100Universal: hard ! Find all companies that make only products with price < 100same as:202. Quantifiers2. Find all companies s.t. all their products have price < 1001. Find the other companies: i.e. s.t. some product ≥ 100SELECT DISTINCT Company.cnameFROM CompanyWHERE Company.cname IN (SELECT Product.companyFROM ProductWHERE Produc.price >= 100SELECT DISTINCT Company.cnameFROM CompanyWHERE Company.cname IN (SELECT Product.companyFROM ProductWHERE Produc.price >= 100SELECT DISTINCT Company.cnameFROM CompanyWHERE Company.cname NOT IN (SELECT Product.companyFROM ProductWHERE Produc.price >= 100SELECT DISTINCT Company.cnameFROM CompanyWHERE Company.cname NOT IN (SELECT Product.companyFROM ProductWHERE Produc.price >= 100213. Group-by v.s. Nested Query• Find authors who wrote ≥ 10 documents:• Attempt 1: with nested queriesSELECT DISTINCT Author.nameFROM AuthorWHERE count(SELECT Wrote.urlFROM WroteWHERE Author.login=Wrote.login)> 10SELECT DISTINCT
View Full Document
Unlocking...