--
-- Join DB FSpring 2024 HW #2
--

-- (a) List the stuId, names and credits of students majoring in CSCI and have 30 to 60 credits (ach) in the following format.
SELECT DISTINCT s.stuId, s.fname, s.lname, s.ach AS credits
FROM student AS s
WHERE s.major = 'CSCI'
AND s.ach >= 30
AND s.ach <= 60;

-- OR

SELECT DISTINCT s.stuId, s.fname, s.lname, s.ach AS credits
FROM student AS s
WHERE s.major = 'CSCI'
AND s.ach BETWEEN 30 AND 60;

-- (b) List the student names of minoring in CINF or ITED with classes (classId) and grades they have taken in the following manner.
SELECT DISTINCT s.fname, s.lname, e.classId, e.grade
FROM student AS s INNER JOIN enroll AS e ON (s.stuId = e.stuId)
WHERE s.minor IN ('CINF', 'ITEC');

-- (c) Repeat (b) but also list the course names in the following manner. Further, if a grade is missing, show an empty string.
SELECT DISTINCT s.fname, s.lname, e.classId,
	co.title AS `Course Title`,
	IFNULL(e.grade,'') AS grade
FROM student AS s INNER JOIN enroll AS e ON (s.stuId = e.stuId)
	INNER JOIN class AS c ON (e.classId = c.classId)
	INNER JOIN course AS co ON (c.courseId = co.courseId)
WHERE s.minor IN ('CINF', 'ITEC');

-- (d) Repeat (c) in the following manner, add a column to show the name of the instructor.
SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS student,
	e.classId,
	co.title AS `Course Title`,
	CONCAT(f.fname, ' ', f.lname) AS instructor,
	IFNULL(e.grade,'') AS grade
FROM student AS s INNER JOIN enroll AS e ON (s.stuId = e.stuId)
	INNER JOIN class AS c ON (e.classId = c.classId)
	INNER JOIN course AS co ON (c.courseId = co.courseId)
	INNER JOIN faculty As f ON (c.facId = f.facId)
WHERE s.minor IN ('CINF', 'ITEC');

-- (e) List all student names, with their major department names and their faculty advisor names in the following manner. Only include students who have [1] a declared minor, [2] a faculty advisor, and [3] enrolled in at least one classes.
SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS student,
	d.deptName AS major,
	CONCAT(f.fname, ' ', f.lname) AS advsior
FROM student AS s INNER JOIN enroll AS e ON (s.stuId = e.stuId)
	INNER JOIN department AS d ON (s.major = d.deptCode)
	INNER JOIN faculty AS f ON (s.advisor = f.facId)
WHERE s.minor IS NOT NULL;