--
--  Solution for even numbered questions toyu SQL query.
--

--
--  Preparation:
--
SELECT * FROM grade;
SELECT * FROM school;
SELECT * FROM department;
SELECT * FROM faculty;
SELECT * FROM course;
SELECT * FROM `class`;
SELECT * FROM student;
SELECT * FROM enroll;

-- Q2. Show all information of students majoring in 'CSCI'.
SELECT *
FROM Student
WHERE major = 'CSCI';

-- Q4. Show the names AND credits of students majoring in 'CSCI' 
-- AND having 40 OR more credits.
SELECT DISTINCT fName, lName, credits
FROM Student
WHERE major = 'CSCI'
AND credits >= 40;

-- Q6. Show the codes of departments with faculty in the rank of 'Professor'.
SELECT DISTINCT deptCode
FROM faculty
WHERE `rank` = 'Professor';

-- Q8. Show the names of students who have enrolled in 10000.
SELECT DISTINCT s.fName, s.lName
FROM student s INNER JOIN enroll e ON (s.stuId = e.stuId)
WHERE e.classId = 10000;

-- Q10. Show the names, major names, and advisor names of every student.
SELECT DISTINCT s.fName, s.lName, d.deptName,
    f.fName, f.lName
FROM student s INNER JOIN department d ON (s.major = d.deptCode)
    INNER JOIN faculty f ON (s.advisor = f.facId);

-- Q12. Show the student names and their major names for all 
-- students who have received a grade A in a class offered 
-- by a faculty from the CSCI department.
SELECT DISTINCT s.fName, s.lName, d.deptName
FROM student s INNER JOIN department d ON (s.major = d.deptCode)
    INNER JOIN enroll e ON (s.stuId = e.stuId)
    INNER JOIN `class` c ON (e.classId = c.classId)
    INNER JOIN faculty f ON (c.facId = f.facId)
WHERE f.deptCode = 'CSCI'
AND e.grade = 'A';

-- Q14. Show the classId, facId and room of all classes 
-- with student 100001 enrolled.
SELECT DISTINCT c.classId, c.facId, c.room
FROM enroll e INNER JOIN `class` c USING (classId)
WHERE e.stuId = 100001;

-- Q15. Show the classId, facId and room of all classes 
-- with student 100001 or 100002 enrolled.
SELECT DISTINCT c.classId, c.facId, c.room
FROM enroll e INNER JOIN `class` c USING (classId)
WHERE (e.stuId = 100001
OR e.stuId = 100002);

-- Q16. Show the classId, facId and room of all classes 
-- with student 100001 and 100002 enrolled.
SELECT DISTINCT c.classId, c.facId, c.room
FROM `class` c INNER JOIN enroll e1 USING (classId)
    INNER JOIN enroll e2 USING (classId)
WHERE e1.stuId = 100001
AND e2.stuId = 100002;
	 
-- Q18. Show the ids and names of all inactive students 
--     (students not enrolled in any class).
SELECT DISTINCT s.stuid, s.fName, s.lName
FROM student s
WHERE s.stuid NOT IN (SELECT DISTINCT stuid FROM enroll);

-- Q20. Show the ids and names of students who are enrolled
--    in classes offerred only in the same room.
SELECT DISTINCT s.stuId, s.fName, s.lName
FROM student s INNER JOIN enroll e ON (s.stuId = e.stuId)
WHERE s.stuId NOT IN
   (SELECT DISTINCT e1.stuId
    FROM enroll e1 INNER JOIN `class` c1 ON (e1.classId = c1.classId)
    INNER JOIN enroll e2 ON (e1.stuId = e2.stuId)
    INNER JOIN `class` c2 ON (e2.classId = c2.classId)
    WHERE c1.room <> c2.room);

-- Q22. Show the id of students majoring in CSCI but have not taken
-- any course from faculty 1012.
SELECT DISTINCT s.Stuid
FROM student s
WHERE s.major = 'CSCI'
AND s.stuId NOT IN
	(SELECT e.stuId
	 FROM enroll e INNER JOIN `class` c ON (e.classId = c.classId)
	 WHERE c.facId = 1012);

-- Q24. Show the ids of students who have enrolled 
-- in all courses taught by faculty 1014.

-- Method #1
SELECT DISTINCT s.stuid
FROM student s
WHERE NOT EXISTS
	(SELECT DISTINCT c.classId
	 FROM class c
	 WHERE c.facId = 1014
	 AND NOT EXISTS
		(SELECT DISTINCT e.*
		 FROM enroll e
		 WHERE e.stuId = s.stuId
		 AND c.classId = e.classId));
		 
-- Method #2
SELECT DISTINCT s.stuId
FROM student s
WHERE 
	(SELECT COUNT(e1.classId)
	 FROM enroll e1 INNER JOIN `class` c1 ON (e1.classId = c1.classId)
	 WHERE e1.StuId = s.stuId
	 AND c1.facId = 1014)
	=
	(SELECT COUNT(c2.classId)
	 FROM class c2
	 WHERE c2.facId = 1014);

-- 26. Show the number of students enrolled in each course 
-- that has at least one student student in descending order 
-- of numbers of students.
SELECT classId, 
    COUNT(stuId) AS `Number of students`
FROM enroll
GROUP BY classId
ORDER BY `Number of students` DESC;

-- 28. Show the numbers of students each faculty member (with facid) teaches.
-- Only faculty with at least one student should be shown.
SELECT c.facId, COUNT(e.stuId) AS `Number of students`
FROM class c LEFT JOIN enroll e ON (c.classId = e.classId)
GROUP BY c.facId
ORDER BY `Number of students` DESC;

-- 30. List the average number of credits students have taken.
SELECT AVG(credits)
FROM student;

-- 32. List the majOR department names and the total number of credits 
--     taken by all students in the major. List only the department with
--     a total number of credits more than 70.
SELECT d.deptName as department, 
       sum(s.credits) as `Total number of credits`
FROM student s join department d on (s.major = d.deptCode)
GROUP BY department
HAVING `Total number of credits` > 70
ORDER BY `Total number of credits` DESC;

-- 34. List the department names and their numbers of major 
-- students and minor students.
SELECT DISTINCT d.deptName AS department, 
    IF (majors.n_majors IS NULL, 0, majors.n_majors) AS n_majors,
    IF (minors.n_minors IS NULL, 0, minors.n_minors) AS n_minors
FROM department d LEFT JOIN 
    (SELECT s1.major AS deptCode, COUNT(s1.stuId) AS n_majors
     FROM student s1
     GROUP BY deptCode) AS majors
        ON (d.deptCode = majors.deptCode)
    LEFT JOIN 
    (SELECT s2.minor AS deptCode, COUNT(s2.stuId) AS n_minors
     FROM student s2
     GROUP BY deptCode) AS minors
        ON (d.deptCode = minors.deptCode);