MariaDB [swim]> -- Suggested solution to HW #5 Joint DB Spring 2024
MariaDB [swim]> 
MariaDB [swim]> -- (1) List the names, phones and join times of all swimmers joining in the years of 1/2014 to 6/2016 ordered by join time in the following manner.
MariaDB [swim]> SELECT DISTINCT s.LName, s.FName, s.Phone, s.joinTime
    -> FROM swimmer AS s
    -> WHERE s.joinTime BETWEEN '2014-01-01' AND '2016-06-30'
    -> ORDER BY s.joinTime ASC;
+---------+--------+--------------+------------+
| LName   | FName  | Phone        | joinTime   |
+---------+--------+--------------+------------+
| Khan    | Bobby  | 832-116-2992 | 2014-02-12 |
| Johnson | Philip | 713-222-1010 | 2015-05-15 |
| Khan    | Billy  | 832-116-2992 | 2015-12-12 |
| Khan    | Nina   | 832-116-2992 | 2016-05-12 |
+---------+--------+--------------+------------+
4 rows in set (0.019 sec)

MariaDB [swim]> -- or
MariaDB [swim]> SELECT DISTINCT s.LName, s.FName, s.Phone, s.joinTime
    -> FROM swimmer AS s
    -> WHERE s.joinTime >= '2014-01-01'
    -> AND s.joinTime <= '2016-06-30'
    -> ORDER BY s.joinTime ASC;
+---------+--------+--------------+------------+
| LName   | FName  | Phone        | joinTime   |
+---------+--------+--------------+------------+
| Khan    | Bobby  | 832-116-2992 | 2014-02-12 |
| Johnson | Philip | 713-222-1010 | 2015-05-15 |
| Khan    | Billy  | 832-116-2992 | 2015-12-12 |
| Khan    | Nina   | 832-116-2992 | 2016-05-12 |
+---------+--------+--------------+------------+
4 rows in set (0.001 sec)

MariaDB [swim]> 
MariaDB [swim]> -- (2) List the names of the swimmers who have the same last name as their primary care takers in the following manner.
MariaDB [swim]> SELECT DISTINCT CONCAT(s.FName, ' ', s.LName) AS swimmer,
    -> CONCAT(c.FName, ' ', c.LName) AS `primary caretaker`
    -> FROM Swimmer AS s INNER JOIN Caretaker c ON (s.Main_CT_Id = c.CT_Id)
    -> WHERE c.LName = s.LName;
+----------------+-------------------+
| swimmer        | primary caretaker |
+----------------+-------------------+
| Bobby Khan     | Azalea Khan       |
| Billy Khan     | Azalea Khan       |
| Nina Khan      | Joseph Khan       |
| Clara Johnson  | Katie Johnson     |
| Philip Johnson | Elizabeth Johnson |
+----------------+-------------------+
5 rows in set (0.009 sec)

MariaDB [swim]> 
MariaDB [swim]> -- (3) List all swimmers (their names, levels) event titles, and meet titles in which the swimmers have participated in a butterfly event of the meet.
MariaDB [swim]> SELECT DISTINCT DISTINCT CONCAT(s.FName, ' ', s.LName) AS swimmer,
    -> l.level, m.title AS meet, e.title AS event
    -> FROM level AS l INNER JOIN Swimmer AS s ON (l.LevelId = s.CurrentLevelId)
    -> INNER JOIN participation AS p ON (s.SwimmerId = p.SwimmerId)
    -> INNER JOIN Event AS e ON (p.EventId = e.EventId)
    -> INNER JOIN Meet AS m ON (e.MeetId = m.MeetId)
    -> WHERE e.Title LIKE '%Butterfly%';
+----------------+--------+-------------+----------------+
| swimmer        | level  | meet        | event          |
+----------------+--------+-------------+----------------+
| Billy Khan     | Blue   | UHCL Open   | 50M Butterfly  |
| Nina Khan      | Blue   | UHCL Open   | 50M Butterfly  |
| Philip Johnson | Blue   | UHCL Open   | 50M Butterfly  |
| Bobby Khan     | Yellow | UHCL Open   | 100M Butterfly |
| Clara Johnson  | Yellow | UHCL Open   | 100M Butterfly |
| Clara Johnson  | Yellow | Shell Trial | 50M Butterfly  |
| Billy Khan     | Blue   | Shell Trial | 50M Butterfly  |
| Nina Khan      | Blue   | Shell Trial | 50M Butterfly  |
| Philip Johnson | Blue   | Shell Trial | 50M Butterfly  |
| Bobby Khan     | Yellow | Shell Trial | 100M Butterfly |
| Clara Johnson  | Yellow | Shell Trial | 100M Butterfly |
+----------------+--------+-------------+----------------+
11 rows in set (0.041 sec)

