Lecture 3: SQL and Project IssuesWelcome to an Award Course!The ProjectYour NumbersOutlineFirst Unintuitive SQLismRenaming ColumnsUnion, Intersection, DifferencePowerPoint PresentationConserving DuplicatesSubqueriesSlide 12Subqueries Returning RelationsSlide 14Removing DuplicatesSlide 16Slide 17Question for Database Fans and their FriendsConditions on TuplesCorrelated QueriesComplex Correlated QueryAggregationAggregation: CountSlide 24Simple AggregationSimple AggregationsGrouping and AggregationSlide 28First 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 35Slide 36Slide 37Slide 38Slide 391Lecture 3: SQL and Project IssuesFriday, January 10th, 20032Welcome to an Award Course!•Who I am.•Where I was.•Our Award.•The course project•Down to business3The Project•3 Phases.•I’ll tell you about them as they happen.•Phase 1:–You get a number between 1 and 3.–The number determines one of 3 domains.–You create a schema, and some data, and send it to us (by Wednesday)–You implement a database with a simple web interface (end of week 3).4Your Numbers•A-H: Domain 1 - Inventory•I-ME(Mehlhorn): Domain 2 - Billing•MO(Moore) - Z: Domain 3 - Shipping5Outline•Unions, intersections, differences(6.2.5, 6.4.2)•Subqueries (6.3)•Aggregations (6.4.3 – 6.4.6)Hint for reading the textbook: read the entire chapter 6 !Reading assignment from “SQL for Nerds”: chapter 4, “Morecomplex queries” (you will find it very useful for subqueries)6First Unintuitive SQLismSELECT DISTINCT R.AFROM R, S, TWHERE R.A=S.A OR R.A=T.ASELECT DISTINCT R.AFROM R, S, TWHERE R.A=S.A OR R.A=T.ALooking for R (S T)But what happens if T is empty?7Renaming ColumnsPName Price Category ManufacturerGizmo $19.99 Gadgets GizmoWorksPowergizmo $29.99 Gadgets GizmoWorksSingleTouch $149.99 Photography CanonMultiTouch $203.99 Household HitachiSELECT Pname AS prodName, Price AS askPriceFROM ProductWHERE Price > 100SELECT Pname AS prodName, Price AS askPriceFROM ProductWHERE Price > 100ProductprodName askPriceSingleTouch $149.99MultiTouch $203.99Query withrenaming8Union, Intersection, Difference(SELECT name FROM Person WHERE City=“Seattle”) UNION(SELECT name FROM Person, Purchase WHERE buyer=name AND store=“The Bon”)(SELECT name FROM Person WHERE City=“Seattle”) UNION(SELECT name FROM Person, Purchase WHERE buyer=name AND store=“The Bon”)Similarly, you can use INTERSECT and EXCEPT.You must have the same attribute names (otherwise: rename).9(SELECT DISTINCT R.A FROM R) INTERSECT ( (SELECT S.A FROM S) UNION (SELECT T.A FROM T))(SELECT DISTINCT R.A FROM R) INTERSECT ( (SELECT S.A FROM S) UNION (SELECT T.A FROM T))10Conserving Duplicates(SELECT name FROM Person WHERE City=“Seattle”) UNION ALL(SELECT name FROM Person, Purchase WHERE buyer=name AND store=“The Bon”)(SELECT name FROM Person WHERE City=“Seattle”) UNION ALL(SELECT name FROM Person, Purchase WHERE buyer=name AND store=“The Bon”)11SubqueriesA subquery producing a single value:In this case, the subquery returns one value.If it returns more, it’s a run-time error.SELECT Purchase.productFROM PurchaseWHERE buyer = (SELECT name FROM Person WHERE ssn = ‘123456789‘);SELECT Purchase.productFROM PurchaseWHERE buyer = (SELECT name FROM Person WHERE ssn = ‘123456789‘);12Can say the same thing without a subquery:This is equivalent to the previous one when the ssn is a keyand ‘123456789’ exists in the database;otherwise they are different.SELECT Purchase.productFROM Purchase, PersonWHERE buyer = name AND ssn = ‘123456789‘SELECT Purchase.productFROM Purchase, PersonWHERE buyer = name AND ssn = ‘123456789‘13Subqueries Returning Relations SELECT Company.name FROM Company, Product WHERE Company.name=Product.maker AND Product.name IN (SELECT Purchase.product FROM Purchase WHERE Purchase .buyer = ‘Joe Blow‘); SELECT Company.name FROM Company, Product WHERE Company.name=Product.maker AND Product.name IN (SELECT Purchase.product FROM Purchase WHERE Purchase .buyer = ‘Joe Blow‘);Find companies who manufacture products bought by Joe Blow.Here the subquery returns a set of values: no moreruntime errors.14Subqueries Returning Relations SELECT Company.name FROM Company, Product, Purchase WHERE Company.name= Product.maker AND Product.name = Purchase.product AND Purchase.buyer = ‘Joe Blow’ SELECT Company.name FROM Company, Product, Purchase WHERE Company.name= Product.maker AND Product.name = Purchase.product AND Purchase.buyer = ‘Joe Blow’Equivalent to:Is this query equivalent to the previous one ?Beware of duplicates !15Removing Duplicates SELECT Company.name FROM Company, Product, Purchase WHERE Company.name= Product.maker AND Product.name = Purchase.product AND Purchase.buyer = ‘Joe Blow’ SELECT Company.name FROM Company, Product, Purchase WHERE Company.name= Product.maker AND Product.name = Purchase.product AND Purchase.buyer = ‘Joe Blow’ SELECT DISTINCT Company.name FROM Company, Product, Purchase WHERE Company.name= Product.maker AND Product.name = Purchase.product AND Purchase.buyer = ‘Joe Blow’ SELECT DISTINCT Company.name FROM Company, Product, Purchase WHERE Company.name= Product.maker AND Product.name = Purchase.product AND Purchase.buyer = ‘Joe Blow’Multiple copies Single copies16Removing Duplicates SELECT DISTINCT Company.name FROM Company, Product, Purchase WHERE Company.name= Product.maker AND Product.name = Purchase.product AND Purchase.buyer = ‘Joe Blow’ SELECT DISTINCT Company.name FROM Company, Product, Purchase WHERE Company.name= Product.maker AND Product.name = Purchase.product AND
View Full Document