-- SQL workarea

-- 3/24

tee 2025_3_24_sql_log.txt

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


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

SELECT table_name, table_type, row_format, table_rows, avg_row_length
FROM information_schema.tables
WHERE table_schema = 'toyu'
ORDER BY table_name ASC;

SELECT COUNT(*) AS `# RC`
FROM information_schema.Referential_constraints AS IRC
WHERE IRC.constraint_schema = 'toyu';

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









-- previous

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


SET @sql = "SELECT * FROM toyu.student";
PREPARE stmt FROM @sql;
EXECUTE stmt;
EXECUTE stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- with placeholders. Parametrized SQL.
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;

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;

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;

DESC school_summary;

SELECT *
FROM school_summary
WHERE n_departments > 0;


-- get the full name of a student.
DELIMITER $$

CREATE OR REPLACE FUNCTION GetStudentFullName(
    student_id INT
)
RETURNS VARCHAR(61)
DETERMINISTIC
BEGIN
    DECLARE full_name VARCHAR(61);
    SELECT CONCAT(fname, ' ', lname) -- INTO full_name
    FROM Student
    WHERE stuId = student_id;
    RETURN full_name;
END $$
DELIMITER ;

SELECT GetStudentFullName(100000);
SELECT GetStudentFullName(100001);

-- In MySQL, parameter modes (IN, OUT, and INOUT) dictate how data is passed to and returned from stored procedures. IN is the default, meaning the parameter only accepts input; OUT is used for parameters that return values, and INOUT enables both input and output. 

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;