false

Class Notes
(834,049)

Canada
(508,296)

University of Waterloo
(18,550)

Computer Science
(798)

CS 348
(13)

Ihab Ilyas
(11)

Lecture

Unlock Document

Computer Science

CS 348

Ihab Ilyas

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

Join OneClass

Access over 10 million pages of study

documents for 1.3 million courses.

Sign up

Join to view

Continue

Continue
OR

By registering, I agree to the
Terms
and
Privacy Policies

Already have an account?
Log in

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.