Express each of the following queries in (i ) relational algebra and (ii) SQL using the Student Registration System schema.

a. List all courses that are taught by professors who belong to the EE or MGT departments.
b. List the names of all students who took courses both in spring 1997 and fall 1998.
c. List the names of all students who took courses from at least two professors in di?erent departments.
d. List all courses that are o?ered by the MGT Department and that have been taken by all students.
e. Find every department that has a professor who taught all courses ever o?ered by that department.

a. Algebra:
```
?CrsCode,Semester(?DeptId='EE' or OR DeptId='MGT' (PROFESSOR)
Teaching)Id=ProfId TEACHING)
```

SQL:
```
SELECT T.CrsCode, T.Semester
FROM Teaching T, Professor P
WHERE T.ProfId=P.Id
AND (P.DeptId = ’EE’ OR P.DeptId = ’EE’)
```

b. Algebra:Let Sems be a relation with Semester as the only column and
’S1997’ and
’F1998’ as the only tuples. Then the answer is:

```
?Name(Student Id=StudId?StudId,Semester(Transcript)/Sems )
```

SQL:

```
SELECT S.Name
FROM Student S
WHERE NOT EXISTS (
(SELECT * FROM Sems)
EXCEPT
(SELECT T.Semester
FROM Teaching T
WHERE T.StudId = S.Id ) )
```

Note that if the query required only the students who took courses in either of the two semesters, then the solution would not have required the division operator and NOT EXISTS and it would have been much simpler.

c. Algebra: We construct the requisite expression in two stages. First, we construct StudDept(StudId, DeptId) as
```
?StudId,DeptId(Transcript  Teaching ProfId=IdProfessor)
```

Tuples in this relation represent instances when a given student took a course from a professor in the given department. Constructing the ?nal answer is now easy:

```
?Name(Student Id=StudId
?DeptId=DeptId2(StudDept  StudDept[StudId,DeptId2]))
```

SQL: The SQL solution is also in two stages, for clarity. First, we create an auxiliary view:
```
CREATE VIEW StudDept(StudId, DeptId) AS
SELECT R.StudId, P.DeptId
FROM Transcript R, Teaching T, Professor P
WHERE R.CrsCode = T.Crscode
AND R.Semester = T.Semester
AND T.ProfId = P.Id
```

```
SELECT S.Name
FROM Student S, StudDept SD1, StudDept SD2
WHERE S.Id = SD1.StudId AND SD1.StudId = SD2.StudId
AND SD1.DeptId = SD2.DeptId
```

d. Algebra:
```
?CrsCode,StudId(?DeptId='MGT' Course  Transcript)/?StudId(Student)
```

SQL:
```
SELECT C.CrsCode
FROM Course C
WHERE C.DeptId = ’MGT’ AND
NOT EXISTS (
(SELECT S.StudId
FROM Student S)
EXCEPT
(SELECT T.StudId
FROM Transcript T
WHERE C.CrsCode = T.CrsCode) )
```

e. Algebra: The algebraic solution is trickier than SQL’s. Let ProfCrsAux(ProfId,CrsCode) be de?ned as follows:
```
?ProfId,CrsCode(Professor DeptId=DeptId Course)
```

A tuple,
p, c here means that c is a course that is o?ered by a department other than the one where p works. Next, consider ProfCrs(ProfId, CrsCode), which is de?ned as follows:
```
ProfCrsAux ? ?ProfId,CrsCode(Teaching)
```

A tuple,
p, c in this relation means that c is a course that was either taught by p or it is o?ered by a department other than the one where p works. Finally, the expression for the requisite query can be constructed as follows:
```
ProfCrs / ?CrsCode(Course)
```

Note that constructing ProfCrsAux was essential. The expression
```
?ProfId,CrsCode(Teaching)/?CrsCode(Course)
```

is not correct, because it ?nds every professor who have taught every course in every department rather than the courses that are o?ered by the department where that professor works. The solution given above uses the division operator. There is a more
straightforward solution where one would ?rst ?nd professors who did not teach one of the courses o?ered by the professor’s department, and then take the complement.
SQL:
```
SELECT P.DeptId
FROM Professor P
WHERE EXISTS (
( SELECT P2.Id
FROM Professor P2
WHERE NOT EXISTS (
(SELECT C.CrsCode
FROM Course C
WHERE C.DeptId = P2.DeptId )
EXCEPT
(SELECT T.CrsCode
FROM Teaching T
WHERE T.ProfId = P2.Id ) )
AND P.Id = P2.Id
```

In fact, the following simpli?ed query is also correct:
```
SELECT P.DeptId
FROM Professor P
WHERE NOT EXISTS (
(SELECT C.CrsCode
FROM Course C
WHERE C.DeptId = P.DeptId )
EXCEPT
(SELECT T.CrsCode
FROM Teaching T
WHERE T.ProfId = P.Id ) )
```