Class Notes (834,049)
Canada (508,296)
CS 348 (13)
Ihab Ilyas (11)
Lecture

Assignment #1 - Solution Winter 2010

20 Pages
220 Views
Unlock Document

Department
Computer Science
Course
CS 348
Professor
Ihab Ilyas
Semester
Fall

Description
l. Print a list of student numbers, student names, and final grades for each past class taught by Smith, ordered b term and then course name (both of which should also be printed in the list) select mm. term, cname, s. Snum. S name grade from student s mark m, course c class cl professor p where s snum m snum. and m. cnum cl.cnum and m. term cl. term and m section E cl. section and cl.cnum C. Cnum. and cl.pnum p.pnum and pname Smith order by m. term. cname joining with the enrollment relation as well is useless, but OK (non-null foreign keys are already included in the join conditions), but joining with the schedule relation might lose tuples 2. How many students have received a mark in CS246? select count (distinct snum) from mark where enum CS246 V* it is unstated whether we want to include repetitions in the count l. Print a list of student numbers, student names, and final grades for each past class taught by Smith, ordered b term and then course name (both of which should also be printed in the list) select mm. term, cname, s. Snum. S name grade from student s mark m, course c class cl professor p where s snum m snum. and m. cnum cl.cnum and m. term cl. term and m section E cl. section and cl.cnum C. Cnum. and cl.pnum p.pnum and pname Smith order by m. term. cname joining with the enrollment relation as well is useless, but OK (non-null foreign keys are already included in the join conditions), but joining with the schedule relation might lose tuples 2. How many students have received a mark in CS246? select count (distinct snum) from mark where enum CS246 V* it is unstated whether we want to include repetitions in the count3. Give the course data for a student transcript showing the course number, term, and grade (if completed) for each course taken by the student whose number is 4321, ordered by term. Show the value -1 for courses in progress select m.cnum, m. term, case when grade is null then 1 else grade end from enrollment e left outer join mark m on (e. snum m snum and e .cnuma m.cnum and e term. m. term. and e. section m section) where m snum 4321 order by m. term 4. List the course numbers for courses that were taught at some time but not taught in W07. select enum from class except select cnum from class where term WOO7 alternatively, "taught at some time" implies presence in the enrollment relation (if assigned courses are not necessarily "taught") in this case, use enrollment for both selects, rather than class 3. Give the course data for a student transcript showing the course number, term, and grade (if completed) for each course taken by the student whose number is 4321, ordered by term. Show the value -1 for courses in progress select m.cnum, m. term, case when grade is null then 1 else grade end from enrollment e left outer join mark m on (e. snum m snum and e .cnuma m.cnum and e term. m. term. and e. section m section) where m snum 4321 order by m. term 4. List the course numbers for courses that were taught at some time but not taught in W07. select enum from class except select cnum from class where term WOO7 alternatively, "taught at some time" implies presence in the enrollment relation (if assigned courses are not necessarily "taught") in this case, use enrollment for both selects, rather than class5. Give a list of rooms that are used in W07 fewer than 3 times on Mondays or more than twice on Tuesdays select room. from schedule where term TWO group by room, day having (day Monday and count 3) or (day Tuesday and count 2) union select room. from schedule where term W07 except select room from schedule where term W07 group by room, day having day J Monday') the "except" part of union gives rooms not used at all on Mondays in 07, but it assumes that the query is asking for rooms used at least once in W07; otherwise remove where clause from middle select statement, the query could also be done by first forming a count for all rooms in W07 select room, day, count as cnt from schedule where term. W07 group by room, day and then selecting from that based on the criteria 6. What was the average grade in the section of any past course when someone named Lee was enrolled in that section? select m.cnum, m. term m. section, avg (grade) as average from mark m (select distinct cnum, term section from student s mark m where sname Lee and s. snum F m. snum) lee where m cnum lee. cnum and m. term lee. term and m. section lee section group by m cnum, m. term, m. section distinct is not strictly needed in the nested select, since we're grouping on all three values and repeating all entries an equal number of times does not change the average 5. Give a list of rooms that are used in W07 fewer than 3 times on Mondays or more than twice on Tuesdays select room. from schedule where term TWO group by room, day having (day Monday and count 3) or (day Tuesday and count 2) union select room. from schedule where term W07 except select room from schedule where term W07 group by room, day having day J Monday') the "except" part of union gives rooms not used at all on Mondays in 07, but it assumes that the query is asking for rooms used at least once in W07; otherwise remove where clause from middle select statement, the query could also be done by first forming a count for all rooms in W07 select room, day, count as cnt from schedule where term. W07 group by room, day and then selecting from that based on the criteria 6. What was the average grade in the section of any past course when someone named Lee was enrolled in that section? select m.cnum, m. term m. section, avg (grade) as average from mark m (select distinct cnum, term section from student s mark m where sname Lee and s. snum F m. snum) lee where m cnum lee. cnum and m. term lee. term and m. section lee section group by m cnum, m. term, m. section distinct is not strictly needed in the nested select, since we're grouping on all three values and repeating all entries an equal number of times does not change the average7. What are the names of professors who have taught the most number of distinct courses, and how many courses did they teach? with taught (pname, cnt) as select pname count from (select distinct p.pnum, pname Cnum from professor p class c where p.pnum E c.pnum) as pclass group by pnum, name select from taught where cnt E select max (cnt) from taught) must include pnum when grouping, in case profs' names repeat 8. Which third year students have taken all CO courses but have not enrolled in any CS classes? select snum S name from student s where snurm not in (select snum. from enrollment where cnum like CSS and year and not exists (select cnum from course where cnum like COS except select cnum from enrollment where enrollment. snum s snum) the last part encodes relational divide 7. What are the names of professors who have taught the most number of distinct courses, and how many courses did they teach? with taught (pname, cnt) as select pname count from (select distinct p.pnum, pname Cnum from professor p class c where p.pnum E c.pnum) as pclass group by pnum, name select from taught where cnt E select max (cnt) from taught) must include pnum when grouping, in case profs' names repeat 8. Which third year students have taken all CO courses but have not enrolled in any CS classes? select snum S name from student s where snurm not in (select snum. from enrollment where cnum like CSS and year and not exists (select cnum from course where cnum like COS except select cnum from enrollment where enrollment. snum s snum) the last part encodes relational divide9. Which courses have had more sections offered on Mondays and Wednesdays than on Tuesday and Thursdays select mw.cnum from (select m.cnum count as cnt from (select Cnum, term section from schedule where day Monday') m, select cnum, term, section from schedule where day E Wednesday w where m cnum w.cnurm and m. term w. term and m section w. section group by m.cnum) as mw left outer join (select t c num count as cnt from (select cnum, term, section from schedule where day Tuesday t, (select Cnum, term section from schedule where day Thursday r where t cnum r.cnurm and t term r. term and t section r section. group by t.cnum) as tr on mw. Cnum. tr cnum where mw.cnt tr .cnt or tr ent is null left outer join and test for null catches courses not offered on T/Th 10. Print a list of student numbers, student names, and cumulative average grades in decreasing order of average grade for all students who have taken at least three courses and have a cumulative average over 85 select s snum S. sname, avg (grade) as grd from student s mark m where s. snum m snum. group by s. snurm s. sname having count 3 and avg (grade) 85 order by grd desc 9. Which courses have had more sections offered on Mondays and Wednesdays than on Tuesday and Thursdays select mw.cnum from (select m.cnum count as cnt from (select Cnum, term section from schedule where day Monday') m, select cnum, term, section from schedule where day E Wednesday w where m cnum w.cnurm and m. term w. term and m section w. section group by m.cnum) as mw left outer join (select t c num count as cnt from (select cnum, term, section from schedule where day Tuesday t, (select Cnum, term section from sc
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