DROP SCHEMA IF EXISTS tinker;
CREATE SCHEMA tinker;
USE tinker;

-- [Q1] Write a MySQL function, GetNumClasses, to use the database toyu. It accepts a student id and returns the number of classes the student has enrolled in. 
 
DELIMITER //

CREATE OR REPLACE FUNCTION GetNumClasses(student_id INT) 
RETURNS INT
DETERMINISTIC
BEGIN	
	DECLARE result INT;
	
	SELECT COUNT(*) INTO result
	FROM toyu.enroll
	WHERE stuId = student_id;
	
	RETURN result;
END //

DELIMITER ;

SELECT GetNumClasses(100000);

-- [Q2] Write a procedure, GetDBTableInfo, to accept the name of a database (schema) in MySQL and output the names of tables in the database, and the number of columns of each table.

DELIMITER //

CREATE PROCEDURE GetDBTableInfo(IN db_name VARCHAR(255))
BEGIN
	SELECT TABLE_NAME AS `Table name`,
		COUNT(*) AS `Number of columns`
	FROM INFORMATION_SCHEMA.COLUMNS
	WHERE TABLE_SCHEMA = db_name
	GROUP BY TABLE_NAME;

END //

DELIMITER ;

CALL GetDBTableInfo('toyu');
CALL GetDBTableInfo('information_schema');

-- [Q3] Write a view, TableInfo, for information of every table in MySQL. For each table, there should be four columns: [1] db_name: name of the database, [2] table_name: name of the table, [3] num_columns: number of columns of the table, [4] num_tables: number of rows of the table.

CREATE OR REPLACE VIEW TableInfo AS
SELECT t.TABLE_SCHEMA AS db_name,
	t.TABLE_NAME AS table_name,
	COUNT(c.COLUMN_NAME) AS num_columns,
	t.TABLE_ROWS AS num_rows
FROM INFORMATION_SCHEMA.TABLES AS t
	INNER JOIN INFORMATION_SCHEMA.COLUMNS AS c
		ON (t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME)
GROUP BY db_name, table_name, num_rows;

SELECT *
FROM TableInfo
WHERE db_name = 'swim';

-- [Q4] Write MySQL code to use the toyu database and return the number of faculty in departments, colleges, and the university in the following manner.

WITH fac AS
(SELECT DISTINCT sc.schoolName AS college, d.deptCode AS deptCode,
   COUNT(f.facId) As numFaculty
FROM toyu.school AS sc LEFT JOIN toyu.department AS d ON (sc.schoolCode = d.schoolCode)
   LEFT JOIN toyu.faculty AS f ON (d.deptCode = f.deptCode)
GROUP BY college, deptCode)
SELECT fac.college, IFNULL(fac.deptCode,'') AS deptCode,
   '        =>' AS 'number of faculty',
   fac.numFaculty AS department,
   SUM(fac.numFaculty) OVER(PARTITION BY college) AS `college`,
   SUM(fac.numFaculty) OVER() AS `university`
FROM fac; 

DROP SCHEMA IF EXISTS tinker;