-- F18 Q5
--
-- [1] Output columns:
-- 1.	Student: student.fname, ' ', student.lname
-- 2.	Major: student.major
-- 3.	Number of credits: credits
-- [2] Source: student
-- [3] condition: credits between 30 and 80
-- [4] Ordering: descending order of the number of credits.
SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS student,
    s.major,
    s.credits AS `Number of credits`
FROM student AS s
WHERE credits BETWEEN 30 AND 80
ORDER BY s.credits DESC;

-- Q6
-- [1] Output columns:
-- 1.	faculty not teaching CSCI classes: 
--        faculty.fname, ‘ ‘,faculty.lname
-- [2] Sources:
-- 1.	faculty
-- [3] do not teach any CSCI course.
--      facId not in result of Q6a
SELECT DISTINCT CONCAT(f.fname, ' ', f.lname) 
    AS `faculty not teaching CSCI classes` 
FROM faculty f
WHERE f.facId NOT IN -- Q6a result: subquery
    (SELECT DISTINCT c.facId
     FROM class c INNER JOIN course co ON (c.courseId = co.courseId)
     WHERE co.rubric = 'CSCI');

-- Q6a
-- Ouptut: facId in faculty  1011, 1012, 1013
-- Source: course (CSCI), class (facId teaching the CSCI courses)
-- Condition: 
-- 1.	Join: class.CourseId (FK in class) = course.CourseID (PK in course)
-- 2.	Problem: course.rubric = 'CSCI'
SELECT DISTINCT c.facId
FROM class c INNER JOIN course co ON (c.courseId = co.courseId)
WHERE co.rubric = 'CSCI';

-- Also a professor.
SELECT DISTINCT CONCAT(f.fname, ' ', f.lname) 
    AS `faculty not teaching CSCI classes` 
FROM faculty f
WHERE f.facId NOT IN -- Q6a result: subquery
    (SELECT DISTINCT c.facId
     FROM class c INNER JOIN course co ON (c.courseId = co.courseId)
     WHERE co.rubric = 'CSCI')
AND `rank` = 'Professor';


-- What about: Name of faculty member 
-- who teaches a nob-CSCI classes.
SELECT DISTINCT CONCAT(f.fname, ' ', f.lname) 
    AS `faculty not teaching CSCI classes` 
FROM faculty f INNER JOIN class c ON (f.facId = c.facId)
    INNER JOIN course co ON (c.courseId = co.courseId)
WHERE co.rubric <> 'CSCI';