--
-- Join DB Spring 2025 Homework #1
--

-- [1] List the stuId, names, and majors of students minoring in ITEC and having 1016 as their faculty advisors in the following manner.
SELECT DISTINCT s.stuId, 
    CONCAT(s.fname, ' ', s.lname) AS student,
	d.deptName AS major
FROM student AS s INNER JOIN department AS d ON (s.major =  d.deptCode)
WHERE s.minor = 'ITEC'
AND s.advisor = 1016;


-- [2] List the names, numbers of credits (ach), majors, and advisor names of students having 1011, 1012 or 1015 as faculty advisor in the following manner.
SELECT DISTINCT s.stuId, CONCAT(s.fname, ' ', s.lname) AS student,
	s.ach AS credits,
	d.deptName AS major,
    CONCAT(f.fname, ' ', f.lname) AS advisor
FROM student AS s INNER JOIN faculty AS f ON (s.advisor = f.facId)
    LEFT JOIN department AS d ON (s.major =  d.deptCode)
WHERE s.advisor IN (1011, 1012, 1015);
-- INNER JOIN department AS d ON (s.major =  d.deptCode) is acceptable as we have not covered LEFT JOIN yet.


-- [3] List the enrollment information of all CSCI courses that have been offered in the following manner.
SELECT DISTINCT CONCAT(co.rubric, ' ', co.number) AS `course id`,
	co.title AS course,
    co.credits,
	cl.semester,
	cl.year,
    CONCAT(f.fname, ' ', f.lname) AS instructor,
	CONCAT(s.fname, ' ', s.lname) AS student,
	e.grade
FROM course AS co INNER JOIN class AS cl ON (co.courseId = cl.courseId)
	INNER JOIN enroll AS e ON (cl.classId = e.classId)
	INNER JOIN student AS s ON (s.stuId = e.stuId)
	INNER JOIN faculty AS f ON (cl.facId = f.facId)
WHERE co.rubric = 'CSCI';

-- [4] Repeat [3], show only those entries with a grade of B or above.
SELECT DISTINCT CONCAT(co.rubric, ' ', co.number) AS `course id`,
	co.title AS course,
    co.credits,
	cl.semester,
	cl.year,
    CONCAT(f.fname, ' ', f.lname) AS instructor,
	CONCAT(s.fname, ' ', s.lname) AS student,
	e.grade
FROM course AS co INNER JOIN class AS cl ON (co.courseId = cl.courseId)
	INNER JOIN enroll AS e ON (cl.classId = e.classId)
	INNER JOIN student AS s ON (s.stuId = e.stuId)
	INNER JOIN faculty AS f ON (cl.facId = f.facId)
WHERE co.rubric = 'CSCI'
AND e.grade IN ('A', 'A-', 'B+', 'B');

-- OR
SELECT DISTINCT CONCAT(co.rubric, ' ', co.number) AS `course id`,
	co.title AS course,
    co.credits,
	cl.semester,
	cl.year,
    CONCAT(f.fname, ' ', f.lname) AS instructor,
	CONCAT(s.fname, ' ', s.lname) AS student,
	e.grade
FROM course AS co INNER JOIN class AS cl ON (co.courseId = cl.courseId)
	INNER JOIN enroll AS e ON (cl.classId = e.classId)
	INNER JOIN student AS s ON (s.stuId = e.stuId)
	INNER JOIN faculty AS f ON (cl.facId = f.facId)
	INNER JOIN grade AS g ON (e.grade = g.grade)
WHERE co.rubric = 'CSCI'
AND g.gradePoint >= 3.0;

-- (e) List the names and ids of all students who have a failing grade (C- or below) or no grade in some courses in the following manner.
SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS `student with failing grades or no grade`,
    s.stuId AS `student id`
FROM student AS s INNER JOIN enroll AS e ON (s.stuId = e.stuId)
WHERE e.grade IS NULL
OR e.grade IN ('C-', 'D+', 'D', 'D-', 'F');