-- 10/22

tee 2024_10_22_sql_log.txt
use toyu;

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;  

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

CREATE OR REPLACE PROCEDURE deptInfo(IN dCode VARCHAR(4), INOUT numFaculty INT)
BEGIN

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

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


SET @dCode = 'ITEC';

CALL deptInfo('CSCI', 1);
ERROR 1414 (42000): OUT or INOUT argument 2 for routine toyu.deptInfo is not a variable or NEW pseudo-variable in BEFORE trigger
SELECT 'CSCI', @numFaculty;


DROP PROCEDURE deptInfo;




-- A simple function: OUT and INOUT not allowed
-- default mode: IN
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');


-- A simple function: OUT and INOUT not allowed
-- default mode: IN
-- SQL limits side effects in functions.
DELIMITER //

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

   SELECT COUNT(*) -- resultset: side effect
   FROM student
   WHERE major = dCode;

   RETURN count;
END //
ERROR 1415 (0A000): Not allowed to return a result set from a function

DELIMITER ;

SHOW CREATE FUNCTION n_major;

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

-- replace arguments by typical values for testing.
SELECT s.stuId, CONCAT(s.fname, ' ', s.lname) AS student,
    ROUND(SUM(g.gradePoint * co.credits) / SUM(co.credits), 2) AS gpa
FROM Student s
JOIN Department d ON s.major = d.deptCode
JOIN Enroll e ON s.stuId = e.stuId
JOIN Class c ON e.classId = c.classId
JOIN Course co ON c.courseId = co.courseId
JOIN Grade g ON e.grade = g.grade
WHERE d.deptCode = 'CSCI'
GROUP BY s.stuId, student
ORDER BY gpa DESC
LIMIT 3;

SELECT *
FROM student
LIMIT 2,5;

SELECT DISTINCT CONCAT(fName, ' ', lName) AS name
FROM student
WHERE major = 'CSCI';

SELECT DISTINCT u.user, u.password
FROM mysql.user AS u
WHERE u.host = 'localhost'; 

-- System Catalog
-- Getting selected columns from information_schema
SELECT table_name, table_type, row_format, table_rows, avg_row_length
FROM information_schema.tables
WHERE table_schema = 'information_schema'
ORDER BY table_name DESC;

-- System Catalog
-- Getting selected columns from information_schema
SELECT table_name, table_type, row_format, table_rows, avg_row_length
FROM information_schema.tables
WHERE table_schema = 'toyu'
ORDER BY table_name DESC;

SELECT table_name, table_type, row_format, table_rows, avg_row_length
FROM information_schema.tables
WHERE table_schema = 'toyu'
AND table_type = 'BASE TABLE'
ORDER BY table_name DESC;

SELECT t.TABLE_SCHEMA AS `schema`, t.ENGINE, COUNT(t.TABLE_NAME) AS num_tables
FROM information_schema.tables t
GROUP BY `schema`, t.ENGINE
ORDER BY `schema`, num_tables DESC;