MariaDB [toyu]> 
MariaDB [toyu]> SELECT s.stuId, d.deptCode, d.deptName AS department
    -> FROM Student AS s LEFT JOIN department AS d ON (s.major = d.deptCode);
+--------+----------+------------------------------+
| stuId  | deptCode | department                   |
+--------+----------+------------------------------+
| 100007 | NULL     | NULL                         |
| 100111 | NULL     | NULL                         |
| 100008 | ARTS     | Arts                         |
| 100009 | ARTS     | Arts                         |
| 100005 | CINF     | Computer Information Systems |
| 100006 | CINF     | Computer Information Systems |
| 100000 | CSCI     | Computer Science             |
| 100001 | CSCI     | Computer Science             |
| 100002 | CSCI     | Computer Science             |
| 100003 | ITEC     | Information Technology       |
| 100004 | ITEC     | Information Technology       |
+--------+----------+------------------------------+
11 rows in set (0.008 sec)

MariaDB [toyu]> 
MariaDB [toyu]> SELECT DISTINCT s.stuId,
    ->    CONCAT(s.fname, ' ', s.lname) AS student,
    ->    IFNULL(e.classId, '') AS classId,
    ->    IFNULL(CONCAT(co.rubric, ' ', co.number), '') AS `CSCI course`,
    ->    IFNULL(e.grade, '') AS grade
    -> FROM enroll AS e INNER JOIN class AS c USING (classId)
    ->    INNER JOIN course AS co ON (c.courseId = co.courseId AND co.rubric = 'CSCI' )
    ->    RIGHT JOIN student AS s USING (stuId);
+--------+-----------------+---------+-------------+-------+
| stuId  | student         | classId | CSCI course | grade |
+--------+-----------------+---------+-------------+-------+
| 100000 | Tony Hawk       | 10000   | CSCI 3333   | A     |
| 100000 | Tony Hawk       | 10001   | CSCI 4333   | A     |
| 100000 | Tony Hawk       | 10002   | CSCI 5333   | B+    |
| 100000 | Tony Hawk       | 11001   | CSCI 4333   | D     |
| 100001 | Mary Hawk       | 10000   | CSCI 3333   |       |
| 100001 | Mary Hawk       | 10001   | CSCI 4333   | A-    |
| 100002 | David Hawk      | 10000   | CSCI 3333   | B-    |
| 100002 | David Hawk      | 10002   | CSCI 5333   | B+    |
| 100003 | Catherine Lim   |         |             |       |
| 100004 | Larry Johnson   |         |             |       |
| 100005 | Linda Johnson   |         |             |       |
| 100006 | Lillian Johnson |         |             |       |
| 100007 | Ben Zico        |         |             |       |
| 100008 | Bill Ching      |         |             |       |
| 100009 | Linda King      |         |             |       |
| 100111 | Cathy Johanson  |         |             |       |
+--------+-----------------+---------+-------------+-------+
16 rows in set (0.004 sec)

MariaDB [toyu]> SELECT DISTINCT s.stuId,
    ->    CONCAT(s.fname, ' ', s.lname) AS student,
    ->    IFNULL(e.classId, '') AS classId,
    ->    IFNULL(CONCAT(co.rubric, ' ', co.number), '') AS `CSCI course`,
    ->    IFNULL(e.grade, '') AS grade
    -> FROM student AS s LEFT JOIN enroll AS e USING (stuId)
    ->    LEFT JOIN class AS c USING (classId)
    ->    LEFT JOIN course AS co USING (courseId)
    -> WHERE co.rubric = 'CSCI';
+--------+------------+---------+-------------+-------+
| stuId  | student    | classId | CSCI course | grade |
+--------+------------+---------+-------------+-------+
| 100000 | Tony Hawk  | 10000   | CSCI 3333   | A     |
| 100001 | Mary Hawk  | 10000   | CSCI 3333   |       |
| 100002 | David Hawk | 10000   | CSCI 3333   | B-    |
| 100000 | Tony Hawk  | 10001   | CSCI 4333   | A     |
| 100001 | Mary Hawk  | 10001   | CSCI 4333   | A-    |
| 100000 | Tony Hawk  | 11001   | CSCI 4333   | D     |
| 100000 | Tony Hawk  | 10002   | CSCI 5333   | B+    |
| 100002 | David Hawk | 10002   | CSCI 5333   | B+    |
+--------+------------+---------+-------------+-------+
8 rows in set (0.002 sec)

MariaDB [toyu]> SELECT DISTINCT MAX(ach)
    -> FROM student;
