MariaDB [(none)]> use toyu
Database changed
MariaDB [toyu]> 
MariaDB [toyu]> SELECT s.*
    -> FROM student AS s
    -> WHERE ach <= 100;
+--------+-----------+----------+-------+-------+------+---------+
| 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 |
| 100111 | Cathy     | Johanson | NULL  | NULL  |    0 |    1018 |
+--------+-----------+----------+-------+-------+------+---------+
10 rows in set (0.002 sec)

MariaDB [toyu]> 
MariaDB [toyu]> SELECT s.stuId
    -> FROM student AS s 
    -> WHERE ach <= 100;
+--------+
| stuId  |
+--------+
| 100000 |
| 100001 |
| 100002 |
| 100003 |
| 100004 |
| 100005 |
| 100006 |
| 100007 |
| 100008 |
| 100111 |
+--------+
10 rows in set (0.001 sec)

MariaDB [toyu]> SELECT s.major
    -> FROM student AS s 
    -> WHERE ach <= 100;
+-------+
| major |
+-------+
| CSCI  |
| CSCI  |
| CSCI  |
| ITEC  |
| ITEC  |
| CINF  |
| CINF  |
| NULL  |
| ARTS  |
| NULL  |
+-------+
10 rows in set (0.000 sec)

MariaDB [toyu]> SELECT s.*
    -> FROM student AS s 
    -> WHERE ach <= 100;
+--------+-----------+----------+-------+-------+------+---------+
| 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 |
| 100111 | Cathy     | Johanson | NULL  | NULL  |    0 |    1018 |
+--------+-----------+----------+-------+-------+------+---------+
10 rows in set (0.000 sec)

MariaDB [toyu]> SELECT s.*
    -> FROM student AS s 
    -> WHERE ach <= 100
    -> GROUP BY s.stuId;
+--------+-----------+----------+-------+-------+------+---------+
| 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 |
| 100111 | Cathy     | Johanson | NULL  | NULL  |    0 |    1018 |
+--------+-----------+----------+-------+-------+------+---------+
10 rows in set (0.001 sec)

MariaDB [toyu]> SELECT s.*
    -> FROM student AS s 
    -> WHERE ach <= 100
    -> GROUP BY s.major;
+--------+-----------+---------+-------+-------+------+---------+
| stuId  | fname     | lname   | major | minor | ach  | advisor |
+--------+-----------+---------+-------+-------+------+---------+
| 100007 | Ben       | Zico    | NULL  | NULL  |   16 |    NULL |
| 100008 | Bill      | Ching   | ARTS  | NULL  |   90 |    NULL |
| 100005 | Linda     | Johnson | CINF  | ENGL  |   13 |    1015 |
| 100000 | Tony      | Hawk    | CSCI  | CINF  |   40 |    1011 |
| 100003 | Catherine | Lim     | ITEC  | CINF  |   20 |    NULL |
+--------+-----------+---------+-------+-------+------+---------+
5 rows in set (0.001 sec)

MariaDB [toyu]> SELECT s.major, s.stuId
    -> FROM student AS s 
    -> WHERE ach <= 100
    -> GROUP BY s.major;
+-------+--------+
| major | stuId  |
+-------+--------+
| NULL  | 100007 |
| ARTS  | 100008 |
| CINF  | 100005 |
| CSCI  | 100000 |
| ITEC  | 100003 |
+-------+--------+
5 rows in set (0.000 sec)

MariaDB [toyu]> SELECT s.major, s.stuId
    -> FROM student AS s 
    -> WHERE ach <= 100;
+-------+--------+
| major | stuId  |
+-------+--------+
| CSCI  | 100000 |
| CSCI  | 100001 |
| CSCI  | 100002 |
| ITEC  | 100003 |
| ITEC  | 100004 |
| CINF  | 100005 |
| CINF  | 100006 |
| NULL  | 100007 |
| ARTS  | 100008 |
| NULL  | 100111 |
+-------+--------+
10 rows in set (0.000 sec)

MariaDB [toyu]> SELECT s.major, 
    -> COUNT(s.stuId) AS `Number of majors`
    -> FROM student AS s 
    -> WHERE ach <= 100
    -> GROUP BY s.major;
+-------+------------------+
| major | Number of majors |
+-------+------------------+
| NULL  |                2 |
| ARTS  |                1 |
| CINF  |                2 |
| CSCI  |                3 |
| ITEC  |                2 |
+-------+------------------+
5 rows in set (0.001 sec)