MariaDB [swim]> 
MariaDB [swim]> -- (4) List the names of the swimmers who have participated in a 100M Freestyle event and a 50M Butterfly event in the following manner.
MariaDB [swim]> SELECT DISTINCT s.swimmerId,
    -> CONCAT(s.FName, ' ', s.LName) AS swimmer,
    -> e1.eventId AS `100M Freestyle eventId`,
    -> e2.eventId AS `50M Butterfly eventId`
    -> FROM Swimmer AS s INNER JOIN participation AS p1 ON (s.SwimmerId = p1.SwimmerId)
    -> INNER JOIN event AS e1 ON (p1.eventId = e1.eventId AND e1.title = '100M Freestyle')
    -> INNER JOIN participation AS p2 ON (s.SwimmerId = p2.SwimmerId)
    -> INNER JOIN event AS e2 ON (p2.eventId = e2.eventId AND e2.title = '50M Butterfly');
+-----------+----------------+------------------------+-----------------------+
| swimmerId | swimmer        | 100M Freestyle eventId | 50M Butterfly eventId |
+-----------+----------------+------------------------+-----------------------+
|         2 | Billy Khan     |                      2 |                     1 |
|         2 | Billy Khan     |                      2 |                     5 |
|         2 | Billy Khan     |                      6 |                     1 |
|         2 | Billy Khan     |                      6 |                     5 |
|         3 | Nina Khan      |                      2 |                     1 |
|         3 | Nina Khan      |                      2 |                     5 |
|         3 | Nina Khan      |                      6 |                     1 |
|         3 | Nina Khan      |                      6 |                     5 |
|         5 | Philip Johnson |                      2 |                     1 |
|         5 | Philip Johnson |                      2 |                     5 |
|         5 | Philip Johnson |                      6 |                     1 |
|         5 | Philip Johnson |                      6 |                     5 |
+-----------+----------------+------------------------+-----------------------+
12 rows in set (0.005 sec)

MariaDB [swim]> 
MariaDB [swim]> -- (5) Redo (4), but show the result in the following manner.
MariaDB [swim]> WITH t1 AS (
    -> SELECT DISTINCT s.swimmerId,
    -> CONCAT(s.FName, ' ', s.LName) AS swimmer,
    -> GROUP_CONCAT(e.eventId SEPARATOR ', ') AS `100M Freestyle eventIds`
    -> FROM Swimmer AS s INNER JOIN participation AS p ON (s.SwimmerId = p.SwimmerId)
    -> INNER JOIN event AS e ON (p.eventId = e.eventId AND e.title = '100M Freestyle')
    -> GROUP BY 1, 2
    -> ),
    -> t2 AS (
    -> SELECT DISTINCT s.swimmerId,
    -> CONCAT(s.FName, ' ', s.LName) AS swimmer,
    -> GROUP_CONCAT(e.eventId SEPARATOR ', ') AS `50M Butterfly eventIds`
    -> FROM Swimmer AS s INNER JOIN participation AS p ON (s.SwimmerId = p.SwimmerId)
    -> INNER JOIN event AS e ON (p.eventId = e.eventId AND e.title = '50M Butterfly')
    -> GROUP BY 1, 2
    -> )
    -> SELECT t1.swimmerId, t1.Swimmer, t1.`100M Freestyle eventIds`, t2.`50M Butterfly eventIds`
    -> FROM t1 NATURAL JOIN t2;
+-----------+----------------+-------------------------+------------------------+
| swimmerId | Swimmer        | 100M Freestyle eventIds | 50M Butterfly eventIds |
+-----------+----------------+-------------------------+------------------------+
|         2 | Billy Khan     | 2, 6                    | 1, 5                   |
|         3 | Nina Khan      | 2, 6                    | 1, 5                   |
|         5 | Philip Johnson | 2, 6                    | 1, 5                   |
+-----------+----------------+-------------------------+------------------------+
3 rows in set (0.036 sec)

MariaDB [swim]> 
MariaDB [swim]> -- (6) List the numbers of events on distances. Assume that the event title uses the format of "<distance>M <style>", e.g. "50M Butterfly". Note the order of the result is in ascending distance.
MariaDB [swim]> 
MariaDB [swim]> SELECT SUBSTR(e.title, 1, LOCATE("M", e.title)-1) AS `event distance`,
    -> COUNT(*) AS `number of events`
    -> FROM event AS e
    -> GROUP BY `event distance`
    -> ORDER BY CAST(`event distance` AS INTEGER);
