#1
CREATE TABLE students(
  sid int primary key,
  name varchar(50),
  age int,
  gpa float
);
CREATE TABLE courses(
  cid int primary key,
  deptid varchar(10),
  name varchar(50)
);
CREATE TABLE professors(
  ssn char(10) primary key,
  name varchar(50),
  address varchar(50),
  phone char(12),
  deptid varchar(10)
);
CREATE TABLE teaches(
  cid int,
  section int,
  primary key (cid, section),
  ssn char(10),
  foreign key (cid) references courses(cid),
  foreign key (ssn) references professors(ssn)
);
CREATE TABLE enrollment(
  sid int,
  cid int,
  primary key (sid, cid),
  section int,
  grade char(1),
  foreign key (sid) references students(sid),
  foreign key (cid) references courses(cid),
  foreign key (cid, section) references teaches(cid, section)
);

# dummy data
INSERT INTO students VALUES 
  (1, 'Brandon', 20, 4.0),
  (2, 'Anna', 20, 4.0),
  (3, 'Vineal', 20, 4.0),
  (4, 'brogman', 99, 0),
  (5, 'brogman2', 99, 0),
  (6, 'brogman3', 99, 0);
select * from students;

INSERT INTO courses VALUES
  (1, 'cs', 'Introduction to cs'),
  (2, 'cs', 'Data Structures'),
  (3, 'math', 'Intro to math reasoning'),
  (4, 'math', 'Real *****');
select * from courses;

INSERT INTO professors VALUES
  ('12-345-678', 'Molnar', '123 guy avenue', '123-456-7890', 'math'),
  ('99-345-678', 'Sesh', '1234 soft meth avenue', '999-456-7890', 'cs'),
  ('98-765-432', 'Parth', '999 UML lane', '999-999-9999', 'cs');
select * from professors;

INSERT INTO teaches VALUES
  (1, 1, '99-345-678'),
  (1, 2, '99-345-678'),
  (1, 3, '99-345-678'),
  (2, 1, '99-345-678'),
  (3, 1, '12-345-678'),
  (3, 2, '12-345-678'),
  (4, 1, '12-345-678');
select * from teaches;

INSERT INTO enrollment VALUES
  (1, 1, 2, 'A'),
  (1, 4, 1, 'B'),
  (2, 2, 1, 'A'),
  (2, 3, 1, 'B'),
  (3, 1, 1, 'A'),
  (3, 2, 1, 'A'),
  (4, 1, 1, 'D'),
  (4, 2, 1, 'D'),
  (4, 3, 1, 'F'),
  (4, 4, 1, 'F'),
  (5, 1, 1, 'F'),
  (5, 3, 1, 'F'),
  (6, 1, 1, 'F');
  
select * from enrollment;

#2 name of professors that work for cs
SELECT name
FROM professors
WHERE deptid = 'cs';

#3 students (sid) enrolled in courses in the cs department
SELECT DISTINCT e.sid
FROM enrollment e
JOIN courses c on e.cid = c.cid;

#4 list ssn and name of professors that work for the cs department, but are not teaching any cs courses
SELECT ssn, name
FROM professors
WHERE deptid = 'cs' 
  AND ssn NOT IN (
    SELECT ssn
    FROM teaches t
    JOIN courses c ON t.cid = c.cid
    WHERE c.deptid = 'cs'
  );

#5 list number of courses offered by each department (just number of courses not sections)
SELECT deptid, count(*) numCourses
FROM courses
GROUP BY deptid;

#6 list the departments that offer more than 10 courses.
# (untested)
SELECT t.deptid
FROM (
  SELECT deptid, count(*) numCourses
  FROM courses
  GROUP BY deptid
) t
WHERE t.numCourses > 10;

#7 list of name of students whose professor's name starts with M
SELECT DISTINCT s.name
FROM students s
JOIN enrollment e on s.sid = e.sid
JOIN (
  # courses taught by professors starting with M
  SELECT c.cid
  FROM courses c
  JOIN teaches t on c.cid = t.cid
  JOIN professors p on t.ssn = p.ssn
  WHERE p.name LIKE 'M%'
) t1 on e.cid = t1.cid;

