tee 2025_10_15_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;

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
AND `Number of majors` > 1  -- no group yet
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
AND COUNT(s.stuId) > 1  -- no group yet
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
GROUP BY s.major -- [3] form group: each s.major value -> 1 group
	-- group becomes the new row.
HAVING s.ach > 20  -- [2] filter the row in the big table
AND COUNT(s.stuId) > 1  -- error.
ORDER BY `Number of majors` DESC;

SELECT DISTINCT s.major AS `major code`, -- group by column
		d.deptName AS `major name`, -- group by column
		COUNT(s.stuId) AS `Number of majors`, -- group function
		GROUP_CONCAT(s.stuId) AS `ids of students in the major`, -- group function
		'ta da' -- constant
-- COUNT: group function; can only use Group functions
FROM student AS s  -- [1] Big table
	INNER JOIN department AS d ON (s.major = d.deptCode)
WHERE s.ach > 20  -- [2] filter the row in the big table
GROUP BY s.major, d.deptName -- [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;

-- Prepared statements.
SET @sql = "SELECT * FROM toyu.student";
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- with placeholders.
SET @sql = "SELECT * FROM toyu.student WHERE major = ? AND ach >= ?";
PREPARE stmt FROM @sql;
SET @major = 'CSCI';
SET @ach = 38;
EXECUTE stmt USING @major, @ach;

EXECUTE stmt USING 'CSCI', 38;

SET @major = 'CINF';
SET @ach = 15;
EXECUTE stmt USING @major, @ach;

SET @major = 'ITEC';
SET @ach = 25;
EXECUTE stmt USING @major, @ach;

DEALLOCATE PREPARE stmt;

CREATE OR REPLACE VIEW school_summary(
   schoolCode, schoolName, n_departments) AS
SELECT s.schoolCode, s.schoolName,
COUNT(d.deptCode) AS n_departments
FROM school AS s LEFT JOIN department AS d ON (s.schoolCode = d.schoolCode)
GROUP BY s.schoolCode, s.schoolName;

SHOW CREATE VIEW school_summary;
-- Note something like "ALGORITHM=UNDEFINED DEFINER=`yue`@`localhost` SQL SECURITY DEFINER"
-- (default values) may be added.
DESC school_summary;

SELECT *
FROM school_summary
WHERE n_departments > 0;
DROP VIEW school_summary;