+----------------+------------------+
| event distance | number of events |
+----------------+------------------+
| 50             |                3 |
| 100            |                5 |
| 200            |                3 |
+----------------+------------------+
3 rows in set (0.003 sec)

MariaDB [swim]> 
MariaDB [swim]> -- (7) List the swimmers (names and their number of participated events) who have participated in two or more non-'100M' events (events without the substring '100M' in their titles) in the following format. Note that the order of the result is in descending of the counts of non-100M event participations.
MariaDB [swim]> SELECT CONCAT(s.FName, ' ', s.LName) AS Swimmer, 
    -> COUNT(e.eventId) AS `# of non-100M Events`
    -> FROM Swimmer AS s INNER JOIN participation AS p ON (s.swimmerId = p.swimmerId)
    -> INNER JOIN event AS e ON (p.eventId = e.eventId)
    -> WHERE e.Title NOT LIKE '%100M%'
    -> GROUP BY Swimmer
    -> HAVING `# of non-100M Events` >= 2
    -> ORDER BY `# of non-100M Events` DESC;
+----------------+----------------------+
| Swimmer        | # of non-100M Events |
+----------------+----------------------+
| Bobby Khan     |                    3 |
| Billy Khan     |                    2 |
| Nina Khan      |                    2 |
| Clara Johnson  |                    2 |
| Philip Johnson |                    2 |
+----------------+----------------------+
5 rows in set (0.001 sec)

MariaDB [swim]> use toyu;
Database changed
MariaDB [toyu]> 
MariaDB [toyu]> SELECT StuId
    -> FROM enroll;
+--------+
| StuId  |
+--------+
| 100000 |
| 100000 |
| 100000 |
| 100000 |
| 100000 |
| 100000 |
| 100001 |
| 100001 |
| 100002 |
| 100002 |
| 100002 |
| 100004 |
| 100004 |
| 100005 |
| 100005 |
| 100005 |
| 100005 |
| 100006 |
| 100006 |
| 100007 |
| 100007 |
| 100008 |
+--------+
22 rows in set (0.002 sec)

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

MariaDB [toyu]> SELECT DISTINCT d.deptCode
    -> FROM Department AS d
    -> WHERE d.schoolCode = 'CSE";
    '> ;
    '> ';
Empty set (0.003 sec)

MariaDB [toyu]> SELECT DISTINCT d.deptCode
    -> FROM Department AS d
    -> WHERE d.schoolCode = 'CSE';
+----------+
| deptCode |
+----------+
| CINF     |
| CSCI     |
| ITEC     |
| MATH     |
+----------+
4 rows in set (0.000 sec)

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

MariaDB [toyu]> SELECT DISTINCT d.deptCode, d.deptName AS department
    -> WHERE d.schoolCode = 'CSE'
    -> FROM Department AS d;
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 Department AS d' at line 3
MariaDB [toyu]> 
MariaDB [toyu]> 
MariaDB [toyu]> DROP SCHEMA IF EXISTS tinker;
Query OK, 0 rows affected, 1 warning (0.002 sec)

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

MariaDB [toyu]> USE tinker;
Database changed
MariaDB [tinker]> 
MariaDB [tinker]> CREATE TABLE s2
    -> SELECT * FROM toyu.student;
Query OK, 11 rows affected (0.014 sec)
Records: 11  Duplicates: 0  Warnings: 0

MariaDB [tinker]> SELECT *
    -> FROM s2;
+--------+-----------+----------+-------+-------+------+---------+
| 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 |
| 100005 | Linda     | Johnson  | CINF  | ENGL  |   13 |    1015 |
| 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 |
| 100111 | Cathy     | Johanson | NULL  | NULL  |    0 |    1018 |
+--------+-----------+----------+-------+-------+------+---------+
11 rows in set (0.001 sec)

MariaDB [tinker]> 
MariaDB [tinker]> CREATE TEMPORARY TABLE s3
    -> SELECT * FROM toyu.student;
Query OK, 11 rows affected (0.003 sec)
Records: 11  Duplicates: 0  Warnings: 0

MariaDB [tinker]> 
MariaDB [tinker]> SELECT *
    -> FROM s3;
+--------+-----------+----------+-------+-------+------+---------+
| 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 |
| 100005 | Linda     | Johnson  | CINF  | ENGL  |   13 |    1015 |
| 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 |
| 100111 | Cathy     | Johanson | NULL  | NULL  |    0 |    1018 |
+--------+-----------+----------+-------+-------+------+---------+
11 rows in set (0.000 sec)

MariaDB [tinker]> 
MariaDB [tinker]> CREATE TABLE s4 LIKE toyu.student;
Query OK, 0 rows affected (0.031 sec)

