MariaDB [toyu]> SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS student,
    ->     IFNULL(d.deptName, 'Undeclared') AS `minor department`
    -> FROM student AS s LEFT JOIN department AS d ON (s.minor = d.deptCode);
+-----------------+------------------------------+
| student         | minor department             |
+-----------------+------------------------------+
| Tony Hawk       | Computer Information Systems |
| Mary Hawk       | Computer Information Systems |
| David Hawk      | Information Technology       |
| Catherine Lim   | Computer Information Systems |
| Larry Johnson   | Undeclared                   |
| Linda Johnson   | English                      |
| Lillian Johnson | Information Technology       |
| Ben Zico        | Undeclared                   |
| Bill Ching      | Undeclared                   |
| Linda King      | Computer Science             |
| Cathy Johanson  | Undeclared                   |
+-----------------+------------------------------+
11 rows in set (0.002 sec)

MariaDB [toyu]> -- subqueries in the WHERE course
MariaDB [toyu]> -- students not enrolled in any class.
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.003 sec)

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

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

MariaDB [toyu]> 
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 concat(fname, ' ', lname) 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) FROM student;
+----------+
| MAX(ach) |
+----------+
|      125 |
+----------+
1 row in set (0.000 sec)

MariaDB [toyu]> select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.000 sec)

MariaDB [toyu]> select 1 AS A
    -> ;
+---+
| A |
+---+
| 1 |
+---+
1 row in set (0.000 sec)

MariaDB [toyu]> select 1 union select 3;
+---+
| 1 |
+---+
| 1 |
| 3 |
+---+
2 rows in set (0.001 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.002 sec)

MariaDB [toyu]> -- CTE
MariaDB [toyu]> WITH m AS -- step [1]
    -> (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]> -- CTE
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 from max credits of all`
    -> FROM student AS s, t1
    -> ORDER BY `ach credits` DESC;
+--------+-------------+------------------------------+
| stuId  | ach credits | diff from max credits of all |
+--------+-------------+------------------------------+
| 100009 |         125 |                            0 |
| 100008 |          90 |                           35 |
| 100002 |          66 |                           59 |
| 100004 |          66 |                           59 |
| 100000 |          40 |                           85 |
| 100001 |          35 |                           90 |
| 100003 |          20 |                          105 |
| 100006 |          18 |                          107 |
| 100007 |          16 |                          109 |
| 100005 |          13 |                          112 |
| 100111 |           0 |                          125 |
+--------+-------------+------------------------------+
11 rows in set (0.002 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]> CREATE TEMPORARY TABLE tt1 AS
    -> SELECT MAX(ach) AS max FROM student;
Query OK, 1 row affected (0.007 sec)
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [toyu]> 
MariaDB [toyu]> SELECT * FROM tt1;
+------+
| max  |
+------+
|  125 |
+------+
1 row in set (0.000 sec)

MariaDB [toyu]> CREATE TEMPORARY TABLE tt2 AS
    -> SELECT s.stuId,
    ->       s.ach AS `ach credits`,
    ->       tt1.max - s.ach AS diff,
    ->       s.major
    -> FROM student AS s, tt1;
Query OK, 11 rows affected (0.005 sec)
Records: 11  Duplicates: 0  Warnings: 0

MariaDB [toyu]> 
MariaDB [toyu]> SELECT * FROM tt2;
+--------+-------------+------+-------+
| 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]> SELECT tt2.stuId, tt2.`ach credits`,
    ->    tt2.diff AS `diff from max credits of all`,
    ->    d.deptName AS department
    -> FROM tt2 LEFT JOIN department d ON (tt2.major = d.deptCode)
    -> ORDER BY tt2.`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]> DROP SCHEMA IF EXISTS CTEtinker;
Query OK, 0 rows affected (0.006 sec)

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

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

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

MariaDB [toyu]> INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES
    ->    ('E70', 'E50');
Query OK, 1 row affected (0.003 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]> SELECT DISTINCT SupervisorEmpId
    -> FROM Employee
    -> WHERE EmpId = 'E100';
+-----------------+
| SupervisorEmpId |
+-----------------+
| E75             |
+-----------------+
1 row 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.002 sec)

MariaDB [toyu]> SELECT DISTINCT s.major, s.stuId, s.ach
    -> 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 'ITEC');
;
'' at line 3
MariaDB [toyu]> SELECT DISTINCT s.major, s.stuId, s.ach
    -> FROM student AS s
    -> WHERE s.major IN ('CSCI', 'CINF', 'ITEC');
+-------+--------+------+
| major | stuId  | ach  |
+-------+--------+------+
| CSCI  | 100000 |   40 |
| CSCI  | 100001 |   35 |
| CSCI  | 100002 |   66 |
| ITEC  | 100003 |   20 |
| ITEC  | 100004 |   66 |
| CINF  | 100005 |   13 |
| CINF  | 100006 |   18 |
+-------+--------+------+
7 rows in set (0.003 sec)

MariaDB [toyu]> SELECT DISTINCT s.major, s.stuId, s.ach
    -> FROM student AS s
    -> WHERE s.major IN ('CSCI', 'CINF', 'ITEC')
    -> GROUP BY s.major;
+-------+--------+------+
| major | stuId  | ach  |
+-------+--------+------+
| CINF  | 100005 |   13 |
| CSCI  | 100000 |   40 |
| ITEC  | 100003 |   20 |
+-------+--------+------+
3 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 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.001 sec)

MariaDB [toyu]> notee