--
--  Joint DB Spring 2025 HW #5
--

-- [1] Using toyu, show the numbers of majors in departments and colleges in the following manner.
WITH temp AS
(SELECT DISTINCT sc.schoolCode AS college, 
    CONCAT(d.deptCode, ': ', d.deptName) AS department,
    COUNT(s.stuId) As numMajors
 FROM toyu.school AS sc LEFT JOIN toyu.department AS d ON (sc.schoolCode = d.schoolCode)
    LEFT JOIN toyu.student AS s ON (d.deptCode = s.major)
 GROUP BY college, department)
SELECT college, 
   SUM(numMajors) OVER(PARTITION BY college) AS `# majors in college`,
   IFNULL(department, ' ') AS department, 
   numMajors AS `# majors in department`
FROM temp;  

-- without using Windows function.
WITH t1 AS
(SELECT DISTINCT sc.schoolCode AS college, 
   COUNT(s.stuId) As numMajorsInCollege
 FROM school AS sc LEFT JOIN department AS d ON (sc.schoolCode = d.schoolCode)
    LEFT JOIN student AS s ON (d.deptCode = s.major)
 GROUP BY college),
t2 AS
(SELECT DISTINCT sc.schoolCode AS college, 
    CONCAT(d.deptCode, ': ', d.deptName) AS department,
    COUNT(s.stuId) As numMajors
 FROM school AS sc LEFT JOIN department AS d ON (sc.schoolCode = d.schoolCode)
    LEFT JOIN student AS s ON (d.deptCode = s.major)
 GROUP BY college, department)
SELECT t1.college, 
   t1.numMajorsInCollege AS `# majors in college`,
   IFNULL(t2.department, ' ') AS department, 
   t2.numMajors AS `# majors in department`
FROM t1 INNER JOIN t2 USING (college); 

-- [2] List the number of FK of tables in toyu that have at least one foreign key. Tips: there a table REFERENTIAL_CONSTRAINTS in the MySQL information_schema database.
SELECT DISTINCT r.TABLE_NAME AS `table`,
	COUNT(*) AS num_fk
FROM information_schema.REFERENTIAL_CONSTRAINTS AS r
WHERE r.CONSTRAINT_SCHEMA = 'toyu'
GROUP BY r.TABLE_NAME;

-- [3] Write a procedure foreign_keys of a table of an INNODB schema. Its behavior as illustrated by the following examples. Tips: you may use the tables information_schema.REFERENTIAL_CONSTRAINTS, information_schema.INNODB_SYS_FOREIGN, and information_schema.INNODB_SYS_FOREIGN_COLS. The prototype of the procedure is provided below.

DELIMITER $$
DROP PROCEDURE IF EXISTS foreign_keys $$
CREATE PROCEDURE foreign_keys(
    IN db_name VARCHAR(64),
    IN table_name VARCHAR(64),
    OUT n_fk INT
)
BEGIN   
    SELECT DISTINCT COUNT(*) INTO n_fk
    FROM information_schema.REFERENTIAL_CONSTRAINTS AS r
    WHERE r.CONSTRAINT_SCHEMA = db_name
    AND r.TABLE_NAME = table_name;
 
    SELECT CONCAT(db_name, '.', table_name, ': ', n_fk)
        AS 'table: number of foreign keys'; 
    
    SELECT CONCAT(c.FOR_COL_NAME) AS `column`,
        CONCAT(SUBSTRING_INDEX(f.REF_NAME, '/', -1), '.', c.REF_COL_NAME)
        AS `referenced_table.column`
    FROM information_schema.INNODB_SYS_FOREIGN AS f INNER JOIN 
        information_schema.INNODB_SYS_FOREIGN_COLS AS c USING (Id)
    WHERE SUBSTRING_INDEX(f.FOR_NAME, '/', 1) = db_name
    AND SUBSTRING_INDEX(f.FOR_NAME, '/', -1) = table_name;

END$$

DELIMITER ;

SET @n_fk = 0;
CALL foreign_keys('toyu', 'student', @n_fk);
SELECT @n_fk;

CALL foreign_keys('toyu', 'enroll', @n_fk);
SELECT @n_fk;

CALL foreign_keys('toyu', 'school', @n_fk);
SELECT @n_fk;

-- [4] Write a function column_count to count the number of columns in a MySQL table. Its behavior is illustrated by the examples below.


DELIMITER $$
DROP FUNCTION IF EXISTS column_count $$
CREATE FUNCTION column_count(
    db_name VARCHAR(64),
    table_name VARCHAR(64)
) RETURNS INT
DETERMINISTIC
BEGIN   
    DECLARE result INT DEFAULT 0; 
    SELECT DISTINCT COUNT(*) INTO result
    FROM INFORMATION_SCHEMA.COLUMNS AS c 
    WHERE c.TABLE_SCHEMA = db_name AND c.TABLE_NAME = table_name;
    
    RETURN result;
END$$

DELIMITER ;

SELECT column_count('toyu', 'student');

SELECT column_count('toyu', 'faculty');

SELECT column_count('information_schema', 'columns');