• 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 name of the youngest student who is either a Finance major or enrolled in a course taught by Linda Davis.
(2) Find the names of faculty members who teach in every room in which some class is taught.
(3) Find the names of faculty members who teach the minimum number of classes.
(4) Find the names of faculty members who do not teach any class.
(5) For each age value that appears in Students, find the level value that appears most often. For example, if there are more FR level students aged 18 than SR, JR, or SO students aged 18, you should print the pair (18, FR).
(6) Find the courses conducted in room R128 for which at least one student has enrolled.
(7) Find the times at which classes occur for those courses for which at least one student has enrolled.
(8) Find the students of standing JR who have enrolled in some course which is conducted in room R128.
(9) List the students who are older than 18 years and have a level of SR and whose major is not a branch of Engineering.
(10) Find the classes for which no student has enrolled.
.
1
