Tutorial 3 Solutions

Relational Algebra

Solutions to relational algebra questions

SQL queries

Easy ones

• Find all courses that have more that 20 students:

 SELECT name FROM Courses WHERE nrStudents > 20

• List all professors at the school:

 SELECT teacher FROM TeacherTitles WHERE title = 'Professor'

• List all courses and their teachers:

 SELECT course, teacher FROM Courses

• Find all classes that have more students than there are seats in the room:

 SELECT course, day, hour
 FROM Classes A, Courses C, Teachers T, Rooms R
 WHERE A.course = C.name
   AND C.teacher = T.name
   AND T.room = R.name
   AND C.nrStudents > R.nrSeats

Harder ones, each done in two different ways

• Find all teachers giving more than one course:

 SELECT DISTINCT A.teacher
 FROM Courses A, Courses B
 WHERE A.teacher = B.teacher
   AND A.course != B.course
 
 SELECT teacher
 FROM Courses
 GROUP BY teacher
 HAVING COUNT(course) >= 2

• Find all rooms that are used on Mondays:

 SELECT room
 FROM Classes A, Courses C, Teachers T
 WHERE A.courses = C.name
   AND C.teacher = T.name
   AND A.day = 'Monday';
 
 SELECT room
 FROM Courses C, Teachers T
 WHERE C.teacher = T.name
   AND C.name IN (SELECT course FROM Classes WHERE day = 'Monday')

• Find the room with the highest number of seats:

 SELECT name
 FROM Rooms
 WHERE nrSeats = (SELECT MAX(nrSeats) FROM Rooms)
 
 SELECT name
 FROM Rooms
 WHERE nrSeats >=ALL (SELECT nrSeats FROM Rooms)

• List all teachers along with their titles. Those without a title should be listed as having NULL:

 (SELECT teacher, title
  FROM Teachers, TeacherTitles
  WHERE name = teacher)
 UNION
 (SELECT name, NULL
  FROM Teachers
  WHERE name NOT IN (SELECT teacher FROM TeacherTitles))
 
 SELECT teacher, title
 FROM Teachers LEFT OUTER JOIN TeacherTitles ON name = teacher

• Find the teacher(s) with the highest average number of students on his or her (their) courses.

 WITH
  AvgStuds AS 
   (SELECT teacher, AVG(nrStudents) as avgStuds
    FROM Courses
    GROUP BY teacher)
 SELECT teacher
 FROM AvgStuds
 WHERE avgStuds = (SELECT MAX(avgStuds) FROM AvgStuds)
 
 SELECT teacher
 FROM Courses
 GROUP BY teacher
 HAVING AVG(nrStudents) >=ALL 
    (SELECT AVG(nrStudents) 
     FROM Courses 
     GROUP BY teacher)

Note that these are suggestions, if you can write two, you can write more.

Translate SQL to relational algebra

 SELECT C.name, teacher
 FROM Courses C, Teachers T
 WHERE teacher = T.name;
 
   pi[C.name, teacher](sigma[teacher = T.name](rho[C](Courses) X rho[T](Teachers))
 
 SELECT day, COUNT(DISTINCT teacher)
 FROM Courses C, Classes S
 WHERE C.name = S.course
 GROUP BY day;
 
   gamma[day, count-distinct(teacher)](sigma[C.name = S.name](rho[C](Courses) X rho[S](Classes)))