MariaDB [toyu]> SELECT COUNT(s.stuId) AS `Number of students`
    -> FROM student AS s 
    -> WHERE ach <= 100;
+--------------------+
| Number of students |
+--------------------+
|                 10 |
+--------------------+
1 row in set (0.001 sec)

MariaDB [toyu]> SELECT s.major, 
    -> COUNT(s.stuId) AS `Number of majors`
    -> FROM student AS s 
    -> WHERE ach <= 100
    -> GROUP BY s.major;
+-------+------------------+
| major | Number of majors |
+-------+------------------+
| NULL  |                2 |
| ARTS  |                1 |
| CINF  |                2 |
| CSCI  |                3 |
| ITEC  |                2 |
+-------+------------------+
5 rows in set (0.000 sec)

MariaDB [toyu]> SELECT s.major, 
    -> COUNT(s.stuId) AS `Number of majors`
    -> FROM student AS s 
    -> WHERE ach <= 100
    -> GROUP BY s.major
    -> HAVING `Number of majors` >= 2;
+-------+------------------+
| major | Number of majors |
+-------+------------------+
| NULL  |                2 |
| CINF  |                2 |
| CSCI  |                3 |
| ITEC  |                2 |
+-------+------------------+
4 rows in set (0.001 sec)

MariaDB [toyu]> SELECT s.major, 
    -> s.stuId  -- not a group by column -> bad
    -> FROM student AS s 
    -> WHERE ach <= 100
    -> GROUP BY s.major;
+-------+--------+
| major | stuId  |
+-------+--------+
| NULL  | 100007 |
| ARTS  | 100008 |
| CINF  | 100005 |
| CSCI  | 100000 |
| ITEC  | 100003 |
+-------+--------+
5 rows in set (0.001 sec)

MariaDB [toyu]> SELECT s.major, -- group by columns
    -> ': ',
    -> COUNT(s.stuId) AS `Number of majors` -- group function.
    -> FROM student AS s 
    -> WHERE ach <= 100
    -> GROUP BY s.major
    -> HAVING `Number of majors` >= 2;
+-------+----+------------------+
| major | :  | Number of majors |
+-------+----+------------------+
| NULL  | :  |                2 |
| CINF  | :  |                2 |
| CSCI  | :  |                3 |
| ITEC  | :  |                2 |
+-------+----+------------------+
4 rows in set (0.001 sec)

MariaDB [toyu]> SELECT s.major, -- group by columns
    -> d.deptName AS department, -- group by columns
    -> ': ',
    -> COUNT(s.stuId) AS `Number of majors` -- group function.
    -> FROM student AS s INNER JOIN department AS d ON (s.major = d.deptCode)
    -> WHERE ach <= 100
    -> GROUP BY s.major, d.deptName
    -> HAVING `Number of majors` >= 2;
+-------+------------------------------+----+------------------+
| major | department                   | :  | Number of majors |
+-------+------------------------------+----+------------------+
| CINF  | Computer Information Systems | :  |                2 |
| CSCI  | Computer Science             | :  |                3 |
| ITEC  | Information Technology       | :  |                2 |
+-------+------------------------------+----+------------------+
3 rows in set (0.001 sec)

MariaDB [toyu]> SELECT d.schoolCode AS `major college`,
    -> s.major, -- group by columns
    -> d.deptName AS department, -- group by columns
    -> ': ',
    -> COUNT(s.stuId) AS `Number of majors` -- group function.
    -> FROM student AS s INNER JOIN department AS d ON (s.major = d.deptCode)
    -> WHERE ach <= 100
    -> GROUP BY s.major, d.deptName
    -> HAVING `Number of majors` >= 2;
+---------------+-------+------------------------------+----+------------------+
| major college | major | department                   | :  | Number of majors |
+---------------+-------+------------------------------+----+------------------+
| CSE           | CINF  | Computer Information Systems | :  |                2 |
| CSE           | CSCI  | Computer Science             | :  |                3 |
| CSE           | ITEC  | Information Technology       | :  |                2 |
+---------------+-------+------------------------------+----+------------------+
3 rows in set (0.002 sec)