MariaDB [tinker]> show tables;
+------------------+
| Tables_in_tinker |
+------------------+
| s2               |
| s4               |
+------------------+
2 rows in set (0.001 sec)

MariaDB [tinker]> SELECT *
    -> FROM s4;
Empty set (0.000 sec)

MariaDB [tinker]> INSERT INTO s4
    -> SELECT * FROM toyu.student;
Query OK, 11 rows affected (0.005 sec)
Records: 11  Duplicates: 0  Warnings: 0

MariaDB [tinker]> 
MariaDB [tinker]> SELECT *
    -> FROM s4;
+--------+-----------+----------+-------+-------+------+---------+
| 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 |
| 100005 | Linda     | Johnson  | CINF  | ENGL  |   13 |    1015 |
| 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 |
| 100111 | Cathy     | Johanson | NULL  | NULL  |    0 |    1018 |
+--------+-----------+----------+-------+-------+------+---------+
11 rows in set (0.000 sec)

MariaDB [tinker]> DESC toyu.student;
+---------+-----------------+------+-----+---------+-------+
| Field   | Type            | Null | Key | Default | Extra |
+---------+-----------------+------+-----+---------+-------+
| stuId   | int(11)         | NO   | PRI | NULL    |       |
| fname   | varchar(30)     | NO   |     | NULL    |       |
| lname   | varchar(30)     | NO   |     | NULL    |       |
| major   | char(4)         | YES  | MUL | NULL    |       |
| minor   | char(4)         | YES  | MUL | NULL    |       |
| ach     | int(3) unsigned | YES  |     | 0       |       |
| advisor | int(11)         | YES  | MUL | NULL    |       |
+---------+-----------------+------+-----+---------+-------+
7 rows in set (0.014 sec)

MariaDB [tinker]> DESC s2;
+---------+-----------------+------+-----+---------+-------+
| Field   | Type            | Null | Key | Default | Extra |
+---------+-----------------+------+-----+---------+-------+
| stuId   | int(11)         | NO   |     | NULL    |       |
| fname   | varchar(30)     | NO   |     | NULL    |       |
| lname   | varchar(30)     | NO   |     | NULL    |       |
| major   | char(4)         | YES  |     | NULL    |       |
| minor   | char(4)         | YES  |     | NULL    |       |
| ach     | int(3) unsigned | YES  |     | 0       |       |
| advisor | int(11)         | YES  |     | NULL    |       |
+---------+-----------------+------+-----+---------+-------+
7 rows in set (0.007 sec)

MariaDB [tinker]> DESC s3;
+---------+-----------------+------+-----+---------+-------+
| Field   | Type            | Null | Key | Default | Extra |
+---------+-----------------+------+-----+---------+-------+
| stuId   | int(11)         | NO   |     | NULL    |       |
| fname   | varchar(30)     | NO   |     | NULL    |       |
| lname   | varchar(30)     | NO   |     | NULL    |       |
| major   | char(4)         | YES  |     | NULL    |       |
| minor   | char(4)         | YES  |     | NULL    |       |
| ach     | int(3) unsigned | YES  |     | 0       |       |
| advisor | int(11)         | YES  |     | NULL    |       |
+---------+-----------------+------+-----+---------+-------+
7 rows in set (0.016 sec)

MariaDB [tinker]> DESC s4;
+---------+-----------------+------+-----+---------+-------+
| Field   | Type            | Null | Key | Default | Extra |
+---------+-----------------+------+-----+---------+-------+
| stuId   | int(11)         | NO   | PRI | NULL    |       |
| fname   | varchar(30)     | NO   |     | NULL    |       |
| lname   | varchar(30)     | NO   |     | NULL    |       |
| major   | char(4)         | YES  | MUL | NULL    |       |
| minor   | char(4)         | YES  | MUL | NULL    |       |
| ach     | int(3) unsigned | YES  |     | 0       |       |
| advisor | int(11)         | YES  | MUL | NULL    |       |
+---------+-----------------+------+-----+---------+-------+
7 rows in set (0.015 sec)

MariaDB [tinker]> INSERT INTO student VALUES
    ->    (100010,'Bun','Yue',null,null,50,null),
    ->    (100011,'Paul','Harris','CSCI','ITEC',23,1015);
ERROR 1146 (42S02): Table 'tinker.student' doesn't exist
MariaDB [tinker]>   
MariaDB [tinker]> SELECT * FROM student;
ERROR 1146 (42S02): Table 'tinker.student' doesn't exist
MariaDB [tinker]>   
MariaDB [tinker]> INSERT INTO student VALUES
    ->    (100010,'Bun','Yue',null,null,50,null),
    ->    (100011,'Paul','Harris','CSCI','ITEC',23,1015);
