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 or database modifications in SQL-92:

1. (8 pts) Assume that the domain of the grade attribute of taking is real numbers from 0.0 to 4.0 (i.e. assume that grades are stored as numbers, not letters). Find the average grade for each offering (section) of Comp 419. The result of your query should be a relation with three attributes: schedule-num, semester, and the average grade.
2. (5 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.) Do NOT use the aggregate function max in your query.
3. (4 pts) Find the name of the senior who has the highest gpa in the senior class. (If there is a tie, the result should contain the name of all seniors who are tied for the highest gpa.) As usual, senior refers to a student whose standing is senior. Do NOT use the aggregate function max in your query.
4. (6 pts) Find the name of the student who is taking the most classes during Spring 1997 semester, and the number of classes that student is taking. (If there is a tie, the result should contain the name and number of courses for all students who are tied.) If a student should happen to be taking two sections of the same course, both count toward the total. (Hint: use a derived relation in your query.)
5. (3 pts) Delete all students whose gpa is less than 1.0 from the student relation.
6. (4 points) Delete all students who are taking or have taken Comp 419 from the student relation. (This is only an example - PSH is not planning to do this.)

• About this document ...