MariaDB [toyu]> UPDATE enroll
    -> SET grade = 'A'
    -> WHERE stuId = 100000 
    -> AND classId = 10003;
Query OK, 1 row affected (0.004 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [toyu]> UPDATE enroll
    -> SET grade = 'A';
Query OK, 17 rows affected (0.011 sec)
Rows matched: 22  Changed: 17  Warnings: 0

MariaDB [toyu]> source createtoyy.sql
ERROR: Failed to open file 'createtoyy.sql', error: 2
MariaDB [toyu]> source createtoyu.sql
Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.001 sec)

Query OK, 9 rows affected (0.146 sec)

Query OK, 1 row affected (0.002 sec)

Database changed
Query OK, 0 rows affected, 1 warning (0.000 sec)

Query OK, 0 rows affected, 1 warning (0.000 sec)

Query OK, 0 rows affected, 1 warning (0.000 sec)

Query OK, 0 rows affected, 1 warning (0.001 sec)

Query OK, 0 rows affected, 1 warning (0.001 sec)

Query OK, 0 rows affected, 1 warning (0.001 sec)

Query OK, 0 rows affected, 1 warning (0.003 sec)

Query OK, 0 rows affected, 1 warning (0.000 sec)

Query OK, 0 rows affected, 1 warning (0.026 sec)

Query OK, 0 rows affected, 1 warning (0.032 sec)

Query OK, 0 rows affected, 1 warning (0.036 sec)

Query OK, 0 rows affected, 1 warning (0.033 sec)

Query OK, 0 rows affected, 1 warning (0.035 sec)

Query OK, 0 rows affected, 1 warning (0.033 sec)

Query OK, 0 rows affected, 1 warning (0.041 sec)

Query OK, 0 rows affected, 1 warning (0.034 sec)

Query OK, 15 rows affected (0.006 sec)
Records: 15  Duplicates: 0  Warnings: 0

Query OK, 4 rows affected (0.005 sec)
Records: 4  Duplicates: 0  Warnings: 0

Query OK, 7 rows affected (0.014 sec)
Records: 7  Duplicates: 0  Warnings: 0

Query OK, 11 rows affected (0.005 sec)
Records: 11  Duplicates: 0  Warnings: 0

Query OK, 11 rows affected (0.013 sec)
Records: 11  Duplicates: 0  Warnings: 0

Query OK, 14 rows affected (0.013 sec)
Records: 14  Duplicates: 0  Warnings: 0

Query OK, 11 rows affected (0.005 sec)
Records: 11  Duplicates: 0  Warnings: 0

Query OK, 22 rows affected (0.012 sec)
Records: 22  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

MariaDB [toyu]> SELECT DISTINCT e.stuID FROM enroll AS e;
+--------+
| stuID  |
+--------+
| 100000 |
| 100001 |
| 100002 |
| 100004 |
| 100005 |
| 100006 |
| 100007 |
| 100008 |
+--------+
8 rows in set (0.001 sec)

MariaDB [toyu]> SELECT DISTINCT s.stuId
    -> FROM student AS s
    -> WHERE s.stuId NOT IN (SELECT DISTINCT e.stuID FROM enroll AS e);
+--------+
| stuId  |
+--------+
| 100003 |
| 100009 |
| 100111 |
+--------+
3 rows in set (0.001 sec)

MariaDB [toyu]> SELECT DISTINCT *
    -> FROM student AS s
    -> WHERE s.stuId NOT IN (SELECT DISTINCT e.stuID FROM enroll AS e);
+--------+-----------+----------+-------+-------+------+---------+
| stuId  | fname     | lname    | major | minor | ach  | advisor |
+--------+-----------+----------+-------+-------+------+---------+
| 100003 | Catherine | Lim      | ITEC  | CINF  |   20 |    NULL |
| 100009 | Linda     | King     | ARTS  | CSCI  |  125 |    1018 |
| 100111 | Cathy     | Johanson | NULL  | NULL  |    0 |    1018 |
+--------+-----------+----------+-------+-------+------+---------+
3 rows in set (0.001 sec)

MariaDB [toyu]> explain SELECT DISTINCT *
    -> FROM student AS s
    -> WHERE s.stuId NOT IN (SELECT DISTINCT e.stuID FROM enroll AS e);
+------+--------------+-------+-------+-----------------+-----------------+---------+------+------+-------------+
| id   | select_type  | table | type  | possible_keys   | key             | key_len | ref  | rows | Extra       |
+------+--------------+-------+-------+-----------------+-----------------+---------+------+------+-------------+
|    1 | PRIMARY      | s     | ALL   | NULL            | NULL            | NULL    | NULL | 11   | Using where |
|    2 | MATERIALIZED | e     | index | Enroll_stuId_fk | Enroll_stuId_fk | 4       | NULL | 22   | Using index |
+------+--------------+-------+-------+-----------------+-----------------+---------+------+------+-------------+
2 rows in set (0.001 sec)

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

MariaDB [toyu]> SELECT DISTINCT COUNT(stuId)
    -> FROM student;
+--------------+
| COUNT(stuId) |
+--------------+
|           11 |
+--------------+
1 row in set (0.001 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.001 sec)

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

