tee 2026_3_16_sql_log.txt


SELECT d.schoolCode, -- step [5] prepare columns using [4]
	COUNT(d.deptCode) AS `Number of departments`,
	AVG(d.numStaff) AS `Average # of staff`
FROM department AS d  -- step [1] big source table
WHERE d.numStaff >= 5 -- step [2] filter the original row in the table in [1]
GROUP BY d.schoolCode  -- step [3] form groups by distinct values of the group by columns. The group -> the new row.
HAVING `Number of departments` >= 2 -- step [4]: filter the group/new row.
ORDER BY `Number of departments` ASC -- step [6] provide order to [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);

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

-- with placeholders.
-- ?: placeholder waiting for value.
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;