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 Computer Science Programming Methodology Programming Abstractions Computer Organization and Systems Introduction to Probability for Computer Scientists From Languages to Information Compilers Introduction to Databases Artificial Intelligence: Principles and Techniques</Title> Structured Probabilistic Models: Principles and Techniques</Title> Machine Learning</Title> Electrical Engineering</Title> Digital Systems I</Title> Digital Systems II</Title> Linguistics</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 Programming Methodology</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 Computer Science</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 {$c1/data(@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:

<French> country-name country-name ... </French> <German> country-name country-name ... </German>

<French> { for $c1 in doc("countries.xml")//country[language = "French"] return { $c1/data(@name) } }  	</French> <German> { for $c1 in doc("countries.xml")//country[language = "German"] return {$c1/data(@name) } }  	</German>

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:

country-name country-name

let $density := doc("countries.xml")//country/(@population div @area) let $high := max($density) let $low := min($density) return <highest density="{$high}"> {	 for $a in doc("countries.xml")//country[(@population div @area) = $high] return $a/data(@name) }   	        <lowest  density="{$low}"> {	 for $a in doc("countries.xml")//country[(@population div @area) = $low] return $a/data(@name) }

-- 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">  <xsl:value-of select="Title" /> </Title> </xsl:template> </xsl:stylesheet>

Computer Science</Title> Electrical Engineering</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 &gt; 9000000 and @population &lt; 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 </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) }"> <xsl:value-of select="@name" /> <xsl:value-of select="@population" /> </xsl:template> </xsl:stylesheet>