MariaDB [toyu]> -- subqueries as derived tables.
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.001 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 -- 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.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                         |
| 100004 |          66 |                           59 | Information Technology       |
| 100002 |          66 |                           59 | Computer Science             |
| 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.002 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;
+--------+-------------+------+-------+
| 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]> INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES
    ->    ('E3', null);
ERROR 1146 (42S02): Table 'toyu.employee' doesn't exist
MariaDB [toyu]> INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES
    ->    ('E15', 'E3');
ERROR 1146 (42S02): Table 'toyu.employee' doesn't exist
MariaDB [toyu]> INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES
    ->    ('E50', 'E15');
ERROR 1146 (42S02): Table 'toyu.employee' doesn't exist
MariaDB [toyu]> INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES
    ->    ('E75', 'E50');
ERROR 1146 (42S02): Table 'toyu.employee' doesn't exist
MariaDB [toyu]> INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES
    ->    ('E100', 'E75');
ERROR 1146 (42S02): Table 'toyu.employee' doesn't exist
MariaDB [toyu]> INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES
    ->    ('E102', 'E75');
ERROR 1146 (42S02): Table 'toyu.employee' doesn't exist
MariaDB [toyu]> INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES
    ->    ('E70', 'E50');
ERROR 1146 (42S02): Table 'toyu.employee' doesn't exist
MariaDB [toyu]> INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES
    ->    ('E103', 'E70');
ERROR 1146 (42S02): Table 'toyu.employee' doesn't exist
MariaDB [toyu]>   
MariaDB [toyu]> SELECT * FROM Employee;
ERROR 1146 (42S02): Table 'toyu.employee' doesn't exist
MariaDB [toyu]> 
MariaDB [toyu]> CREATE SCHEMA CTETinker;
ERROR 1007 (HY000): Can't create database 'ctetinker'; database exists
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.039 sec)

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

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

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

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

MariaDB [toyu]> INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES
    ->    ('E100', 'E75');
Query OK, 1 row affected (0.005 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.007 sec)

MariaDB [toyu]> INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES
    ->    ('E103', 'E70');
Query OK, 1 row affected (0.004 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 SupervisorEmpId AS SEId FROM Employee AS e WHERE e.EmpId = 'E100';
+------+
| SEId |
+------+
| E75  |
+------+
1 row in set (0.001 sec)

MariaDB [toyu]> SELECT s.*
    -> FROM student AS s
    -> WHERE s.ach > 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 |
| 100003 | Catherine | Lim     | ITEC  | CINF  |   20 |    NULL |
| 100004 | Larry     | Johnson | ITEC  | NULL  |   66 |    1017 |
| 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 |
+--------+-----------+---------+-------+-------+------+---------+
9 rows in set (0.001 sec)

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

MariaDB [toyu]> SELECT DISTINCT s.major
    -> FROM student AS s
    -> WHERE s.ach > 15;
+-------+
| major |
+-------+
| NULL  |
| ARTS  |
| CINF  |
| CSCI  |
| ITEC  |
+-------+
5 rows in set (0.001 sec)

MariaDB [toyu]> SELECT s.major
    -> FROM student AS s
    -> WHERE s.ach > 15 -- 9 intial rows
    -> GROUP BY major;
+-------+
| major |
+-------+
| NULL  |
| ARTS  |
| CINF  |
| CSCI  |
| ITEC  |
+-------+
5 rows in set (0.000 sec)

MariaDB [toyu]> SELECT s.major, COUNT(s.stuId)
    -> FROM student AS s
    -> WHERE s.ach > 15 -- 9 intial rows
    -> GROUP BY s.major; -- 5 groups: 5 new rows
+-------+----------------+
| major | COUNT(s.stuId) |
+-------+----------------+
| NULL  |              1 |
| ARTS  |              2 |
| CINF  |              1 |
| CSCI  |              3 |
| ITEC  |              2 |
+-------+----------------+
5 rows in set (0.001 sec)

MariaDB [toyu]> 
MariaDB [toyu]> SELECT s.major, COUNT(s.stuId) AS `Number of major`
    -> FROM student AS s
    -> WHERE s.ach > 15 -- 9 intial rows
    -> GROUP BY s.major -- 5 groups: 5 new rows
    -> HAVING `Number of major` > 1;
+-------+-----------------+
| major | Number of major |
+-------+-----------------+
| ARTS  |               2 |
| CSCI  |               3 |
| ITEC  |               2 |
+-------+-----------------+
3 rows in set (0.001 sec)

MariaDB [toyu]> SELECT d.deptCode, d.deptName, COUNT(s.stuId) AS `Number of major`
    -> FROM department AS d LEFT JOIN student AS s ON (d.deptCode = s.major)
    -> WHERE s.ach > 15 -- 9 intial rows
    -> GROUP BY d.deptCode, d.deptName -- 5 groups: 5 new rows
    -> HAVING `Number of major` > 1;
+----------+------------------------+-----------------+
| deptCode | deptName               | Number of major |
+----------+------------------------+-----------------+
| ARTS     | Arts                   |               2 |
| CSCI     | Computer Science       |               3 |
| ITEC     | Information Technology |               2 |
+----------+------------------------+-----------------+
3 rows in set (0.002 sec)

MariaDB [toyu]>