Introduction to Databases with Stanford University : XML Course-Catalog XPath, XQuery, and XSLT Exercises
XML data courses-noID.xml
-- XML Course-Catalog XPath and XQuery Exercises
1. Return all Title elements (of both departments and courses).
doc("courses.xml")//Title
<Title>Computer Science</Title>
<Title>Programming Methodology</Title>
<Title>Programming Abstractions</Title>
<Title>Computer Organization and Systems</Title>
<Title>Introduction to Probability for Computer Scientists</Title>
<Title>From Languages to Information</Title>
<Title>Compilers</Title>
<Title>Introduction to Databases</Title>
<Title>Artificial Intelligence: Principles and Techniques</Title>
<Title>Structured Probabilistic Models: Principles and Techniques</Title>
<Title>Machine Learning</Title>
<Title>Electrical Engineering</Title>
<Title>Digital Systems I</Title>
<Title>Digital Systems II</Title>
<Title>Linguistics</Title>
<Title>From Languages to Information</Title>
2. Return last names of all department chairs.
doc("courses.xml")//Chair/*/Last_Name
<Last_Name>Widom</Last_Name> <Last_Name>Horowitz</Last_Name> <Last_Name>Levin</Last_Name>
3. Return titles of courses with enrollment greater than 500.
doc("courses.xml")//Course[@Enrollment > 500]/Title
<Title>Programming Methodology</Title>
<Title>Programming Abstractions</Title>
4. Return titles of departments that have some course that takes "CS106B" as a prerequisite.
doc("courses.xml")//Department[Course/Prerequisites/Prereq = "CS106B"]/Title
<Title>Computer Science</Title>
<Title>Electrical Engineering</Title>
5. Return last names of all professors or lecturers who use a middle initial. Don't worry about eliminating duplicates
doc("courses.xml")//(Professor|Lecturer)[Middle_Initial]/Last_Name
6. Return the count of courses that have a cross-listed course (i.e., that have "Cross-listed" in their description).
count(doc("courses.xml")//Course[contains(Description,"Cross-listed")]/Title)
2
7. Return the average enrollment of all courses in the CS department.
let $cenroll := doc("courses.xml")//Department[@Code="CS"]/Course/@Enrollment
return avg($cenroll)
336
8. Return last names of instructors teaching at least one course that has "system" in its description and enrollment greater than 100.
for $b in doc("courses.xml")//Course
where contains($b/Description, "system")
and $b/@Enrollment > 100
return $b/Instructors//Last_Name
9. Return the title of the course with the largest enrollment.
let $m := max(doc("courses.xml")//Course/@Enrollment)
for $c in doc("courses.xml")//Course
where $c/@Enrollment = $m
return $c/Title
-- XML World-Countries XPath and XQuery Exercises
1. Return the area of Mongolia.
doc("countries.xml")//country[@name = "Mongolia"]/data(@area)
2. Return the names of all cities that have the same name as the country in which they are located.
for $c1 in doc("countries.xml")//country
for $c2 in doc("countries.xml")//country
where $c1/city/name = $c2/@name
return <name>{$c1/data(@name)}</name>
3. Return the average population of Russian-speaking countries.
let $pop := doc("countries.xml")//country[language ="Russian"]/@population
return avg($pop)
4. Return the names of all countries that have at least three cities with population greater than 3 million.
for $c in doc("countries.xml")//country
where count($c/city[population > 3000000]) > 3
return $c/data(@name)
China India
5. Create a list of French-speaking and German-speaking countries. The result should take the form:
<result> <French> <country>country-name</country> <country>country-name</country> ... </French> <German> <country>country-name</country> <country>country-name</country> ... </German> </result>
<result>
<French>
{ for $c1 in doc("countries.xml")//country[language = "French"]
return <country> { $c1/data(@name) } </country>
}
</French>
<German>
{ for $c1 in doc("countries.xml")//country[language = "German"]
return <country> {$c1/data(@name) } </country>
}
</German>
</result>
6. Return the countries with the highest and lowest population densities. Note that because the "/" operator has its own meaning in XPath and XQuery, the division operator is infix "div". To compute population density use "(@population div @area)". You can assume density values are unique. The result should take the form:
<result> <highest density="value">country-name</highest> <lowest density="value">country-name</lowest> </result>
let $density := doc("countries.xml")//country/(@population div @area)
let $high := max($density)
let $low := min($density)
return <result>
<highest density="{$high}">
{ for $a in doc("countries.xml")//country[(@population div @area) = $high] return $a/data(@name) }
</highest>
<lowest density="{$low}">
{ for $a in doc("countries.xml")//country[(@population div @area) = $low] return $a/data(@name) }
</lowest> </result>
-- XML Course-Catalog XSLT Exercises
1. Return a list of department titles.
<?xml version="1.0" encoding="ISO-8859-1"?> <xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="Department"> <Title> <xsl:value-of select="Title" /> </Title> </xsl:template> </xsl:stylesheet>
<Title>Computer Science</Title> <Title>Electrical Engineering</Title> <Title>Linguistics</Title>
2. Return a list of department elements with no attributes and two subelements each: the department title and the entire Chair subelement structure.
<?xml version="1.0" encoding="ISO-8859-1"?> <xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="Department">
<Department>
<xsl:copy-of select="Title" />
<xsl:copy-of select="Chair" />
</Department>
</xsl:template>
</xsl:stylesheet>
-- XML World-Countries XSLT Exercises
1. Return all countries with population between 9 and 10 million. Retain the structure of country elements from the original data.
<?xml version="1.0" encoding="ISO-8859-1"?> <xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="country[@population > 9000000 and @population < 10000000]"> <xsl:copy-of select="." /> </xsl:template>
<xsl:template match="text()" /> </xsl:stylesheet>
2. Create a table using HTML constructs that lists all countries that have more than 3 languages. Each row should contain the country name in bold, population, area, and number of languages. Sort the rows in descending order of number of languages. No header is needed for the table, but use
to make it format nicely, should you choose to check your result in a browser. (Hint: You may find the data-type and order attributes of <xsl:sort> to be useful.) <xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<html>
<table border="1">
<xsl:for-each select="/countries/country">
<xsl:sort order="descending" select="count(language)" />
<xsl:if test="count(language) > 3">
<tr>
<td><b><xsl:value-of select="@name" /></b></td>
<td><xsl:value-of select="@population" /></td>
<td><xsl:value-of select="@area" /></td>
<td><xsl:value-of select="count(language)" /></td>
</tr>
</xsl:if>
</xsl:for-each>
</table>
</html>
</xsl:template>
</xsl:stylesheet>
3. Create an alternate version of the countries database: for each country, include its name and population as sublements, and the number of languages and number of cities as attributes (called "languages" and "cities" respectively).
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="yes" omit-xml-declaration="yes" />
<xsl:template match="*|@*|text()">
<xsl:copy>
<xsl:apply-templates select="*|@*|text()" />
</xsl:copy>
</xsl:template>
<xsl:template match="country">
<country cities="{ count(city) }" languages="{ count(language) }">
<name><xsl:value-of select="@name" /></name>
<population><xsl:value-of select="@population" /></population>
</country>
</xsl:template>
</xsl:stylesheet>
