--
--  DB 2025 HW #5
--

-- [1] Write MySQL code to return the tables and their primary keys of the db 'mysql' in the following manner. Tips: you may use information_schema.KEY_COLUMN_USAGE.
SELECT kcu.TABLE_NAME AS `TABLE`,
    CONCAT('{', GROUP_CONCAT(kcu.COLUMN_NAME SEPARATOR ', '), '}') AS 
        `Primary key`
FROM information_schema.KEY_COLUMN_USAGE AS kcu
WHERE kcu.CONSTRAINT_NAME = 'PRIMARY'

AND kcu.CONSTRAINT_SCHEMA = 'mysql'
GROUP BY kcu.TABLE_NAME;

-- If table/view with no primary key are also desirable.
WITH temp AS
(SELECT kcu.CONSTRAINT_SCHEMA,
    kcu.TABLE_NAME,
    CONCAT('{', GROUP_CONCAT(kcu.COLUMN_NAME SEPARATOR ', '), '}') AS PK
FROM information_schema.KEY_COLUMN_USAGE AS kcu
WHERE kcu.CONSTRAINT_NAME = 'PRIMARY'
AND kcu.CONSTRAINT_SCHEMA = 'mysql'
GROUP BY 1, 2)
SELECT t.TABLE_NAME AS `table`,
    temp.PK AS `Primary Key`
FROM information_schema.TABLES AS t LEFT JOIN temp
    ON (t.TABLE_SCHEMA = temp.CONSTRAINT_SCHEMA
    AND t.TABLE_NAME = temp.TABLE_NAME)
WHERE TABLE_SCHEMA = 'mysql';

-- [2] Write a MySQL procedure ShowForeignKeys(db_name, table_name) to list all foreign key of the table table_name in the schema db_name). The result table should have four columns in the following manner.

DELIMITER ##
DROP PROCEDURE IF EXISTS ShowForeignKeys ##
CREATE PROCEDURE ShowForeignKeys(
    IN db_name VARCHAR(64), 
    IN table_name VARCHAR(64))
BEGIN
    SELECT rc.CONSTRAINT_NAME AS `FK constraint`,
        kcu.COLUMN_NAME AS `FK column`,
        kcu.REFERENCED_TABLE_NAME AS `parent table`,
        kcu.REFERENCED_COLUMN_NAME AS `referenced PK`
    FROM information_schema.REFERENTIAL_CONSTRAINTS rc
    JOIN information_schema.KEY_COLUMN_USAGE kcu
        ON (rc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
            AND rc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME)
    WHERE rc.CONSTRAINT_SCHEMA = db_name
    AND rc.TABLE_NAME = table_name;
END##

DELIMITER ;

CALL ShowForeignKeys('toyu', 'student');

CALL ShowForeignKeys('swim', 'swimmer');

-- [3] Write a view deptInfo for departmental information. Its behavior can be illustrated by the execution of the following SQL statement.

CREATE OR REPLACE VIEW toyu.deptInfo AS
SELECT d.deptCode, 
    d.deptName, 
    d.schoolCode,
    COUNT(DISTINCT s1.stuId) AS numMajor,
    COUNT(DISTINCT s2.stuId) AS numMinor
FROM toyu.department AS d LEFT JOIN toyu.student AS s1 ON (d.deptCode = s1.major)
    LEFT JOIN toyu.student AS s2 ON (d.deptCode = s2.minor)
GROUP BY 1, 2, 3;

-- or
CREATE OR REPLACE VIEW toyu.deptInfo AS
WITH maj AS
    (SELECT d.deptCode,
        COUNT(DISTINCT s.stuId) AS numMajor
    FROM toyu.department AS d LEFT JOIN toyu.student AS s 
        ON (d.deptCode = s.major)
    GROUP BY d.deptCode)
SELECT d.deptCode, 
    d.deptName, 
    d.schoolCode,
    IFNULL(maj.numMajor, 0) AS numMajor,
    COUNT(s.stuId) AS numMinor
FROM toyu.department AS d LEFT JOIN maj ON (d.deptCode = maj.deptCode)
    LEFT JOIN toyu.student AS s ON (d.deptCode = s.minor)   
GROUP BY d.deptCode;
    
SELECT *
FROM toyu.deptInfo 
WHERE numMajor >= 1
AND numMinor >= 1;


-- [4] Wirte a function WeightedStudentCount to return the weighted count of a department. A major has a weight of 3 and a minor has a count of 1. The behavior of the function can be illustrated by the results of the following calls. Your function must use the view deptInfo of question [3].
    
DELIMITER //

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

   SELECT d.numMajor * 3 + d.numMinor INTO count
   FROM toyu.deptInfo AS d
   WHERE d.deptCode = dCode;

   RETURN count;
END //

DELIMITER ;

SELECT WeightedStudentCount('CSCI');
SELECT WeightedStudentCount('CINF');