-- SQL workarea

-- 3/10

tee 2025_3_10_sql_log.txt



SELECT DISTINCT <<result_columns>> -- [5] construct result columns
FROM <<source_tables>> -- [1] conceptually join tables to form a large table to produce initial rows
WHERE <<conditions_for_inclusion>> -- [2] Filter initial rows
GROUP BY <<group_by_columns>>
         --[3] group initial rows into groups by values of the group_by_column. A group becomes a new row.
HAVING <<conditions for filtering group>> -- [4] filter groups
ORDER BY <<columns>>; -- [6] Order the result of [5].


SELECT s.stuId, s.major
FROM student AS s
WHERE s.ach >= 10
ORDER BY s.major;

SELECT s.stuId, s.major
FROM student AS s
WHERE s.ach >= 10
GROUP BY s.major  -- form group according to distinct values in s.major
ORDER BY s.major;


SELECT s.major, COUNT(s.stuId) AS `# major`
FROM student AS s
WHERE s.ach >= 10
GROUP BY s.major  -- form group according to distinct values in s.major
ORDER BY s.major;

CONCAT(s.fname, ' ', s.lname)


SELECT s.major, d.deptName,
    COUNT(s.stuId) AS `# major`
FROM student AS s LEFT JOIN department AS d ON (s.major = d.deptCode)
WHERE s.ach >= 10
GROUP BY s.major, d.deptName -- form group according to distinct values in s.major
ORDER BY s.major;

SELECT s.major, d.deptName, -- group by columns
    COUNT(s.stuId) AS `# major`, -- group functions
    'Hello'
FROM student AS s LEFT JOIN department AS d ON (s.major = d.deptCode)
WHERE s.ach >= 10
GROUP BY s.major, d.deptName -- form group according to distinct values in s.major
HAVING `# major` > 1
ORDER BY s.major;

SELECT s.*, d.*
FROM student AS s LEFT JOIN department AS d ON (s.major = d.deptCode)
WHERE s.ach >= 10;


SELECT s.major, COUNT(s.stuId) AS `# major`
FROM student AS s
GROUP BY s.major 
WHERE s.ach >= 10;


SELECT s.major, COUNT(s.stuId) AS `# major`
FROM student AS s
GROUP BY s.major 
HAVING s.ach >= 10;


SELECT s.major, COUNT(s.stuId) AS `# major`
FROM student AS s
WHERE s.ach >= 10 AND `# major` > 1
GROUP BY s.major;

SELECT s.major, COUNT(s.stuId) AS `# major`
FROM student AS s
WHERE s.ach >= 10 AND COUNT(s.stuId) > 1
GROUP BY s.major;


SELECT s.major, d.deptName, -- group by columns
    COUNT(s.stuId) AS `# major`, -- group functions
    'Hello'
    , s.sid, CONCAT(s.fname, '', f.lname)
FROM student AS s LEFT JOIN department AS d ON (s.major = d.deptCode)
WHERE s.ach >= 10
GROUP BY s.major, d.deptName -- form group according to distinct values in s.major
HAVING `# major` > 1
ORDER BY s.major;

-- error

SELECT CONCAT(s.fname, ' ', s.lname) AS student,
    COUNT(e.classId) AS `Enrolled classes`
FROM student AS s LEFT JOIN enroll e ON (s.stuId = e.stuId)
GROUP BY student 
ORDER BY `Enrolled classes` DESC; 

Steps:

[1] deptCode and numFaculty (columns 1 and 3)
SELECT f.deptCode, COUNT(f.facId) AS numFaculty
    FROM faculty AS f
    GROUP BY f.deptCode;
    
[2] deptCode and numMajors (columns 1 and 4)
SELECT s.major AS deptCode, COUNT(s.stuId) AS numMajor
    FROM student AS s
    GROUP BY s.major;
 
[3] deptCode and numMinors (columns 1 and 5) 
SELECT s.minor AS deptCode, COUNT(s.stuId) AS numMinor
    FROM student AS s
    GROUP BY s.minor;

[4]
    
Solution:
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;


SET @sql = "SELECT * FROM toyu.student";
PREPARE stmt FROM @sql;
EXECUTE stmt;
EXECUTE stmt;
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;