XML Course-Catalog XPath and XQuery Exercises

core-set.xml

<!-- 1. Return all Title elements (of both departments and courses). -->

//Title


<!-- 2. Return last names of all department chairs. -->

//Chair//Last_Name

//Chair/*/Last_Name

//Chair/Professor/Last_Name


<!-- 3. Return titles of courses with enrollment greater than 500. -->

//Course[@Enrollment > 500]/Title


<!-- 4. Return titles of departments that have some course that takes "CS106B" as a prerequisite. -->

//Department[Course/Prerequisites/Prereq = 'CS106B']/Title

for $dep in //Department
where $dep/Course/Prerequisites/Prereq = 'CS106B'
return $dep/Title


<!-- 5. Return last names of all professors or lecturers who use a middle initial. Don't worry about eliminating duplicates. -->

//(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(//Course[contains(Description, 'Cross-listed')])


<!-- 7. Return the average enrollment of all courses in the CS department. -->

avg(//Department[@Code = 'CS']/Course/data(@Enrollment))


<!-- 8. Return last names of instructors teaching at least one course that has "system" in its description and enrollment greater than 100. -->

//Course[contains(Description, 'system') and @Enrollment > 100]/Instructors/*/Last_Name


<!-- 9. Return the title of the course with the largest enrollment. -->

//Course[@Enrollment = max(//Course/data(@Enrollment))]/Title

extras.xml

<!-- 1. Return the course number of the course that is cross-listed as "LING180". -->

//Course[contains(Description, 'LING180')]/data(@Number)


<!-- 2. Return course numbers of courses that have the same title as some other course. (Hint: You might want to use the "preceding" and "following" navigation axes for this query, which were not covered in the video or our demo script; they match any preceding or following node, not just siblings.)  -->

//Course[Title = following::Course/Title or Title = preceding::Course/Title]/data(@Number)

//Course[Title = following::*/Title or Title = preceding::*/Title]/data(@Number)

for $course in //Course
where $course/Title = $course/following::*/Title or $course/Title = $course/preceding::*/Title
return $course/data(@Number)


<!-- 3. Return course numbers of courses taught by an instructor with first name "Daphne" or "Julie". -->

//Course[Instructors/*/First_Name = 'Daphne' or Instructors/*/First_Name = 'Julie']/data(@Number)

for $course in //Course
let $instructor := $course/Instructors/*
where $instructor/First_Name = 'Daphne' or $instructor/First_Name = 'Julie'
return $course/data(@Number)


<!-- 4. Return the number (count) of courses that have no lecturers as instructors. -->

count(//Course[count(Instructors/Lecturer) = 0])

return count(
  for $course in //Course
  where count($course/Instructors/Lecturer) = 0
  return $course
)


<!-- 5. Return titles of courses taught by the chair of a department. For this question, you may assume that all professors have distinct last names. -->

//Course[Instructors/Professor/Last_Name = parent::Department/Chair/Professor/Last_Name]/Title

//Course[Instructors/Professor/Last_Name = //Chair/Professor/Last_Name]/Title

for $course in //Course
where $course/Instructors/Professor/Last_Name = $course/parent::Department/Chair/Professor/Last_Name
return $course/Title


<!-- 6. Return titles of courses that have both a lecturer and a professor as instructors. Return each title only once. -->

//Course[count(Instructors/Professor) > 0 and count(Instructors/Lecturer) > 0]/Title

for $course in //Course
where count($course/Instructors/Professor) > 0 and count($course/Instructors/Lecturer) > 0
return $course/Title


<!-- 7. Return titles of courses taught by a professor with the last name "Ng" but not by a professor with the last name "Thrun". -->

//Course[Instructors/Professor[Last_Name = 'Ng'] and Title != //Course[Instructors/Professor[Last_Name = 'Thrun']]/Title]/Title

for $course in (
  for $c in //Course
  where every $name in $c/Instructors/Professor/Last_Name satisfies $name != 'Thrun'
  return $c
)
where $course/Instructors/Professor/Last_Name = 'Ng'
return $course/Title


<!-- 8. Return course numbers of courses that have a course taught by Eric Roberts as a prerequisite. -->

//Course[Prerequisites/Prereq = //Course[Instructors/*[First_Name = 'Eric' and Last_Name = 'Roberts']]/data(@Number)]/data(@Number)

let $courses := //Course 
for $course in $courses
where $course/Prerequisites/Prereq = (
  for $c in $courses
  let $instructor := $c/Instructors/*
  where $instructor/First_Name = 'Eric' and $instructor/Last_Name = 'Roberts'
  return $c/data(@Number)
)
return $course/data(@Number)


<!-- 9. Create a summary of CS classes: List all CS department courses in order of enrollment. For each course include only its Enrollment (as an attribute) and its Title (as a subelement). -->

<Summary>
  {
    for $course in //Department[@Code = 'CS']/Course
    order by xs:int($course/@Enrollment)
    return
      <Course>
        { $course/@Enrollment }
        { $course/Title }
      </Course>
  }
</Summary>


<!-- 10. Return a "Professors" element that contains as subelements a listing of all professors in all departments, sorted by last name with each professor appearing once. The "Professor" subelements should have the same structure as in the original data. For this question, you may assume that all professors have distinct last names. Watch out -- the presence/absence of middle initials may require some special handling. (This problem is quite challenging; congratulations if you get it right.) -->

<Professors>
  {
    for $ln in distinct-values(//Professor/Last_Name)
    let $professor := //Professor[Last_Name = $ln]
    for $fn in distinct-values($professor/First_Name)
    order by $ln
    return
      <Professor>
        <First_Name>{ $fn }</First_Name>
        {
          for $mn in $professor/Middle_Initial
          return $mn
        }      
        <Last_Name>{ $ln }</Last_Name>
      </Professor>
  }
</Professors>


<!-- 11. Expanding on the previous question, create an inverted course listing: Return an "Inverted_Course_Catalog" element that contains as subelements professors together with the courses they teach, sorted by last name. You may still assume that all professors have distinct last names. The "Professor" subelements should have the same structure as in the original data, with an additional single "Courses" subelement under Professor, containing a further "Course" subelement for each course number taught by that professor. Professors who do not teach any courses should have no Courses subelement at all. (This problem is very challenging; extra congratulations if you get it right.) -->

<Inverted_Course_Catalog>
  {
    for $ln in distinct-values(//Professor/Last_Name)
    let $professor := //Professor[Last_Name = $ln]
    for $fn in distinct-values($professor/First_Name)
    let $courses := //Course[Instructors/Professor/Last_Name = $ln]
    order by $ln
    return
      <Professor>
        <First_Name>{ $fn }</First_Name>
        {
          for $mn in $professor/Middle_Initial
          return $mn
        }      
        <Last_Name>{ $ln }</Last_Name>
        {
          if (count($courses) > 0)
          then
            <Courses>
              {
                for $course in $courses
                return
                  <Course>
                    { $course/data(@Number) }
                  </Course>
              }
            </Courses>
          else ()
        }
      </Professor>
  }
</Inverted_Course_Catalog>