Question 1:
Q1.1:
SELECT distinct p.pnum,p.pname,p.dept
FROM professor p,class c, schedule s
WHERE p.pnum=c.pnum and c.cnum=s.cnum and c.term=s.term and
c.section=s.section and s.day=’Tuesday’
and c.term NOT IN (SELECT term FROM mark)
Q1.2:
SELECT count (distinct p.pnum) as Past CS348 Instructors
FROM professor p,class c
WHERE p.pnum=c.pnum and c.cnum=’CS348’
and c.term IN (SELECT term FROM mark)
Q1.3:
SELECT co.cnum, co.cname, m.grade
FROM enrollment e, mark m, student s, class c, course co
WHERE e.snum = s.snum and e.snum = m.snum and e.cnum = m.cnum
and e.term = m.term and e.section = m.section and e.cnum = c.cnum
and e.term = c.term and e.section = c.section and c.cnum = co.cnum
and s.snum = 1224
Q1.4:
SELECT S.snum, S.sname, S.year
FROM Student S
WHERE S.snum NOT IN
(
SELECT S2.snum
FROM Student S2
JOIN Mark M ON S2.snum = M.snum
JOIN Class C ON M.cnum = C.cnum AND M.term = C.term
AND M.section = C.section
JOIN Professor P ON C.pnum = P.pnum
WHERE M.grade < 92 AND P.dept <> ’Philosophy’
)
1 Q1.5:
SELECT distinct pnum,pname,dept
FROM professor p1
WHERE p1.pnum NOT IN
(
SELECT p.pnum
FROM class c, schedule s, professor p
WHERE c.pnum=p.pnum and
c.cnum=s.cnum and c.term=s.term and c.section=s.section
and c.term NOT IN (select term from mark)
and (day=’Monday’ or day =’Friday’)
)
ORDER BY dept,pname
Q1.6:
SELECT distinct c.cnum,c.term,c.section, p.pnum,p.pname
FROM professor p,class c
WHERE p.pnum=c.pnum and c.term IN (select term from mark)
Q1.7:
WITH EnrollmentCount AS
(
SELECT cnum,COUNT(*) CNT FROM ENROLLMENT GROUP BY cnum
UNION
SELECT c.cnum,0 CNT FROM COURSE C
WHERE NOT EXISTS (SELECT * FROM ENROLLMENT E WHERE E.CNUM = C.CNUM)
)
SELECT EC1.cnum, EC1.CNT, COUNT (DISTINCT(EC2.CNT)) DENSE RANK
FROM EnrollmentCount EC1, EnrollmentCount EC2
WHERE EC1.CNT > EC2.CNT
OR (EC1.CNT=EC2.CNT and EC1.cnum = EC2.cnum)
GROUP BY EC1.cnum, EC1.CNT HAVING COUNT (DISTINCT(EC2.CNT))<=3
ORDER BY EC1.CNT DESC
Q1.8:
WITH CurrentEnrollments AS
(
SELECT e.snum AS snum, e.cnum AS cnum, e.term AS term,
2 e.section AS section, s.year AS year
FROM enrollment e, student s
WHERE e.snum = s.snum
AND e.term NOT IN ( SELECT DISTINCT m.term from mark m )
)
SELECT p.pnum, p.pname, c.cnum, c.term, c.section, count (*)
FROM professor p, CurrentEnrollments e, class c
WHERE p.pnum =c.pnum and c.cnum = e.cnum and e.term = c.term
AND c.section = e.section and e.year = 2
GROUP BY p.pnum, p.pname, c.cnum, c.section, c.term
UNION
-->This part ensures all zero counts are also included

More
Less