MariaDB [toyu]> SELECT s.major, -- group by columns
    -> d.deptName AS department, -- group by columns
    -> ': ',
    -> COUNT(s.stuId) AS `Number of majors`, -- group function.
    -> CONCAT(s.fname, ' ', s.lname) AS students
    -> FROM student AS s INNER JOIN department AS d ON (s.major = d.deptCode)
    -> WHERE ach <= 100
    -> GROUP BY s.major, d.deptName
    -> HAVING `Number of majors` >= 2;
+-------+------------------------------+----+------------------+---------------+
| major | department                   | :  | Number of majors | students      |
+-------+------------------------------+----+------------------+---------------+
| CINF  | Computer Information Systems | :  |                2 | Linda Johnson |
| CSCI  | Computer Science             | :  |                3 | Tony Hawk     |
| ITEC  | Information Technology       | :  |                2 | Catherine Lim |
+-------+------------------------------+----+------------------+---------------+
3 rows in set (0.001 sec)

MariaDB [toyu]> SELECT s.major, -- group by columns
    -> d.deptName AS department, -- group by columns
    -> ': ',
    -> COUNT(s.stuId) AS `Number of majors`, -- group function.
    -> GROUP_CONCAT(CONCAT(s.fname, ' ', s.lname)) AS students
    -> FROM student AS s INNER JOIN department AS d ON (s.major = d.deptCode)
    -> WHERE ach <= 100
    -> GROUP BY s.major, d.deptName
    -> HAVING `Number of majors` >= 2;
+-------+------------------------------+----+------------------+--------------------------------+
| major | department                   | :  | Number of majors | students                       |
+-------+------------------------------+----+------------------+--------------------------------+
| CINF  | Computer Information Systems | :  |                2 | Linda Johnson,Lillian Johnson  |
| CSCI  | Computer Science             | :  |                3 | Tony Hawk,Mary Hawk,David Hawk |
| ITEC  | Information Technology       | :  |                2 | Catherine Lim,Larry Johnson    |
+-------+------------------------------+----+------------------+--------------------------------+
3 rows in set (0.002 sec)

MariaDB [toyu]> SELECT s.major, -- group by columns
    -> d.deptName AS department, -- group by columns
    -> ': ',
    -> COUNT(s.stuId) AS `Number of majors`, -- group function.
    -> 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 ach <= 100
    -> GROUP BY s.major, d.deptName
    -> HAVING `Number of majors` >= 2;
+-------+------------------------------+----+------------------+--------------------------------+
| major | department                   | :  | Number of majors | students                       |
+-------+------------------------------+----+------------------+--------------------------------+
| CINF  | Computer Information Systems | :  |                2 | Lillian Johnson,Linda Johnson  |
| 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, -- group by columns
    -> d.deptName AS department, -- group by columns
    -> ': ',
    -> COUNT(s.stuId) AS `Number of majors`, -- group function.
    -> 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 ach <= 100
    -> GROUP BY s.major, d.deptName
    -> HAVING `Number of majors` >= 2;
+-------+------------------------------+----+------------------+----------------------------------+
| major | department                   | :  | Number of majors | students                         |
+-------+------------------------------+----+------------------+----------------------------------+
| CINF  | Computer Information Systems | :  |                2 | Lillian Johnson; Linda Johnson   |
| 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, -- group by columns
    -> d.deptName AS department, -- group by columns
    -> COUNT(s.stuId) AS `Number of majors`, -- group function.
    -> 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 ach <= 100
    -> GROUP BY 1, 2
    -> HAVING `Number of majors` >= 2;
+-------+------------------------------+------------------+----------------------------------+
| major | department                   | Number of majors | students                         |
+-------+------------------------------+------------------+----------------------------------+
| CINF  | Computer Information Systems |                2 | Lillian Johnson; Linda Johnson   |
| 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, -- group by columns
    -> d.deptName AS department, -- group by columns
    -> ': ',
    -> COUNT(s.stuId) AS `Number of majors`, -- group function.
    -> 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 ach <= 100
    -> GROUP BY s.major, 2  -- named arguments
    -> HAVING `Number of majors` >= 2;
+-------+------------------------------+----+------------------+----------------------------------+
| major | department                   | :  | Number of majors | students                         |
+-------+------------------------------+----+------------------+----------------------------------+
| CINF  | Computer Information Systems | :  |                2 | Lillian Johnson; Linda Johnson   |
| 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 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 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.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.003 sec)

MariaDB [toyu]>