Consider a relational database for a university registrar's office. The relations are student, class, instructor, teaches, and taking. The schemes of these relations are:

Student-scheme = (name, standing, student-num, gpa)
Class-scheme = (schedule-num, semester, department, number, days, time, place, enrollment)
Instructor-scheme = (name, department, office)
Teaches-scheme = (name, office, schedule-num, semester)
Taking-scheme = (student-num, schedule-num, semester, grade)

where the value of attribute standing is one of {freshman, sophomore, junior, senior}, and the schedule-num attribute of Class-scheme, Teaches-scheme and Taking-scheme is a unique number used to identify a particular section of a course (i.e. each course or section of a course has a unique schedule number), and the semester attribute of these relations identifies the year and semester of the course (i.e. Spring 1997). The teaches relation is intended to represent relationships between instructors and classes, and the taking relation is intended to represent relationships between students and classes. Note that Student-scheme now has a gpa attribute. The domain of gpa is real numbers from 0.0 to 4.0.

Express each of the following queries in SQL-92:

1. (4 pts) Find the names of all students taking any section of Comp 419 during Spring 1997 semester, where Comp is the department and 419 is the number.
2. (3 pts) Do question 1 again, but use a nested query (i.e. a query with two select statements).
3. (6 pts) Find the name and standing of all students who are taking or have ever taken a course taught by Tim Wahls or Linda Null. The result relation should not contain duplicates.
4. (3 pts) Find the average gpa of all students.
5. (3 pts) Find the number of students with a gpa greater than 3.0.
6. (4 pts) Find the name and standing of the student who has the highest gpa. (If there is a tie, the result should contain the name and standing of all students who are tied for the highest gpa.)
7. (4 pts) Find the number of students that have each standing. (In other words, give a query that produces a relation listing the number of freshmen, the number sophomores, the number of juniors, and the number of seniors.)

• About this document ...