------------------------------------------------------------------
-- Section 9.2.4 FLWOR Queries
------------------------------------------------------------------
--
set long 1024
--
-- Query 9.1 
-- Get all students.
--
xquery
let $d := doc("/public/book/gb.xml")
for $s in $d/gradebook/students/student
return $s
/
--
-- Query 9.2 
-- Get distinct student id values from all <enroll> nodes.
--
xquery
let $d := doc("/public/book/gb.xml")
for $c in distinct-values($d//enroll/@sid)
return $c
/
--
-- Query 9.3 
-- Get students whose last name starts with "Raj".
--
xquery
let $d := doc("/public/book/gb.xml")
for $s in $d/gradebook/students/student
where starts-with($s/lname,"Raj")
return $s
/
--
-- Query 9.4 
-- Get names of students who are enrolled in course csc226.
--
xquery
<studentsEnrolledInCsc226> 
{
  let $d := doc("/public/book/gb.xml")
  for $s in $d/gradebook/students/student,
      $c in $d/gradebook/courses/course[@cno="csc226"],
      $e in $d/gradebook/enrolls/enroll
  where ($s/sid eq $e/@sid) and ($e/@cid eq $c/@cid)
  order by $s/lname,$s/fname
  return <student sid="{$s/sid}">{$s/fname,$s/lname}</student>
}
</studentsEnrolledInCsc226>
/
--
-- Query 9.5 
-- Get students whose have enrolled in Programming I in f96 term.
--
xquery
let $d := doc("/public/book/gb.xml")
let $p1 := $d/gradebook/catalog/entry[ends-with(ctitle,"Programming I")]
let $c := $d/gradebook/courses/course[@term="f96" and @cno=$p1/cno]
let $e := $d/gradebook/enrolls/enroll[@cid=$c/@cid]
for $s in $d/gradebook/students/student
where $s/sid = $e/@sid
return $s
/
--
-- Query 9.6 
-- Get number of grading components for each course.
--
xquery
let $d := doc("/public/book/gb.xml")
for $c in $d/gradebook/courses/course
return <course cid="{$c/@cid}">
         <courseNo>{$c/@cno}</courseNo>
         <numComponents>{count($c/component)}</numComponents>
       </course>
/
--
-- Query 9.7 
-- Get the average score for grading component exam1 in the f96-1 course.
--
xquery
let $d := doc("/public/book/gb.xml")
for $c in $d//course[@cid="f96-1"]/component[@name="exam1"]
return xs:integer(fn:avg($c/score))
/
--
-- Query 9.8 
-- Get the overall average score for student 1111 in course f96-1.
--
xquery
sum(
let $d := doc("/public/book/gb.xml")
for $c in $d//course[@cid="f96-1"]/component,
    $s in $c/score[@sid="1111"]
return round-half-to-even(($s div $c/@maxpoints)*$c/@weight,2)
)
/
--
-- Query 9.9 
-- Get the overall average for all students in course f96-1.
--
xquery
<classAvgf96-1>
{
let $d := doc("/public/book/gb.xml")
for $st in $d//enrolls/enroll[@cid="f96-1"]
return
<studentAvg sid="{$st/@sid}">
{
sum(
for $c in $d//course[@cid="f96-1"]/component,
    $s in $c/score[@sid=$st/@sid]
return round-half-to-even(($s div $c/@maxpoints)*$c/@weight,2)
)
}
</studentAvg>
}
</classAvgf96-1>
/
--
-- Query 9.10 
-- Get numeric as well as letter grades for all students in course f96-1.
--
xquery
<classGradesf96-1>
{
let $d := doc("/public/book/gb.xml")
let $agrade := $d//course[@cid="f96-1"]/@a,
    $bgrade := $d//course[@cid="f96-1"]/@b,
    $cgrade := $d//course[@cid="f96-1"]/@c,
    $dgrade := $d//course[@cid="f96-1"]/@d
for $st in $d//enrolls/enroll[@cid="f96-1"]
let $ngr := 
sum(for $c in $d//course[@cid="f96-1"]/component,
    $s in $c/score[@sid=$st/@sid]
    return round-half-to-even(($s div $c/@maxpoints)*$c/@weight,2))
return
<studentGrade sid="{$st/@sid}">
<numericGrade>
{ $ngr }
</numericGrade>
<letterGrade>
{ 
if ($ngr >= $agrade) then "A"
  else if ($ngr >= $bgrade) then "B"
  else if ($ngr >= $cgrade) then "C"
  else if ($ngr >= $dgrade) then "D"
  else "F"
}
</letterGrade>
</studentGrade>
}
</classGradesf96-1>
/
--
-- Query 9.11 
-- Classify the courses as into two categories: "Too many components"
-- if number of grading components is more than 2 and "Too few components"
-- if number of grading components is less than or equal to 2.
--
xquery
<CourseClassification>
{
let $d := doc("/public/book/gb.xml")
for $c in $d/gradebook/courses/course
return
<course cid="{$c/@cid}" cno="{$c/@cno}">
{
if (count($c/component) gt 2) then "Too many components"
else "Too few components"
}
</course>

}
</CourseClassification>
/
--
-- Query 9.12 
-- Get students whose have enrolled in csc226 or csc227.
--
xquery
let $d := doc("/public/book/gb.xml")
let $c1 := $d/gradebook/courses/course[@cno="csc226"]
let $c2 := $d/gradebook/courses/course[@cno="csc227"]
for $s in $d/gradebook/students/student
where $s/sid = $d/gradebook/enrolls/enroll[@cid=$c1/@cid]/@sid
 or
      $s/sid = $d/gradebook/enrolls/enroll[@cid=$c2/@cid]/@sid
return $s
/
--
-- Query 9.13
-- Get students whose have not enrolled in any course.
xquery
let $d := doc("/public/book/gb.xml")
let $e := $d/gradebook/enrolls/enroll
for $s in $d/gradebook/students/student
where not ($s/sid = $e/@sid)
return $s
/
--
-- Query 9.14 
-- Get students whose have enrolled in all courses in the catalog.
--
xquery
let $d := doc("/public/book/gb.xml")
for $s in $d/gradebook/students/student
where every $t in $d/gradebook/catalog/entry satisfies
       (some $e in $d/gradebook/enrolls/enroll,
             $c in $d/gradebook/courses/course satisfies
                $e/@cid=$c/@cid and $s/sid=$e/@sid and $t/cno=$c/@cno)
return $s
/
--
-- Query 9.15 
-- Get students whose have secured more than or equal to 90% in every
-- grading component they have taken.
--
xquery
let $d := doc("/public/book/gb.xml")
for $s in $d/gradebook/students/student
where 
  (some $c in $d/gradebook/courses/course/component/score
   satisfies
     $s/sid eq $c/@sid
  )
  and 
  (every $t in $d/gradebook/courses/course/component/score[@sid=$s/sid]
   satisfies
   ((($t*100) div $t/../@maxpoints) ge 90)
  )
return $s
/
--
