• INSTRUCTIONS
(1) This lab is graded.
(2) Use the student database that was created in Lab2.
(3) Each question carries 1 mark.
• 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.
(1) For each level, print the level and the average age of students for that level.
(2) For all levels except JR, print the level and the average age of students for that level.
(3) For each faculty member that has taught classes, print the faculty member’s name and the total number of classes she or he has taught.
(4) Find the name and number of students who have enrolled in Database Systems but not in Operating System Design.
(5) Find the average age of all students taking a course, if that course has at least 2 students.
(6) Find the ids of faculty members who are teaching more than one course.
(7) Find the ids of all students who have enrolled for more than one course.
(8) List students (their ids, names, majors , level and age) in ascending order of age.
(9) Find the names and ids of all students whose major is some branch of Engineering (Electrical Engineering, Mechanical Engineering, Computer Engineering, Civil Engineering).
(10) Count the number of students in each branch of Engineering.
.
1
