SQL (almost end)AgendaHAVING ClauseGeneral form of Grouping and AggregationSlide 5AggregationPowerPoint PresentationSlide 8ViewsDefining ViewsA Different ViewSlide 12What Happens When We Query a View ?Types of ViewsUpdating ViewsNon-Updatable ViewsAnswering Queries Using ViewsReusing a Materialized ViewQuery Rewriting Using ViewsAnother ExampleAnd Now?Slide 22Finally…The General ProblemQuerying the WWWSQL (almost end)April 26th, 2002Agenda•HAVING clause•Views •Modifying views•Reusing viewsHAVING ClauseSELECT product, Sum(price * quantity)FROM PurchaseWHERE date > “9/1”GROUP BY productHAVING Sum(quantity) > 30Same query, except that we consider only products that hadat least 100 buyers.HAVING clause contains conditions on aggregates.General form of Grouping and AggregationSELECT SFROM R1,…,RnWHERE C1GROUP BY a1,…,akHAVING C2S = may contain attributes a1,…,ak and/or any aggregates but NO OTHER ATTRIBUTESC1 = is any condition on the attributes in R1,…,RnC2 = is any condition on aggregate expressionsGeneral form of Grouping and AggregationSELECT SFROM R1,…,RnWHERE C1GROUP BY a1,…,akHAVING C2Evaluation steps:1. Compute the FROM-WHERE part, obtain a table with all attributes in R1,…,Rn2. Group by the attributes a1,…,ak 3. Compute the aggregates in C2 and keep only groups satisfying C24. Compute aggregates in S and return the resultAggregationAuthor(login,name)Document(url, title)Wrote(login,url)Mentions(url,word)•Find all authors who wrote at least 10 documents:Select author.nameFrom author, wroteWhere author.login=wrote.loginGroupby author.nameHaving count(wrote.url) > 10Select author.nameFrom author, wroteWhere author.login=wrote.loginGroupby author.nameHaving count(wrote.url) > 10•Find all authors who have a vocabulary over 10000:Select author.nameFrom author, wrote, mentionsWhere author.login=wrote.login and wrote.url=mentions.urlGroupby author.nameHaving count(distinct mentions.word) > 10000Select author.nameFrom author, wrote, mentionsWhere author.login=wrote.login and wrote.url=mentions.urlGroupby author.nameHaving count(distinct mentions.word) > 10000ViewsDefining ViewsViews are relations, except that they are not physically stored.For presenting different information to different usersEmployee(ssn, name, department, project, salary)Payroll has access to Employee, others only to DevelopersCREATE VIEW Developers AS SELECT name, project FROM Employee WHERE department = “Development”CREATE VIEW Developers AS SELECT name, project FROM Employee WHERE department = “Development”A Different ViewPerson(name, city)Purchase(buyer, seller, product, store)Product(name, maker, category)We have a new virtual table:Seattle-view(buyer, seller, product, store)CREATE VIEW Seattle-view AS SELECT buyer, seller, product, store FROM Person, Purchase WHERE Person.city = “Seattle” AND Person.name = Purchase.buyerCREATE VIEW Seattle-view AS SELECT buyer, seller, product, store FROM Person, Purchase WHERE Person.city = “Seattle” AND Person.name = Purchase.buyerA Different ViewSELECT name, storeFROM Seattle-view, ProductWHERE Seattle-view.product = Product.name AND Product.category = “shoes”SELECT name, storeFROM Seattle-view, ProductWHERE Seattle-view.product = Product.name AND Product.category = “shoes”We can later use the view:What Happens When We Query a View ? SELECT name, Seattle-view.store FROM Seattle-view, Product WHERE Seattle-view.product = Product.name AND Product.category = “shoes” SELECT name, Seattle-view.store FROM Seattle-view, Product WHERE Seattle-view.product = Product.name AND Product.category = “shoes”SELECT name, Purchase.storeFROM Person, Purchase, ProductWHERE Person.city = “Seattle” AND Person.name = Purchase.buyer AND Purchase.poduct = Product.name AND Product.category = “shoes”SELECT name, Purchase.storeFROM Person, Purchase, ProductWHERE Person.city = “Seattle” AND Person.name = Purchase.buyer AND Purchase.poduct = Product.name AND Product.category = “shoes”Types of Views•Virtual views:–Used in databases–Computed only on-demand – slow at runtime–Always up to date•Materialized views–Used in data warehouses (but recently also in DBMS)–Precomputed offline – fast at runtime–May have stale dataUpdating ViewsHow can I insert a tuple into a table that doesn’t exist?Employee(ssn, name, department, project, salary)CREATE VIEW Developers AS SELECT name, project FROM Employee WHERE department = “Development”CREATE VIEW Developers AS SELECT name, project FROM Employee WHERE department = “Development”INSERT INTO Developers VALUES(“Joe”, “Optimizer”)INSERT INTO Developers VALUES(“Joe”, “Optimizer”)INSERT INTO Employee VALUES(NULL, “Joe”, NULL, “Optimizer”, NULL)INSERT INTO Employee VALUES(NULL, “Joe”, NULL, “Optimizer”, NULL)If we make thefollowing insertion: It becomes:Non-Updatable ViewsCREATE VIEW Seattle-view AS SELECT seller, product, store FROM Person, Purchase WHERE Person.city = “Seattle” AND Person.name = Purchase.buyerCREATE VIEW Seattle-view AS SELECT seller, product, store FROM Person, Purchase WHERE Person.city = “Seattle” AND Person.name = Purchase.buyerHow can we add the following tuple to the view? (“Joe”, “Shoe Model 12345”, “Nine West”)We need to add “Joe” to Person first. One copy ? More copies ?Answering Queries Using Views•What if we want to use a set of views to answer a query.•Why?–The obvious reason…–Answering queries over web data sources.•Very cool stuff! (i.e., I did a lot of research on this).Reusing a Materialized View•Suppose I have only the result of SeattleView: SELECT buyer, seller, product, store FROM Person, Purchase WHERE Person.city = ‘Seattle’ AND Person.per-name = Purchase.buyer•and I want to answer the query SELECT buyer, seller FROM Person, Purchase WHERE Person.city = ‘Seattle’ AND Person.per-name = Purchase.buyer AND
View Full Document