Lecture 4: More SQLAgendaSubqueries Returning RelationsConditions on TuplesCorrelated QueriesComplex Correlated QueryAggregationAggregation: CountSlide 9Simple AggregationSimple AggregationsGrouping and AggregationSlide 13First compute the FROM-WHERE clauses (date > “10/1”) then GROUP BY product:Then, aggregateGROUP BY v.s. Nested QueriesAnother ExampleHAVING ClauseGeneral form of Grouping and AggregationSlide 20Slide 21PowerPoint PresentationSlide 23Slide 24INTERSECT and EXCEPT: Not in SQL ServerNull Values and OuterjoinsSlide 27Slide 28Slide 29Slide 30Slide 31Slide 32Outer JoinsModifying the DatabaseInsertionsSlide 36Insertion: an ExampleSlide 38Slide 39DeletionsUpdatesData Definition in SQLData Types in SQLCreating TablesDeleting or Modifying a TableDefault ValuesIndexesSlide 48Creating IndexesSlide 50Slide 51Defining ViewsA Different ViewSlide 54What Happens When We Query a View ?Types of ViewsUpdating ViewsNon-Updatable Views1Lecture 4: More SQLMonday, January 13th, 20032Agenda•Finish subqueries, correlated queries.•Grouping and aggregation•Creating a schema•Modifying the database•Defining views3Subqueries Returning Relations SELECT name FROM Product WHERE price > ALL (SELECT price FROM Purchase WHERE maker=‘Gizmo-Works’) SELECT name FROM Product WHERE price > ALL (SELECT price FROM Purchase WHERE maker=‘Gizmo-Works’)Product ( pname, price, category, maker)Find products that are more expensive than all those producedBy “Gizmo-Works”You can also use: s > ALL R s > ANY R EXISTS R4Conditions on Tuples SELECT DISTINCT Company.name FROM Company, Product WHERE Company.name= Product.maker AND (Product.name,price) IN (SELECT Purchase.product, Purchase.price) FROM Purchase WHERE Purchase.buyer = “Joe Blow”); SELECT DISTINCT Company.name FROM Company, Product WHERE Company.name= Product.maker AND (Product.name,price) IN (SELECT Purchase.product, Purchase.price) FROM Purchase WHERE Purchase.buyer = “Joe Blow”);May not work in SQL server...5Correlated Queries SELECT DISTINCT title FROM Movie AS x WHERE year <> ANY (SELECT year FROM Movie WHERE title = x.title); SELECT DISTINCT title FROM Movie AS x WHERE year <> ANY (SELECT year FROM Movie WHERE title = x.title); Movie (title, year, director, length) Find movies whose title appears more than once.Note (1) scope of variables (2) this can still be expressed as single SFWcorrelation6Complex Correlated QueryProduct ( pname, price, category, maker, year)•Find products (and their manufacturers) that are more expensive than all products made by the same manufacturer before 1972Powerful, but much harder to optimize !SELECT DISTINCT pname, makerFROM Product AS xWHERE price > ALL (SELECT price FROM Product AS y WHERE x.maker = y.maker AND y.year < 1972);SELECT DISTINCT pname, makerFROM Product AS xWHERE price > ALL (SELECT price FROM Product AS y WHERE x.maker = y.maker AND y.year < 1972);7AggregationSELECT Avg(price)FROM ProductWHERE maker=“Toyota”SELECT Avg(price)FROM ProductWHERE maker=“Toyota”SQL supports several aggregation operations: SUM, MIN, MAX, AVG, COUNT8Aggregation: CountSELECT Count(*)FROM ProductWHERE year > 1995SELECT Count(*)FROM ProductWHERE year > 1995Except COUNT, all aggregations apply to a single attribute9Aggregation: CountCOUNT applies to duplicates, unless otherwise stated:SELECT Count(category) same as Count(*)FROM ProductWHERE year > 1995Better:SELECT Count(DISTINCT category)FROM ProductWHERE year > 199510Simple AggregationPurchase(product, date, price, quantity)Example 1: find total sales for the entire databaseSELECT Sum(price * quantity)FROM PurchaseExample 1’: find total sales of bagelsSELECT Sum(price * quantity)FROM PurchaseWHERE product = ‘bagel’11Simple AggregationsProduct Date Price Quantity Bagel 10/21 0.85 15 Banana 10/22 0.52 7 Banana 10/19 0.52 17 Bagel 10/20 0.85 20 Purchase12Grouping and AggregationUsually, we want aggregations on certain parts of the relation.Purchase(product, date, price, quantity)Example 2: find total sales after 10/1 per product.SELECT product, Sum(price*quantity) AS TotalSalesFROM PurchaseWHERE date > “10/1”GROUPBY productSELECT product, Sum(price*quantity) AS TotalSalesFROM PurchaseWHERE date > “10/1”GROUPBY productLet’s see what this means…13Grouping and Aggregation1. Compute the FROM and WHERE clauses.2. Group by the attributes in the GROUPBY3. Select one tuple for every group (and apply aggregation)SELECT can have (1) grouped attributes or (2) aggregates.14First compute the FROM-WHERE clauses (date > “10/1”) then GROUP BY product:Product Date Price Quantity Banana 10/19 0.52 17 Banana 10/22 0.52 7 Bagel 10/20 0.85 20 Bagel 10/21 0.85 1515Then, aggregateProduct TotalSales Bagel $29.75 Banana $12.48 SELECT product, Sum(price*quantity) AS TotalSalesFROM PurchaseWHERE date > “10/1”GROUPBY productSELECT product, Sum(price*quantity) AS TotalSalesFROM PurchaseWHERE date > “10/1”GROUPBY product16GROUP BY v.s. Nested QueriesSELECT product, Sum(price*quantity) AS TotalSalesFROM PurchaseWHERE date > “10/1”GROUP BY productSELECT product, Sum(price*quantity) AS TotalSalesFROM PurchaseWHERE date > “10/1”GROUP BY productSELECT DISTINCT x.product, (SELECT Sum(y.price*y.quantity) FROM Purchase y WHERE x.product = y.product AND y.date > ‘10/1’) AS TotalSalesFROM Purchase xWHERE x.date > “10/1”SELECT DISTINCT x.product, (SELECT Sum(y.price*y.quantity) FROM Purchase y
View Full Document
Unlocking...