MariaDB [(none)]> use toyu; Database changed MariaDB [toyu]> MariaDB [toyu]> WITH temp AS -> (SELECT DISTINCT sc.schoolName AS college, d.deptName AS department, -> COUNT(s.stuId) As deptMajor -> FROM school AS sc INNER JOIN department AS d ON (sc.schoolCode = d.schoolCode) -> LEFT JOIN student AS s ON (s.major = d.deptCode) -> GROUP BY college, department) -> SELECT temp.college, temp.department, -> temp.deptMajor AS `major in department`, -> SUM(deptMajor) OVER(PARTITION BY college) AS `major in college`, -> SUM(deptMajor) OVER() AS `major in university` -> FROM temp; +-------------------------------+------------------------------+---------------------+------------------+---------------------+ | college | department | major in department | major in college | major in university | +-------------------------------+------------------------------+---------------------+------------------+---------------------+ | Business | Accounting | 0 | 0 | 9 | | Human Sciences and Humanities | English | 0 | 2 | 9 | | Human Sciences and Humanities | Arts | 2 | 2 | 9 | | Science and Engineering | Mathematics | 0 | 7 | 9 | | Science and Engineering | Computer Information Systems | 2 | 7 | 9 | | Science and Engineering | Computer Science | 3 | 7 | 9 | | Science and Engineering | Information Technology | 2 | 7 | 9 | +-------------------------------+------------------------------+---------------------+------------------+---------------------+ 7 rows in set (0.005 sec) MariaDB [toyu]> SELECT DISTINCT sc.schoolName AS college, d.deptName AS department, -> COUNT(s.stuId) As deptMajor -> FROM school AS sc INNER JOIN department AS d ON (sc.schoolCode = d.schoolCode) -> LEFT JOIN student AS s ON (s.major = d.deptCode) -> GROUP BY college, department; +-------------------------------+------------------------------+-----------+ | college | department | deptMajor | +-------------------------------+------------------------------+-----------+ | Business | Accounting | 0 | | Human Sciences and Humanities | Arts | 2 | | Human Sciences and Humanities | English | 0 | | Science and Engineering | Computer Information Systems | 2 | | Science and Engineering | Computer Science | 3 | | Science and Engineering | Information Technology | 2 | | Science and Engineering | Mathematics | 0 | +-------------------------------+------------------------------+-----------+ 7 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> WITH temp AS -> (SELECT DISTINCT sc.schoolName AS college, d.deptName AS department, -> COUNT(s.stuId) As deptMajor -> FROM school AS sc INNER JOIN department AS d ON (sc.schoolCode = d.schoolCode) -> LEFT JOIN student AS s ON (s.major = d.deptCode) -> GROUP BY college, department) -> SELECT temp.college, temp.department, -> temp.deptMajor AS `major in department`, -> SUM(deptMajor) OVER(PARTITION BY college) AS `major in college`, -> SUM(deptMajor) OVER() AS `major in university` -> FROM temp; +-------------------------------+------------------------------+---------------------+------------------+---------------------+ | college | department | major in department | major in college | major in university | +-------------------------------+------------------------------+---------------------+------------------+---------------------+ | Business | Accounting | 0 | 0 | 9 | | Human Sciences and Humanities | English | 0 | 2 | 9 | | Human Sciences and Humanities | Arts | 2 | 2 | 9 | | Science and Engineering | Mathematics | 0 | 7 | 9 | | Science and Engineering | Computer Information Systems | 2 | 7 | 9 | | Science and Engineering | Computer Science | 3 | 7 | 9 | | Science and Engineering | Information Technology | 2 | 7 | 9 | +-------------------------------+------------------------------+---------------------+------------------+---------------------+ 7 rows in set (0.001 sec) MariaDB [toyu]> DELIMITER // MariaDB [toyu]> MariaDB [toyu]> 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 // Query OK, 0 rows affected (0.016 sec) MariaDB [toyu]> MariaDB [toyu]> DELIMITER ; MariaDB [toyu]> MariaDB [toyu]> SHOW CREATE PROCEDURE deptInfo; +-----------+-----------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation | +-----------+-----------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | deptInfo | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`yue`@`localhost` PROCEDURE `deptInfo`(IN dCode VARCHAR(4), OUT numFaculty INT) BEGIN 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; 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 | cp850 | cp850_general_ci | utf8mb4_general_ci | +-----------+-----------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ 1 row in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SET @numFaculty = 0; Query OK, 0 rows affected (0.001 sec) MariaDB [toyu]> SET @dCode = 'CSCI'; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> CALL deptInfo(@dCode, @numFaculty); +------------------+------------+----------+----------+ | deptName | SchoolCode | n_majors | n_minors | +------------------+------------+----------+----------+ | Computer Science | CSE | 3 | 1 | +------------------+------------+----------+----------+ 1 row in set (0.005 sec) +-----------------------------------+ | Debuggin comment can be put here. | +-----------------------------------+ | Debuggin comment can be put here. | +-----------------------------------+ 1 row in set (0.098 sec) +---------------------------------+ | faculty | +---------------------------------+ | Faculty in the department: CSCI | +---------------------------------+ 1 row in set (0.215 sec) +-------+--------+--------+----------+---------------------+ | facId | fname | lname | deptCode | rank | +-------+--------+--------+----------+---------------------+ | 1011 | Paul | Smith | CSCI | Professor | | 1012 | Mary | Tran | CSCI | Associate Professor | | 1013 | David | Love | CSCI | NULL | | 1014 | Sharon | Mannes | CSCI | Assistant Professor | +-------+--------+--------+----------+---------------------+ 4 rows in set (0.315 sec) Query OK, 1 row affected (0.465 sec) MariaDB [toyu]> SELECT @dCode, @numFaculty; +--------+-------------+ | @dCode | @numFaculty | +--------+-------------+ | CSCI | 4 | +--------+-------------+ 1 row in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SET @dCode = 'ITEC'; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> CALL deptInfo(@dCode, @numFaculty); +------------------------+------------+----------+----------+ | deptName | SchoolCode | n_majors | n_minors | +------------------------+------------+----------+----------+ | Information Technology | CSE | 2 | 2 | +------------------------+------------+----------+----------+ 1 row in set (0.002 sec) +-----------------------------------+ | Debuggin comment can be put here. | +-----------------------------------+ | Debuggin comment can be put here. | +-----------------------------------+ 1 row in set (0.099 sec) +---------------------------------+ | faculty | +---------------------------------+ | Faculty in the department: ITEC | +---------------------------------+ 1 row in set (0.230 sec) +-------+----------+-------+----------+-----------+ | facId | fname | lname | deptCode | rank | +-------+----------+-------+----------+-----------+ | 1017 | Deborah | Gump | ITEC | Professor | | 1019 | Benjamin | Yu | ITEC | Lecturer | +-------+----------+-------+----------+-----------+ 2 rows in set (0.316 sec) Query OK, 1 row affected (0.455 sec) MariaDB [toyu]> SELECT @dCode, @numFaculty; +--------+-------------+ | @dCode | @numFaculty | +--------+-------------+ | ITEC | 2 | +--------+-------------+ 1 row in set (0.000 sec) MariaDB [toyu]> SET @dCode = 'ITEC'; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> CALL deptInfo('CSCI', @numFaculty); +------------------+------------+----------+----------+ | deptName | SchoolCode | n_majors | n_minors | +------------------+------------+----------+----------+ | Computer Science | CSE | 3 | 1 | +------------------+------------+----------+----------+ 1 row in set (0.001 sec) +-----------------------------------+ | Debuggin comment can be put here. | +-----------------------------------+ | Debuggin comment can be put here. | +-----------------------------------+ 1 row in set (0.123 sec) +---------------------------------+ | faculty | +---------------------------------+ | Faculty in the department: CSCI | +---------------------------------+ 1 row in set (0.237 sec) +-------+--------+--------+----------+---------------------+ | facId | fname | lname | deptCode | rank | +-------+--------+--------+----------+---------------------+ | 1011 | Paul | Smith | CSCI | Professor | | 1012 | Mary | Tran | CSCI | Associate Professor | | 1013 | David | Love | CSCI | NULL | | 1014 | Sharon | Mannes | CSCI | Assistant Professor | +-------+--------+--------+----------+---------------------+ 4 rows in set (0.354 sec) Query OK, 1 row affected (0.524 sec) MariaDB [toyu]> SELECT 'CSCI', @numFaculty; +------+-------------+ | CSCI | @numFaculty | +------+-------------+ | CSCI | 4 | +------+-------------+ 1 row in set (0.000 sec) MariaDB [toyu]> CALL deptInfo('CSCI', 1); ERROR 1414 (42000): OUT or INOUT argument 2 for routine toyu.deptInfo is not a variable or NEW pseudo-variable in BEFORE trigger MariaDB [toyu]> SELECT 'CSCI', @numFaculty; +------+-------------+ | CSCI | @numFaculty | +------+-------------+ | CSCI | 4 | +------+-------------+ 1 row in set (0.001 sec) MariaDB [toyu]> DELIMITER // MariaDB [toyu]> MariaDB [toyu]> CREATE OR REPLACE PROCEDURE deptInfo(IN dCode VARCHAR(4), OUT numFaculty INT) -> BEGIN -> -> SELECT numFaculty; -> -- 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 // Query OK, 0 rows affected (0.019 sec) MariaDB [toyu]> MariaDB [toyu]> DELIMITER ; MariaDB [toyu]> MariaDB [toyu]> SHOW CREATE PROCEDURE deptInfo; +-----------+-----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation | +-----------+-----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | deptInfo | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`yue`@`localhost` PROCEDURE `deptInfo`(IN dCode VARCHAR(4), OUT numFaculty INT) BEGIN SELECT numFaculty; 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; 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 | cp850 | cp850_general_ci | utf8mb4_general_ci | +-----------+-----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ 1 row in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SET @numFaculty = 0; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> SET @dCode = 'CSCI'; Query OK, 0 rows affected (0.001 sec) MariaDB [toyu]> CALL deptInfo(@dCode, @numFaculty); +------------+ | numFaculty | +------------+ | NULL | +------------+ 1 row in set (0.001 sec) +------------------+------------+----------+----------+ | deptName | SchoolCode | n_majors | n_minors | +------------------+------------+----------+----------+ | Computer Science | CSE | 3 | 1 | +------------------+------------+----------+----------+ 1 row in set (0.112 sec) +-----------------------------------+ | Debuggin comment can be put here. | +-----------------------------------+ | Debuggin comment can be put here. | +-----------------------------------+ 1 row in set (0.193 sec) +---------------------------------+ | faculty | +---------------------------------+ | Faculty in the department: CSCI | +---------------------------------+ 1 row in set (0.331 sec) +-------+--------+--------+----------+---------------------+ | facId | fname | lname | deptCode | rank | +-------+--------+--------+----------+---------------------+ | 1011 | Paul | Smith | CSCI | Professor | | 1012 | Mary | Tran | CSCI | Associate Professor | | 1013 | David | Love | CSCI | NULL | | 1014 | Sharon | Mannes | CSCI | Assistant Professor | +-------+--------+--------+----------+---------------------+ 4 rows in set (0.413 sec) Query OK, 1 row affected (0.581 sec) MariaDB [toyu]> SELECT @dCode, @numFaculty; +--------+-------------+ | @dCode | @numFaculty | +--------+-------------+ | CSCI | 4 | +--------+-------------+ 1 row in set (0.000 sec) MariaDB [toyu]> MariaDB [toyu]> -- A very simple stored procedure. MariaDB [toyu]> -- Redefine the delimiter to end the procedure. MariaDB [toyu]> DELIMITER // MariaDB [toyu]> MariaDB [toyu]> CREATE OR REPLACE PROCEDURE deptInfo(IN dCode VARCHAR(4), INOUT numFaculty INT) -> BEGIN -> -> SELECT numFaculty; -> -- 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 // Query OK, 0 rows affected (0.016 sec) MariaDB [toyu]> MariaDB [toyu]> DELIMITER ; MariaDB [toyu]> MariaDB [toyu]> SHOW CREATE PROCEDURE deptInfo; +-----------+-----------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation | +-----------+-----------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | deptInfo | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`yue`@`localhost` PROCEDURE `deptInfo`(IN dCode VARCHAR(4), INOUT numFaculty INT) BEGIN SELECT numFaculty; 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; 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 | cp850 | cp850_general_ci | utf8mb4_general_ci | +-----------+-----------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ 1 row in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SET @numFaculty = 0; Query OK, 0 rows affected (0.001 sec) MariaDB [toyu]> SET @dCode = 'CSCI'; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> CALL deptInfo(@dCode, @numFaculty); +------------+ | numFaculty | +------------+ | 0 | +------------+ 1 row in set (0.002 sec) +------------------+------------+----------+----------+ | deptName | SchoolCode | n_majors | n_minors | +------------------+------------+----------+----------+ | Computer Science | CSE | 3 | 1 | +------------------+------------+----------+----------+ 1 row in set (0.092 sec) +-----------------------------------+ | Debuggin comment can be put here. | +-----------------------------------+ | Debuggin comment can be put here. | +-----------------------------------+ 1 row in set (0.184 sec) +---------------------------------+ | faculty | +---------------------------------+ | Faculty in the department: CSCI | +---------------------------------+ 1 row in set (0.282 sec) +-------+--------+--------+----------+---------------------+ | facId | fname | lname | deptCode | rank | +-------+--------+--------+----------+---------------------+ | 1011 | Paul | Smith | CSCI | Professor | | 1012 | Mary | Tran | CSCI | Associate Professor | | 1013 | David | Love | CSCI | NULL | | 1014 | Sharon | Mannes | CSCI | Assistant Professor | +-------+--------+--------+----------+---------------------+ 4 rows in set (0.413 sec) Query OK, 1 row affected (0.621 sec) MariaDB [toyu]> SELECT @dCode, @numFaculty; +--------+-------------+ | @dCode | @numFaculty | +--------+-------------+ | CSCI | 4 | +--------+-------------+ 1 row in set (0.000 sec) MariaDB [toyu]> MariaDB [toyu]> -- A simple function MariaDB [toyu]> DELIMITER // MariaDB [toyu]> MariaDB [toyu]> CREATE OR REPLACE FUNCTION n_major(dCode varchar(4)) RETURNS INT -> READS SQL DATA -> BEGIN -> DECLARE count INT DEFAULT 0; -> -> SELECT COUNT(*) INTO count -> FROM student -> WHERE major = dCode; -> -> RETURN count; -> END // Query OK, 0 rows affected (0.013 sec) MariaDB [toyu]> MariaDB [toyu]> DELIMITER ; MariaDB [toyu]> MariaDB [toyu]> SHOW CREATE FUNCTION n_major; +----------+-----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | Function | sql_mode | Create Function | character_set_client | collation_connection | Database Collation | +----------+-----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | n_major | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`yue`@`localhost` FUNCTION `n_major`(dCode varchar(4)) RETURNS int(11) READS SQL DATA BEGIN DECLARE count INT DEFAULT 0; SELECT COUNT(*) INTO count FROM student WHERE major = dCode; RETURN count; END | cp850 | cp850_general_ci | utf8mb4_general_ci | +----------+-----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ 1 row in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT n_major('CSCI'); +-----------------+ | n_major('CSCI') | +-----------------+ | 3 | +-----------------+ 1 row in set (0.002 sec) MariaDB [toyu]> SELECT n_major('ITEC'); +-----------------+ | n_major('ITEC') | +-----------------+ | 2 | +-----------------+ 1 row in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> MariaDB [toyu]> -- A simple function: OUT and INOUT not allowed MariaDB [toyu]> -- default mode: IN MariaDB [toyu]> DELIMITER // MariaDB [toyu]> MariaDB [toyu]> CREATE OR REPLACE FUNCTION n_major(dCode varchar(4)) RETURNS INT -> READS SQL DATA -> BEGIN -> DECLARE count INT DEFAULT 0; -> -> SELECT COUNT(*) -> FROM student -> WHERE major = dCode; -> -> RETURN count; -> END // ERROR 1415 (0A000): Not allowed to return a result set from a function MariaDB [toyu]> MariaDB [toyu]> DELIMITER ; MariaDB [toyu]> MariaDB [toyu]> SHOW CREATE FUNCTION n_major; +----------+-----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | Function | sql_mode | Create Function | character_set_client | collation_connection | Database Collation | +----------+-----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | n_major | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`yue`@`localhost` FUNCTION `n_major`(dCode varchar(4)) RETURNS int(11) READS SQL DATA BEGIN DECLARE count INT DEFAULT 0; SELECT COUNT(*) INTO count FROM student WHERE major = dCode; RETURN count; END | cp850 | cp850_general_ci | utf8mb4_general_ci | +----------+-----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ 1 row in set (0.000 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT n_major('CSCI'); +-----------------+ | n_major('CSCI') | +-----------------+ | 3 | +-----------------+ 1 row in set (0.002 sec) MariaDB [toyu]> SELECT n_major('ITEC'); +-----------------+ | n_major('ITEC') | +-----------------+ | 2 | +-----------------+ 1 row in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> MariaDB [toyu]> -- A simple function: OUT and INOUT not allowed MariaDB [toyu]> -- default mode: IN MariaDB [toyu]> DELIMITER // MariaDB [toyu]> MariaDB [toyu]> CREATE OR REPLACE FUNCTION n_major(dCode varchar(4)) RETURNS INT -> READS SQL DATA -> BEGIN -> DECLARE count INT DEFAULT 0; -> -> SELECT COUNT(*) -> FROM student -> WHERE major = dCode; -> -> RETURN count; -> END // ERROR 1415 (0A000): Not allowed to return a result set from a function MariaDB [toyu]> MariaDB [toyu]> DELIMITER ; MariaDB [toyu]> MariaDB [toyu]> SHOW CREATE FUNCTION n_major; +----------+-----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | Function | sql_mode | Create Function | character_set_client | collation_connection | Database Collation | +----------+-----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | n_major | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`yue`@`localhost` FUNCTION `n_major`(dCode varchar(4)) RETURNS int(11) READS SQL DATA BEGIN DECLARE count INT DEFAULT 0; SELECT COUNT(*) INTO count FROM student WHERE major = dCode; RETURN count; END | cp850 | cp850_general_ci | utf8mb4_general_ci | +----------+-----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ 1 row in set (0.000 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT n_major('CSCI'); +-----------------+ | n_major('CSCI') | +-----------------+ | 3 | +-----------------+ 1 row in set (0.001 sec) MariaDB [toyu]> SELECT n_major('ITEC'); +-----------------+ | n_major('ITEC') | +-----------------+ | 2 | +-----------------+ 1 row in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT GetStudentGPA(100000); +-----------------------+ | GetStudentGPA(100000) | +-----------------------+ | 3.00 | +-----------------------+ 1 row in set (0.003 sec) MariaDB [toyu]> SELECT s.stuId, CONCAT(s.fname, ' ', s.lname) AS student, -> ROUND(SUM(g.gradePoint * co.credits) / SUM(co.credits), 2) AS gpa -> FROM Student s -> JOIN Department d ON s.major = d.deptCode -> JOIN Enroll e ON s.stuId = e.stuId -> JOIN Class c ON e.classId = c.classId -> JOIN Course co ON c.courseId = co.courseId -> JOIN Grade g ON e.grade = g.grade -> WHERE d.deptCode = dept_code -> GROUP BY s.stuId, student -> ORDER BY gpa DESC -> LIMIT top_count; ERROR 1327 (42000): Undeclared variable: top_count MariaDB [toyu]> SELECT s.stuId, CONCAT(s.fname, ' ', s.lname) AS student, -> ROUND(SUM(g.gradePoint * co.credits) / SUM(co.credits), 2) AS gpa -> FROM Student s -> JOIN Department d ON s.major = d.deptCode -> JOIN Enroll e ON s.stuId = e.stuId -> JOIN Class c ON e.classId = c.classId -> JOIN Course co ON c.courseId = co.courseId -> JOIN Grade g ON e.grade = g.grade -> WHERE d.deptCode = 'CSC' -> GROUP BY s.stuId, student -> ORDER BY gpa DESC -> LIMIT 3; Empty set (0.001 sec) MariaDB [toyu]> SELECT s.stuId, CONCAT(s.fname, ' ', s.lname) AS student, -> ROUND(SUM(g.gradePoint * co.credits) / SUM(co.credits), 2) AS gpa -> FROM Student s -> JOIN Department d ON s.major = d.deptCode -> JOIN Enroll e ON s.stuId = e.stuId -> JOIN Class c ON e.classId = c.classId -> JOIN Course co ON c.courseId = co.courseId -> JOIN Grade g ON e.grade = g.grade -> WHERE d.deptCode = 'CSCI' -> GROUP BY s.stuId, student -> ORDER BY gpa DESC -> LIMIT 3; +--------+------------+------+ | stuId | student | gpa | +--------+------------+------+ | 100001 | Mary Hawk | 3.67 | | 100000 | Tony Hawk | 3.00 | | 100002 | David Hawk | 2.33 | +--------+------------+------+ 3 rows in set (0.001 sec) MariaDB [toyu]> SELECT * -> FROM student; +--------+-----------+----------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+-----------+----------+-------+-------+------+---------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | +--------+-----------+----------+-------+-------+------+---------+ 11 rows in set (0.000 sec) MariaDB [toyu]> SELECT * -> FROM student -> LIMIT 5; +--------+-----------+---------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+-----------+---------+-------+-------+------+---------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | +--------+-----------+---------+-------+-------+------+---------+ 5 rows in set (0.000 sec) MariaDB [toyu]> SELECT * -> FROM student -> LIMIT 2,5; +--------+-----------+---------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+-----------+---------+-------+-------+------+---------+ | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | +--------+-----------+---------+-------+-------+------+---------+ 5 rows in set (0.000 sec) MariaDB [toyu]> MariaDB [toyu]> -- using cursor. MariaDB [toyu]> DELIMITER // MariaDB [toyu]> MariaDB [toyu]> CREATE FUNCTION major_students(dept VARCHAR(4)) -> RETURNS VARCHAR(1000) -> READS SQL DATA -> BEGIN -> DECLARE result VARCHAR(1000) DEFAULT ''; -> DECLARE name VARCHAR(41) DEFAULT ''; -> DECLARE done INT DEFAULT FALSE; -> -> DECLARE cursor_1 CURSOR FOR -> SELECT DISTINCT CONCAT(fName, ' ', lName) AS name -> FROM student -> WHERE major = dept; -> -> DECLARE continue handler FOR NOT FOUND SET done = TRUE; -> -> OPEN cursor_1; -> SET result = ''; -> -> compute_loop: LOOP -> -- This is needed as there are other SQL statements that may set done to true. -> SET done = false; -> FETCH cursor_1 INTO name; -> IF done THEN -> LEAVE compute_loop; -> END IF; -> -> IF (result <> '') THEN -> SET result = CONCAT(result, ', '); -> END IF; -> -> SET result = CONCAT(result, name); -> END LOOP; -> -> CLOSE cursor_1; -> -> RETURN result; -> END // Query OK, 0 rows affected (0.006 sec) MariaDB [toyu]> MariaDB [toyu]> DELIMITER ; MariaDB [toyu]> MariaDB [toyu]> SELECT major_students('CSCI'); +----------------------------------+ | major_students('CSCI') | +----------------------------------+ | Tony Hawk, Mary Hawk, David Hawk | +----------------------------------+ 1 row in set (0.002 sec) MariaDB [toyu]> SELECT major_students('CINF'); +--------------------------------+ | major_students('CINF') | +--------------------------------+ | Linda Johnson, Lillian Johnson | +--------------------------------+ 1 row in set (0.001 sec) MariaDB [toyu]> SELECT DISTINCT CONCAT(fName, ' ', lName) AS name -> FROM student -> WHERE major = dept; ERROR 1054 (42S22): Unknown column 'dept' in 'where clause' MariaDB [toyu]> SELECT DISTINCT CONCAT(fName, ' ', lName) AS name -> FROM student -> WHERE major = 'CSCI'; +------------+ | name | +------------+ | Tony Hawk | | Mary Hawk | | David Hawk | +------------+ 3 rows in set (0.001 sec) MariaDB [toyu]> SELECT DISTINCT u.user, u.password -> FROM mysql.user AS u -> WHERE u.host = 'localhost'; +-------+-------------------------------------------+ | User | Password | +-------+-------------------------------------------+ | d5333 | *C2D24DCA38E9E862098B85BF0AB35CAA52803797 | | demo | *C2D24DCA38E9E862098B85BF0AB35CAA52803797 | | pma | | | root | | | s1 | *C2D24DCA38E9E862098B85BF0AB35CAA52803797 | | s2 | *C2D24DCA38E9E862098B85BF0AB35CAA52803797 | | yue | *0BE23F42F79ABCFD46E7AF357A07122B6C495EA8 | +-------+-------------------------------------------+ 7 rows in set (0.011 sec) MariaDB [toyu]> -- System Catalog MariaDB [toyu]> -- Getting selected columns from information_schema MariaDB [toyu]> 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; +---------------------------------------+-------------+------------+------------+----------------+ | table_name | table_type | row_format | table_rows | avg_row_length | +---------------------------------------+-------------+------------+------------+----------------+ | VIEWS | SYSTEM VIEW | Page | NULL | 0 | | user_variables | SYSTEM VIEW | Fixed | NULL | 6630 | | USER_STATISTICS | SYSTEM VIEW | Fixed | NULL | 567 | | USER_PRIVILEGES | SYSTEM VIEW | Fixed | NULL | 2314 | | TRIGGERS | SYSTEM VIEW | Page | NULL | 0 | | TABLE_STATISTICS | SYSTEM VIEW | Fixed | NULL | 1181 | | TABLE_PRIVILEGES | SYSTEM VIEW | Fixed | NULL | 2700 | | TABLE_CONSTRAINTS | SYSTEM VIEW | Fixed | NULL | 2504 | | TABLESPACES | SYSTEM VIEW | Fixed | NULL | 6951 | | TABLES | SYSTEM VIEW | Fixed | NULL | 14841 | | SYSTEM_VARIABLES | SYSTEM VIEW | Page | NULL | 0 | | STATISTICS | SYSTEM VIEW | Fixed | NULL | 5753 | | SQL_FUNCTIONS | SYSTEM VIEW | Fixed | NULL | 194 | | SPATIAL_REF_SYS | SYSTEM VIEW | Fixed | NULL | 7691 | | SESSION_VARIABLES | SYSTEM VIEW | Fixed | NULL | 6340 | | SESSION_STATUS | SYSTEM VIEW | Fixed | NULL | 6340 | | SCHEMA_PRIVILEGES | SYSTEM VIEW | Fixed | NULL | 2507 | | SCHEMATA | SYSTEM VIEW | Fixed | NULL | 3464 | | ROUTINES | SYSTEM VIEW | Page | NULL | 0 | | REFERENTIAL_CONSTRAINTS | SYSTEM VIEW | Fixed | NULL | 4814 | | PROFILING | SYSTEM VIEW | Fixed | NULL | 308 | | PROCESSLIST | SYSTEM VIEW | Page | NULL | 0 | | PLUGINS | SYSTEM VIEW | Page | NULL | 0 | | PARTITIONS | SYSTEM VIEW | Page | NULL | 0 | | PARAMETERS | SYSTEM VIEW | Page | NULL | 0 | | OPTIMIZER_TRACE | SYSTEM VIEW | Page | NULL | 0 | | KEY_COLUMN_USAGE | SYSTEM VIEW | Fixed | NULL | 4637 | | KEY_CACHES | SYSTEM VIEW | Fixed | NULL | 659 | | KEYWORDS | SYSTEM VIEW | Fixed | NULL | 194 | | INNODB_TRX | SYSTEM VIEW | Fixed | NULL | 4530 | | INNODB_TABLESPACES_SCRUBBING | SYSTEM VIEW | Fixed | NULL | 2020 | | INNODB_TABLESPACES_ENCRYPTION | SYSTEM VIEW | Fixed | NULL | 2012 | | INNODB_SYS_VIRTUAL | SYSTEM VIEW | Fixed | NULL | 17 | | INNODB_SYS_TABLESTATS | SYSTEM VIEW | Fixed | NULL | 1215 | | INNODB_SYS_TABLESPACES | SYSTEM VIEW | Fixed | NULL | 2102 | | INNODB_SYS_TABLES | SYSTEM VIEW | Fixed | NULL | 2060 | | INNODB_SYS_SEMAPHORE_WAITS | SYSTEM VIEW | Fixed | NULL | 60189 | | INNODB_SYS_INDEXES | SYSTEM VIEW | Fixed | NULL | 618 | | INNODB_SYS_FOREIGN_COLS | SYSTEM VIEW | Fixed | NULL | 1748 | | INNODB_SYS_FOREIGN | SYSTEM VIEW | Fixed | NULL | 1752 | | INNODB_SYS_FIELDS | SYSTEM VIEW | Fixed | NULL | 594 | | INNODB_SYS_DATAFILES | SYSTEM VIEW | Fixed | NULL | 12007 | | INNODB_SYS_COLUMNS | SYSTEM VIEW | Fixed | NULL | 610 | | INNODB_MUTEXES | SYSTEM VIEW | Fixed | NULL | 24017 | | INNODB_METRICS | SYSTEM VIEW | Fixed | NULL | 3003 | | INNODB_LOCK_WAITS | SYSTEM VIEW | Fixed | NULL | 599 | | INNODB_LOCKS | SYSTEM VIEW | Fixed | NULL | 31244 | | INNODB_FT_INDEX_TABLE | SYSTEM VIEW | Fixed | NULL | 1054 | | INNODB_FT_INDEX_CACHE | SYSTEM VIEW | Fixed | NULL | 1054 | | INNODB_FT_DELETED | SYSTEM VIEW | Fixed | NULL | 9 | | INNODB_FT_DEFAULT_STOPWORD | SYSTEM VIEW | Fixed | NULL | 56 | | INNODB_FT_CONFIG | SYSTEM VIEW | Fixed | NULL | 1163 | | INNODB_FT_BEING_DELETED | SYSTEM VIEW | Fixed | NULL | 9 | | INNODB_CMP_RESET | SYSTEM VIEW | Fixed | NULL | 25 | | INNODB_CMP_PER_INDEX_RESET | SYSTEM VIEW | Fixed | NULL | 1755 | | INNODB_CMP_PER_INDEX | SYSTEM VIEW | Fixed | NULL | 1755 | | INNODB_CMPMEM_RESET | SYSTEM VIEW | Fixed | NULL | 29 | | INNODB_CMPMEM | SYSTEM VIEW | Fixed | NULL | 29 | | INNODB_CMP | SYSTEM VIEW | Fixed | NULL | 25 | | INNODB_BUFFER_POOL_STATS | SYSTEM VIEW | Fixed | NULL | 257 | | INNODB_BUFFER_PAGE_LRU | SYSTEM VIEW | Fixed | NULL | 6669 | | INNODB_BUFFER_PAGE | SYSTEM VIEW | Fixed | NULL | 6852 | | INDEX_STATISTICS | SYSTEM VIEW | Fixed | NULL | 1743 | | GLOBAL_VARIABLES | SYSTEM VIEW | Fixed | NULL | 6340 | | GLOBAL_STATUS | SYSTEM VIEW | Fixed | NULL | 6340 | | GEOMETRY_COLUMNS | SYSTEM VIEW | Fixed | NULL | 4244 | | FILES | SYSTEM VIEW | Fixed | NULL | 4022 | | EVENTS | SYSTEM VIEW | Page | NULL | 0 | | ENGINES | SYSTEM VIEW | Fixed | NULL | 731 | | ENABLED_ROLES | SYSTEM VIEW | Fixed | NULL | 387 | | COLUMN_PRIVILEGES | SYSTEM VIEW | Fixed | NULL | 2893 | | COLUMNS | SYSTEM VIEW | Page | NULL | 0 | | COLLATION_CHARACTER_SET_APPLICABILITY | SYSTEM VIEW | Fixed | NULL | 195 | | COLLATIONS | SYSTEM VIEW | Fixed | NULL | 231 | | CLIENT_STATISTICS | SYSTEM VIEW | Fixed | NULL | 386 | | CHECK_CONSTRAINTS | SYSTEM VIEW | Page | NULL | 0 | | CHARACTER_SETS | SYSTEM VIEW | Fixed | NULL | 384 | | APPLICABLE_ROLES | SYSTEM VIEW | Fixed | NULL | 979 | | ALL_PLUGINS | SYSTEM VIEW | Page | NULL | 0 | +---------------------------------------+-------------+------------+------------+----------------+ 79 rows in set (0.069 sec) MariaDB [toyu]> SELECT table_name, table_type, row_format, table_rows, avg_row_length -> FROM information_schema.tables -> WHERE table_schema = 'toyu' -> ORDER BY table_name DESC; +----------------+------------+------------+------------+----------------+ | table_name | table_type | row_format | table_rows | avg_row_length | +----------------+------------+------------+------------+----------------+ | student | BASE TABLE | Dynamic | 11 | 1489 | | school_summary | VIEW | NULL | NULL | NULL | | school | BASE TABLE | Dynamic | 4 | 4096 | | grade | BASE TABLE | Dynamic | 15 | 1092 | | faculty | BASE TABLE | Dynamic | 11 | 1489 | | enroll | BASE TABLE | Dynamic | 22 | 744 | | department | BASE TABLE | Dynamic | 7 | 2340 | | course | BASE TABLE | Dynamic | 11 | 1489 | | class | BASE TABLE | Dynamic | 14 | 1170 | +----------------+------------+------------+------------+----------------+ 9 rows in set (0.001 sec) MariaDB [toyu]> SELECT table_name, table_type, row_format, table_rows, avg_row_length -> FROM information_schema.tables -> WHERE table_schema = 'toyu' -> AND table_type = 'BASE TABLE' -> ORDER BY table_name DESC; +------------+------------+------------+------------+----------------+ | table_name | table_type | row_format | table_rows | avg_row_length | +------------+------------+------------+------------+----------------+ | student | BASE TABLE | Dynamic | 11 | 1489 | | school | BASE TABLE | Dynamic | 4 | 4096 | | grade | BASE TABLE | Dynamic | 15 | 1092 | | faculty | BASE TABLE | Dynamic | 11 | 1489 | | enroll | BASE TABLE | Dynamic | 22 | 744 | | department | BASE TABLE | Dynamic | 7 | 2340 | | course | BASE TABLE | Dynamic | 11 | 1489 | | class | BASE TABLE | Dynamic | 14 | 1170 | +------------+------------+------------+------------+----------------+ 8 rows in set (0.002 sec) MariaDB [toyu]> SELECT t.TABLE_SCHEMA AS `schema`, t.ENGINE, COUNT(t.TABLE_NAME) AS num_tables -> FROM information_schema.tables t -> GROUP BY `schema`, t.ENGINE -> ORDER BY `schema`, num_tables DESC; +--------------------+--------------------+------------+ | schema | ENGINE | num_tables | +--------------------+--------------------+------------+ | ds | InnoDB | 1 | | information_schema | MEMORY | 66 | | information_schema | Aria | 13 | | mysql | Aria | 24 | | mysql | InnoDB | 4 | | mysql | CSV | 2 | | mysql | NULL | 1 | | performance_schema | PERFORMANCE_SCHEMA | 52 | | phpmyadmin | InnoDB | 19 | | swim | InnoDB | 13 | | toyu | InnoDB | 8 | | toyu | NULL | 1 | +--------------------+--------------------+------------+ 12 rows in set (0.103 sec) MariaDB [toyu]> notee