ERROR 1146 (42S02): Table 'tinker.student' doesn't exist
MariaDB [tinker]>   
MariaDB [tinker]> INSERT INTO student VALUES  
    ->    (100020,'Bunno','Yue','GEOG',null,50,null);
ERROR 1146 (42S02): Table 'tinker.student' doesn't exist
MariaDB [tinker]> INSERT INTO student VALUES  
    ->    (100021,'Bunna','Yue',null,'GEOG',50,null);
ERROR 1146 (42S02): Table 'tinker.student' doesn't exist
MariaDB [tinker]> INSERT INTO student VALUES  
    ->    (100022,'Bunno','Yue',null,null,50,8888);
ERROR 1146 (42S02): Table 'tinker.student' doesn't exist
MariaDB [tinker]> 
MariaDB [tinker]>   
MariaDB [tinker]> -- Remove the two new rows.
MariaDB [tinker]> DELETE FROM Student
    -> WHERE stuId = 100010 OR stuId = 100011;
ERROR 1146 (42S02): Table 'tinker.student' doesn't exist
MariaDB [tinker]> 
MariaDB [tinker]> SELECT * FROM student;
ERROR 1146 (42S02): Table 'tinker.student' doesn't exist
MariaDB [tinker]> 
MariaDB [tinker]> use toyu;
Database changed
MariaDB [toyu]> 
MariaDB [toyu]> DROP TABLE s2;
ERROR 1051 (42S02): Unknown table 'toyu.s2'
MariaDB [toyu]> DROP TABLE s3;
ERROR 1051 (42S02): Unknown table 'toyu.s3'
MariaDB [toyu]> DROP TABLE s4;
ERROR 1051 (42S02): Unknown table 'toyu.s4'
MariaDB [toyu]> 
MariaDB [toyu]> SHOW TABLES;
+----------------+
| Tables_in_toyu |
+----------------+
| class          |
| course         |
| department     |
| enroll         |
| faculty        |
| grade          |
| school         |
| student        |
+----------------+
8 rows in set (0.001 sec)

MariaDB [toyu]> 
MariaDB [toyu]> DROP SCHEMA IF EXISTS tinker;
Query OK, 2 rows affected (0.025 sec)

MariaDB [toyu]> use toyu;
Database changed
MariaDB [toyu]> INSERT INTO student VALUES
    ->    (100010,'Bun','Yue',null,null,50,null),
    ->    (100011,'Paul','Harris','CSCI','ITEC',23,1015);
Query OK, 2 rows affected (0.006 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [toyu]>   
MariaDB [toyu]> SELECT * FROM student;
+--------+-----------+----------+-------+-------+------+---------+
| 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 |
| 100005 | Linda     | Johnson  | CINF  | ENGL  |   13 |    1015 |
| 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 |
| 100010 | Bun       | Yue      | NULL  | NULL  |   50 |    NULL |
| 100011 | Paul      | Harris   | CSCI  | ITEC  |   23 |    1015 |
| 100111 | Cathy     | Johanson | NULL  | NULL  |    0 |    1018 |
+--------+-----------+----------+-------+-------+------+---------+
13 rows in set (0.001 sec)

MariaDB [toyu]>   
MariaDB [toyu]> INSERT INTO student VALUES
    ->    (100010,'Bun','Yue',null,null,50,null),
    ->    (100011,'Paul','Harris','CSCI','ITEC',23,1015);
ERROR 1062 (23000): Duplicate entry '100010' for key 'PRIMARY'
MariaDB [toyu]>   
MariaDB [toyu]> INSERT INTO student VALUES  
    ->    (100020,'Bunno','Yue','GEOG',null,50,null);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`toyu`.`student`, CONSTRAINT `Student_major_fk` FOREIGN KEY (`major`) REFERENCES `department` (`deptCode`) ON DELETE CASCADE)
MariaDB [toyu]> INSERT INTO student VALUES  
    ->    (100021,'Bunna','Yue',null,'GEOG',50,null);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`toyu`.`student`, CONSTRAINT `Student_minor_fk` FOREIGN KEY (`minor`) REFERENCES `department` (`deptCode`) ON DELETE CASCADE)
MariaDB [toyu]> INSERT INTO student VALUES  
    ->    (100022,'Bunno','Yue',null,null,50,8888);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`toyu`.`student`, CONSTRAINT `Student_advisor_fk` FOREIGN KEY (`advisor`) REFERENCES `faculty` (`facId`))
MariaDB [toyu]> 
MariaDB [toyu]>   
MariaDB [toyu]> -- Remove the two new rows.
MariaDB [toyu]> DELETE FROM Student
    -> WHERE stuId = 100010 OR stuId = 100011;
