cs348/a2/a2.sql

-- 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”.
*/
select distinct sname, snum from student where year = 2 and snum in
(
  select snum from mark m1 where grade < 65 and deptcode in
  (
    select deptcode from department where deptname = "computer science"
  ) /*having count(distinct section) > 1*/ and snum in
  (
    select snum from mark where grade < 65 and deptcode in
    (
      select deptcode from department where deptname = "computer science"
    ) 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.
*/
select distinct pnum, pname from professor where deptcode not in
(
  select deptcode fromm 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 distinct snum, sname, year from student where snum in
(
  select snum, grade 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.
ASSUMING THIS PROFESSOR RESTRICTION ONLY APPLIES TO THE CS COURSES
*/
select distinct snum, sname from student where year > 2 and snum not in
(
  select snum, grade from mark where deptcode = "CS" and grade < 85
) and snum not in
(
  select snum as prev_snum, deptcode as prev_deptcode, cnum as prev_cnum, term as prev_term, section as prev_section from mark where cnum in
  (
    select pnum, cnum from class where prev_deptcode = deptcode and prev_cnum = cnum and prev_term = term and prev_section = section and pnum in
    (
      select pnum from professor where deptcode = prev_deptcode
    )
  )
)

/*
 A sorted list of all departments who do not have a professor currently
teaching a course offered by a different department.
*/
select deptname as prof_dept from department where deptcode not in
(
  select deptcode as old_dept, pnum as old_pnum from professor where pnum in
  (
    select pnum from class where term = 1185 and deptcode != old_dept and pnum = old_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 oprofessor number and name for both the
professors.
*/


-- 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.
*/
select course.cnum, count(class) from course, class, where count(class)



select course.cnum, COUNT() from course, joinedTable
inner join class on enrollment.deptcode = class.deptcode, enrollment.cnum = class.cnum, enrollment.term = class.term, enrollment.section = class.section as joinedTable

/*
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 (select count(*) from professor p where deptcode = "PM" and pnum in
(
  select c.pnum, c.deptcode, c.cnum, c.term, c.section from class c where c.pnum = p.pnum and c.pnum not in
  (
    select c1.pnum, c1.deptcode, c1.cnum, c1.term, c1.section 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") as percentage

/*
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.)
*/

/*
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 (select count(*) from professor p where deptcode = "PM" and pnum in
(
  (select c.pnum, c.deptcode, c.cnum, c.term, c.section 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, c.deptcode, c.cnum, c.term, c.section 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
)) as ratio

/*
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.
*/