tee 2025_10_13_sql_log.txt

-- subqueries in the WHERE course
-- students not enrolled in any class.
SELECT DISTINCT *
FROM student AS s
WHERE s.stuId NOT IN (SELECT DISTINCT e.stuID FROM enroll AS e);

-- student with the maximum number of ach.
SELECT DISTINCT MAX(ach)
FROM student;

-- student within 60 credits of the maximum number of ach any student may have.

SELECT DISTINCT MAX(ach), COUNT(*) FROM student;

-- MAX: group functions apply to a group of rows.

-- CONCAT: regular functions apply to each row

SELECT CONCAT(fname, ' ', lname) FROM student
WHERE major = 'CSCI';

SELECT DISTINCT s.stuId,
   CONCAT(s.fname, ' ', s.lname) AS student,
   s.ach AS credits
FROM student AS s
WHERE s.ach + 60 >=
   (SELECT DISTINCT MAX(ach), COUNT(*) FROM student);
   -- subquery returns a table of one row with one value
   -- implicit type conversion

-- subqueries as derived tables.
SELECT DISTINCT s.stuId,
   CONCAT(s.fname, ' ', s.lname) AS student,
   s.ach AS credits, m.max
FROM student AS s INNER JOIN
   (SELECT DISTINCT MAX(ach) AS max FROM student) AS m -- an alias is required.
WHERE s.ach + 60 >= m.max;

WITH  t1 AS
   (SELECT MAX(ach) AS max FROM student)
SELECT s.stuId,
   s.ach AS `ach credits`,
   t1.max - s.ach AS `diff from max credits of all`
FROM student AS s, t1
ORDER BY `ach credits` DESC;

-- multiple common tables (not efficient; used as demonstration.)
WITH t1 AS
   (SELECT MAX(ach) AS max FROM student),
t2 AS
   (SELECT s.stuId,
      s.ach AS `ach credits`,
      t1.max - s.ach AS diff,
      s.major
    FROM student AS s, t1)
SELECT t2.stuId, t2.`ach credits`,
   t2.diff AS `diff from max credits of all`,
   d.deptName AS department
FROM t2 LEFT JOIN department d ON (t2.major = d.deptCode)
ORDER BY t2.`ach credits` DESC;

-- [1] t1
SELECT MAX(ach) AS max FROM student;

-- [2] t2
WITH t1 AS
   (SELECT MAX(ach) AS max FROM student)
SELECT s.stuId,
      s.ach AS `ach credits`,
      t1.max - s.ach AS diff,
      s.major
    FROM student AS s, t1;

SELECT DISTINCT s.stuId, s.major  -- not good as stuId is for the old raw row, not the new row (group)
FROM student AS s  -- [1] Big table
WHERE s.ach > 20  -- [2] filter the row in the big table
GROUP BY s.major -- [3] form group: each s.major value -> 1 group
	-- group becomes the new row.
;

SELECT DISTINCT s.stuId, s.major  -- not good as stuId is for the old raw row, not the new row (group)
FROM student AS s  -- [1] Big table
WHERE s.ach > 20 ;

SELECT DISTINCT COUNT(s.stuId), s.major  -- COUNT: group function
FROM student AS s  -- [1] Big table
WHERE s.ach > 20  -- [2] filter the row in the big table
GROUP BY s.major -- [3] form group: each s.major value -> 1 group
	-- group becomes the new row.
;

SELECT DISTINCT s.major, COUNT(s.stuId) AS `Number of majors`  
-- COUNT: group function; can only use Group functions
FROM student AS s  -- [1] Big table
WHERE s.ach > 20  -- [2] filter the row in the big table
GROUP BY s.major -- [3] form group: each s.major value -> 1 group
	-- group becomes the new row.
;

SELECT MAX(ach) AS max FROM student;
-- MAX: group function: need a group. THe group is the entire student table.

SELECT DISTINCT s.stuId, s.major  -- not good as stuId is for the old raw row, not the new row (group)
FROM student AS s  -- [1] Big table
WHERE s.ach > 20 ;

SELECT DISTINCT s.major, COUNT(s.stuId) AS `Number of majors`,
		GROUP_CONCAT(s.stuId) AS `ids of students in the major`
-- COUNT: group function; can only use Group functions
FROM student AS s  -- [1] Big table
WHERE s.ach > 20  -- [2] filter the row in the big table
GROUP BY s.major -- [3] form group: each s.major value -> 1 group
	-- group becomes the new row.
;

SELECT DISTINCT s.major, COUNT(s.stuId) AS `Number of majors`,
		GROUP_CONCAT(s.stuId) AS `ids of students in the major`
-- COUNT: group function; can only use Group functions
FROM student AS s  -- [1] Big table
WHERE s.ach > 20  -- [2] filter the row in the big table
GROUP BY s.major -- [3] form group: each s.major value -> 1 group
	-- group becomes the new row.
ORDER BY `Number of majors` DESC;

SELECT DISTINCT s.major, COUNT(s.stuId) AS `Number of majors`,
		GROUP_CONCAT(s.stuId) AS `ids of students in the major`
-- COUNT: group function; can only use Group functions
FROM student AS s  -- [1] Big table
WHERE s.ach > 20  -- [2] filter the row in the big table
GROUP BY s.major -- [3] form group: each s.major value -> 1 group
	-- group becomes the new row.
HAVING `Number of majors` > 1
ORDER BY `Number of majors` DESC;

;