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 as old_snum from mark 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 != old_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 snum >= (select grade from mark where deptcode = "CS" and cnum = 240 order by grade desc limit 1)
)

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


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

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

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