Query OK, 2 rows affected (0.001 sec)

MariaDB [toyu]> 
MariaDB [toyu]> SELECT * FROM student;
+--------+-----------+----------+-------+-------+------+---------+
| 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 |
| 100005 | Linda     | Johnson  | CINF  | ENGL  |   13 |    1015 |
| 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 |
| 100111 | Cathy     | Johanson | NULL  | NULL  |    0 |    1018 |
+--------+-----------+----------+-------+-------+------+---------+
11 rows in set (0.000 sec)

MariaDB [toyu]> UPDATE student
    -> SET major = 'CSCI';
Query OK, 8 rows affected (0.011 sec)
Rows matched: 11  Changed: 8  Warnings: 0

MariaDB [toyu]> UPDATE student
    -> SET major = 'CINF'
    -> WHERE lname = 'Hawk';
Query OK, 3 rows affected (0.005 sec)
Rows matched: 3  Changed: 3  Warnings: 0

MariaDB [toyu]> SELECT * FROM student;
+--------+-----------+----------+-------+-------+------+---------+
| stuId  | fname     | lname    | major | minor | ach  | advisor |
+--------+-----------+----------+-------+-------+------+---------+
| 100000 | Tony      | Hawk     | CINF  | CINF  |   40 |    1011 |
| 100001 | Mary      | Hawk     | CINF  | CINF  |   35 |    1011 |
| 100002 | David     | Hawk     | CINF  | ITEC  |   66 |    1012 |
| 100003 | Catherine | Lim      | CSCI  | CINF  |   20 |    NULL |
| 100004 | Larry     | Johnson  | CSCI  | NULL  |   66 |    1017 |
| 100005 | Linda     | Johnson  | CSCI  | ENGL  |   13 |    1015 |
| 100006 | Lillian   | Johnson  | CSCI  | ITEC  |   18 |    1016 |
| 100007 | Ben       | Zico     | CSCI  | NULL  |   16 |    NULL |
| 100008 | Bill      | Ching    | CSCI  | NULL  |   90 |    NULL |
| 100009 | Linda     | King     | CSCI  | CSCI  |  125 |    1018 |
| 100111 | Cathy     | Johanson | CSCI  | NULL  |    0 |    1018 |
+--------+-----------+----------+-------+-------+------+---------+
11 rows in set (0.001 sec)

MariaDB [toyu]> source createtoyu.sql
Query OK, 0 rows affected (0.002 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 8 rows affected (0.065 sec)

Query OK, 1 row affected (0.001 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.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.000 sec)

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

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

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

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

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

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

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

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

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

Query OK, 0 rows affected (0.005 sec)

Query OK, 0 rows affected (0.004 sec)

Query OK, 0 rows affected (0.006 sec)

Query OK, 0 rows affected (0.005 sec)

Query OK, 0 rows affected (0.005 sec)

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

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

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

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

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

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

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

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

Query OK, 0 rows affected (0.001 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.001 sec)

MariaDB [toyu]> SELECT DISTINCT *
    -> FROM Student
    -> WHERE credits BETWEEN 30 AND 70;
ERROR 1054 (42S22): Unknown column 'credits' in 'where clause'
MariaDB [toyu]> SELECT DISTINCT *
    -> FROM Student
    -> WHERE ach BETWEEN 30 AND 70;
+--------+-------+---------+-------+-------+------+---------+
| 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 |
| 100004 | Larry | Johnson | ITEC  | NULL  |   66 |    1017 |
+--------+-------+---------+-------+-------+------+---------+
4 rows in set (0.001 sec)

MariaDB [toyu]> SELECT DISTINCT *
    -> FROM Student
    -> WHERE ach >= 30 
    -> AND ach <= 70;
+--------+-------+---------+-------+-------+------+---------+
| 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 |
| 100004 | Larry | Johnson | ITEC  | NULL  |   66 |    1017 |
+--------+-------+---------+-------+-------+------+---------+
4 rows in set (0.001 sec)

MariaDB [toyu]> -- student in selected majors
MariaDB [toyu]> SELECT DISTINCT *
    -> FROM Student
    -> WHERE major IN ('CSCI', 'CINF', 'ITEC');
+--------+-----------+---------+-------+-------+------+---------+
| 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 |
| 100005 | Linda     | Johnson | CINF  | ENGL  |   13 |    1015 |
| 100006 | Lillian   | Johnson | CINF  | ITEC  |   18 |    1016 |
+--------+-----------+---------+-------+-------+------+---------+
7 rows in set (0.001 sec)

MariaDB [toyu]> SELECT DISTINCT *
    -> FROM Student AS s
    -> WHERE EXISTS
    -> (SELECT *  -- a subquery
    -> FROM Enroll AS e
    -> WHERE e.stuId = s.stuId);
+--------+---------+---------+-------+-------+------+---------+
| 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 |
| 100004 | Larry   | Johnson | ITEC  | NULL  |   66 |    1017 |
| 100005 | Linda   | Johnson | CINF  | ENGL  |   13 |    1015 |
| 100006 | Lillian | Johnson | CINF  | ITEC  |   18 |    1016 |
| 100007 | Ben     | Zico    | NULL  | NULL  |   16 |    NULL |
| 100008 | Bill    | Ching   | ARTS  | NULL  |   90 |    NULL |
+--------+---------+---------+-------+-------+------+---------+
8 rows in set (0.003 sec)

MariaDB [toyu]> SELECT DISTINCT s.*
    -> FROM Student AS s INNER JOIN Enroll AS e USING (stuId);
+--------+---------+---------+-------+-------+------+---------+
| 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 |
| 100004 | Larry   | Johnson | ITEC  | NULL  |   66 |    1017 |
| 100005 | Linda   | Johnson | CINF  | ENGL  |   13 |    1015 |
| 100006 | Lillian | Johnson | CINF  | ITEC  |   18 |    1016 |
| 100007 | Ben     | Zico    | NULL  | NULL  |   16 |    NULL |
| 100008 | Bill    | Ching   | ARTS  | NULL  |   90 |    NULL |
+--------+---------+---------+-------+-------+------+---------+
8 rows in set (0.001 sec)

MariaDB [toyu]> SELECT DISTINCT s.*
    -> FROM student AS s
    -> WHERE s.lname LIKE '%k%';
+--------+-------+-------+-------+-------+------+---------+
| 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 |
| 100009 | Linda | King  | ARTS  | CSCI  |  125 |    1018 |
+--------+-------+-------+-------+-------+------+---------+
4 rows in set (0.001 sec)

MariaDB [toyu]> SELECT 'Michael!' REGEXP '.*';
+------------------------+
| 'Michael!' REGEXP '.*' |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.003 sec)

