a.
SELECT * FROM Course
WHERE CrsCode IN
(SELECT T.CrsCode 
 FROM Teaching T, Professor P
 WHERE T.ProfId = P.ProfId 
  AND P.DeptId IN ('EE', 'MGT'))

b.
SELECT Name FROM Student
WHERE StudId IN
(SELECT TA.StudId FROM Transcript TA, Transcript TB
 WHERE TA.StudId = TB.StudID 
  AND TA.Semester = 'S1997' 
  AND TB.Semester = 'F1998')

c.
SELECT Name FROM Student
WHERE StudId IN
(SELECT TA.StudId
 FROM Transcript T, Teaching C, Professor P
 WHERE T.CrsCode = C.CrsCode AND T.Semester = C.Semester AND P.ProfId = C.ProfId
  AND Teaching
 HAVING (COUNT(DISTINCT P.DeptId) > 1))

d. 
SELECT * FROM Course C
WHERE DeptId = 'MGT'
AND NOT EXISTS (
 (SELECT DISTINCT T1.StudId 
  FROM Transcript T1)
 EXCEPT 
 (SELECT DISTINCT T2.StudId 
  FROM Transcript T2
  WHERE C.CrsCode = T2.CrsCode))

e.
SELECT * FROM Department D
WHERE COUNT(
 SELECT * 
 FROM Professor P 
 WHERE NOT EXISTS (
  (SELECT DISTINCT T.CrsCode 
   FROM Teaching T, Course C 
   WHERE C.CrsCode = T.CrsCode 
    AND C.DeptId = D.DeptId)
  EXCEPT
  (SELECT DISTINCT T.CrsCode
   FROM Teaching T, Course C
   WHERE C.CrsCode = T.CrsCode 
    AND C.DeptId = D.DeptId 
    AND T.ProfId = P.ProfId))
) > 0

5.17
a.
SELECT AVG(Age)
FROM 
 (SELECT DISTINCT StudId, Age 
  FROM Student S, Transcript T
  WHERE T.Grade = 'A')

b.
SELECT C.CrsCode, MIN(S.Age)
FROM (
 SELECT DISTINCT S.StudId, ST.CrsCode, S.Age
 FROM Student S, Transcript ST
 WHERE S.StudId = ST.StudId
  AND NOT EXISTS (
   SELECT *
   FROM Transcript T
   WHERE T.StudId = S.StudId
    AND T.Grade <> 'A')))
GROUP BY C.CrsCode

c.
SELECT C.CrsCode, MIN(S.Age)
FROM (
 SELECT DISTINCT S.StudId, ST.CrsCode, S.Age
 FROM Student S, Transcript ST
 WHERE S.StudId = ST.StudId
  AND EXISTS (
   SELECT * 
   FROM Transcript T 
   WHERE S.StudId = ST.StudId 
    AND ST.CrsCode in ("CS305", "MAT123"))
  AND NOT EXISTS (
   SELECT *
   FROM Transcript T
   WHERE T.StudId = S.StudId
    AND T.Grade <> 'A')))
GROUP BY C.CrsCode

d.
UPDATE Professor
SET Salary = 1.1 * Salary 
WHERE ProfId IN (
 SELECT DISTINCT P.ProfId
 FROM Professor P, Teaching T
 WHERE T.ProfId = P.ProfId 
  AND T.CrsCode = 'MAT123' 
  AND (Semester = 'S1997' 
   OR Semester = 'F1997') 
  AND P.Age < 40)

e.
SELECT * FROM Professor P1
WHERE (SELECT AVG(P2.Salary) FROM Professor P2) / P1.Salary >= 1.1)

5.20
SELECT StudId 
FROM Transcript ST
WHERE NOT EXISTS(
 (SELECT DISTINCT P.ProfId
  FROM Professor P, Transcript T
  WHERE P.Id = T.ProfId 
   AND P.Dept = 'MUS' 
   AND ST.StudId = T.StudId)
 EXCEPT
 (SELECT Id  
  FROM Professor P
  WHERE P.Dept = 'MUS'))