DOC PREVIEW
UW CSE 444 - Introduction to Database Systems

This preview shows page 1-2-3-27-28-29 out of 29 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 29 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 29 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 29 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 29 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 29 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 29 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 29 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

Introduction to Database SystemsCSE 444Lecture 26: XQueryCSE 444 - Summer 2010 1dAbout the Final 2ndMidterm• Open book and open notes – But you won’t have time to read during final!– No laptops, no mobile devices • Topics: Basically 2ndhalf of the course– No ER diagrams, no FDs, transactions, serializibility.No Pig Latin details (i e no“write a Pig Latin–No Pig Latin details (i.e., no write a Pig Latin program to do X”), but know the basics of the language and its relation to Map-ReduceCSE 444 - Summer 2010 2Review Advice• Review lectures• Review hw2 and hw3 • Review project 3 and project 4• Practice sample finals posted on websiteCSE 444 - Summer 2010 3XQuery• Standard for high-level querying of databases containing data in XML form• Uses XPath to express more complex queries– An XPath expression is a simple XQuery• Readings– Section 12.2 – [Nothing about XQuery in old Edition]4CSE 444 - Summer 2010FLWR (“Flower”) ExpressionsZero or moreFOR ... LET... Zero or moreZero or moreWHERE...RETURN...Zero or oneExactly one5CSE 444 - Summer 2010FOR-WHERE-RETURNFind all book titles published after 1995:FOR $x IN document("bib.xml")/bib/bookWHERE $x/year/text() > 1995RETURN $x/titleResult:Result:<title> abc </title><title> def </title><title>ghi</title>6<title> ghi</title>CSE 444 - Summer 2010FOR-WHERE-RETURNEquivalently (perhaps more geekish)FOR $x IN document("bib.xml")/bib/book[year/text() > 1995] /title RETURN $xAnd even shorter:document("bib.xml")/bib/book[year/text() > 1995] /title 7CSE 444 - Summer 2010COERCIONThe query:FOR $x IN document("bib.xml")/bib/book[year > 1995] /title RETURN $xIs rewritten by the system into:yyFOR $x IN document("bib.xml")/bib/book[year/text() > 1995] /title RETURN$x8RETURN$xCSE 444 - Summer 2010FOR-WHERE-RETURN• Find all book titles and the year when they were published:FOR $x IN document("bib.xml")/ bib/bookRETURN <answer> <title>{$x/title/text() } </title><title>{ $x/title/text() } </title><year>{ $x/year/text() } </year></answer>Result:<answer> <title> abc </title> <year> 1995 </ year > </answer><answer> <title> def </title> < year > 2002 </ year > </answer> <answer> <title> ghk </title> < year > 1980 </ year > </answer>FOR-WHERE-RETURN• Notice the use of “{“ and “}”• What is the result without them ?FOR $x IN document("bib.xml")/ bib/bookRETURN<answer>RETURNanswer <title> $x/title/text() </title><year> $x/year/text() </year></answer></answer>10CSE 444 - Summer 2010FOR-WHERE-RETURN• Notice the use of “{“ and “}”• What is the result without them ?FOR $x IN document("bib.xml")/ bib/bookRETURN <answer> titl$/titl/t t() /titl<title> $x/title/text() </title><year> $x/year/text() </year></answer><answer> <title> $x/title/text() </title> <year> $x/year/text() </year> </answer><answer> <title> $x/title/text() </title> <year> $x/year/text() </year> </answer>11<answer> <title> $x/title/text() </title> <year> $x/year/text() </year> </answer>NestingFor each author of a book by MorganFor each author of a book by Morgan Kaufmann, list all books she published:FOR$bINdocument(“bib xml”)/bibFOR$bIN document( bib.xml)/bib,$a IN $b/book[publisher /text()=“Morgan Kaufmann”]/authorRETURN <result>{ $a,FOR $t IN $b/book[author/text()=$a/text()]/titleRETURN$tRETURN$t}</result>12In the RETURN clause comma concatenates XML fragmentsResult<result><author>Jones</author><title> abc </title><title> def </title>/lt</result><result><author> Smith </author><author> Smith </author><title> ghi </title></result>13CSE 444 - Summer 2010AggregatesFind all books with more than 3 authors:FOR$xINdocument("bib xml")/bib/bookFOR$xIN document("bib.xml")/bib/bookWHERE count($x/author)>3 RETURN $xcount = a function that countsavgcomputes the averageavg= computes the averagesum = computes the sumdistinct-values = eliminates duplicates14CSE 444 - Summer 2010AggregatesSame thing:FOR $x IN document("bib.xml")/bib/book[count(author)>3] RETURN $x15CSE 444 - Summer 2010Eliminating DuplicatesPrint all authors:FOR $a IN distinct-values($b/book/author/text())RETURN<author>{$a}</author>RETURN<author> { $a }</author>Note: distinct-values applies ONLY to values, NOT elements16CSE 444 - Summer 2010The LET ClauseFind books whose price is larger than average:FOR $b in document(“bib.xml”)/bibLET$a:=avg($b/book/price/text())FOR $x in $b/bookWHERE$x/price/text() > $aRETURN$xRETURN$x17CSE 444 - Summer 2010FlatteningC t li t f ( th titl )•Compute a list of (author, title)pairsInput:<book><title> Databases </title>FOR $b IN document("bib.xml")/bib/book,$xIN$b/title/text()<author> Widom </author><author> Ullman </author></book>$xIN$b/title/text(),$y IN$b/author/text()RETURN<answer><title>{$x}</title>Output:<answer><title> Databases </title><author>Widom</author><title> { $x} </title><author> { $y } </author></answer><author> Widom</author></answer><answer><title> Databases </title><title> Databases </title><author> Ullman </author></answer>Re-grouping• For each author, return all titles of her/his booksRltFOR $b IN document("bib.xml")/bib,$x IN$b/book/author/text()RETURNResult:<answer><author> efg </author><title>abc</title>RETURN<answer><author> { $x } </author>{FOR$IN$b/book[a thor/te t()$]/title<title> abc</title><title> klm </title>. . . .</answer>{ FOR$yIN$b/book[author/text()=$x]/titleRETURN $y }</answer>What aboutduplicate</answer>19authors ?CSE 444 - Summer 2010Re-grouping• Same, but eliminate duplicate authors:FOR$bINdocument("bib xml")/bibFOR$bIN document( bib.xml)/bibLET $a := distinct-values($b/book/author/text())FOR $x IN $aRETURNRETURN<answer><author> $x </author>{ FOR $y IN $b/book[author/text()=$x]/titleRETURN $y }</answer>20</answer>CSE 444 - Summer 2010Re-grouping• Same thing:$FOR$b IN document("bib.xml")/bib,$x IN distinct-values($b/book/author/text())RETURN<answer><author> $x </author>{FOR$yIN$b/book[author/text()=$x]/title{ FOR$yIN$b/book[author/text()=$x]/titleRETURN $y }</answer>21CSE 444 - Summer 2010SQL and XQuery Side-by-sideProduct(pidname maker price)Find all product names pricesProduct(pid, name, maker, price)Find all product names, prices,sort by priceSELECT x.name,x.priceFROM ProductxFOR $x in document(“db.xml”)/db/Product/rowORDER BY$x/price/text()RETURN<answer>FROM Product xORDER BY x.priceRETURN<answer>{ $x/name, $x/price }</answer>SQLXQuery22yCSE 444 - Summer 2010XQuery’s Answer<answer><name> abc </name>i7/i<price> 7 </price></answer><answer><name> def </name><price> 23


View Full Document

UW CSE 444 - Introduction to Database Systems

Documents in this Course
XML

XML

48 pages

SQL

SQL

25 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 Introduction to Database Systems
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 Introduction to Database Systems 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 Introduction to Database Systems 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?