-- Q1
select p.pnum as pnum, p.lastname as lastname \
from   professor p \
where  p.dept = 'CS' and exists (select * \
  from   section s, enrollment e, course c \
  where  s.cnum = e.cnum and s.term = e.term and s.section = e.section \
    and  s.cnum = c.cnum and c.cnum = 'CS348' \
    and  s.pnum = p.pnum \
    and  e.grade < 60 )


-- Q2
select p.pnum as pnum, p.lastname as lastname \
from   professor p \
where p.dept = 'CS' \
and ( ( not exists (select * \
    from   section s, enrollment e, course c \
    where  s.cnum = e.cnum and s.term = e.term and s.section = e.section \
      and  s.cnum = c.cnum and c.cnum = 'CS348'  \
      and  s.pnum = p.pnum ) \
  and  not exists (select * \
    from   section s, enrollment e, course c \
    where  s.cnum = e.cnum and s.term = e.term and s.section = e.section \
      and  s.cnum = c.cnum and c.cnum = 'CS234'  \
      and  s.pnum = p.pnum \
      and  e.grade is not null ) \
  and  exists (select * \
    from   section s, enrollment e, course c \
    where  s.cnum = e.cnum and s.term = e.term and s.section = e.section \
      and  s.cnum = c.cnum and c.cnum = 'CS234'  \
      and  s.pnum = p.pnum \
      and  e.grade is null ) ) \
or ( not exists (select * \
    from   section s, enrollment e, course c \
    where  s.cnum = e.cnum and s.term = e.term and s.section = e.section \
      and  s.cnum = c.cnum and c.cnum = 'CS234'  \
      and  s.pnum = p.pnum ) \
  and  not exists (select * \
    from   section s, enrollment e, course c \
    where  s.cnum = e.cnum and s.term = e.term and s.section = e.section \
      and  s.cnum = c.cnum and c.cnum = 'CS348'  \
      and  s.pnum = p.pnum \
      and  e.grade is not null ) \
  and  exists (select * \
    from   section s, enrollment e, course c \
    where  s.cnum = e.cnum and s.term = e.term and s.section = e.section \
      and  s.cnum = c.cnum and c.cnum = 'CS348'  \
      and  s.pnum = p.pnum \
      and  e.grade is null ) ) )

-- Q3
select p.pnum as pnum, p.lastname as lastname \
from   professor p \
where  exists (select * \
  from   section s, enrollment e, course c \
  where  s.cnum = e.cnum and s.term = e.term and s.section = e.section \
    and  s.cnum = c.cnum and c.cnum = 'CS245' \
    and  s.pnum = p.pnum \
    and  e.grade <= all ( \
      select ea.grade \
      from   enrollment ea, course ca \
      where  ca.cnum = ea.cnum and ca.cnum = 'CS245' ) )

-- Q4
select s.snum as number, s.firstname as name, s.year as year \
from   student s \
where  s.year = 4 \
  and  not exists (select * \
    from  enrollment e \
    where s.snum = e.snum \
      and ( substring(e.cnum,1,2) = 'CS' or substring(e.cnum,1,2) = 'CO') \
      and e.grade is not null and e.grade < 90 )

-- Q5
select distinct e1.grade as mingrade, e2.grade as maxgrade, p.lastname, s.cnum, s.term, s.section \
from   professor p, section s, enrollment e1, enrollment e2 \
where  p.pnum = s.pnum and  p.dept = 'CS' \
  and  s.cnum = e1.cnum and s.term = e1.term and s.section = e1.section \
  and  s.cnum = e2.cnum and s.term = e2.term and s.section = e2.section \
  and  e1.grade is not null and e2.grade is not null \
  and  exists ( select * \
     from  officehour o \
     where o.cnum = s.cnum and o.term = s.term \
       and o.day = 'Monday' ) \
  and  exists ( select * \
     from  officehour o \
     where o.cnum = s.cnum and o.term = s.term \
       and o.day = 'Friday' ) \
  and  not exists (select * \
    from enrollment e \
    where e.grade < e1.grade \
      and s.cnum = e.cnum and s.term = e.term and s.section = e.section ) \
  and  not exists (select * \
    from enrollment e \
    where e.grade > e2.grade \
      and s.cnum = e.cnum and s.term = e.term and s.section = e.section )

-- Q6
select count(e.snum) as number, ps.pnum, ps.lastname, ps.cnum, ps.term, ps.section \
from   ( select p.pnum, p.lastname, s.cnum, s.term, s.section \
         from  professor p, section s \
         where  p.pnum = s.pnum and p.dept = 'PM' \
         and exists ( select * \
            from enrollment e \
            where e.cnum = s.cnum and e.term = s.term and e.section = s.section and e.grade is not null ) ) ps \
       left join \
       ( select  t.snum, e.cnum, e.term, e.section \
         from  enrollment e, student t \
         where  t.snum = e.snum and ( t.year = 3 or t.year = 4 ) ) e \
       on  ps.cnum = e.cnum and ps.term = e.term and ps.section = e.section \
group by ps.pnum, ps.lastname, ps.cnum, ps.term, ps.section \
order by ps.lastname, ps.pnum, ps.cnum, substr(ps.term,2,4), case substr(ps.term,1,1) when 'W' then 1 when 'S' then 2 when 'F' then 3 end, ps.section 

-- Q7
select count(p.pnum)*100/(select count(pnum) from professor) as percantage \
from   professor p \
where   exists ( select * \
    from  section s1, section s2, enrollment e1, enrollment e2 \
    where s1.cnum = s2.cnum and s1.section != s2.section \
      and s1.cnum = e1.cnum and s1.term = e1.term and s1.section = e1.section \
      and s2.cnum = e2.cnum and s2.term = e2.term and s2.section = e2.section \
      and s1.pnum = p.pnum and s2.pnum = p.pnum \
      and e1.grade is not null and e2.grade is not null ) \
and not exists ( select * \
    from  section s1, section s2, enrollment e1, enrollment e2 \
    where s1.cnum = s2.cnum and s1.section != s2.section \
      and s1.cnum = e1.cnum and s1.term = e1.term and s1.section = e1.section \
      and s2.cnum = e2.cnum and s2.term = e2.term and s2.section = e2.section \
      and s1.pnum = p.pnum and s2.pnum = p.pnum \
      and e1.grade is null and e2.grade is null )