Solutions to relational algebra questions
SELECT name FROM Courses WHERE nrStudents > 20
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
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;
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');
SELECT name FROM Rooms WHERE nrSeats = (SELECT MAX(nrSeats) FROM Rooms); SELECT name FROM Rooms WHERE nrSeats >=ALL (SELECT nrSeats FROM Rooms);
(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
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.
SELECT C.name, teacher FROM Courses C, Teachers T WHERE teacher = T.name; πC.name, teacher (σteacher = T.name(ρC(Courses) ⨯ ρT(Teachers))) (Non Unicode: 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; γday, count-distinct(teacher)(σC.name = S.name(ρC(Courses) ⨯ ρS(Classes))) (Non-Unicode: gamma[day, count-distinct(teacher)](sigma[C.name = S.name](rho[C](Courses) X rho[S](Classes))))