DOC PREVIEW
UW CSE 444 - SQL

This preview shows page 1-2-24-25 out of 25 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 25 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 25 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 25 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 25 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 25 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

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

UW CSE 444 - SQL

Documents in this Course
XML

XML

48 pages

SQL

SQL

42 pages

Recovery

Recovery

30 pages

SQL

SQL

36 pages

Indexes

Indexes

35 pages

Security

Security

36 pages

Wrap-up

Wrap-up

6 pages

SQL

SQL

37 pages

More SQL

More SQL

48 pages

SQL

SQL

35 pages

XML

XML

46 pages

Triggers

Triggers

26 pages

Load more
Download SQL
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view SQL and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view SQL 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?