MariaDB [(none)]> use toyu; Database changed MariaDB [toyu]> SELECT s.stuId, s.major -- [3] construct columns -> FROM student AS s -- [1] big raw table: raw rows -> WHERE ach > 0; -- [2] filter out the rows. +--------+-------+ | stuId | major | +--------+-------+ | 100000 | CSCI | | 100001 | CSCI | | 100002 | CSCI | | 100003 | ITEC | | 100004 | ITEC | | 100005 | CINF | | 100006 | CINF | | 100007 | NULL | | 100008 | ARTS | | 100009 | ARTS | +--------+-------+ 10 rows in set (0.033 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT s.major, COUNT(s.stuId) AS `no of major` -- [4] construct output columns -> FROM student AS s -- [1] big raw table: old raw rows -> WHERE ach > 0 -- [2] filter out the rows. -> GROUP BY s.major; -- [3] From group with the same group by column: s.major; each value of s.major defines a group. New group -> new row. +-------+-------------+ | major | no of major | +-------+-------------+ | NULL | 1 | | ARTS | 2 | | CINF | 2 | | CSCI | 3 | | ITEC | 2 | +-------+-------------+ 5 rows in set (0.007 sec) MariaDB [toyu]> MariaDB [toyu]> MariaDB [toyu]> SELECT s.major, COUNT(s.stuId) AS `no of major` -- [5] construct output columns -> FROM student AS s -- [1] big raw table: old raw rows -> WHERE ach > 0 -- [2] filter out the rows. -> GROUP BY s.major -- [3] Form group with the same group by columns: s.major; each value of s.major defines a group. New group -> new row. -> HAVING `no of major` > 1; [4] filter out the group/new row. +-------+-------------+ | major | no of major | +-------+-------------+ | ARTS | 2 | | CINF | 2 | | CSCI | 3 | | ITEC | 2 | +-------+-------------+ 4 rows in set (0.001 sec) -> -> SELECT s.major, -- group by column -> COUNT(s.stuId) AS `no of major`, -- group function. -> 'hello world'; -- constant ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '[4] filter out the group/new row. SELECT s.major, COUNT(s.stuId) AS `no...' at line 1 MariaDB [toyu]> -- [5] construct output columns MariaDB [toyu]> FROM student AS s -- [1] big raw table: old raw rows -> WHERE ach > 0 -- [2] filter out the rows. -> GROUP BY s.major -- [3] Form group with the same group by columns: s.major; each value of s.major defines a group. New group -> new row. -> HAVING `no of major` > 1; [4] filter out the group/new row.; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM student AS s WHERE ach > 0 GROUP BY s.major HAVING `no of major` > 1' at line 1 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '[4] filter out the group/new row.' at line 1 MariaDB [toyu]> SELECT s.major, -- group by column -> COUNT(s.stuId) AS `no of major`, -- group function. -> 'hello world'; -- constant ERROR 1109 (42S02): Unknown table 's' in field list MariaDB [toyu]> -- [5] construct output columns MariaDB [toyu]> FROM student AS s -- [1] big raw table: old raw rows -> WHERE ach > 0 -- [2] filter out the rows. -> GROUP BY s.major -- [3] Form group with the same group by columns: s.major; each value of s.major defines a group. New group -> new row. -> HAVING `no of major` > 1; [4] filter out the group/new row. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM student AS s WHERE ach > 0 GROUP BY s.major HAVING `no of major` > 1' at line 1 -> SELECT s.major, -- group by column -> COUNT(s.stuId) AS `no of major`, -- group function. -> 'hello world'; -- constant ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '[4] filter out the group/new row. SELECT s.major, COUNT(s.stuId) AS `no ...' at line 1 MariaDB [toyu]> -- [5] construct output columns MariaDB [toyu]> FROM student AS s -- [1] big raw table: old raw rows -> WHERE ach > 0 -- [2] filter out the rows. -> GROUP BY s.major -- [3] Form group with the same group by columns: s.major; each value of s.major defines a group. New group -> new row. -> HAVING `no of major` > 1; -- [4] filter out the group/new row. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM student AS s WHERE ach > 0 GROUP BY s.major HAVING `no of major` > 1' at line 1 MariaDB [toyu]> SELECT s.major, -- group by column -> COUNT(s.stuId) AS `no of major`, -- group function. -> 'hello world'; -- constant ERROR 1109 (42S02): Unknown table 's' in field list MariaDB [toyu]> -- [5] construct output columns MariaDB [toyu]> FROM student AS s -- [1] big raw table: old raw rows -> WHERE ach > 0 -- [2] filter out the rows. -> GROUP BY s.major -- [3] Form group with the same group by columns: s.major; each value of s.major defines a group. New group -> new row. -> HAVING `no of major` > 1; -- [4] filter out the group/new row. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM student AS s WHERE ach > 0 GROUP BY s.major HAVING `no of major` > 1' at line 1 MariaDB [toyu]> SELECT s.major, -- group by column -> COUNT(s.stuId) AS `no of major`, -- group function. -> 'hello world' -- constant -> -- [5] construct output columns -> FROM student AS s -- [1] big raw table: old raw rows -> WHERE ach > 0 -- [2] filter out the rows. -> GROUP BY s.major -- [3] Form group with the same group by columns: s.major; each value of s.major defines a group. New group -> new row. -> HAVING `no of major` > 1; -- [4] filter out the group/new row. +-------+-------------+-------------+ | major | no of major | hello world | +-------+-------------+-------------+ | ARTS | 2 | hello world | | CINF | 2 | hello world | | CSCI | 3 | hello world | | ITEC | 2 | hello world | +-------+-------------+-------------+ 4 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT s.major, -- group by column -> COUNT(s.stuId) AS `no of major`, -- group function. -> 'hello world' -- constant -> , CONCAT(s.fname, ' ', f.lname) -> -- [5] construct output columns -> FROM student AS s -- [1] big raw table: old raw rows -> WHERE ach > 0 -- [2] filter out the rows. -> GROUP BY s.major -- [3] Form group with the same group by columns: s.major; each value of s.major defines a group. New group -> new row. -> HAVING `no of major` > 1; -- [4] filter out the group/new row. ERROR 1054 (42S22): Unknown column 'f.lname' in 'field list' MariaDB [toyu]> SELECT s.major, -- group by column -> COUNT(s.stuId) AS `no of major`, -- group function. -> 'hello world' -- constant -> , CONCAT(s.fname, ' ', s.lname) -> -- [5] construct output columns -> FROM student AS s -- [1] big raw table: old raw rows -> WHERE ach > 0 -- [2] filter out the rows. -> GROUP BY s.major -- [3] Form group with the same group by columns: s.major; each value of s.major defines a group. New group -> new row. -> HAVING `no of major` > 1; -- [4] filter out the group/new row. +-------+-------------+-------------+-------------------------------+ | major | no of major | hello world | CONCAT(s.fname, ' ', s.lname) | +-------+-------------+-------------+-------------------------------+ | ARTS | 2 | hello world | Bill Ching | | CINF | 2 | hello world | Linda Johnson | | CSCI | 3 | hello world | Tony Hawk | | ITEC | 2 | hello world | Catherine Lim | +-------+-------------+-------------+-------------------------------+ 4 rows in set (0.002 sec) MariaDB [toyu]> SELECT s.major, -- group by column -> COUNT(s.stuId) AS `no of major` -- group function. -> , GROUP_CONCAT(CONCAT(s.fname, ' ', s.lname)) AS students -- semantic error. -> -- [5] construct output columns -> FROM student AS s -- [1] big raw table: old raw rows -> WHERE ach > 0 -- [2] filter out the rows. -> GROUP BY s.major -- [3] Form group with the same group by columns: s.major; each value of s.major defines a group. New group -> new row. -> HAVING `no of major` > 1; -- [4] filter out the group/new row. +-------+-------------+--------------------------------+ | major | no of major | students | +-------+-------------+--------------------------------+ | ARTS | 2 | Bill Ching,Linda King | | CINF | 2 | Linda Johnson,Lillian Johnson | | CSCI | 3 | Tony Hawk,Mary Hawk,David Hawk | | ITEC | 2 | Catherine Lim,Larry Johnson | +-------+-------------+--------------------------------+ 4 rows in set (0.001 sec) MariaDB [toyu]> SELECT s.major, -- group by column -> COUNT(s.stuId) AS `no of major` -- group function. -> , GROUP_CONCAT(CONCAT(s.fname, ' ', s.lname) ORDER BY lname DESC, fname DESC SEPARATOR '; ') AS students -- semantic error. -> -- [5] construct output columns -> FROM student AS s -- [1] big raw table: old raw rows -> WHERE ach > 0 -- [2] filter out the rows. -> GROUP BY s.major -- [3] Form group with the same group by columns: s.major; each value of s.major defines a group. New group -> new row. -> HAVING `no of major` > 1; -- [4] filter out the group/new row. +-------+-------------+----------------------------------+ | major | no of major | students | +-------+-------------+----------------------------------+ | ARTS | 2 | Linda King; Bill Ching | | CINF | 2 | Linda Johnson; Lillian Johnson | | CSCI | 3 | Tony Hawk; Mary Hawk; David Hawk | | ITEC | 2 | Catherine Lim; Larry Johnson | +-------+-------------+----------------------------------+ 4 rows in set (0.002 sec) MariaDB [toyu]> SELECT s.major, COUNT(s.sid) AS numStudents -- [3] construct columns -> FROM student AS s -> GROUP BY s.major -> WHERE s.ach > 0; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE s.ach > 0' at line 4 MariaDB [toyu]> SELECT s.major, COUNT(s.sid) AS numStudents -- [3] construct columns -> FROM student AS s -> WHERE s.ach > 0 -> GROUP BY s.major; ERROR 1054 (42S22): Unknown column 's.sid' in 'field list' MariaDB [toyu]> SELECT s.major, -> COUNT(s.sid) AS numStudents -> FROM student AS s -> WHERE s.ach > 0 -> GROUP BY s.major; ERROR 1054 (42S22): Unknown column 's.sid' in 'field list' MariaDB [toyu]> MariaDB [toyu]> SELECT s.major, -> COUNT(s.stuId) AS numStudents -> FROM student AS s -> WHERE s.ach > 0 -> GROUP BY s.major; +-------+-------------+ | major | numStudents | +-------+-------------+ | NULL | 1 | | ARTS | 2 | | CINF | 2 | | CSCI | 3 | | ITEC | 2 | +-------+-------------+ 5 rows in set (0.002 sec) MariaDB [toyu]> SELECT s.major, COUNT(s.stuid) AS numStudents -- [3] construct columns -> FROM student AS s -> GROUP BY s.major -> WHERE s.ach > 0; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE s.ach > 0' at line 4 MariaDB [toyu]> SELECT s.major, -> COUNT(s.stuId) AS numStudents -> FROM student AS s -> WHERE s.ach > 0 AND COUNT(s.stuId) > 1 -> GROUP BY s.major; ERROR 1111 (HY000): Invalid use of group function MariaDB [toyu]> SELECT s.major, -> COUNT(s.stuId) AS numStudents -> FROM student AS s -> WHERE s.ach > 0 AND numStudents > 1 -> GROUP BY s.major; ERROR 1054 (42S22): Unknown column 'numStudents' in 'where clause' MariaDB [toyu]> SELECT s.major, -> COUNT(s.stuId) AS numStudents -> FROM student AS s -> GROUP BY s.major -> HAVING s.ach > 0 AND numStudents > 1; ERROR 1054 (42S22): Unknown column 's.ach' in 'having clause' MariaDB [toyu]> SELECT CONCAT(s.fname, ' ', s.lname) AS student, -> COUNT(e.classId) AS `Enrolled classes` -> FROM student AS s LEFT JOIN enroll e ON (s.stuId = e.stuId) -> GROUP BY student -> ORDER BY `Enrolled classes` DESC; +-----------------+------------------+ | student | Enrolled classes | +-----------------+------------------+ | Tony Hawk | 6 | | Linda Johnson | 4 | | David Hawk | 3 | | Mary Hawk | 2 | | Larry Johnson | 2 | | Lillian Johnson | 2 | | Ben Zico | 2 | | Bill Ching | 1 | | Catherine Lim | 0 | | Linda King | 0 | | Cathy Johanson | 0 | +-----------------+------------------+ 11 rows in set (0.003 sec) MariaDB [toyu]> SELECT CONCAT(s.fname, ' ', s.lname) AS student, -> COUNT(e.classId) AS `Enrolled classes` -> FROM student AS s INNER JOIN enroll e ON (s.stuId = e.stuId) -> GROUP BY student -> ORDER BY `Enrolled classes` DESC; +-----------------+------------------+ | student | Enrolled classes | +-----------------+------------------+ | Tony Hawk | 6 | | Linda Johnson | 4 | | David Hawk | 3 | | Mary Hawk | 2 | | Larry Johnson | 2 | | Lillian Johnson | 2 | | Ben Zico | 2 | | Bill Ching | 1 | +-----------------+------------------+ 8 rows in set (0.001 sec) MariaDB [toyu]> SELECT CONCAT(s.fname, ' ', s.lname) AS student, -> COUNT(e.classId) AS `Enrolled classes` -> FROM student AS s INNER JOIN enroll e ON (s.stuId = e.stuId) -> GROUP BY student -> HAVING `Enrolled classes` < 4 -> ORDER BY `Enrolled classes` DESC; +-----------------+------------------+ | student | Enrolled classes | +-----------------+------------------+ | David Hawk | 3 | | Mary Hawk | 2 | | Larry Johnson | 2 | | Lillian Johnson | 2 | | Ben Zico | 2 | | Bill Ching | 1 | +-----------------+------------------+ 6 rows in set (0.001 sec) MariaDB [toyu]> SELECT f.deptCode, COUNT(f.facId) AS numFaculty -> FROM faculty AS f -> GROUP BY f.deptCode; +----------+------------+ | deptCode | numFaculty | +----------+------------+ | ACCT | 1 | | ARTS | 1 | | CINF | 2 | | CSCI | 4 | | ENGL | 1 | | ITEC | 2 | +----------+------------+ 6 rows in set (0.002 sec) MariaDB [toyu]> SELECT s.major AS deptCode, COUNT(s.stuId) AS numMajor -> FROM student AS s -> GROUP BY s.major; +----------+----------+ | deptCode | numMajor | +----------+----------+ | NULL | 2 | | ARTS | 2 | | CINF | 2 | | CSCI | 3 | | ITEC | 2 | +----------+----------+ 5 rows in set (0.001 sec) MariaDB [toyu]> SELECT s.major AS deptCode, COUNT(s.stuId) AS numMinor -> FROM student AS s -> GROUP BY s.minor; +----------+----------+ | deptCode | numMinor | +----------+----------+ | ITEC | 4 | | CSCI | 3 | | ARTS | 1 | | CINF | 1 | | CSCI | 2 | +----------+----------+ 5 rows in set (0.002 sec) MariaDB [toyu]> WITH ma AS -> (SELECT s.major AS deptCode, COUNT(s.stuId) AS numMajor -> FROM student AS s -> GROUP BY s.major), -> mi AS -> (SELECT s.minor AS deptCode, COUNT(s.stuId) AS numMinor -> FROM student AS s -> GROUP BY s.minor), -> f AS -> (SELECT f.deptCode, COUNT(f.facId) AS numFaculty -> FROM faculty AS f -> GROUP BY f.deptCode) -> SELECT d.deptCode, -> d.deptName, -> IFNULL(f.numFaculty, 0) AS numFaculty, -> IFNULL(ma.numMajor, 0) AS numMajor, -> IFNULL(mi.numMinor, 0) AS numMinor -> FROM department AS d LEFT JOIN ma USING (deptCode) -> LEFT JOIN mi USING (deptCode) -> LEFT JOIN f USING (deptCode); +----------+------------------------------+------------+----------+----------+ | deptCode | deptName | numFaculty | numMajor | numMinor | +----------+------------------------------+------------+----------+----------+ | ACCT | Accounting | 1 | 0 | 0 | | ARTS | Arts | 1 | 2 | 0 | | CINF | Computer Information Systems | 2 | 2 | 3 | | CSCI | Computer Science | 4 | 3 | 1 | | ENGL | English | 1 | 0 | 1 | | ITEC | Information Technology | 2 | 2 | 2 | | MATH | Mathematics | 0 | 0 | 0 | +----------+------------------------------+------------+----------+----------+ 7 rows in set (0.005 sec) 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 | Arts | 2 | 2 | 9 | | Human Sciences and Humanities | English | 0 | 2 | 9 | | Science and Engineering | Computer Science | 3 | 7 | 9 | | Science and Engineering | Information Technology | 2 | 7 | 9 | | Science and Engineering | Mathematics | 0 | 7 | 9 | | Science and Engineering | Computer Information Systems | 2 | 7 | 9 | +-------------------------------+------------------------------+---------------------+------------------+---------------------+ 7 rows in set (0.002 sec) MariaDB [toyu]> 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); +----------+------------+-----------+ | college | department | deptMajor | +----------+------------+-----------+ | Business | Accounting | 9 | +----------+------------+-----------+ 1 row in set (0.001 sec) 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 | Arts | 2 | 2 | 9 | | Human Sciences and Humanities | English | 0 | 2 | 9 | | Science and Engineering | Computer Science | 3 | 7 | 9 | | Science and Engineering | Information Technology | 2 | 7 | 9 | | Science and Engineering | Mathematics | 0 | 7 | 9 | | Science and Engineering | Computer Information Systems | 2 | 7 | 9 | +-------------------------------+------------------------------+---------------------+------------------+---------------------+ 7 rows in set (0.003 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); +----------+------------+-----------+ | college | department | deptMajor | +----------+------------+-----------+ | Business | Accounting | 9 | +----------+------------+-----------+ 1 row in set (0.002 sec) MariaDB [toyu]> GROUP BY college, department; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'GROUP BY college, department' at line 1 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.002 sec) 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 | Arts | 2 | 2 | 9 | | Human Sciences and Humanities | English | 0 | 2 | 9 | | Science and Engineering | Computer Science | 3 | 7 | 9 | | Science and Engineering | Information Technology | 2 | 7 | 9 | | Science and Engineering | Mathematics | 0 | 7 | 9 | | Science and Engineering | Computer Information Systems | 2 | 7 | 9 | +-------------------------------+------------------------------+---------------------+------------------+---------------------+ 7 rows in set (0.001 sec) MariaDB [toyu]> SELECT * FROM toyu.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.001 sec) MariaDB [toyu]> SET @sql = "SELECT * FROM toyu.student"; Query OK, 0 rows affected (0.003 sec) MariaDB [toyu]> PREPARE stmt FROM @sql; Query OK, 0 rows affected (0.003 sec) Statement prepared MariaDB [toyu]> EXECUTE stmt; +--------+-----------+----------+-------+-------+------+---------+ | 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.001 sec) MariaDB [toyu]> DEALLOCATE PREPARE stmt; Query OK, 0 rows affected (0.001 sec) MariaDB [toyu]> EXPLAIN WITH ma AS -- [2] -> (SELECT s.major AS deptCode, COUNT(s.stuId) AS numMajor -> FROM student AS s -> GROUP BY s.major), -> mi AS -- [3] -> (SELECT s.minor AS deptCode, COUNT(s.stuId) AS numMinor -> FROM student AS s -> GROUP BY s.minor), -> f AS -- [1] -> (SELECT f.deptCode, COUNT(f.facId) AS numFaculty -> FROM faculty AS f -> GROUP BY f.deptCode) -> SELECT d.deptCode, -> d.deptName, -> IFNULL(f.numFaculty, 0) AS numFaculty, -> IFNULL(ma.numMajor, 0) AS numMajor, -> IFNULL(mi.numMinor, 0) AS numMinor -> FROM department AS d LEFT JOIN ma USING (deptCode) -> LEFT JOIN mi USING (deptCode) -> LEFT JOIN f USING (deptCode); +------+-------------+------------+-------+---------------------+---------------------+---------+-----------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+-------+---------------------+---------------------+---------+-----------------+------+----------------------------------------------+ | 1 | PRIMARY | d | index | NULL | Department_name_ck | 122 | NULL | 7 | Using index | | 1 | PRIMARY | | ref | key0 | key0 | 17 | toyu.d.deptCode | 2 | | | 1 | PRIMARY | | ref | key0 | key0 | 17 | toyu.d.deptCode | 2 | | | 1 | PRIMARY | | ref | key0 | key0 | 17 | toyu.d.deptCode | 2 | | | 2 | DERIVED | s | index | Student_major_fk | Student_major_fk | 17 | NULL | 11 | Using index; Using temporary; Using filesort | | 3 | DERIVED | s | index | Student_minor_fk | Student_minor_fk | 17 | NULL | 11 | Using index; Using temporary; Using filesort | | 4 | DERIVED | f | index | Faculty_deptCode_fk | Faculty_deptCode_fk | 16 | NULL | 11 | Using index; Using temporary; Using filesort | +------+-------------+------------+-------+---------------------+---------------------+---------+-----------------+------+----------------------------------------------+ 7 rows in set (0.002 sec) MariaDB [toyu]> -- Prepared statements. MariaDB [toyu]> SET @sql = "SELECT * FROM toyu.student"; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> PREPARE stmt FROM @sql; Query OK, 0 rows affected (0.000 sec) Statement prepared MariaDB [toyu]> EXECUTE stmt; +--------+-----------+----------+-------+-------+------+---------+ | 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]> DEALLOCATE PREPARE stmt; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> MariaDB [toyu]> -- with placeholders. MariaDB [toyu]> SET @sql = "SELECT * FROM toyu.student WHERE major = ? AND ach >= ?"; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> PREPARE stmt FROM @sql; Query OK, 0 rows affected (0.000 sec) Statement prepared MariaDB [toyu]> SET @major = 'CSCI'; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> SET @ach = 38; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> EXECUTE stmt USING @major, @ach; +--------+-------+-------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+-------+-------+-------+-------+------+---------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | +--------+-------+-------+-------+-------+------+---------+ 2 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> EXECUTE stmt USING 'CSCI', 38; +--------+-------+-------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+-------+-------+-------+-------+------+---------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | +--------+-------+-------+-------+-------+------+---------+ 2 rows in set (0.000 sec) MariaDB [toyu]> MariaDB [toyu]> SET @major = 'CINF'; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> SET @ach = 15; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> EXECUTE stmt USING @major, @ach; +--------+---------+---------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+---------+---------+-------+-------+------+---------+ | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | +--------+---------+---------+-------+-------+------+---------+ 1 row in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SET @major = 'ITEC'; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> SET @ach = 25; Query OK, 0 rows affected (0.001 sec) MariaDB [toyu]> EXECUTE stmt USING @major, @ach; +--------+-------+---------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+-------+---------+-------+-------+------+---------+ | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | +--------+-------+---------+-------+-------+------+---------+ 1 row in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> DEALLOCATE PREPARE stmt; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> SELECT s.schoolCode, s.schoolName, -> COUNT(d.deptCode) AS n_departments -> FROM school AS s LEFT JOIN department AS d ON (s.schoolCode = d.schoolCode) -> GROUP BY s.schoolCode, s.schoolName; +------------+-------------------------------+---------------+ | schoolCode | schoolName | n_departments | +------------+-------------------------------+---------------+ | BUS | Business | 1 | | CSE | Science and Engineering | 4 | | EDU | Education | 0 | | HSH | Human Sciences and Humanities | 2 | +------------+-------------------------------+---------------+ 4 rows in set (0.003 sec) MariaDB [toyu]> MariaDB [toyu]> CREATE OR REPLACE VIEW school_summary( -> schoolCode, schoolName, n_departments) AS -> SELECT s.schoolCode, s.schoolName, -> COUNT(d.deptCode) AS n_departments -> FROM school AS s LEFT JOIN department AS d ON (s.schoolCode = d.schoolCode) -> GROUP BY s.schoolCode, s.schoolName; Query OK, 0 rows affected (0.014 sec) MariaDB [toyu]> MariaDB [toyu]> SHOW CREATE VIEW school_summary; +----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | View | Create View | character_set_client | collation_connection | +----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | school_summary | CREATE ALGORITHM=UNDEFINED DEFINER=`yue`@`localhost` SQL SECURITY DEFINER VIEW `school_summary` AS select `s`.`schoolCode` AS `schoolCode`,`s`.`schoolName` AS `schoolName`,count(`d`.`deptCode`) AS `n_departments` from (`school` `s` left join `department` `d` on(`s`.`schoolCode` = `d`.`schoolCode`)) group by `s`.`schoolCode`,`s`.`schoolName` | cp850 | cp850_general_ci | +----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ 1 row in set (0.004 sec) MariaDB [toyu]> -- Note something like "ALGORITHM=UNDEFINED DEFINER=`yue`@`localhost` SQL SECURITY DEFINER" MariaDB [toyu]> -- (default values) may be added. MariaDB [toyu]> DESC school_summary; +---------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+-------------+------+-----+---------+-------+ | schoolCode | char(3) | NO | | NULL | | | schoolName | varchar(30) | NO | | NULL | | | n_departments | bigint(21) | NO | | 0 | | +---------------+-------------+------+-----+---------+-------+ 3 rows in set (0.031 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT * -> FROM school_summary -> WHERE n_departments > 0; +------------+-------------------------------+---------------+ | schoolCode | schoolName | n_departments | +------------+-------------------------------+---------------+ | BUS | Business | 1 | | CSE | Science and Engineering | 4 | | HSH | Human Sciences and Humanities | 2 | +------------+-------------------------------+---------------+ 3 rows in set (0.001 sec) MariaDB [toyu]> DROP VIEW school_summary; Query OK, 0 rows affected (0.003 sec) MariaDB [toyu]> MariaDB [toyu]> CREATE OR REPLACE VIEW school_summary( -> schoolCode, schoolName, n_departments) AS -> SELECT s.schoolCode, s.schoolName, -> COUNT(d.deptCode) AS n_departments -> FROM school AS s LEFT JOIN department AS d ON (s.schoolCode = d.schoolCode) -> GROUP BY s.schoolCode, s.schoolName; Query OK, 0 rows affected (0.015 sec) MariaDB [toyu]> SELECT *, sc. -> FROM school_summary AS s INNER JOIN school AS sc.schoolName USING (schoolCode) -> WHERE n_departments > 0; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM school_summary AS s INNER JOIN school AS sc.schoolName USING (schoolCode...' at line 2 MariaDB [toyu]> SELECT *, sc.schoolName -> FROM school_summary AS s INNER JOIN school AS sc.schoolName USING (schoolCode) -> WHERE n_departments > 0; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.schoolName USING (schoolCode) WHERE n_departments > 0' at line 2 MariaDB [toyu]> SELECT *, sc.schoolName -> FROM school_summary AS s INNER JOIN school AS sc USING (schoolCode) -> WHERE n_departments > 0; +------------+-------------------------------+---------------+-------------------------------+-------------------------------+ | schoolCode | schoolName | n_departments | schoolName | schoolName | +------------+-------------------------------+---------------+-------------------------------+-------------------------------+ | BUS | Business | 1 | Business | Business | | HSH | Human Sciences and Humanities | 2 | Human Sciences and Humanities | Human Sciences and Humanities | | CSE | Science and Engineering | 4 | Science and Engineering | Science and Engineering | +------------+-------------------------------+---------------+-------------------------------+-------------------------------+ 3 rows in set (0.001 sec) MariaDB [toyu]> Execute the following code and ensure that you understand the result. -> -- A very simple stored procedure. -> -- Redefine the delimiter to end the procedure. -> DELIMITER // -> -> CREATE OR REPLACE PROCEDURE deptInfo(IN dCode VARCHAR(4), OUT numFaculty INT) -> BEGIN -> -- Display some information. -> SELECT d.deptName, d.SchoolCode, t1.n_majors, t2.n_minors -> FROM department AS d INNER JOIN -> (SELECT COUNT(stuId) AS n_majors -> FROM student -> WHERE major = dCode) AS t1 INNER JOIN -> (SELECT COUNT(stuId) AS n_minors -> FROM student -> WHERE minor = dCode) AS t2 -> WHERE d.deptCode = dCode; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'following code and ensure that you understand the result. DELIMITER // CREA...' at line 1 MariaDB [toyu]> MariaDB [toyu]> -- MySQL does not direct sending output to console. MariaDB [toyu]> -- It is necessary to use a SQL statement. MariaDB [toyu]> SELECT 'Debuggin comment can be put here.'; +-----------------------------------+ | Debuggin comment can be put here. | +-----------------------------------+ | Debuggin comment can be put here. | +-----------------------------------+ 1 row in set (0.000 sec) MariaDB [toyu]> SELECT CONCAT('Faculty in the department: ', dCode) AS faculty; ERROR 1054 (42S22): Unknown column 'dCode' in 'field list' MariaDB [toyu]> MariaDB [toyu]> SELECT * -> FROM faculty AS f -> WHERE f.deptCode = dCode; ERROR 1054 (42S22): Unknown column 'dCode' in 'where clause' MariaDB [toyu]> MariaDB [toyu]> SELECT COUNT(f.facId) INTO numFaculty -> FROM faculty AS f -> WHERE f.deptCode = dCode; ERROR 1327 (42000): Undeclared variable: numFaculty MariaDB [toyu]> END // -> -> DELIMITER ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'END // DELIMITER' at line 1 MariaDB [toyu]> MariaDB [toyu]> SHOW CREATE PROCEDURE deptInfo; ERROR 1305 (42000): PROCEDURE deptInfo does not exist 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.000 sec) MariaDB [toyu]> CALL deptInfo(@dCode, @numFaculty); ERROR 1305 (42000): PROCEDURE toyu.deptInfo does not exist MariaDB [toyu]> SELECT @dCode, @numFaculty; +--------+-------------+ | @dCode | @numFaculty | +--------+-------------+ | CSCI | 0 | +--------+-------------+ 1 row in set (0.000 sec) MariaDB [toyu]> MariaDB [toyu]> SET @dCode = 'ITEC'; Query OK, 0 rows affected (0.001 sec) MariaDB [toyu]> CALL deptInfo(@dCode, @numFaculty); ERROR 1305 (42000): PROCEDURE toyu.deptInfo does not exist MariaDB [toyu]> SELECT @dCode, @numFaculty; +--------+-------------+ | @dCode | @numFaculty | +--------+-------------+ | ITEC | 0 | +--------+-------------+ 1 row in set (0.000 sec) MariaDB [toyu]> Execute t -> ; ERROR 1243 (HY000): Unknown prepared statement handler (t) given to EXECUTE 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.008 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.000 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.231 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.332 sec) Query OK, 1 row affected (0.546 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.001 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.120 sec) +---------------------------------+ | faculty | +---------------------------------+ | Faculty in the department: ITEC | +---------------------------------+ 1 row in set (0.217 sec) +-------+----------+-------+----------+-----------+ | facId | fname | lname | deptCode | rank | +-------+----------+-------+----------+-----------+ | 1017 | Deborah | Gump | ITEC | Professor | | 1019 | Benjamin | Yu | ITEC | Lecturer | +-------+----------+-------+----------+-----------+ 2 rows in set (0.331 sec) Query OK, 1 row affected (0.467 sec) MariaDB [toyu]> SELECT @dCode, @numFaculty; +--------+-------------+ | @dCode | @numFaculty | +--------+-------------+ | ITEC | 2 | +--------+-------------+ 1 row in set (0.001 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.009 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.004 sec) MariaDB [toyu]> SELECT n_major('ITEC'); +-----------------+ | n_major('ITEC') | +-----------------+ | 2 | +-----------------+ 1 row in set (0.001 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.010 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.002 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 $$ 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.001 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]> 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.009 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]> 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.008 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.000 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.002 sec) +-----------------------------------+ | Debuggin comment can be put here. | +-----------------------------------+ | Debuggin comment can be put here. | +-----------------------------------+ 1 row in set (0.166 sec) +---------------------------------+ | faculty | +---------------------------------+ | Faculty in the department: CSCI | +---------------------------------+ 1 row in set (0.303 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.487 sec) Query OK, 1 row affected (0.798 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.001 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.178 sec) +---------------------------------+ | faculty | +---------------------------------+ | Faculty in the department: ITEC | +---------------------------------+ 1 row in set (0.328 sec) +-------+----------+-------+----------+-----------+ | facId | fname | lname | deptCode | rank | +-------+----------+-------+----------+-----------+ | 1017 | Deborah | Gump | ITEC | Professor | | 1019 | Benjamin | Yu | ITEC | Lecturer | +-------+----------+-------+----------+-----------+ 2 rows in set (0.478 sec) Query OK, 1 row affected (0.661 sec) MariaDB [toyu]> SELECT @dCode, @numFaculty; +--------+-------------+ | @dCode | @numFaculty | +--------+-------------+ | ITEC | 2 | +--------+-------------+ 1 row in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> MariaDB [toyu]> notee