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

Assignment #1 - Solution Fall 2009

6 Pages
248 Views
Unlock Document

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

Log In


OR

Join OneClass

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

Sign up

Join to view


OR

By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.


Submit