#8 number of small, medium, and large sections per dept
#potential problem: does not account for empty sections
CREATE TEMPORARY TABLE smallMediumLarge
SELECT deptid, 
  SUM(IF(t1.numStudents < 30, 1, 0)) small, 
  SUM(IF(t1.numStudents >= 30 AND numStudents < 80, 1, 0)) medium, 
  SUM(IF(t1.numStudents >= 80, 1, 0)) large
FROM (
  # number of students per (course,section)
  SELECT c.deptid, e.cid, e.section, count(*) numStudents
  FROM enrollment e
  JOIN courses c on e.cid = c.cid
  GROUP BY c.deptid, e.cid, e.section
) t1
GROUP BY deptid;
SELECT * FROM smallMediumLarge;

#9 list of professors that work for departments with more than 20 faculty members 
# and that offer more large sections than small and medium sections combined

SELECT p.name
FROM professors p
WHERE p.deptid IN (
    # table of departments with more than 20 profs
    SELECT DISTINCT deptid
    FROM(
      SELECT p.deptid, count(*) numProfs
      FROM professors p
      GROUP BY p.deptid
    ) t1
    WHERE numProfs > 20
  )
  AND
  p.deptid IN (
  # table of departments with more large sections than small and medium
    SELECT deptid
    FROM smallMediumLarge
    WHERE large > small + medium
  );
  
#10 for each course find percentage of students that failed the course
CREATE TEMPORARY TABLE passPercentTable
SELECT e.cid, e.section, AVG(IF(e.grade = 'A' OR e.grade = 'B' OR e.grade = 'C', 1, 0)) passPercent
FROM enrollment e
GROUP BY e.cid, e.section;
SELECT * FROM passPercentTable;

#11 name of professor with max percentage of students that failed their course
# assuming most fail percent in a single section:
CREATE TEMPORARY TABLE passPercentTable2
SELECT * FROM passPercentTable;

SELECT DISTINCT p.name
FROM professors p
JOIN teaches t on p.ssn = t.ssn
JOIN (
  SELECT cid, section
  FROM passPercentTable2 pt2
  WHERE pt2.passPercent = (
    SELECT DISTINCT MIN(pt1.passPercent)
    FROM passPercentTable pt1
  )
) pt3 on t.cid = pt3.cid AND t.section = pt3.section;

#12 on average what percentage of students fail a course? total number 
# of students that failed / total number enrolled
CREATE TEMPORARY TABLE coursesFailTable
SELECT e.cid, AVG(IF(e.grade = 'D' OR e.grade = 'F', 1, 0)) failPercent
FROM enrollment e
GROUP BY e.cid;

#13 list of courses (sections) where the percentage of students with D/F is greater than average
SELECT AVG(cft.failPercent)
FROM coursesFailTable cft;

SELECT pp.cid, pp.section
FROM passPercentTable pp
WHERE pp.passPercent < (
  SELECT AVG(cft.failPercent)
  FROM coursesFailTable cft
);

#14 SPS table

SELECT * FROM enrollment e JOIN courses c on e.cid = c.cid WHERE c.deptid = 'math';

# total numbers for each course
SELECT c.deptid,
  SUM(numStudents) / SUM(numSections) SPS,
  SUM(numAs)/sum(numStudents) pA,
  SUM(numBs)/sum(numStudents) pB,
  SUM(numCs)/sum(numStudents) pC,
  SUM(numDs)/sum(numStudents) pD,
  SUM(numFs)/sum(numStudents) pF
FROM courses c
JOIN (
  #total numbers for each course
  SELECT t.cid, SUM(t.numStudents) numStudents, COUNT(*) numSections,
    SUM(numAs) numAs, SUM(numBs) numBs, SUM(numCs) numCs, SUM(numDs) numDs, SUM(numFs) numFs
  FROM (
    # total numbers for each section
    SELECT e.cid, e.section, count(*) numStudents, 
      SUM(IF(e.grade = 'A', 1, 0)) numAs,
      SUM(IF(e.grade = 'B', 1, 0)) numBs,
      SUM(IF(e.grade = 'C', 1, 0)) numCs,
      SUM(IF(e.grade = 'D', 1, 0)) numDs,
      SUM(IF(e.grade = 'F', 1, 0)) numFs
    FROM enrollment e
    GROUP BY e.cid, e.section
  ) t
  GROUP BY t.cid
) t2 on c.cid = t2.cid
GROUP BY c.deptid;