MariaDB [toyu]> SELECT DISTINCT s.*
    -> FROM student AS s
    -> WHERE s.lname REGEXP '.*k.*';
+--------+-------+-------+-------+-------+------+---------+
| 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 |
| 100009 | Linda | King  | ARTS  | CSCI  |  125 |    1018 |
+--------+-------+-------+-------+-------+------+---------+
4 rows in set (0.002 sec)

MariaDB [toyu]> SELECT DISTINCT s.*
    -> FROM student AS s
    -> WHERE s.lname REGEXP '.*[jkl].*';
+--------+-----------+----------+-------+-------+------+---------+
| 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 |
| 100005 | Linda     | Johnson  | CINF  | ENGL  |   13 |    1015 |
| 100006 | Lillian   | Johnson  | CINF  | ITEC  |   18 |    1016 |
| 100009 | Linda     | King     | ARTS  | CSCI  |  125 |    1018 |
| 100111 | Cathy     | Johanson | NULL  | NULL  |    0 |    1018 |
+--------+-----------+----------+-------+-------+------+---------+
9 rows in set (0.000 sec)

MariaDB [toyu]> SELECT DISTINCT s.fname, s.lname, c.classId, e.grade
    -> FROM student AS s, enroll AS e, class AS c
    -> WHERE s.stuId = e.stuId -- Join condition
    -> AND e.classId = c.classId -- Join condition
    -> AND c.semester = 'Fall' -- problem condition
    -> AND c.year = 2019; -- problem condition
+---------+---------+---------+-------+
| fname   | lname   | classId | grade |
+---------+---------+---------+-------+
| Tony    | Hawk    |   10000 | A     |
| Mary    | Hawk    |   10000 | NULL  |
| David   | Hawk    |   10000 | B-    |
| Tony    | Hawk    |   10001 | A     |
| Mary    | Hawk    |   10001 | A-    |
| Tony    | Hawk    |   10002 | B+    |
| David   | Hawk    |   10002 | B+    |
| Tony    | Hawk    |   10003 | C     |
| David   | Hawk    |   10003 | D     |
| Larry   | Johnson |   10003 | A     |
| Linda   | Johnson |   10003 | NULL  |
| Tony    | Hawk    |   10004 | A-    |
| Larry   | Johnson |   10004 | B+    |
| Linda   | Johnson |   10004 | A-    |
| Lillian | Johnson |   10004 | C+    |
| Linda   | Johnson |   10005 | A-    |
| Lillian | Johnson |   10005 | A     |
| Linda   | Johnson |   10006 | B+    |
| Ben     | Zico    |   10007 | F     |
| Bill    | Ching   |   10007 | C-    |
| Ben     | Zico    |   10008 | A-    |
+---------+---------+---------+-------+
21 rows in set (0.002 sec)

