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