Description
-
INSTRUCTIONS
-
-
This lab is graded.
-
-
-
Use the student database that was created in Lab2.
-
-
-
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.
-
For each level, print the level and the average age of students for that level.
-
For all levels except JR, print the level and the average age of students for that level.
-
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.
-
Find the name and number of students who have enrolled in Database Systems but not in Operating System Design.
-
Find the average age of all students taking a course, if that course has at least 2 students.
-
Find the ids of faculty members who are teaching more than one course.
-
Find the ids of all students who have enrolled for more than one course.
-
List students (their ids, names, majors , level and age) in ascending order of age.
-
Find the names and ids of all students whose major is some branch of Engineering (Electrical Engineering, Mechanical Engineering, Computer Engineering, Civil Engineering).
-
Count the number of students in each branch of Engineering.
.
1