+----------+
| MAX(ach) |
+----------+
|      125 |
+----------+
1 row in set (0.006 sec)

MariaDB [toyu]> -- student within 60 credits of the maximum number of ach any student may have.
MariaDB [toyu]> SELECT DISTINCT s.stuId,
    ->    CONCAT(s.fname, ' ', s.lname) AS student,
    ->    s.ach AS credits
    -> FROM student AS s
    -> WHERE s.ach + 60 >=
    ->    (SELECT DISTINCT MAX(ach) FROM student);
+--------+---------------+---------+
| stuId  | student       | credits |
+--------+---------------+---------+
| 100002 | David Hawk    |      66 |
| 100004 | Larry Johnson |      66 |
| 100008 | Bill Ching    |      90 |
| 100009 | Linda King    |     125 |
+--------+---------------+---------+
4 rows in set (0.003 sec)

MariaDB [toyu]> SELECT DISTINCT s.stuId,
    ->    CONCAT(s.fname, ' ', s.lname) AS student,
    ->    s.ach AS credits
    -> FROM student AS s INNER JOIN
    ->    (SELECT DISTINCT MAX(ach) AS max FROM student) AS m -- an alias is required.
    -> WHERE s.ach + 60 >= m.max;
+--------+---------------+---------+
| stuId  | student       | credits |
+--------+---------------+---------+
| 100002 | David Hawk    |      66 |
| 100004 | Larry Johnson |      66 |
| 100008 | Bill Ching    |      90 |
| 100009 | Linda King    |     125 |
+--------+---------------+---------+
4 rows in set (0.002 sec)

MariaDB [toyu]> WITH m AS
    -> (SELECT DISTINCT MAX(ach) AS max FROM student)
    -> SELECT DISTINCT s.stuId,
    ->    CONCAT(s.fname, ' ', s.lname) AS student,
    ->    s.ach AS credits
    -> FROM student AS s INNER JOIN m
    -> WHERE s.ach + 60 >= m.max;
+--------+---------------+---------+
| stuId  | student       | credits |
+--------+---------------+---------+
| 100002 | David Hawk    |      66 |
| 100004 | Larry Johnson |      66 |
| 100008 | Bill Ching    |      90 |
| 100009 | Linda King    |     125 |
+--------+---------------+---------+
4 rows in set (0.001 sec)

MariaDB [toyu]> -- multiple common tables (not efficient; used as demonstration.)
MariaDB [toyu]> WITH t1 AS
    ->    (SELECT MAX(ach) AS max FROM student),
    -> t2 AS
    ->    (SELECT s.stuId,
    ->       s.ach AS `ach credits`,
    ->       t1.max - s.ach AS diff,
    ->       s.major
    ->     FROM student AS s, t1)
    -> SELECT t2.stuId, t2.`ach credits`,
    ->    t2.diff AS `diff from max credits of all`,
    ->    d.deptName AS department
    -> FROM t2 LEFT JOIN department d ON (t2.major = d.deptCode)
    -> ORDER BY t2.`ach credits` DESC;
+--------+-------------+------------------------------+------------------------------+
| stuId  | ach credits | diff from max credits of all | department                   |
+--------+-------------+------------------------------+------------------------------+
| 100009 |         125 |                            0 | Arts                         |
| 100008 |          90 |                           35 | Arts                         |
| 100002 |          66 |                           59 | Computer Science             |
| 100004 |          66 |                           59 | Information Technology       |
| 100000 |          40 |                           85 | Computer Science             |
| 100001 |          35 |                           90 | Computer Science             |
| 100003 |          20 |                          105 | Information Technology       |
| 100006 |          18 |                          107 | Computer Information Systems |
| 100007 |          16 |                          109 | NULL                         |
| 100005 |          13 |                          112 | Computer Information Systems |
| 100111 |           0 |                          125 | NULL                         |
+--------+-------------+------------------------------+------------------------------+
11 rows in set (0.001 sec)

MariaDB [toyu]> SELECT MAX(ach) AS max FROM student;
+------+
| max  |
+------+
|  125 |
+------+
1 row in set (0.001 sec)

MariaDB [toyu]> WITH t1 AS
    ->    (SELECT MAX(ach) AS max FROM student),
    -> SELECT s.stuId,
    ->       s.ach AS `ach credits`,
    ->       t1.max - s.ach AS diff,
    ->       s.major
    ->     FROM student AS s, t1;
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 'SELECT s.stuId,
      s.ach AS `ach credits`,
      t1.max - s.ach AS diff,
 ...' at line 3
