• INSTRUCTIONS
(1) This lab is graded.
(2) Use the student database that was created in Lab2.
(3) Each question carries 1 marks.
• QUESTION 1
Consider the following relations:
Student(snum: integer, sname: string, major: string, level: string, age: integer)
Class(name: string, meets_at: time, room: string, fid: integer)
Enrolled(snum: integer, cname: string)
Faculty (fid: integer, fname: string, deptid: integer)
The meaning of these relations is straightforward; for example, Enrolled has one record per student-class pair such that the student is enrolled in the class.
Write the following queries in SQL. No duplicates should be printed in any of the answers.
You may find it easier to write queries if you use the with clause (or if you create views).
(1) Find the names of all classes that either meet in room 20 AVW or have five or more students enrolled.
(2) Find the number of courses conducted per room.
(3) List all faculty members, showing their id, name and the number of classes they teach. The number of classes of those who teach no classes must be shown as 0.
(4) List all the courses with their names, where they are taught and the number of students enrolled for each. If no students are enrolled, show the number of students as 0.
(5) Find all faculty members who belong to department 20 and whose courses are conducted in room R128.
(6) Find the maximum age of all students of each major
(7) Find the names of students and faculty members whose names contain the string “son”. For the following set of questions, create a new database:
Table 1. Employees and Supervisors
person supervisor
Ravi Amit
Mary Sujata
Amit Devi
Devi Mary
(8) Find the supervisor of Ravi.
(9) Find the supervisor of the supervisor of Ravi.
(10) Find all the supervisors (direct and indirect) of Ravi.
.
1
