-- 10/28/2019

SELECT e.*     -- step 2
FROM Enroll e; -- step 1

SELECT s.*, e.*     -- step 2
FROM Student s INNER JOIN Enroll e
    ON (s.stuId = e.stuId) -- step 1: bit temp table
; 

SELECT s.*, e.*               -- step 3
FROM Student s INNER JOIN Enroll e
    ON (s.stuId = e.stuId)    -- step 1: bit temp table
WHERE s.major = 'CSCI'        -- step 2: filter step 1
; 

SELECT s.stuId, s.fname, s.lname, e.classId, e.grade               
                              -- step 3: get the columns
FROM Student s INNER JOIN Enroll e
    ON (s.stuId = e.stuId)    -- step 1: bit temp table (22 rows)
WHERE s.major = 'CSCI'        -- step 2: filter step 1 (11 rows)
; 

SELECT s.stuId
        -- s.stuId, s.fname, s.lname, e.classId, e.grade               
                              -- step 4: get the columns
FROM Student s INNER JOIN Enroll e
    ON (s.stuId = e.stuId)    -- step 1: bit temp1 table (22 rows)
WHERE s.major = 'CSCI'        -- step 2: filter step 1 (11 rows)
GROUP BY s.stuId              -- step 3: form groups by unique values of s.stuId
    -- stuId = 100000 -> group #1 => 6 rows become 1 group -> 1 new row (Temp2)
    -- stuId = 100001 -> group #2 => 2 rows become 1 group -> 1 new row (Temp2)
    -- stuId = 100002 -> group #3 => 3 rows become 1 group -> 1 new row (Temp2)
; 


SELECT s.stuId,
       COUNT(e.classId) AS `# of classes`  -- COUNT: group function
        -- s.fname, s.lname, e.classId, e.grade               
                              -- step 4: get the columns
FROM Student s INNER JOIN Enroll e
    ON (s.stuId = e.stuId)    -- step 1: bit temp1 table (22 rows)
WHERE s.major = 'CSCI'        -- step 2: filter step 1 (11 rows)
GROUP BY s.stuId              -- step 3: form groups by unique values of s.stuId
    -- stuId = 100000 -> group #1 => 6 rows become 1 group -> 1 new row (Temp2)
    -- stuId = 100001 -> group #2 => 2 rows become 1 group -> 1 new row (Temp2)
    -- stuId = 100002 -> group #3 => 3 rows become 1 group -> 1 new row (Temp2)
; 

SELECT s.stuId, s.fname, s.lname, 
       COUNT(e.classId) AS `# of classes`  -- COUNT: group function      
                              -- step 4: get the columns
FROM Student s INNER JOIN Enroll e
    ON (s.stuId = e.stuId)    -- step 1: bit temp1 table (22 rows)
WHERE s.major = 'CSCI'        -- step 2: filter step 1 (11 rows)
GROUP BY s.stuId , s.fname, s.lname             -- step 3: form groups by unique values of s.stuId
    -- stuId = 100000 -> group #1 => 6 rows become 1 group -> 1 new row (Temp2)
    -- stuId = 100001 -> group #2 => 2 rows become 1 group -> 1 new row (Temp2)
    -- stuId = 100002 -> group #3 => 3 rows become 1 group -> 1 new row (Temp2)
; 

SELECT s.stuId, s.fname, s.lname, s.major, e.classId
FROM Student s INNER JOIN Enroll e
    ON (s.stuId = e.stuId)    -- step 1: bit temp1 table (22 rows)
; 

SELECT s.stuId, CONCAT(s.fname, ' ', s.lname) AS student, 
    s.major, e.classId
FROM Student s INNER JOIN Enroll e
    ON (s.stuId = e.stuId)    -- step 1
; 

SELECT s.stuId, 
    CONCAT(s.fname, ' ', s.lname) AS `student`, 
    s.major,                       -- step #4
    COUNT(e.classId) AS `Number of classes`
FROM Student s INNER JOIN Enroll e
    ON (s.stuId = e.stuId)         -- step 1
GROUP BY s.stuId, `student`, s.major -- step 3
; 


-- Only include stuId with enrollment. E.g. 100003 not in the result
-- because 100003 not in enroll table.
SELECT s.stuId, 
    CONCAT(s.fname, ' ', s.lname) AS `student`, 
    s.major,                       -- step #4
    COUNT(e.classId) AS `Number of classes`
FROM Student s INNER JOIN Enroll e
    ON (s.stuId = e.stuId)         -- step 1
GROUP BY s.stuId, `student`, s.major -- step 3
ORDER BY `Number of classes` DESC   -- step #5
;     

-- include student with no enrollment.
SELECT s.stuId, 
    CONCAT(s.fname, ' ', s.lname) AS `student`, 
    s.major,                       -- step #4
    COUNT(e.classId) AS `Number of classes`
FROM Student s LEFT JOIN Enroll e
    ON (s.stuId = e.stuId)         -- step 1
GROUP BY s.stuId, `student`, s.major -- step 3
ORDER BY `Number of classes` DESC   -- step #5
;    


SELECT s.stuId, s.fname, s.lname, s.major, e.*
FROM Student s INNER JOIN Enroll e
    ON (s.stuId = e.stuId)    -- step 1: bit temp1 table (22 rows)
; 

SELECT s.*, e.*
FROM Student s INNER JOIN Enroll e
    ON (s.stuId = e.stuId)    -- step 1: bit temp1 table (22 rows)
; 

SELECT s.*, e.*
FROM Student s LEFT JOIN Enroll e
    ON (s.stuId = e.stuId)    -- step 1: bit temp1 table (22 rows)
; 


SELECT sc.*, d.*
FROM school sc INNER JOIN department d ON  (d.schoolCode = sc.schoolCode);


SELECT sc.*, d.*
FROM school sc LEFT JOIN department d ON  (d.schoolCode = sc.schoolCode);


SELECT sc.schoolCode, sc.schoolName,
    COUNT(d.deptCode) AS `# of departments`
FROM school sc LEFT JOIN department d ON  (d.schoolCode = sc.schoolCode)
GROUP BY sc.schoolCode, sc.schoolName;


SELECT s.major, s.minor, COUNT(s.stuId) AS `count`,
    AVG(s.credits) AS `average credits`
FROM student s
GROUP BY s.major, s.minor;

SELECT d.deptCode, d.deptName, sc.schoolName -- step 2 full query.
FROM department d INNER JOIN school sc ON (d.schoolCode = sc.schoolCode);

SELECT f.fname, f.lname, temp.deptName, temp.schoolName
FROM faculty f,
    (SELECT d.deptCode, d.deptName, sc.schoolName
     FROM department d INNER JOIN school sc ON (d.schoolCode = sc.schoolCode))
    AS temp  -- subqery  -- step 1
WHERE f.deptCode = temp.deptCode;