queries.sql

SELECT DISTINCT F.pid, P.fName, P.lName, T.cid
FROM Person P, Faculty F, Teaches T
WHERE P.pid = F.pid
AND F.pid = T.pid
AND F.pid IN (SELECT T.pid FROM Teaches T GROUP BY T.pid HAVING COUNT(*) > 1)
ORDER BY F.pid;

SELECT DISTINCT B.pid, P.fName, P.lName, B.lot, B.spotNum
FROM Person P, Buys B
WHERE P.pid = B.pid
ORDER BY B.lot, B.spotNum;

SELECT PP.PID, PP.FNAME, PP.LNAME, V.HRS
FROM VOLUNTEERS V, PERSON PP
WHERE PP.PID = V.PID 
	AND V.HRS > 20;

SELECT DISTINCT C.CID, C.TITLE
FROM COURSE C
WHERE C.CID IN (SELECT R.CID FROM REGISTERS R GROUP BY R.CID 
	HAVING COUNT(*) < 3);

SELECT PP.PID, PP.FNAME, PP.LNAME
FROM PERSON PP
WHERE PP.PID IN 
(SELECT DISTINCT FF.PID FROM FINALGRADE FF 
    WHERE FF.GRADE = 'F');

SELECT DISTINCT PP.FNAME, PP.LNAME
FROM FINALGRADE FG, STUDENT S, PERSON PP
WHERE FG.PID = S.PID AND
      S.PID = PP.PID AND
      FG.GRADE = 'Z';

SELECT DISTINCT C.CID, C.TITLE
FROM COURSE C
WHERE C.CID NOT IN (
        SELECT DISTINCT C.CID
	FROM COURSE C, LOCATEDAT LA
	WHERE C.CID = LA.CID);

SELECT O.orid, O.oname, COUNT(V.pid)
FROM Organizations O, Volunteers V
WHERE O.orid = V.orid
GROUP BY O.orid, O.oname
ORDER BY COUNT(V.pid);

SELECT C.CID, C.TITLE, COUNT(*) AS A_TOT
FROM FINALGRADE FG, COURSE C
WHERE FG.GRADE = 'A' AND
FG.CID = C.CID AND
FG.SEM = C.SEM AND
FG.YR = C.YR
  GROUP BY C.CID, C.TITLE
     HAVING COUNT(*) = (
	SELECT MAX(COUNT(*))
	FROM FINALGRADE FG, COURSE C
	WHERE FG.GRADE = 'A' AND
	FG.CID = C.CID AND
	FG.SEM = C.SEM AND
	FG.YR = C.YR
	GROUP BY FG.CID);