Question

Consider the following relational schema about a University (the primary keys are underlined and foreign keys are italic) STUDENT(StudentID, name, major, year, age) CLASS(ClassName, meetsAt, room, LecturerID) ENROLLED(StudentID, ClassName, mark) LECTURER(LecturerID, name, DepartmentID) DEPARTMENT(DepartmentID, name) Write the SQL statements for the following query:

B1. Find the age of the oldest student.

B2. Find the ID’s of lecturers whose name begins with “K”

B3. Find the age of the youngest student who is enrolled in Mechatronics.

B4. Find the age of the oldest student enrolled in any of Max Headroom’s classes.

B5. Find the names of all classes that have more than 100 students enrolled

B6. Find the names of students who are not enrolled in any class

Answer 1

1-

SELECT age

FROM STUDENT

WHERE age=(

SELECT MAX(age)

FROM STUDENT

)

2-

SELECT LecturerID

FROM LECTURER

WHERE name LIKE 'K%'

3-

SELECT MIN(age)

FROM STUDENT

WHERE StudentID IN( SELECT StudentID

FROM ENROLLED

WHERE ClassName = 'Mechatronics')

4-

SELECT MIN(age)

FROM STUDENT

WHERE StudentID IN(

SELECT StudentID

FROM ENROLLED

WHERE ClassName IN(

SELECT ClassName

FROM CLASS

WHERE LecturerID IN

(SELECT LecturerID

FROM LECTURER

WHERE name ='Max Headroom')))

5-

SELECT ClassName

FROM ENROLLED

GROUP BY ClassName

HAVING COUNT(*)>100

6-

SELECT StudentID

FROM STUDENT

WHERE StudentID NOT IN

(SELECT StudentID

FROM ENROLLED

)