Question

1. Consider the following relations. The primary keys are underlined. All attributes are of type string if not indicated otherwise.

 Student(s_ID, s_name, s_degree: integer, advisorID, d_ID)

 Lecture(l_ID, l_name,l_degree: integer, p_ID, d_ID)

 Register(s_ID,l_ID,score: integer, Semester)

 Professor(p_ID,p_name, d_ID)

 Department(d_ID, d_name, address)

a. Find the names of professors who have taught in every semester.

b. List the names of lectures that the CISE department offers but that are taught by a professor whose department is not CISE.

c. Find the names of students who got the highest score in the lecture ‘Databases’.

d. Find the names of students who have registered every lecture of the CISE department.

e. Find the names of students who got more than 90 in the ‘DB’ lecture and less than 70 in the ‘Algorithm’ lecture.

Answer 1

a. SELECT Professor.p_ID,Professor.p_name,Register.Semester FROM Lecture INNER JOIN Register ON Lecture.l_ID = Register.l_ID INNER JOIN Professor ON Professor.p_ID = Lecturer.p_ID

b. SELECT Lecture.l_ID,Lecturer.l_name FROM Lecturer INNER JOIN Professor ON Lecturer.p_ID = Professor.p_ID INNER JOIN Department ON Department.d_ID = Professor.d_ID GROUP BY Lecturer.l_name HAVING Department.d_name = 'CISE'

c.SELECT Student.s_ID,Student.s_name,Max(Register.score) FROM Student INNER JOIN Register ON Student.s_ID = Register.s_ID INNER JOIN Lecture ON Lecture.l_ID = Register.l_ID

d. SELECT Student.s_ID,Student.s_name FROM Student INNER JOIN Lecture ON Student.d_ID = Lecture.d_ID INNER JOIN Department ON Department.d_ID = Student.d_ID WHERE Department.d_name = 'CISE'

e. SELECT Student.s_ID,Student.s_name FROM Student INNER JOIN Lecture ON Student.d_ID = Lecture.d_ID INNER JOIN Register ON Student.s_ID = Register.s_ID WHERE Register.score > 90 AND Lecture.l_degree = 'DB' OR Register.score < 70 AND Lecture.l_degree = 'Algorithm' GROUP BY Student.s_ID