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` > 2 -> ORDER BY `Enrolled classes` DESC; +---------------+------------------+ | student | Enrolled classes | +---------------+------------------+ | Tony Hawk | 6 | | Linda Johnson | 4 | | David Hawk | 3 | +---------------+------------------+ 3 rows in set (0.019 sec) MariaDB [toyu]> SELECT * -> FROM student AS s INNER JOIN enroll e ON (s.stuId = e.stuId); +--------+---------+---------+-------+-------+------+---------+--------+---------+-------+----------+ | stuId | fname | lname | major | minor | ach | advisor | stuId | classId | grade | n_alerts | +--------+---------+---------+-------+-------+------+---------+--------+---------+-------+----------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10000 | A | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10001 | A | 2 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10002 | B+ | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10003 | C | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10004 | A- | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 11001 | D | 4 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100001 | 10000 | NULL | NULL | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100001 | 10001 | A- | 0 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100002 | 10000 | B- | 3 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100002 | 10002 | B+ | 2 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100002 | 10003 | D | 4 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100004 | 10003 | A | 0 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100004 | 10004 | B+ | NULL | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10003 | NULL | NULL | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10004 | A- | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10005 | A- | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10006 | B+ | NULL | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100006 | 10004 | C+ | NULL | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100006 | 10005 | A | NULL | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100007 | 10007 | F | 4 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100007 | 10008 | A- | 0 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | 100008 | 10007 | C- | 0 | +--------+---------+---------+-------+-------+------+---------+--------+---------+-------+----------+ 22 rows in set (0.001 sec) MariaDB [toyu]> SELECT * -> FROM student AS s INNER JOIN enroll e ON (s.stuId = e.stuId) -> WHERE s.major IN ('CSCI', 'CINF'); +--------+---------+---------+-------+-------+------+---------+--------+---------+-------+----------+ | stuId | fname | lname | major | minor | ach | advisor | stuId | classId | grade | n_alerts | +--------+---------+---------+-------+-------+------+---------+--------+---------+-------+----------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10000 | A | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10001 | A | 2 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10002 | B+ | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10003 | C | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10004 | A- | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 11001 | D | 4 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100001 | 10000 | NULL | NULL | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100001 | 10001 | A- | 0 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100002 | 10000 | B- | 3 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100002 | 10002 | B+ | 2 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100002 | 10003 | D | 4 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10003 | NULL | NULL | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10004 | A- | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10005 | A- | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10006 | B+ | NULL | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100006 | 10004 | C+ | NULL | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100006 | 10005 | A | NULL | +--------+---------+---------+-------+-------+------+---------+--------+---------+-------+----------+ 17 rows in set (0.003 sec) MariaDB [toyu]> SELECT * -> FROM student AS s INNER JOIN enroll e ON (s.stuId = e.stuId) -> WHERE s.major IN ('CSCI', 'CINF') -> GROUP BY student; ERROR 1054 (42S22): Unknown column 'student' in 'group statement' MariaDB [toyu]> SELECT CONCAT(s.fname, ' ', s.lname) AS student, * -> FROM student AS s INNER JOIN enroll e ON (s.stuId = e.stuId) -> WHERE s.major IN ('CSCI', 'CINF') -> GROUP BY student; 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 INNER JOIN enroll e ON (s.stuId = e.stuId) WHERE s.major ...' at line 1 MariaDB [toyu]> SELECT CONCAT(s.fname, ' ', s.lname) AS student, s.*, e.* -> FROM student AS s INNER JOIN enroll e ON (s.stuId = e.stuId) -> WHERE s.major IN ('CSCI', 'CINF') -> GROUP BY student; +-----------------+--------+---------+---------+-------+-------+------+---------+--------+---------+-------+----------+ | student | stuId | fname | lname | major | minor | ach | advisor | stuId | classId | grade | n_alerts | +-----------------+--------+---------+---------+-------+-------+------+---------+--------+---------+-------+----------+ | David Hawk | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100002 | 10000 | B- | 3 | | Lillian Johnson | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100006 | 10004 | C+ | NULL | | Linda Johnson | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10003 | NULL | NULL | | Mary Hawk | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100001 | 10000 | NULL | NULL | | Tony Hawk | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10000 | A | 0 | +-----------------+--------+---------+---------+-------+-------+------+---------+--------+---------+-------+----------+ 5 rows in set (0.001 sec) MariaDB [toyu]> SELECT * -> FROM student AS s INNER JOIN enroll e ON (s.stuId = e.stuId) -> WHERE s.major IN ('CSCI', 'CINF'); +--------+---------+---------+-------+-------+------+---------+--------+---------+-------+----------+ | stuId | fname | lname | major | minor | ach | advisor | stuId | classId | grade | n_alerts | +--------+---------+---------+-------+-------+------+---------+--------+---------+-------+----------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10000 | A | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10001 | A | 2 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10002 | B+ | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10003 | C | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10004 | A- | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 11001 | D | 4 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100001 | 10000 | NULL | NULL | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100001 | 10001 | A- | 0 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100002 | 10000 | B- | 3 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100002 | 10002 | B+ | 2 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100002 | 10003 | D | 4 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10003 | NULL | NULL | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10004 | A- | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10005 | A- | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10006 | B+ | NULL | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100006 | 10004 | C+ | NULL | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100006 | 10005 | A | NULL | +--------+---------+---------+-------+-------+------+---------+--------+---------+-------+----------+ 17 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT CONCAT(s.fname, ' ', s.lname) AS student, s.*, e.* -> FROM student AS s INNER JOIN enroll e ON (s.stuId = e.stuId) -> WHERE s.major IN ('CSCI', 'CINF') -> GROUP BY student; +-----------------+--------+---------+---------+-------+-------+------+---------+--------+---------+-------+----------+ | student | stuId | fname | lname | major | minor | ach | advisor | stuId | classId | grade | n_alerts | +-----------------+--------+---------+---------+-------+-------+------+---------+--------+---------+-------+----------+ | David Hawk | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100002 | 10000 | B- | 3 | | Lillian Johnson | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100006 | 10004 | C+ | NULL | | Linda Johnson | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10003 | NULL | NULL | | Mary Hawk | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100001 | 10000 | NULL | NULL | | Tony Hawk | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10000 | A | 0 | +-----------------+--------+---------+---------+-------+-------+------+---------+--------+---------+-------+----------+ 5 rows in set (0.001 sec) MariaDB [toyu]> GROUP BY student;SELECT s.major, CONCAT(s.fname, ' ', s.lname) AS student, s.*, e.* 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 student' at line 1 -> FROM student AS s INNER JOIN enroll e ON (s.stuId = e.stuId) -> WHERE s.major IN ('CSCI', 'CINF') -> GROUP BY s.major, student; +-------+-----------------+--------+---------+---------+-------+-------+------+---------+--------+---------+-------+----------+ | major | student | stuId | fname | lname | major | minor | ach | advisor | stuId | classId | grade | n_alerts | +-------+-----------------+--------+---------+---------+-------+-------+------+---------+--------+---------+-------+----------+ | CINF | Lillian Johnson | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100006 | 10004 | C+ | NULL | | CINF | Linda Johnson | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10003 | NULL | NULL | | CSCI | David Hawk | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100002 | 10000 | B- | 3 | | CSCI | Mary Hawk | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100001 | 10000 | NULL | NULL | | CSCI | Tony Hawk | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10000 | A | 0 | +-------+-----------------+--------+---------+---------+-------+-------+------+---------+--------+---------+-------+----------+ 5 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; +-----------------+------------------+ | student | Enrolled classes | +-----------------+------------------+ | Ben Zico | 2 | | Bill Ching | 1 | | David Hawk | 3 | | Larry Johnson | 2 | | Lillian Johnson | 2 | | Linda Johnson | 4 | | Mary Hawk | 2 | | Tony Hawk | 6 | +-----------------+------------------+ 8 rows in set (0.001 sec) MariaDB [toyu]> SELECT CONCAT(s.fname, ' ', s.lname) AS student, s.*, e.* -> FROM student AS s INNER JOIN enroll e ON (s.stuId = e.stuId) -> WHERE s.major IN ('CSCI', 'CINF') -> GROUP BY student -> HAVING `Enrolled classes` > 2 -> ORDER BY `Enrolled classes` DESC; ERROR 1054 (42S22): Unknown column 'Enrolled classes' in 'order clause' 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` > 2 -> ORDER BY `Enrolled classes` DESC; +---------------+------------------+ | student | Enrolled classes | +---------------+------------------+ | Tony Hawk | 6 | | Linda Johnson | 4 | | David Hawk | 3 | +---------------+------------------+ 3 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) -> WHERE s.ach > 20 -> GROUP BY student -> HAVING `Enrolled classes` > 2 -> ORDER BY `Enrolled classes` DESC; +------------+------------------+ | student | Enrolled classes | +------------+------------------+ | Tony Hawk | 6 | | David Hawk | 3 | +------------+------------------+ 2 rows in set (0.002 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) -> WHERE s.ach > 20 -> AND `Enrolled classes` > 2 -> GROUP BY student -> ORDER BY `Enrolled classes` DESC; ERROR 1054 (42S22): Unknown column 'Enrolled classes' in 'where clause' 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) -> WHERE s.ach > 20 -> AND COUNT(e.classId) > 2 -> GROUP BY student -> ORDER BY `Enrolled classes` DESC; ERROR 1111 (HY000): Invalid use of group function 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 s.ach > 20 -> AND COUNT(e.classId) > 2 -> ORDER BY `Enrolled classes` DESC; ERROR 1054 (42S22): Unknown column 's.ach' in 'having clause' MariaDB [toyu]> SELECT CONCAT(s.fname, ' ', s.lname) AS student, -- group by columns -> COUNT(e.classId) AS `Enrolled classes` -- group function -> , 'bad projector' -> FROM student AS s INNER JOIN enroll e ON (s.stuId = e.stuId) -> GROUP BY student -> HAVING s.ach > 20 -- ERROR 1054 (42S22): Unknown column 's.ach' in 'having clause' -> AND COUNT(e.classId) > 2 -> ORDER BY `Enrolled classes` DESC; 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`, -> 'hope' -> FROM student AS s INNER JOIN enroll e ON (s.stuId = e.stuId) -> WHERE s.ach > 20 -> GROUP BY student -> HAVING `Enrolled classes` > 2 -> ORDER BY `Enrolled classes` DESC; +------------+------------------+------+ | student | Enrolled classes | hope | +------------+------------------+------+ | Tony Hawk | 6 | hope | | David Hawk | 3 | hope | +------------+------------------+------+ 2 rows in set (0.001 sec) MariaDB [toyu]> SELECT DISTINCT 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.003 sec) MariaDB [toyu]> SELECT DISTINCT s.major, COUNT(f.facId) as numMajor -> FROM student AS s -> GROUP BY s.deptCode; ERROR 1054 (42S22): Unknown column 'f.facId' in 'field list' MariaDB [toyu]> SELECT DISTINCT s.major, COUNT(fs.stuId) as numMajor -> FROM student AS s -> GROUP BY s.deptCode; ERROR 1054 (42S22): Unknown column 'fs.stuId' in 'field list' MariaDB [toyu]> SELECT DISTINCT s.major, COUNT(f.stuId) as numMajor -> FROM student AS s -> GROUP BY s.deptCode; ERROR 1054 (42S22): Unknown column 'f.stuId' in 'field list' MariaDB [toyu]> SELECT DISTINCT s.major, COUNT(s.stuId) as numMajor -> FROM student AS s -> GROUP BY s.deptCode; ERROR 1054 (42S22): Unknown column 's.deptCode' in 'group statement' MariaDB [toyu]> SELECT DISTINCT s.major, COUNT(s.stuId) as numMajor -> FROM student AS s -> GROUP BY s.major; +-------+----------+ | major | numMajor | +-------+----------+ | NULL | 2 | | ARTS | 2 | | CINF | 2 | | CSCI | 3 | | ITEC | 2 | +-------+----------+ 5 rows in set (0.001 sec) MariaDB [toyu]> SELECT DISTINCT s.minor, COUNT(s.stuId) as numMinor -> FROM student AS s -> GROUP BY s.minor; +-------+----------+ | minor | numMinor | +-------+----------+ | NULL | 4 | | CINF | 3 | | CSCI | 1 | | ENGL | 1 | | ITEC | 2 | +-------+----------+ 5 rows in set (0.002 sec) MariaDB [toyu]> SELECT d.deptCode, d.deptName -> FROM department AS d; +----------+------------------------------+ | deptCode | deptName | +----------+------------------------------+ | ACCT | Accounting | | ARTS | Arts | | CINF | Computer Information Systems | | CSCI | Computer Science | | ENGL | English | | ITEC | Information Technology | | MATH | Mathematics | +----------+------------------------------+ 7 rows in set (0.001 sec) MariaDB [toyu]> WITH t1 AS -> (SELECT DISTINCT f.deptCode, COUNT(f.facId) as numFaculty -> FROM faculty AS f -> GROUP BY f.deptCode), -> t2 AS -> (SELECT DISTINCT s.major AS deptCode, COUNT(s.stuId) as numMajor -> FROM student AS s -> GROUP BY s.major), -> t3 AS -> (SELECT DISTINCT s.minor AS deptCdoe, COUNT(s.stuId) as numMinor -> FROM student AS s -> GROUP BY s.minor) -> 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); ERROR 1054 (42S22): Unknown column 'deptCode' in 'from clause' MariaDB [toyu]> MariaDB [toyu]> WITH t1 AS -> (SELECT DISTINCT f.deptCode, COUNT(f.facId) as numFaculty -> FROM faculty AS f -> GROUP BY f.deptCode), -> t2 AS -> (SELECT DISTINCT s.major AS deptCode, COUNT(s.stuId) as numMajor -> FROM student AS s -> GROUP BY s.major), -> t3 AS -> (SELECT DISTINCT s.minor AS deptCode, COUNT(s.stuId) as numMinor -> FROM student AS s -> GROUP BY s.minor) -> 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 | +----------+------------------------------+------------+----------+----------+ | CINF | Computer Information Systems | 2 | 2 | 3 | | CSCI | Computer Science | 4 | 3 | 1 | | ITEC | Information Technology | 2 | 2 | 2 | +----------+------------------------------+------------+----------+----------+ 3 rows in set (0.003 sec) MariaDB [toyu]> WITH t1 AS -> (SELECT DISTINCT f.deptCode, COUNT(f.facId) as numFaculty -> FROM faculty AS f -> GROUP BY f.deptCode), -> t2 AS -> (SELECT DISTINCT s.major AS deptCode, COUNT(s.stuId) as numMajor -> FROM student AS s -> GROUP BY s.major), -> t3 AS -> (SELECT DISTINCT s.minor AS deptCode, COUNT(s.stuId) as numMinor -> FROM student AS s -> GROUP BY s.minor) -> SELECT d.deptCode, d.deptName, t1.numFaculty, t2.numMajor, t3.numMinor -> FROM department AS d -> LEFT JOIN t1 USING (deptCode) -> LEFT JOIN t2 USING (deptCode) -> LEFT JOIN t3 USING (deptCode); +----------+------------------------------+------------+----------+----------+ | deptCode | deptName | numFaculty | numMajor | numMinor | +----------+------------------------------+------------+----------+----------+ | ACCT | Accounting | 1 | NULL | NULL | | ARTS | Arts | 1 | 2 | NULL | | CINF | Computer Information Systems | 2 | 2 | 3 | | CSCI | Computer Science | 4 | 3 | 1 | | ENGL | English | 1 | NULL | 1 | | ITEC | Information Technology | 2 | 2 | 2 | | MATH | Mathematics | NULL | NULL | NULL | +----------+------------------------------+------------+----------+----------+ 7 rows in set (0.002 sec) MariaDB [toyu]> MariaDB [toyu]> WITH t1 AS -> (SELECT DISTINCT f.deptCode, COUNT(f.facId) as numFaculty -> FROM faculty AS f -> GROUP BY f.deptCode), -> t2 AS -> (SELECT DISTINCT s.major AS deptCode, COUNT(s.stuId) as numMajor -> FROM student AS s -> GROUP BY s.major), -> t3 AS -> (SELECT DISTINCT s.minor AS deptCode, COUNT(s.stuId) as numMinor -> FROM student AS s -> GROUP BY s.minor) -> SELECT d.deptCode, d.deptName, -> IFNULL(t1.numFaculty, 0) AS numFaculty, -> IFNULL(t2.numMajor, 0) AS numMajor, -> IFNULL(t3.numMinor, 0) AS numMinor, -> FROM department AS d -> LEFT JOIN t1 USING (deptCode) -> LEFT JOIN t2 USING (deptCode) -> LEFT JOIN t3 USING (deptCode); 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 department AS d LEFT JOIN t1 USING (deptCode) LEFT JOIN t2 USING (dep...' at line 17 MariaDB [toyu]> MariaDB [toyu]> WITH t1 AS -> (SELECT DISTINCT f.deptCode, COUNT(f.facId) as numFaculty -> FROM faculty AS f -> GROUP BY f.deptCode), -> t2 AS -> (SELECT DISTINCT s.major AS deptCode, COUNT(s.stuId) as numMajor -> FROM student AS s -> GROUP BY s.major), -> t3 AS -> (SELECT DISTINCT s.minor AS deptCode, COUNT(s.stuId) as numMinor -> FROM student AS s -> GROUP BY s.minor) -> SELECT d.deptCode, d.deptName, -> IFNULL(t1.numFaculty, 0) AS numFaculty, -> IFNULL(t2.numMajor, 0) AS numMajor, -> IFNULL(t3.numMinor, 0) AS numMinor -> FROM department AS d -> LEFT JOIN t1 USING (deptCode) -> LEFT JOIN t2 USING (deptCode) -> LEFT 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.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]> -- 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]> 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.001 sec) MariaDB [toyu]> EXECUTE stmt USING 'CSCI', 15; +--------+-------+-------+-------+-------+------+---------+ | 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 | +--------+-------+-------+-------+-------+------+---------+ 3 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.000 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.000 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.000 sec) MariaDB [toyu]> MariaDB [toyu]> DEALLOCATE PREPARE stmt; Query OK, 0 rows affected (0.001 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.005 sec) 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.008 sec) 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.001 sec) MariaDB [toyu]> 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.013 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]> notee