tee 2026_3_16_sql_log.txt

SELECT d.schoolCode, 
	AVG(d.numStaff) AS `Average staff in departments`,
	COUNT(d.deptCode) AS `Number of departments` -- step [5] prepare the output columns from [4]
FROM department AS d -- step [1]: create a big source table with raw rows.
WHERE d.numStaff >= 5 -- step [2]: filter the rows of [1].
GROUP BY d.schoolCode -- step [3] form groups using distinct values of the group by columns: d.schoolCode. A group -> new row. Original row will no longer be accessible.
HAVING `Number of departments` >= 2 -- step [4] filter out the group/new row.
ORDER BY `Number of departments` ASC -- step [6] order the result of [5]
;

WITH ma AS
   (SELECT s.major AS deptCode, COUNT(s.stuId) AS numMajor
    FROM student AS s
    GROUP BY s.major),
mi AS
   (SELECT s.minor AS deptCode, COUNT(s.stuId) AS numMinor
    FROM student AS s
    GROUP BY s.minor),
f AS
   (SELECT f.deptCode, COUNT(f.facId) AS numFaculty
    FROM faculty AS f
    GROUP BY f.deptCode)
SELECT d.deptCode,
   d.deptName,
   IFNULL(f.numFaculty, 0) AS numFaculty,
   IFNULL(ma.numMajor, 0) AS numMajor,
   IFNULL(mi.numMinor, 0) AS numMinor
FROM department AS d LEFT JOIN ma USING (deptCode)
   LEFT JOIN mi USING (deptCode)
   LEFT JOIN f USING (deptCode);

WITH temp AS
(SELECT DISTINCT sc.schoolName AS college, d.deptName AS department,
   COUNT(s.stuId) As deptMajor
FROM school AS sc INNER JOIN department AS d ON (sc.schoolCode = d.schoolCode)
   LEFT JOIN student AS s ON (s.major = d.deptCode)
GROUP BY college, department)
SELECT temp.college, temp.department,
   temp.deptMajor AS `major in department`,
   SUM(deptMajor) OVER(PARTITION BY college) AS `major in college`,
   SUM(deptMajor) OVER() AS `major in university`
FROM temp;  

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

-- with placeholders.
SET @sql = "SELECT * FROM toyu.student WHERE major = ? AND ach >= ?";
-- ?: placeholder to hold a value.
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;

SET @sql = "SELECT * FROM toyu.student";  -- @: session variable.
PREPARE stmt_2 FROM @sql;
EXECUTE stmt_2;
DEALLOCATE PREPARE stmt_2;


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;


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;