MariaDB [(none)]> use toyu
Database changed
MariaDB [toyu]> 
MariaDB [toyu]> SELECT DISTINCT s.major, s.stuId
    -> FROM student AS s
    -> WHERE s.major IN ('CSCI', 'CINF', 'ITEC');
+-------+--------+
| major | stuId  |
+-------+--------+
| CINF  | 100005 |
| CINF  | 100006 |
| CSCI  | 100000 |
| CSCI  | 100001 |
| CSCI  | 100002 |
| ITEC  | 100003 |
| ITEC  | 100004 |
+-------+--------+
7 rows in set (0.002 sec)

MariaDB [toyu]> 
MariaDB [toyu]> SELECT DISTINCT 'ready to go in 4 minutes.', s.major, COUNT(s.stuId) AS numStudent, 
    ->     AVG(s.ach) AS `average ach` -- group functions, group by columns, constants
    -> FROM student AS s
    -> WHERE s.major IN ('CSCI', 'CINF', 'ITEC')
    -> GROUP BY s.major;
+---------------------------+-------+------------+-------------+
| ready to go in 4 minutes. | major | numStudent | average ach |
+---------------------------+-------+------------+-------------+
| ready to go in 4 minutes. | CINF  |          2 |     15.5000 |
| ready to go in 4 minutes. | CSCI  |          3 |     47.0000 |
| ready to go in 4 minutes. | ITEC  |          2 |     43.0000 |
+---------------------------+-------+------------+-------------+
3 rows in set (0.004 sec)

MariaDB [toyu]> SELECT DISTINCT s.major, s.stuId,
    ->     CONCAT(s.flname, ' ', s.lname) AS student,
    -> FROM student AS s
    -> WHERE s.major IN ('CSCI', 'CINF', 'ITEC');
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 s.major IN ('CSCI', 'CINF', 'ITEC')' at line 3
MariaDB [toyu]> SELECT DISTINCT s.major, s.stuId,
    ->     CONCAT(s.flname, ' ', s.lname) AS student
    -> FROM student AS s
    -> WHERE s.major IN ('CSCI', 'CINF', 'ITEC');
ERROR 1054 (42S22): Unknown column 's.flname' in 'field list'
MariaDB [toyu]> SELECT DISTINCT s.major, s.stuId,
    ->     CONCAT(s.fname, ' ', s.lname) AS student
    -> FROM student AS s
    -> WHERE s.major IN ('CSCI', 'CINF', 'ITEC');
+-------+--------+-----------------+
| major | stuId  | student         |
+-------+--------+-----------------+
| CSCI  | 100000 | Tony Hawk       |
| CSCI  | 100001 | Mary Hawk       |
| CSCI  | 100002 | David Hawk      |
| ITEC  | 100003 | Catherine Lim   |
| ITEC  | 100004 | Larry Johnson   |
| CINF  | 100005 | Linda Johnson   |
| CINF  | 100006 | Lillian Johnson |
+-------+--------+-----------------+
7 rows in set (0.001 sec)

MariaDB [toyu]> SELECT DISTINCT 'ready to go in 4 minutes.', s.major, COUNT(s.stuId) AS numStudent, 
    ->     AVG(s.ach) AS `average ach`,
    ->     GROUP_CONCAT(CONCAT(s.fname, ' ', s.lname)) AS students -- group functions, group by columns, constants
    -> FROM student AS s
    -> WHERE s.major IN ('CSCI', 'CINF', 'ITEC')
    -> GROUP BY s.major;
+---------------------------+-------+------------+-------------+--------------------------------+
| ready to go in 4 minutes. | major | numStudent | average ach | students                       |
+---------------------------+-------+------------+-------------+--------------------------------+
| ready to go in 4 minutes. | CINF  |          2 |     15.5000 | Linda Johnson,Lillian Johnson  |
| ready to go in 4 minutes. | CSCI  |          3 |     47.0000 | Tony Hawk,Mary Hawk,David Hawk |
| ready to go in 4 minutes. | ITEC  |          2 |     43.0000 | Catherine Lim,Larry Johnson    |
+---------------------------+-------+------------+-------------+--------------------------------+
3 rows in set (0.002 sec)

MariaDB [toyu]> SELECT DISTINCT 'ready to go in 4 minutes.', s.major, COUNT(s.stuId) AS numStudent, 
    ->     AVG(s.ach) AS `average ach`,
    ->     GROUP_CONCAT(CONCAT(s.fname, ' ', s.lname) ORDER BY s.lname ASC, s.fname ASC SEPARATOR "; ") AS students -- group functions, group by columns, constants
    -> FROM student AS s
    -> WHERE s.major IN ('CSCI', 'CINF', 'ITEC')
    -> GROUP BY s.major;
+---------------------------+-------+------------+-------------+----------------------------------+
| ready to go in 4 minutes. | major | numStudent | average ach | students                         |
+---------------------------+-------+------------+-------------+----------------------------------+
| ready to go in 4 minutes. | CINF  |          2 |     15.5000 | Lillian Johnson; Linda Johnson   |
| ready to go in 4 minutes. | CSCI  |          3 |     47.0000 | David Hawk; Mary Hawk; Tony Hawk |
| ready to go in 4 minutes. | ITEC  |          2 |     43.0000 | Larry Johnson; Catherine Lim     |
+---------------------------+-------+------------+-------------+----------------------------------+
3 rows in set (0.002 sec)

