MariaDB [(none)]> use toyu; Database changed MariaDB [toyu]> MariaDB [toyu]> SELECT DISTINCT 'I am ready to go', f.deptCode, COUNT(f.facId) AS numFaculty-- [4] COUNT: group function -> FROM faculty AS f -- [1] -> WHERE f.deptCode IN ('CSCI', 'CINF', 'ITEC') -- [2] -> GROUP BY f.deptCode -- [3] -> ; +------------------+----------+------------+ | I am ready to go | deptCode | numFaculty | +------------------+----------+------------+ | I am ready to go | CINF | 2 | | I am ready to go | CSCI | 4 | | I am ready to go | ITEC | 2 | +------------------+----------+------------+ 3 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> MariaDB [toyu]> SELECT DISTINCT 'I am ready to go', f.deptCode, f.facId, CONCAT(f.fname, ' ', f.lname) -- [4] COUNT: group function -> FROM faculty AS f -- [1] -> WHERE f.deptCode IN ('CSCI', 'CINF', 'ITEC'); -- [2] +------------------+----------+-------+-------------------------------+ | I am ready to go | deptCode | facId | CONCAT(f.fname, ' ', f.lname) | +------------------+----------+-------+-------------------------------+ | I am ready to go | CSCI | 1011 | Paul Smith | | I am ready to go | CSCI | 1012 | Mary Tran | | I am ready to go | CSCI | 1013 | David Love | | I am ready to go | CSCI | 1014 | Sharon Mannes | | I am ready to go | CINF | 1015 | Daniel Kim | | I am ready to go | CINF | 1016 | Andrew Byre | | I am ready to go | ITEC | 1017 | Deborah Gump | | I am ready to go | ITEC | 1019 | Benjamin Yu | +------------------+----------+-------+-------------------------------+ 8 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT DISTINCT 'I am ready to go', f.deptCode, COUNT(f.facId) AS numFaculty, -- [4] COUNT: group function -> GROUP_CONCAT(CONCAT(f.fname, ' ', f.lname) ORDER BY f.lname ASC SEPARATOR '; ') AS faculty -> FROM faculty AS f -- [1] -> WHERE f.deptCode IN ('CSCI', 'CINF', 'ITEC') -- [2] -> HAVING numFaculty < = 3 -> GROUP BY f.deptCode -- [3] -> ; 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 '= 3 GROUP BY f.deptCode' at line 5 MariaDB [toyu]> MariaDB [toyu]> SELECT DISTINCT 'I am ready to go', f.deptCode, COUNT(f.facId) AS numFaculty, -- [4] COUNT: group function -> GROUP_CONCAT(CONCAT(f.fname, ' ', f.lname) ORDER BY f.lname ASC SEPARATOR '; ') AS faculty -> FROM faculty AS f -- [1] -> WHERE f.deptCode IN ('CSCI', 'CINF', 'ITEC') -- [2] -> GROUP BY f.deptCode -- [3] -> HAVING numFaculty <= 3 -> ; +------------------+----------+------------+---------------------------+ | I am ready to go | deptCode | numFaculty | faculty | +------------------+----------+------------+---------------------------+ | I am ready to go | CINF | 2 | Andrew Byre; Daniel Kim | | I am ready to go | ITEC | 2 | Deborah Gump; Benjamin Yu | +------------------+----------+------------+---------------------------+ 2 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> 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]> MariaDB [toyu]> -- [2.b] columns 1 and 4. MariaDB [toyu]> MariaDB [toyu]> SELECT DISTINCT d.deptCode, COUNT(s.stuId) AS numMajor -> FROM department AS d LEFT JOIN student AS s ON (d.deptCode = s.major) -> GROUP BY d.deptCode; +----------+----------+ | deptCode | numMajor | +----------+----------+ | ACCT | 0 | | ARTS | 2 | | CINF | 2 | | CSCI | 3 | | ENGL | 0 | | ITEC | 2 | | MATH | 0 | +----------+----------+ 7 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> MariaDB [toyu]> -- [2.c] columns 1 and 5. MariaDB [toyu]> MariaDB [toyu]> SELECT DISTINCT d.deptCode, COUNT(s.stuId) AS numMinor -> FROM department AS d LEFT JOIN student AS s ON (d.deptCode = s.minor) -> GROUP BY d.deptCode; +----------+----------+ | deptCode | numMinor | +----------+----------+ | ACCT | 0 | | ARTS | 0 | | CINF | 3 | | CSCI | 1 | | ENGL | 1 | | ITEC | 2 | | MATH | 0 | +----------+----------+ 7 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> -- [2] MariaDB [toyu]> WITH t1 AS -> (SELECT DISTINCT d.deptCode, COUNT(f.facId) AS numFaculty -> FROM department AS d LEFT JOIN faculty AS f ON (f.deptCode = d.deptCode) -> GROUP BY d.deptCode), -> t2 AS -> (SELECT DISTINCT d.deptCode, COUNT(s.stuId) AS numMajor -> FROM department AS d LEFT JOIN student AS s ON (d.deptCode = s.major) -> GROUP BY d.deptCode), -> t3 AS -> (SELECT DISTINCT d.deptCode, COUNT(s.stuId) AS numMinor -> FROM department AS d LEFT JOIN student AS s ON (d.deptCode = s.minor) -> GROUP BY d.deptCode) -> SELECT d.deptCode, d.deptName, t1.numFaculty, t2.numMajor, t3.numMinor -> FROM department AS d INNER JOIN t1 USING (deptCode) -> INNER JOIN t2 USING (deptCode) -> INNER JOIN t3 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.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]> MariaDB [toyu]> MariaDB [toyu]> MariaDB [toyu]> notee