MariaDB [toyu]> WITH t1 AS
    ->    (SELECT MAX(ach) AS max FROM student)
    -> SELECT s.stuId,
    ->       s.ach AS `ach credits`,
    ->       t1.max - s.ach AS diff,
    ->       s.major
    ->     FROM student AS s, t1;
+--------+-------------+------+-------+
| stuId  | ach credits | diff | major |
+--------+-------------+------+-------+
| 100000 |          40 |   85 | CSCI  |
| 100001 |          35 |   90 | CSCI  |
| 100002 |          66 |   59 | CSCI  |
| 100003 |          20 |  105 | ITEC  |
| 100004 |          66 |   59 | ITEC  |
| 100005 |          13 |  112 | CINF  |
| 100006 |          18 |  107 | CINF  |
| 100007 |          16 |  109 | NULL  |
| 100008 |          90 |   35 | ARTS  |
| 100009 |         125 |    0 | ARTS  |
| 100111 |           0 |  125 | NULL  |
+--------+-------------+------+-------+
11 rows in set (0.001 sec)

MariaDB [toyu]> WITH t1 AS
    ->    (SELECT MAX(ach) AS max FROM student),
    -> t2 AS
    ->    (SELECT s.stuId,
    ->       s.ach AS `ach credits`,
    ->       t1.max - s.ach AS diff,
    ->       s.major
    ->     FROM student AS s, t1)
    -> SELECT t2.stuId, t2.`ach credits`,
    ->    t2.diff AS `diff from max credits of all`,
    ->    d.deptName AS department
    -> FROM t2 LEFT JOIN department d ON (t2.major = d.deptCode)
    -> ORDER BY t2.`ach credits` DESC;
+--------+-------------+------------------------------+------------------------------+
| stuId  | ach credits | diff from max credits of all | department                   |
+--------+-------------+------------------------------+------------------------------+
| 100009 |         125 |                            0 | Arts                         |
| 100008 |          90 |                           35 | Arts                         |
| 100002 |          66 |                           59 | Computer Science             |
| 100004 |          66 |                           59 | Information Technology       |
| 100000 |          40 |                           85 | Computer Science             |
| 100001 |          35 |                           90 | Computer Science             |
| 100003 |          20 |                          105 | Information Technology       |
| 100006 |          18 |                          107 | Computer Information Systems |
| 100007 |          16 |                          109 | NULL                         |
| 100005 |          13 |                          112 | Computer Information Systems |
| 100111 |           0 |                          125 | NULL                         |
+--------+-------------+------------------------------+------------------------------+
11 rows in set (0.001 sec)

MariaDB [toyu]> WITH RECURSIVE cte (n) AS
    -> (
    ->   SELECT 1
    ->   UNION ALL
    ->   SELECT n + 1 FROM cte WHERE n < 5
    -> )
    -> SELECT * FROM cte;
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.000 sec)

MariaDB [toyu]> CREATE SCHEMA CTETinker;
Query OK, 1 row affected (0.008 sec)

MariaDB [toyu]> USE SCHEMA CTEtinker;
ERROR 1049 (42000): Unknown database 'schema'
MariaDB [toyu]> CREATE OR REPLACE TABLE Employee (
    ->     EmpId CHAR(7) NOT NULL,
    ->     SupervisorEmpId CHAR(7) NULL,
    ->     CONSTRAINT Emp_EmpId_pk PRIMARY KEY (EmpId),
    ->     CONSTRAINT Emp_SupervisorEmpId_fk FOREIGN KEY (SupervisorEmpId)
    ->         REFERENCES Employee(EmpId)
    -> );
Query OK, 0 rows affected, 1 warning (0.030 sec)

MariaDB [toyu]> 
MariaDB [toyu]> INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES
    ->    ('E3', null);
Query OK, 1 row affected (0.004 sec)

MariaDB [toyu]> INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES
    ->    ('E15', 'E3');
Query OK, 1 row affected (0.003 sec)

MariaDB [toyu]> INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES
    ->    ('E50', 'E15');
Query OK, 1 row affected (0.003 sec)

MariaDB [toyu]> INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES
    ->    ('E75', 'E50');
Query OK, 1 row affected (0.003 sec)

MariaDB [toyu]> INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES
    ->    ('E100', 'E75');
Query OK, 1 row affected (0.003 sec)

MariaDB [toyu]> INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES
    ->    ('E102', 'E75');
Query OK, 1 row affected (0.003 sec)

