MariaDB [toyu]> SELECT s.major, COUNT(s.stuId) AS `Number of major` -> FROM student AS s -> WHERE s.ach > 15 -- 9 intial rows -> GROUP BY s.major -- 5 groups: 5 new rows -> HAVING `Number of major` > 1; +-------+-----------------+ | major | Number of major | +-------+-----------------+ | ARTS | 2 | | CSCI | 3 | | ITEC | 2 | +-------+-----------------+ 3 rows in set (0.009 sec) MariaDB [toyu]> SELECT s.major, COUNT(s.stuId) AS `Number of major` -> FROM student AS s -> WHERE s.ach > 15 -- 9 intial rows -> GROUP BY s.major -- 5 groups: 5 new rows -> ; +-------+-----------------+ | major | Number of major | +-------+-----------------+ | NULL | 1 | | ARTS | 2 | | CINF | 1 | | CSCI | 3 | | ITEC | 2 | +-------+-----------------+ 5 rows in set (0.003 sec) MariaDB [toyu]> SELECT s.major, d.deptName AS department, COUNT(s.stuId) AS `Number of major` -> FROM student AS s INNER JOIN department AS d (s.major = d.deptCode) -> WHERE s.ach > 15 -- 9 intial rows -> GROUP BY s.major, department -- 5 groups: 5 new rows -> HAVING `Number of major` > 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 '(s.major = d.deptCode) WHERE s.ach > 15 GROUP BY s.major, department HAVING...' at line 2 MariaDB [toyu]> SELECT s.major, d.deptName AS department, COUNT(s.stuId) AS `Number of major` -> FROM student AS s INNER JOIN department AS d ON (s.major = d.deptCode) -> WHERE s.ach > 15 -- 9 intial rows -> GROUP BY s.major, department -- 5 groups: 5 new rows -> HAVING `Number of major` > 1; +-------+------------------------+-----------------+ | major | department | Number of major | +-------+------------------------+-----------------+ | ARTS | Arts | 2 | | CSCI | Computer Science | 3 | | ITEC | Information Technology | 2 | +-------+------------------------+-----------------+ 3 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT s.major, d.deptName AS department, '= ', -> COUNT(s.stuId) AS `Number of major` -> FROM student AS s INNER JOIN department AS d ON (s.major = d.deptCode) -> WHERE s.ach > 15 -- 9 intial rows -> GROUP BY s.major, department -- 5 groups: 5 new rows -> HAVING `Number of major` > 1; +-------+------------------------+----+-----------------+ | major | department | = | Number of major | +-------+------------------------+----+-----------------+ | ARTS | Arts | = | 2 | | CSCI | Computer Science | = | 3 | | ITEC | Information Technology | = | 2 | +-------+------------------------+----+-----------------+ 3 rows in set (0.001 sec) MariaDB [toyu]> SELECT s.major, d.deptName AS department, '= ', -> COUNT(s.stuId) AS `Number of major`, -> CONCAT(s.fname, ' ', s.lname) AS students -> FROM student AS s INNER JOIN department AS d ON (s.major = d.deptCode) -> WHERE s.ach > 15 -- 9 intial rows -> GROUP BY s.major, department -- 5 groups: 5 new rows -> HAVING `Number of major` > 1; +-------+------------------------+----+-----------------+---------------+ | major | department | = | Number of major | students | +-------+------------------------+----+-----------------+---------------+ | ARTS | Arts | = | 2 | Bill Ching | | CSCI | Computer Science | = | 3 | Tony Hawk | | ITEC | Information Technology | = | 2 | Catherine Lim | +-------+------------------------+----+-----------------+---------------+ 3 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT s.major, d.deptName AS department, '= ', -> COUNT(s.stuId) AS `Number of major`, -> GROUP_CONCAT(CONCAT(s.fname, ' ', s.lname)) AS students -> FROM student AS s INNER JOIN department AS d ON (s.major = d.deptCode) -> WHERE s.ach > 15 -- 9 intial rows -> GROUP BY s.major, department -- 5 groups: 5 new rows -> HAVING `Number of major` > 1; +-------+------------------------+----+-----------------+--------------------------------+ | major | department | = | Number of major | students | +-------+------------------------+----+-----------------+--------------------------------+ | ARTS | Arts | = | 2 | Bill Ching,Linda King | | CSCI | Computer Science | = | 3 | Tony Hawk,Mary Hawk,David Hawk | | ITEC | Information Technology | = | 2 | Catherine Lim,Larry Johnson | +-------+------------------------+----+-----------------+--------------------------------+ 3 rows in set (0.001 sec) MariaDB [toyu]> SELECT s.major, d.deptName AS department, '= ', -> COUNT(s.stuId) AS `Number of major`, -> GROUP_CONCAT(CONCAT(s.fname, ' ', s.lname) ORDER BY s.fname ASC) AS students -> FROM student AS s INNER JOIN department AS d ON (s.major = d.deptCode) -> WHERE s.ach > 15 -- 9 intial rows -> GROUP BY s.major, department -- 5 groups: 5 new rows -> HAVING `Number of major` > 1; +-------+------------------------+----+-----------------+--------------------------------+ | major | department | = | Number of major | students | +-------+------------------------+----+-----------------+--------------------------------+ | ARTS | Arts | = | 2 | Bill Ching,Linda King | | CSCI | Computer Science | = | 3 | David Hawk,Mary Hawk,Tony Hawk | | ITEC | Information Technology | = | 2 | Catherine Lim,Larry Johnson | +-------+------------------------+----+-----------------+--------------------------------+ 3 rows in set (0.001 sec) MariaDB [toyu]> SELECT s.major, d.deptName AS department, '= ', -> COUNT(s.stuId) AS `Number of major`, -> GROUP_CONCAT(CONCAT(s.fname, ' ', s.lname) ORDER BY s.fname ASC SEPARATOR '; ') AS students -> FROM student AS s INNER JOIN department AS d ON (s.major = d.deptCode) -> WHERE s.ach > 15 -- 9 intial rows -> GROUP BY s.major, department -- 5 groups: 5 new rows -> HAVING `Number of major` > 1; +-------+------------------------+----+-----------------+----------------------------------+ | major | department | = | Number of major | students | +-------+------------------------+----+-----------------+----------------------------------+ | ARTS | Arts | = | 2 | Bill Ching; Linda King | | CSCI | Computer Science | = | 3 | David Hawk; Mary Hawk; Tony Hawk | | ITEC | Information Technology | = | 2 | Catherine Lim; Larry Johnson | +-------+------------------------+----+-----------------+----------------------------------+ 3 rows in set (0.001 sec) MariaDB [toyu]> SELECT s.major, d.deptName AS department, '= ', -> COUNT(s.stuId) AS `Number of major`, -> GROUP_CONCAT(CONCAT(s.fname, ' ', s.lname) ORDER BY s.fname ASC SEPARATOR '; ') AS students -> FROM student AS s INNER JOIN department AS d ON (s.major = d.deptCode) -> WHERE s.ach > 15 -- 9 intial rows -> GROUP BY s.major, department -- 5 groups: 5 new rows -> HAVING `Number of major` > 1 -> ORDER BY `Number of major` DESC; +-------+------------------------+----+-----------------+----------------------------------+ | major | department | = | Number of major | students | +-------+------------------------+----+-----------------+----------------------------------+ | CSCI | Computer Science | = | 3 | David Hawk; Mary Hawk; Tony Hawk | | ARTS | Arts | = | 2 | Bill Ching; Linda King | | ITEC | Information Technology | = | 2 | Catherine Lim; Larry Johnson | +-------+------------------------+----+-----------------+----------------------------------+ 3 rows in set (0.014 sec) MariaDB [toyu]> SELECT s.major, d.deptName AS department, '= ', -> COUNT(s.stuId) AS `Number of major`, -> GROUP_CONCAT(CONCAT(s.fname, ' ', s.lname) ORDER BY s.fname ASC SEPARATOR '; ') AS students -> FROM student AS s INNER JOIN department AS d ON (s.major = d.deptCode) -> WHERE s.ach > 15 -- 9 intial rows -> GROUP BY 1, 2-- 5 groups: 5 new rows -> HAVING `Number of major` > 1 -> ORDER BY `Number of major` DESC; +-------+------------------------+----+-----------------+----------------------------------+ | major | department | = | Number of major | students | +-------+------------------------+----+-----------------+----------------------------------+ | CSCI | Computer Science | = | 3 | David Hawk; Mary Hawk; Tony Hawk | | ARTS | Arts | = | 2 | Bill Ching; Linda King | | ITEC | Information Technology | = | 2 | Catherine Lim; Larry Johnson | +-------+------------------------+----+-----------------+----------------------------------+ 3 rows in set (0.014 sec) MariaDB [toyu]> SELECT d.deptCode, d.deptName, -> COUNT(f.facId) AS numFaculty -> FROM department AS d LEFT JOIN faculty AS f ON (f.deptCode = d.deptCode) -> GROUP BY d.deptCode, d.deptName; +----------+------------------------------+------------+ | deptCode | deptName | numFaculty | +----------+------------------------------+------------+ | ACCT | Accounting | 1 | | ARTS | Arts | 1 | | CINF | Computer Information Systems | 2 | | CSCI | Computer Science | 4 | | ENGL | English | 1 | | ITEC | Information Technology | 2 | | MATH | Mathematics | 0 | +----------+------------------------------+------------+ 7 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]> 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.004 sec) MariaDB [toyu]> select * from f; ERROR 1146 (42S02): Table 'toyu.f' doesn't exist MariaDB [toyu]>