Lecture 3: More SQLAgendaSQL PlanJoins in SQLJoinsSlide 6Slide 7Slide 8When are two tables related?Disambiguating AttributesTuple VariablesSlide 12Meaning (Semantics) of SQL QueriesSlide 14Renaming ColumnsFirst Unintuitive SQLismUnion, Intersection, DifferenceConserving DuplicatesSubqueriesSlide 20Subqueries Returning RelationsSlide 22Removing DuplicatesSlide 24Slide 25Question for Database Fans and their FriendsConditions on TuplesCorrelated QueriesComplex Correlated QueryAggregationAggregation: CountSlide 32Simple AggregationSimple AggregationsGrouping and AggregationSlide 36First 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 43Slide 44Slide 45Slide 46Slide 47Exercises1Lecture 3: More SQLFriday, January 9, 20042Agenda•Homework #1 on the web site today.•Sign up for the mailing list!•Next Friday:–In class ‘activity’–Plan for a couple of hours later that afternoon.•Until then:–SQL–Some conceptual modeling (Chapter 2)3SQL Plan•Joins (6.2)•Set operations (unions, differences)•Sub-queries (6.3)•Grouping and aggregation (6.4)•More will come later.4Joins in SQL•Connect two or more tables:PName Price Category ManufacturerGizmo $19.99 Gadgets GizmoWorksPowergizmo $29.99 Gadgets GizmoWorksSingleTouch $149.99 Photography CanonMultiTouch $203.99 Household HitachiProductCompanyCname StockPrice CountryGizmoWorks 25 USACanon 65 JapanHitachi 15 JapanWhat isthe connectionbetweenthem ?5Joins Product (pname, price, category, manufacturer)Company (cname, stockPrice, country)Find all products under $200 manufactured in Japan;return their names and prices. SELECT pname, priceFROM Product, CompanyWHERE manufacturer=cname AND country=‘Japan’ AND price <= 200SELECT pname, priceFROM Product, CompanyWHERE manufacturer=cname AND country=‘Japan’ AND price <= 200Joinbetween Productand Company6Joins in SQLPName Price Category ManufacturerGizmo $19.99 Gadgets GizmoWorksPowergizmo $29.99 Gadgets GizmoWorksSingleTouch $149.99 Photography CanonMultiTouch $203.99 Household HitachiProductCompanyCname StockPrice CountryGizmoWorks 25 USACanon 65 JapanHitachi 15 JapanPName PriceSingleTouch $149.99SELECT pname, priceFROM Product, CompanyWHERE manufacturer=cname AND country=‘Japan’ AND price <= 200SELECT pname, priceFROM Product, CompanyWHERE manufacturer=cname AND country=‘Japan’ AND price <= 2007Joins Product (pname, price, category, manufacturer)Company (cname, stockPrice, country)Find all countries that manufacture some product in the ‘Gadgets’ category.SELECT countryFROM Product, CompanyWHERE manufacturer=cname AND category=‘Gadgets’SELECT countryFROM Product, CompanyWHERE manufacturer=cname AND category=‘Gadgets’8Joins Product (pname, price, category, manufacturer)Purchase (buyer, seller, store, product)Person(persname, phoneNumber, city)Find names of people living in Seattle that bought some product in the ‘Gadgets’ category, and the names of the stores they bought such product from SELECT DISTINCT persname, storeFROM Person, Purchase, ProductWHERE persname=buyer AND product = pname AND city=‘Seattle’ AND category=‘Gadgets’SELECT DISTINCT persname, storeFROM Person, Purchase, ProductWHERE persname=buyer AND product = pname AND city=‘Seattle’ AND category=‘Gadgets’9When are two tables related?•You guess they are•I tell you so•Foreign keys are a method for schema designers to tell you so (7.1)–A foreign key states that a column is a reference to the key of another tableex: Product.manufacturer is foreign key of Company–Gives information and enforces constraint10Disambiguating Attributes•Sometimes two relations have the same attr:Person(pname, address, worksfor)Company(cname, address)SELECT DISTINCT pname, addressFROM Person, CompanyWHERE worksfor = cnameSELECT DISTINCT pname, addressFROM Person, CompanyWHERE worksfor = cnameSELECT DISTINCT Person.pname, Company.addressFROM Person, CompanyWHERE Person.worksfor = Company.cnameSELECT DISTINCT Person.pname, Company.addressFROM Person, CompanyWHERE Person.worksfor = Company.cnameWhichaddress ?11Tuple VariablesSELECT DISTINCT x.storeFROM Purchase AS x, Purchase AS yWHERE x.product = y.product AND y.store = ‘BestBuy’SELECT DISTINCT x.storeFROM Purchase AS x, Purchase AS yWHERE x.product = y.product AND y.store = ‘BestBuy’Find all stores that sold at least one product that the store‘BestBuy’ also sold:Answer (store)Product (pname, price, category, manufacturer)Purchase (buyer, seller, store, product)Person(persname, phoneNumber, city)12Tuple VariablesGeneral rule: tuple variables introduced automatically by the system: Product (name, price, category, manufacturer)Becomes: Doesn’t work when Product occurs more than once:In that case the user needs to define variables explicitly. SELECT name FROM Product WHERE price > 100 SELECT name FROM Product WHERE price > 100 SELECT Product.name FROM Product AS Product WHERE Product.price > 100 SELECT Product.name FROM Product AS Product WHERE Product.price > 10013Meaning (Semantics) of SQL QueriesSELECT a1, a2, …, akFROM R1 AS x1, R2 AS x2, …, Rn AS xnWHERE Conditions1. Nested loops:Answer = {}for x1 in R1 do for x2 in R2 do ….. for xn in Rn do if Conditions then Answer = Answer {(a1,…,ak)}return AnswerAnswer = {}for x1 in R1 do for x2 in R2 do ….. for xn in Rn do if Conditions then Answer = Answer {(a1,…,ak)}return Answer14Meaning (Semantics) of SQL QueriesSELECT a1, a2, …, akFROM R1 AS x1, R2 AS x2, …, Rn AS xnWHERE Conditions2. Parallel assignmentDoesn’t impose any order !Answer = {}for all assignments x1 in R1, …, xn in Rn do if Conditions then Answer = Answer {(a1,…,ak)}return AnswerAnswer = {}for all assignments x1 in R1, …, xn in Rn do if Conditions then Answer = Answer {(a1,…,ak)}return Answer15Renaming 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
View Full Document
Unlocking...