MariaDB [toyu]> SELECT DISTINCT s.fname, s.lname, c.classId, e.grade
    -> FROM student AS s INNER JOIN enroll e ON (s.stuId = e.stuId) -- Join condition
    ->     INNER JOIN class AS c ON (e.classId = c.classId) -- Join condition
    -> WHERE c.semester = 'Fall' -- Problem condition
    -> AND c.year = 2019; -- Problem condition
+---------+---------+---------+-------+
| fname   | lname   | classId | grade |
+---------+---------+---------+-------+
| Tony    | Hawk    |   10000 | A     |
| Mary    | Hawk    |   10000 | NULL  |
| David   | Hawk    |   10000 | B-    |
| Tony    | Hawk    |   10001 | A     |
| Mary    | Hawk    |   10001 | A-    |
| Tony    | Hawk    |   10002 | B+    |
| David   | Hawk    |   10002 | B+    |
| Tony    | Hawk    |   10003 | C     |
| David   | Hawk    |   10003 | D     |
| Larry   | Johnson |   10003 | A     |
| Linda   | Johnson |   10003 | NULL  |
| Tony    | Hawk    |   10004 | A-    |
| Larry   | Johnson |   10004 | B+    |
| Linda   | Johnson |   10004 | A-    |
| Lillian | Johnson |   10004 | C+    |
| Linda   | Johnson |   10005 | A-    |
| Lillian | Johnson |   10005 | A     |
| Linda   | Johnson |   10006 | B+    |
| Ben     | Zico    |   10007 | F     |
| Bill    | Ching   |   10007 | C-    |
| Ben     | Zico    |   10008 | A-    |
+---------+---------+---------+-------+
21 rows in set (0.001 sec)

MariaDB [toyu]> 
MariaDB [toyu]> SELECT DISTINCT s.fname, s.lname, c.classId, e.grade
    -> FROM student AS s INNER JOIN enroll e USING (stuId) -- Join condition
    ->     INNER JOIN class AS c USING (classId) -- Join condition
    -> WHERE c.semester = 'Fall' -- Problem condition
    -> AND c.year = 2019; -- Problem condition
+---------+---------+---------+-------+
| fname   | lname   | classId | grade |
+---------+---------+---------+-------+
| Tony    | Hawk    |   10000 | A     |
| Mary    | Hawk    |   10000 | NULL  |
| David   | Hawk    |   10000 | B-    |
| Tony    | Hawk    |   10001 | A     |
| Mary    | Hawk    |   10001 | A-    |
| Tony    | Hawk    |   10002 | B+    |
| David   | Hawk    |   10002 | B+    |
| Tony    | Hawk    |   10003 | C     |
| David   | Hawk    |   10003 | D     |
| Larry   | Johnson |   10003 | A     |
| Linda   | Johnson |   10003 | NULL  |
| Tony    | Hawk    |   10004 | A-    |
| Larry   | Johnson |   10004 | B+    |
| Linda   | Johnson |   10004 | A-    |
| Lillian | Johnson |   10004 | C+    |
| Linda   | Johnson |   10005 | A-    |
| Lillian | Johnson |   10005 | A     |
| Linda   | Johnson |   10006 | B+    |
| Ben     | Zico    |   10007 | F     |
| Bill    | Ching   |   10007 | C-    |
| Ben     | Zico    |   10008 | A-    |
+---------+---------+---------+-------+
21 rows in set (0.002 sec)

MariaDB [toyu]> SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS student,
    ->     d.deptName AS `minor department`
    -> FROM student AS s INNER 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 |
| Linda Johnson   | English                      |
| Lillian Johnson | Information Technology       |
| Linda King      | Computer Science             |
+-----------------+------------------------------+
7 rows in set (0.001 sec)

MariaDB [toyu]> SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS student,
    ->     d.deptName 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   | NULL                         |
| Linda Johnson   | English                      |
| Lillian Johnson | Information Technology       |
| Ben Zico        | NULL                         |
| Bill Ching      | NULL                         |
| Linda King      | Computer Science             |
| Cathy Johanson  | NULL                         |
+-----------------+------------------------------+
11 rows in set (0.001 sec)

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

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.003 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 *
    -> 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]> SELECT DISTINCT MAX(ach) FROM student;
+----------+
| MAX(ach) |
+----------+
|      125 |
+----------+
1 row in set (0.000 sec)

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 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.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.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 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.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]> -- 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]> 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.033 sec)

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

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

MariaDB [toyu]> notee