-- Question 1.
select distinct p.pnum, p.pname, p.dept \
from professor as p, class as c, schedule as s \
where p.pnum = c.pnum \
and c.cnum = s.cnum \
and c.term = s.term \
and c.section = s.section \
and s.day = 'Thursday' \
and not exists (select * \
from mark as m \
where c.cnum = m.cnum \
and c.term = m.term \
and c.section = m.section)
-- Question 2.
select count(*) as "Num of CS348 Professors" \
from professor as p \
where exists ( select * \
from class as c, mark as m \
where p.pnum = c.pnum \
and c.cnum = m.cnum \
and c.term = m.term \
and c.section = m.section \
and m.cnum = 'CS348' )
-- Question 3.
select c.cnum, c.cname, m.grade \
from course as c, mark as m \
where c.cnum = m.cnum \
and m.snum = 1234
-- Question 4.
select s.snum, s.sname, s.year \
from student as s \
where not exists (select * \
from mark as m \
where m.snum = s.snum \
and m.grade < 90 \
and not exists (select * \
from professor as p, class as c, mark as m1 \
where p.pnum = c.pnum \
and c.cnum = m1.cnum \
and c.term = m1.term \
and c.section = m1.section \
and p.dept = 'philosophy')) \
or not exists (select * \
from mark m \
where m.snum = s.snum)
-- Question 5.
select p.pnum, p.pname, p.dept \
from professor p \
where not exists (select * \
from schedule as s, class as c \
where s.cnum = c.cnum \
and s.term = c.term \
and s.section = c.section \
and c.pnum = p.pnum \
and (s.day = 'Monday' or s.day = 'Friday') \
and not exists (select * \
from mark as m \
where m.cnum = c.cnum \
and m.term = c.term \
and m.section = c.section \
)) \
order by p.dept asc, p.pname asc
-- Question 6.
select c.cnum, c.term, c.section, p.pnum, p.pname \
from class as c, professor as p \
where c.pnum = p.pnum \
and exists (select * \
from mark as m \
where m.cnum = c.cnum \
and m.term = c.term \
and m.section = c.section)
-- Question 7.
with t as ( select cnum, count(*) as cnt \
from enrollment \
group by cnum ) \
select * \
from t \
where not exists ( select * \
from t as t1, t as t2, t as t3 \
where t1.cnum <> t.cnum \
and t2.cnum <> t1.cnum and t2.cnum <> t.cnum \
and t3.cnum <> t2.cnum and t3.cnum <> t1.cnum and t3.cnum <> t.cnum \
and t1.cnt < t.cnt \
and t2.cnt < t.cnt \
and t3.cnt < t.cnt )
-- Question 8.
select p.pname, p.pnum, e.cnum, e.term, e.section, count(distinct e.snum) as "Second year student count" \
from enrollment e, professor p, student s, class c \
where p.pnum = c.pnum \
and c.cnum = e.cnum \
and c.term = e.term \
and c.section = e.section \
and e.snum = s.snum \
and s.year = 2 \
and not exists (select * \
from mark m \
where m.cnum = e.cnum \
and m.term = e.term \
and m.section = e.section) \
group by e.cnum, e.term, e.section, p.pnum, p.pname \
order by p.pname asc, p.pnum asc, e.cnum asc, e.term asc, e.section asc
-- Question 9.
select 100 * count(distinct p.dept)/(t.dcnt) as "Department Percentage Stat %" \
from professor as p, (select count(distinct dept) as dcnt from professor) as t \
where exists ( select * \
from class as c1, mark as m1, class as c2, mark as m2 \
where c1.pnum = p.pnum \
and c2.pnum = p.pnum \
and c1.cnum = m1.cnum and c1.term = m1.term and c1.section = m1.section \
and c2.cnum = m2.cnum and c2.term = m2.term and c2.section = m2.section \
and c1.term = c2.term \
and (c1.cnum <> c2.cnum or c1.section <> c2.section) ) \
group by t.dcnt