MariaDB [toyu]> UPDATE enroll
    -> SET grade = 'A'
    -> WHERE stuId = 100002
    -> AND classId - 10002;
Query OK, 2 rows affected (0.005 sec)
Rows matched: 2  Changed: 2  Warnings: 0

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

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

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

Query OK, 9 rows affected (0.191 sec)

Query OK, 1 row affected (0.004 sec)

Database changed
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.001 sec)

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

Query OK, 0 rows affected, 1 warning (0.001 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.032 sec)

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

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

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

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

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

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

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

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

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

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

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

Query OK, 11 rows affected (0.006 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.014 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.002 sec)

MariaDB [toyu]> SELECT s.*  -- all swimmers with first name starting with B.
    -> FROM Swimmer AS s
    -> WHERE s.FName LIKE 'B%';
ERROR 1146 (42S02): Table 'toyu.swimmer' doesn't exist
MariaDB [toyu]> use swim;
Database changed
MariaDB [swim]> SELECT s.*  -- all swimmers with first name starting with B.
    -> FROM Swimmer AS s
    -> WHERE s.FName LIKE 'B%';
+-----------+-------+-------+--------------+---------------------+------------+----------------+------------+----------------------+---------------+
| SwimmerId | LName | FName | Phone        | EMail               | JoinTime   | CurrentLevelId | Main_CT_Id | Main_CT_Relationship | Main_CT_Since |
+-----------+-------+-------+--------------+---------------------+------------+----------------+------------+----------------------+---------------+
|         1 | Khan  | Bobby | 832-116-2992 | theBKhan1@gmail.com | 2014-02-12 |              3 |          1 | Mother               | 2014-02-12    |
|         2 | Khan  | Billy | 832-116-2992 | theBKhan2@gmail.com | 2015-12-12 |              2 |          1 | Mother               | 2015-12-12    |
+-----------+-------+-------+--------------+---------------------+------------+----------------+------------+----------------------+---------------+
2 rows in set (0.000 sec)

MariaDB [swim]> -- student with the maximum number of ach.
MariaDB [swim]> SELECT DISTINCT MAX(ach)
    -> FROM student;
ERROR 1146 (42S02): Table 'swim.student' doesn't exist
MariaDB [swim]> use toyu;
Database changed
MariaDB [toyu]> -- student with the maximum number of ach.
MariaDB [toyu]> SELECT DISTINCT MAX(ach)
    -> FROM student;
+----------+
| MAX(ach) |
+----------+
|      125 |
+----------+
1 row in set (0.001 sec)

MariaDB [toyu]> SELECT CONCAT(fname, ' ', lname) -- regular function
    -> FROM student;
+---------------------------+
| CONCAT(fname, ' ', lname) |
+---------------------------+
| Tony Hawk                 |
| Mary Hawk                 |
| David Hawk                |
| Catherine Lim             |
| Larry Johnson             |
| Linda Johnson             |
| Lillian Johnson           |
| Ben Zico                  |
| Bill Ching                |
| Linda King                |
| Cathy Johanson            |
+---------------------------+
11 rows 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) -- MAX: group function
    -> FROM student;
+----------+
| MAX(ach) |
+----------+
|      125 |
+----------+
1 row in set (0.001 sec)

MariaDB [toyu]> EXPLAIN +----------+
    -> | MAX(ach) |
    -> +----------+
    -> |      125 |
    -> +----------+;
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 '+----------+
| MAX(ach) |
+----------+
|      125 |
+----------+' at line 1
MariaDB [toyu]> EXPLAIN -- subqueries as derived tables.
    -> 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;
+------+-------------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+
| id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                                           |
+------+-------------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+
|    1 | PRIMARY     | s          | ALL  | NULL          | NULL | NULL    | NULL | 11   | Using temporary                                 |
|    1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL | 11   | Using where; Using join buffer (flat, BNL join) |
|    2 | DERIVED     | student    | ALL  | NULL          | NULL | NULL    | NULL | 11   |                                                 |
+------+-------------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+
3 rows in set (0.001 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.
    ->    -- m: a derived table from a subquery.
    -> 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 
    -> 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 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]> 
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.002 sec)

MariaDB [toyu]> CREATE SCHEMA CTETinker;
Query OK, 1 row affected (0.005 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.099 sec)

MariaDB [toyu]> 
MariaDB [toyu]> INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES
    ->    ('E3', null);
Query OK, 1 row affected (0.007 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.004 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.006 sec)

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

MariaDB [toyu]> INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES
    ->    ('E103', 'E70');
Query OK, 1 row affected (0.006 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.001 sec)

MariaDB [toyu]> -- supervisor of E100:
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]> WITH RECURSIVE Super(SEId) AS
    -> (  SELECT SupervisorEmpId AS SEId FROM Employee AS e WHERE e.EmpId = 'E100' -- initial condition/action: SEId: E75
    ->    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.002 sec)

MariaDB [toyu]>