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

SQL QUERIES:

B1.

SELECT StudentID, MAX(age)

FROM STUDENT;

EXPLANATION:

Select statement selects columns studentID and maximum age using MAX() function.

FROM is used to specify the tableName.

---------------------------------------------------

B2.

SELECT LecturerID

FROM LECTURER

WHERE name LIKE 'K%';

EXPLANATION:

IDs of lecturers are retrived from table 'LECTURERS'.

WHERE clause is used to specify the condition i.e., those IDs are selected whose names start from 'K' using LIKE keyword.

----------------------------------------------------

B3.

SELECT Min(s.age)

FROM STUDENT s, ENROLLED e

WHERE s.StudentID= e.StudentID

AND e.ClassName= 'Mechatronics';

B4.

SELECT MAX(s.age)

FROM STUDENT s, ENROLLED e

WHERE s.StudentID=e.StudentID

AND (e.ClassName IN (SELECT ClassName FROM Class WHERE LecturerID= (SELECT LecturerID FROM LECTURER WHERE name= 'Max Headroom' )));

B5.

SELECT ClassName

FROM ENROLLED

GROUP BY ClassName

HAVING COUNT(StudentID)> 100;

B6.

SELECT name

FROM STUDENT

WHERE StudentID NOT IN(SELECT StudentID FROM ENROLLED);