MariaDB [toyu]> INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES
    ->    ('E70', 'E50');
Query OK, 1 row affected (0.004 sec)

MariaDB [toyu]> INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES
    ->    ('E103', 'E70');
Query OK, 1 row affected (0.003 sec)

MariaDB [toyu]>   
MariaDB [toyu]> SELECT * FROM Employee;
+-------+-----------------+
| EmpId | SupervisorEmpId |
+-------+-----------------+
| E3    | NULL            |
| E50   | E15             |
| E15   | E3              |
| E70   | E50             |
| E75   | E50             |
| E103  | E70             |
| E100  | E75             |
| E102  | E75             |
+-------+-----------------+
8 rows in set (0.000 sec)

MariaDB [toyu]> WITH RECURSIVE Super(SEId) AS
    -> (  SELECT SupervisorEmpId AS SEId FROM Employee AS e WHERE e.EmpId = 'E100' -- initial condition/action
    ->    UNION ALL -- union all: add rows created by the recursive action to the result, table Super.
    ->    SELECT e.SupervisorEmpId AS SEId -- recursive action
    ->       FROM Employee AS e INNER JOIN Super
    ->       WHERE e.EmpId = Super.SEId
    ->       AND e.SupervisorEmpId IS NOT NULL
    ->       -- exit condition: when the recursive action returns an empty table.
    -> )
    -> SELECT *
    -> FROM Super;
+------+
| SEId |
+------+
| E75  |
| E50  |
| E15  |
| E3   |
+------+
4 rows in set (0.001 sec)

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

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

MariaDB [toyu]> SELECT s.stuId, s.major
    -> FROM student AS s
    -> WHERE s.ach >= 10
    -> GROPU BY s.major  -- form group according to distinct values in s.major
    -> ORDER BY s.major;
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 'GROPU BY s.major  
ORDER BY s.major' at line 4
MariaDB [toyu]> SELECT s.stuId, s.major
    -> FROM student AS s
    -> WHERE s.ach >= 10
    -> GROUP BY s.major  -- form group according to distinct values in s.major
    -> ORDER BY s.major;
+--------+-------+
| stuId  | major |
+--------+-------+
| 100007 | NULL  |
| 100008 | ARTS  |
| 100005 | CINF  |
| 100000 | CSCI  |
| 100003 | ITEC  |
+--------+-------+
5 rows in set (0.001 sec)

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

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

MariaDB [toyu]> SELECT s.major, COUNT(s.stuId) AS `# major`
    -> FROM student AS s
    -> WHERE s.ach >= 10
    -> GROUP BY s.major  -- form group according to distinct values in s.major
    -> ORDER BY s.major;
+-------+---------+
| major | # major |
+-------+---------+
| NULL  |       1 |
| ARTS  |       2 |
| CINF  |       2 |
| CSCI  |       3 |
| ITEC  |       2 |
+-------+---------+
5 rows in set (0.002 sec)

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

MariaDB [toyu]> 
MariaDB [toyu]> SELECT s.major, d.deptName,
    ->     COUNT(s.stuId) AS `# major`
    -> FROM student AS s LEFT JOIN department AS d ON (s.major = d.deptCode)
    -> WHERE s.ach >= 10
    -> GROUP BY s.major, d.deptName -- form group according to distinct values in s.major
    -> ORDER BY s.major;
+-------+------------------------------+---------+
| major | deptName                     | # major |
+-------+------------------------------+---------+
| NULL  | NULL                         |       1 |
| ARTS  | Arts                         |       2 |
| CINF  | Computer Information Systems |       2 |
| CSCI  | Computer Science             |       3 |
| ITEC  | Information Technology       |       2 |
+-------+------------------------------+---------+
5 rows in set (0.001 sec)

MariaDB [toyu]> SELECT s.major, d.deptName,
    ->     COUNT(s.stuId) AS `# major`
    -> FROM student AS s LEFT JOIN department AS d ON (s.major = d.deptCode)
    -> WHERE s.ach >= 10
    -> GROUP BY s.major, d.deptName -- form group according to distinct values in s.major
    -> HAVING `# major` > 1
    -> ORDER BY s.major;
+-------+------------------------------+---------+
| major | deptName                     | # major |
+-------+------------------------------+---------+
| ARTS  | Arts                         |       2 |
| CINF  | Computer Information Systems |       2 |
| CSCI  | Computer Science             |       3 |
| ITEC  | Information Technology       |       2 |
+-------+------------------------------+---------+
4 rows in set (0.001 sec)

MariaDB [toyu]> notee