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