--
-- Database Systems, Fall 2025 HW #1
--

-- (a) Show the stuId, name, major, and minor of all students who are majoring in CSCI, CINF or ITEC.
SELECT DISTINCT s.stuId, 
	CONCAT(s.fname, ' ' ,s.lname) AS name,
	s.major, s.minor
FROM student AS s
WHERE s.major IN ('CSCI', 'CINF', 'ITEC');

-- (b) List the names of all departments together with their faculty members' names and ranks of the School 'Science and Engineering' in the following format. You should not use 'CSE' in your query.
SELECT DISTINCT CONCAT(f.fname, ' ' ,f.lname) AS faculty,
	f.`rank`,
	d.deptName AS department
FROM faculty AS f INNER JOIN department AS d ON (d.deptCode = f.deptCode)	 
	INNER JOIN school AS s ON (s.schoolCode = d.schoolCode)
WHERE s.schoolName = 'Science and Engineering';

-- (c) List the names of students, and their grades of all enrolled 'ENGL', 'CSCI' and 'ITEC' classes, together with the students' grades, semesters and years of the classes, and the course names in the following manner. Note that the result is shown in the ascending order of the student names.

SELECT DISTINCT s.stuId, 
	CONCAT(s.fname, ' ' ,s.lname) AS student,
	co.rubric, co.`number`, co.title,
	c.semester, c.year, e.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 co.rubric IN ('ENGL', 'CSCI', 'ITEC')
ORDER BY student ASC;

-- (d) List the ids, names, and their faculty advisor names of every student in the following manner. List only students with a faculty advisor.
SELECT DISTINCT s.stuId, 
	CONCAT(s.fname, ' ' ,s.lname) AS student,
	CONCAT(f.fname, ' ' ,f.lname) AS advisor
FROM student AS s INNER JOIN faculty AS f ON (s.advisor = f.facId);

-- (e) List the ids, names and major names of all students with a declared major but no declared minor 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 IS NULL;