-- 10/16
tee 2024_10_16_sql_log.txt
use toyu


SELECT DISTINCT s.stuId, CONCAT(s.fname, ' ', s.lname) AS student,
	d.deptName AS major,
	s.minor,
	CONCAT(f.fname, ' ', f.lname) AS advisor
FROM student AS s INNER JOIN department AS d ON (s.major =  d.deptCode)
	INNER JOIN faculty AS f ON (s.advisor = f.facId)
WHERE s.major IN ('CSCI', 'CINF', 'ITEC')
OR s.minor IN ('CSCI', 'CINF', 'ITEC');

-- explain the execution plan

EXPLAIN SELECT DISTINCT s.stuId, CONCAT(s.fname, ' ', s.lname) AS student,
	d.deptName AS major,
	s.minor,
	CONCAT(f.fname, ' ', f.lname) AS advisor
FROM student AS s INNER JOIN department AS d ON (s.major =  d.deptCode)
	INNER JOIN faculty AS f ON (s.advisor = f.facId)
WHERE s.major IN ('CSCI', 'CINF', 'ITEC')
OR s.minor IN ('CSCI', 'CINF', 'ITEC');

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

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

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;

-- not updatable.
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;

SELECT *
FROM school_summary
WHERE n_departments > 0;

SHOW CREATE VIEW school_summary;
-- Note something like "ALGORITHM=UNDEFINED DEFINER=`yue`@`localhost` SQL SECURITY DEFINER"
-- (default values) may be added.
DESC school_summary;


-- A very simple stored procedure.
-- Redefine the delimiter to end the procedure.
-- default delimiter: ;
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;

CALL deptInfo('CINF', @numFaculty);
SELECT @dCode, @numFaculty;

CALL deptInfo('ABCD', @numFaculty);
SELECT 'ABCD', @numFaculty;

CALL deptInfo('ABCD', 'PQRS');
SELECT 'ABCD', @numFaculty;


DROP PROCEDURE deptInfo;