-- logging.
-- 3/11/25

mysql -u yue -p

use toyu;


tee 2025_3_11_sql_log.txt

SELECT s.stuId, s.major -- [3] construct columns
FROM student AS s -- [1] big raw table: raw rows
WHERE ach > 0; -- [2] filter out the rows.

SELECT s.major, COUNT(s.stuId) AS `no of major` -- [4] construct output columns
FROM student AS s -- [1] big raw table: old raw rows
WHERE ach > 0 -- [2] filter out the rows.
GROUP BY s.major; -- [3] From group with the same group by column: s.major; each value of s.major defines a group. New group -> new row.


SELECT s.stuId, s.major -- [3] construct columns
FROM student AS s -- [1] big raw table: raw rows
WHERE ach > 0; -- [2] filter out the rows.

SELECT s.major, -- group by column
    COUNT(s.stuId) AS `no of major`, -- group function.
    'hello world'  -- constant
    -- [5] construct output columns
FROM student AS s -- [1] big raw table: old raw rows
WHERE ach > 0 -- [2] filter out the rows.
GROUP BY s.major -- [3] Form group with the same group by columns: s.major; each value of s.major defines a group. New group -> new row.
HAVING `no of major`  > 1;  -- [4] filter out the group/new row.

SELECT s.major, -- group by column
    COUNT(s.stuId) AS `no of major`, -- group function.
    'hello world'  -- constant
    , CONCAT(s.fname, ' ', s.lname) -- semantic error.
    -- [5] construct output columns
FROM student AS s -- [1] big raw table: old raw rows
WHERE ach > 0 -- [2] filter out the rows.
GROUP BY s.major -- [3] Form group with the same group by columns: s.major; each value of s.major defines a group. New group -> new row.
HAVING `no of major`  > 1;  -- [4] filter out the group/new row.

-- all student names too.
SELECT s.major, -- group by column
    COUNT(s.stuId) AS `no of major` -- group function.
    , GROUP_CONCAT(CONCAT(s.fname, ' ', s.lname) ORDER BY lname DESC, fname DESC SEPARATOR '; ') AS students -- semantic error.
    -- [5] construct output columns
FROM student AS s -- [1] big raw table: old raw rows
WHERE ach > 0 -- [2] filter out the rows.
GROUP BY s.major -- [3] Form group with the same group by columns: s.major; each value of s.major defines a group. New group -> new row.
HAVING `no of major`  > 1;  -- [4] filter out the group/new row.

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])
             
  
SELECT s.major, COUNT(s.stuid) AS numStudents -- [3] construct columns
FROM student AS s 
GROUP BY s.major
WHERE s.ach > 0; 
-- error,

SELECT s.major, 
    COUNT(s.stuId) AS numStudents 
FROM student AS s 
WHERE s.ach > 0
GROUP BY s.major; 

SELECT s.major, 
    COUNT(s.stuId) AS numStudents 
FROM student AS s 
WHERE s.ach > 0 AND COUNT(s.stuId) > 1
GROUP BY s.major; 

SELECT s.major, 
    COUNT(s.stuId) AS numStudents 
FROM student AS s 
GROUP BY s.major
HAVING s.ach > 0 AND numStudents > 1; 


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; 

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

[2] Can you write a query to generate the following output?

+----------+------------------------------+------------+----------+----------+
| deptCode | deptName                     | numFaculty | numMajor | numMinor |
+----------+------------------------------+------------+----------+----------+
| ACCT     | Accounting                   |          1 |        0 |        0 |
| ARTS     | Arts                         |          1 |        2 |        0 |
| CINF     | Computer Information Systems |          2 |        2 |        3 |
| CSCI     | Computer Science             |          4 |        3 |        1 |
| ENGL     | English                      |          1 |        0 |        2 |
| ITEC     | Information Technology       |          2 |        2 |        2 |
| MATH     | Mathematics                  |          0 |        0 |        0 |
+----------+------------------------------+------------+----------+----------+
7 rows in set (0.00 sec)


Steps:

[1] columns #1 and #3: DeptCOde, numFaculty

SELECT f.deptCode, COUNT(f.facId) AS numFaculty
    FROM faculty AS f
    GROUP BY f.deptCode;

[2] columns #1 and #4: deptCode , numMajor
SELECT s.major AS deptCode, COUNT(s.stuId) AS numMajor
    FROM student AS s
    GROUP BY s.major;
    
[3] columns #1 and #5: deptCode , numMinor
SELECT s.major AS deptCode, COUNT(s.stuId) AS numMinor
    FROM student AS s
    GROUP BY s.minor;
    
 [4] Final: column #2: department
WITH ma AS  -- [2]
   (SELECT s.major AS deptCode, COUNT(s.stuId) AS numMajor
    FROM student AS s
    GROUP BY s.major),
mi AS  -- [3]
   (SELECT s.minor AS deptCode, COUNT(s.stuId) AS numMinor
    FROM student AS s
    GROUP BY s.minor),
f AS  -- [1]
   (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;  
 
 SELECT * FROM toyu.student;
 
 SET @sql = "SELECT * FROM toyu.student";
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

 
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;

-- 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;

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(  -- not updatable.
   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 *, sc.schoolName
FROM school_summary AS s INNER JOIN school AS sc USING (schoolCode)
WHERE n_departments > 0;

DROP VIEW school_summary;

-- A very simple stored procedure.
-- Redefine the delimiter to end the procedure.
DELIMITER //

CREATE OR REPLACE PROCEDURE deptInfo(IN dCode VARCHAR(4), OUT numFaculty INT)
BEGIN
   --  Display some information.
   SELECT d.deptName, d.SchoolCode, t1.n_majors, t2.n_minors
   FROM department AS d INNER JOIN
      (SELECT COUNT(stuId) AS n_majors
      FROM student
      WHERE major = dCode) AS t1 INNER JOIN
      (SELECT COUNT(stuId) AS n_minors
      FROM student
      WHERE minor = dCode) AS t2
   WHERE d.deptCode = dCode;

   -- MySQL does not direct sending output to console.
   -- It is necessary to use a SQL statement.
   SELECT 'Debuggin comment can be put here.';
   SELECT CONCAT('Faculty in the department: ', dCode) AS faculty;

   SELECT *
   FROM faculty AS f
   WHERE f.deptCode = dCode;

   SELECT COUNT(f.facId) INTO numFaculty
   FROM faculty AS f
   WHERE f.deptCode = dCode;
END //

DELIMITER ;

SHOW CREATE PROCEDURE deptInfo;

SET @numFaculty = 0;
SET @dCode = 'CSCI';
CALL deptInfo(@dCode, @numFaculty);
SELECT @dCode, @numFaculty;

SET @dCode = 'ITEC';
CALL deptInfo(@dCode, @numFaculty);
SELECT @dCode, @numFaculty;



-- A simple function
DELIMITER $$

CREATE OR REPLACE FUNCTION n_major(dCode varchar(4)) RETURNS INT
READS SQL DATA
BEGIN
   DECLARE count INT DEFAULT 0;

   SELECT COUNT(*) INTO count
   FROM student
   WHERE major = dCode;

   RETURN count;
END $$

DELIMITER ;

SHOW CREATE FUNCTION n_major;

SELECT n_major('CSCI');
SELECT n_major('ITEC');