This

**preview**shows pages 1-2. to view the full**6 pages of the document.**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

Only pages 1-2 are available for preview. Some parts have been intentionally blurred.

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

###### You're Reading a Preview

Unlock to view full version