-- Current term is 1185

/*
The student number and name of second year students who have obtained
a grade lower than 65 in at least two courses in a department with the
name “computer science”.
*/
-- ran on empty
select distinct sname, snum from student where year = 2 and snum in
(
  select snum from mark m1 where grade < 65 and deptcode = 'CS' and snum in
  (
    select snum from mark where grade < 65 and deptcode = 'CS' and snum != m1.snum
  )
);

/*
The number and name of professors who are not in the pure math (PM)
department, and who are teaching CS245 for the first time.
*/
-- ran on empty
select distinct pnum, pname from professor where deptcode not in
(
  select deptcode from department where deptname = 'PM'
) and pnum in
(
  select pnum from class where deptcode = 'CS' and cnum = 245 and term = 1185
) and pnum not in
(
  select pnum from class where deptcode = 'CS' and cnum = 245 and term != 1185
);

/*
The number, name and year of each student who has obtained a grade in
CS240 that is within 3 marks of the highest ever grade recorded for that
course.
*/
select s.snum, s.sname, s.year from student s where s.snum in
(
  select snum from mark where deptcode = 'CS' and cnum = 240 and grade >= ((select grade from mark where deptcode = 'CS' and cnum = 240 order by grade desc limit 1) - 3)
);

/*
 The number and name of students who have completed two years, who
have a final grade of at least 85 in every computer science course that
they have taken, and who have always been taught by a professor in the
computer science (CS) department.
*/
select snum, sname from student where year > 2 and snum not in
(
  select snum from mark where deptcode = 'CS' and grade < 85
) and snum not in
(
  select e.snum from enrollment e where exists
  (
    select * from class c where
    c.deptcode = e.deptcode and c.cnum = e.cnum and c.term = e.term and c.section = e.section and c.pnum not in
    (
      select pnum from professor where deptcode != 'CS'
    )
  )
);

/*
 A sorted list of all departments who do not have a professor currently
teaching a course offered by a different department.
*/
select deptname from department where deptcode not in
(
  select p.deptcode from professor p where pnum in
  (
    select pnum from class where term = 1185 and deptcode != p.deptcode and pnum = p.pnum
  )
) order by deptname;

/*
For each pair of classes for the same course that were taught in the same
term, and that where also taught by different professors: the minimum
grades obtained and the maximum grades obtained. In addition to these
four values, each result should include the number and name of each professor,
as well as the identifying attributes for each class.
*/
with combineTable (grade, pname, pnum, deptcode, cnum, term, section) as
(select m.grade as grade, p.pname as pname, p.pnum as pnum,
        cl.deptcode as deptcode, cl.cnum as cnum, cl.term as term, cl.section as section
  from mark m, enrollment e, class cl, course co, professor p
  where m.snum = e.snum and m.deptcode = e.deptcode and m.cnum = e.cnum and m.term = e.term and m.section = e.section
      and
      cl.deptcode = e.deptcode and cl.cnum = e.cnum and cl.term = e.term and cl.section = e.section
      and
      cl.deptcode = co.deptcode and cl.cnum = co.cnum
      and
      cl.pnum = p.pnum)

select
(select grade from mark where deptcode = c1.deptcode and cnum = c1.cnum and term = c1.term and section = c1.section order by grade desc limit 1)
,
(select grade from mark where deptcode = c1.deptcode and cnum = c1.cnum and term = c1.term and section = c1.section order by grade asc limit 1)
,
(select grade from mark where deptcode = c2.deptcode and cnum = c2.cnum and term = c2.term and section = c2.section order by grade desc limit 1)
,
(select grade from mark where deptcode = c2.deptcode and cnum = c2.cnum and term = c2.term and section = c2.section order by grade asc limit 1),
c1.cnum, c1.term, c1.pnum, c1.pname, c1.deptcode, c1.section, c1.pnum,
c2.cnum, c2.term, c2.pnum, c2.pname, c2.deptcode, c2.section, c2.pnum
from combineTable c1, combineTable c2
where c1.term = c2.term and c1.deptcode = c2.deptcode and c1.cnum = c2.cnum and c1.pnum != c2.pnum;

/*
Pairs of distinct professors such that whenever the first one teaches a class
in a particular term the second also teaches a class for the same course
in the same term. Report a professor number and name for both the
professors.
*/
select p1.pnum, p1.pname, p2.pnum, p2.pname from professor p1, professor p2
 where p1.pnum != p2.pnum and not exists
 (
  select * from class c, class c2 where
    c.pnum = p1.pnum and c2.pnum = p2.pnum and (
    c.deptcode != c2.deptcode or
    c.term != c2.term or
    c.cnum != c2.cnum)
 );
-- NOW CAN USE AGGREGATION

/*
The course number and total enrollment count for all of its classes of each
course. Also, include only those course numbers for courses with a total
enrollment count among the three lowest such counts.
*/
-- Not sure if this will return an error, or 3 rows per old.deptcode, old.cnum since the 3rd parameter (the subquery) has 3 rows
-- didn't break running on an empty dataset
select old.deptcode, old.cnum, (select count(*) from enrollment where deptcode = old.deptcode and cnum = old.cnum group by deptcode, cnum order by count(*) asc limit 3) from class old;

/*
The percentage of professors in pure math who have always taught no
more than a single course in any given term. (Note that a percentage
should be a number between 0 and 100.)
*/
select count(*) from professor p where deptcode = 'PM' and pnum in
(
  select c.pnum from class c where c.pnum = p.pnum and c.pnum not in
  (
    select c1.pnum from class c1 where pnum = p.pnum and c1.term = c.term and (c1.deptcode != c.deptcode or c1.cnum != c.cnum)
  )
)
/ (select count(*) from professor where deptcode = 'PM');

/*
The number of different third or fourth year students in each section of
each course taught by a pure math professor in past terms. The result
should include the professor number, professor name, course number and
section, and should also be sorted first by the name of the professor,
then by the professor number, third by the course number, and finally by
section. (Note that a section is identified by a term and a section number.
Also assume that sorting by section means sorting by term and then by
section number. The result will therefore have a total of six columns.)
*/
-- ran on empty dataset
select (select pname from professor where pnum = c.pnum), c.pnum, c.cnum, c.term, c.section,
  (select count(distinct snum) from mark where snum in
  (
    select snum from student where year between 3 and 4
  ) and deptcode = c.deptcode and cnum = c.cnum and term = c.term and section = c.section)
from class c where pnum in
(
  select pnum from professor where deptcode = 'PM'
) and term != 1185;

/*
The ratio of professors in pure math (PM) to professors in applied math
(AM) who have taught a class in which the average grade obtained in the
class was greater than 77.
*/
select count(*) from professor p where deptcode = 'PM' and pnum in
(
  (select c.pnum from class c where
  (
    select avg(grade) from mark where c.deptcode = deptcode and c.cnum = cnum and c.term = term and c.section = section
  ) > 77)
)
/
(select count(*) from professor p where deptcode = 'AM' and pnum in
(
  select c.pnum from class c where
  (
    select avg(grade) from mark where c.deptcode = deptcode and c.cnum = cnum and c.term = term and c.section = section
  ) > 77
));

/*
For the current term, report how many courses there are in the schedule
with a particular number of classes. For example an output
{[5, 1], [4, 2], [1, 5]}
indicates that there are 5 courses with a single class (section), 4 courses
with 2 classes, and 1 course with 5 classes scheduled in the curent term.
*/
-- ran on empty dataset
with eachclass (sectioncount) as
(select count(*) from class
  where term = 1185
  group by (deptcode, cnum))
select count(*), sectioncount from eachclass group by sectioncount;