Class Notes (836,380)
CS 348 (13)
Ihab Ilyas (11)
Lecture

# Assignment #1 - Solution Fall 2009

6 Pages
248 Views

School
Department
Computer Science
Course
CS 348
Professor
Ihab Ilyas
Semester
Fall

Description
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

Related notes for CS 348
Me

OR

Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Join to view

OR

By registering, I agree to the Terms and Privacy Policies
Just a few more details

So we can recommend you notes for your school.