MariaDB [toyu]> 
MariaDB [toyu]> SELECT DISTINCT 'ready to go in 4 minutes.', s.major, COUNT(s.stuId) AS numStudent, 
    ->     AVG(s.ach) AS `average ach`,
    ->     GROUP_CONCAT(CONCAT(s.fname, ' ', s.lname) ORDER BY s.lname ASC, s.fname ASC SEPARATOR "; ") AS students -- group functions, group by columns, constants
    -> FROM student AS s
    -> WHERE s.major IN ('CSCI', 'CINF', 'ITEC')
    -> GROUP BY s.major
    -> HAVING `average ach` >= 40;
+---------------------------+-------+------------+-------------+----------------------------------+
| ready to go in 4 minutes. | major | numStudent | average ach | students                         |
+---------------------------+-------+------------+-------------+----------------------------------+
| ready to go in 4 minutes. | CSCI  |          3 |     47.0000 | David Hawk; Mary Hawk; Tony Hawk |
| ready to go in 4 minutes. | ITEC  |          2 |     43.0000 | Larry Johnson; Catherine Lim     |
+---------------------------+-------+------------+-------------+----------------------------------+
2 rows in set (0.002 sec)

MariaDB [toyu]> SELECT DISTINCT 'ready to go in 4 minutes.', s.major, COUNT(s.stuId) AS numStudent, 
    ->     AVG(s.ach) AS `average ach`,
    ->     GROUP_CONCAT(CONCAT(s.fname, ' ', s.lname) ORDER BY s.lname ASC, s.fname ASC SEPARATOR "; ") AS students -- group functions, group by columns, constants
    -> FROM student AS s
    -> WHERE s.major IN ('CSCI', 'CINF', 'ITEC')
    -> AND `average ach` >= 40
    -> GROUP BY s.major;
ERROR 1054 (42S22): Unknown column 'average ach' in 'where clause'
MariaDB [toyu]> SELECT DISTINCT 'ready to go in 4 minutes.', s.major, COUNT(s.stuId) AS numStudent, 
    ->     AVG(s.ach) AS `average ach`,
    ->     GROUP_CONCAT(CONCAT(s.fname, ' ', s.lname) ORDER BY s.lname ASC, s.fname ASC SEPARATOR "; ") AS students -- group functions, group by columns, constants
    -> FROM student AS s
    -> WHERE s.major IN ('CSCI', 'CINF', 'ITEC')
    -> AND AVG(s.ach) >= 40 -- no group function here.
    -> GROUP BY s.major;
ERROR 1111 (HY000): Invalid use of group function
MariaDB [toyu]> SELECT DISTINCT 'ready to go in 4 minutes.', s.major, COUNT(s.stuId) AS numStudent, 
    ->     AVG(s.ach) AS `average ach`,
    ->     GROUP_CONCAT(CONCAT(s.fname, ' ', s.lname) ORDER BY s.lname ASC, s.fname ASC SEPARATOR "; ") AS students -- group functions, group by columns, constants
    -> FROM student AS s
    -> GROUP BY s.major
    -> HAVING s.minor IN ('CSCI', 'CINF', 'ITEC')
    -> AND `average ach` >= 40;
ERROR 1054 (42S22): Unknown column 's.minor' in 'having clause'
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]> 
MariaDB [toyu]> SELECT DISTINCT s.major, COUNT(s.stuId) AS numMajor 
    -> FROM department AS d INNER JOIN student AS s ON (d.deptCode = s.major)
    -> GROUP BY s.major;
+-------+----------+
| major | numMajor |
+-------+----------+
| ARTS  |        2 |
| CINF  |        2 |
| CSCI  |        3 |
| ITEC  |        2 |
+-------+----------+
4 rows in set (0.001 sec)

MariaDB [toyu]> SELECT DISTINCT s.major, COUNT(s.stuId) AS numMajor 
    -> FROM department AS d LEFT JOIN student AS s ON (d.deptCode = s.major)
    -> GROUP BY s.major;
+-------+----------+
| major | numMajor |
+-------+----------+
| NULL  |        0 |
| ARTS  |        2 |
| CINF  |        2 |
| CSCI  |        3 |
| ITEC  |        2 |
+-------+----------+
5 rows in set (0.002 sec)

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)

ERROR: No query specified

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.000 sec)

MariaDB [toyu]> SELECT DISTINCT d.deptCode, COUNT(f.facId) AS numFaculty 
    -> FROM department AS d LEFT JOIN faculty AS f ON (d.deptCode = f.deptCode)
    -> GROUP BY d.deptCode;
+----------+------------+
| deptCode | numFaculty |
+----------+------------+
| ACCT     |          1 |
| ARTS     |          1 |
| CINF     |          2 |
| CSCI     |          4 |
| ENGL     |          1 |
| ITEC     |          2 |
| MATH     |          0 |
+----------+------------+
7 rows in set (0.001 sec)

MariaDB [toyu]> WITH t1 AS
    -> (SELECT DISTINCT d.deptCode, COUNT(f.facId) AS numFaculty 
    -> FROM department AS d LEFT JOIN faculty AS f ON (d.deptCode = f.deptCode)
    -> GROUP BY d.deptCode),
    -> t2 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),
    -> 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);
ERROR 1054 (42S22): Unknown column 't2.numMajor' in 'field list'
MariaDB [toyu]> WITH t1 AS
    -> (SELECT DISTINCT d.deptCode, COUNT(f.facId) AS numFaculty 
    -> FROM department AS d LEFT JOIN faculty AS f ON (d.deptCode = f.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.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 | 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.002 sec)

MariaDB [toyu]> notee