MariaDB [toyu]> drop schema swim; Query OK, 13 rows affected (0.201 sec) MariaDB [toyu]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | phpmyadmin | | test | | toyu | +--------------------+ 6 rows in set (0.004 sec) MariaDB [toyu]> source createSwimDB.sql Query OK, 0 rows affected (0.003 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected, 1 warning (0.001 sec) Query OK, 1 row affected (0.003 sec) Database changed 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.001 sec) Query OK, 0 rows affected, 1 warning (0.000 sec) Query OK, 0 rows affected, 1 warning (0.002 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.000 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.000 sec) Query OK, 0 rows affected, 1 warning (0.031 sec) Query OK, 0 rows affected, 1 warning (0.001 sec) Query OK, 0 rows affected, 1 warning (0.026 sec) Query OK, 0 rows affected, 1 warning (0.001 sec) Query OK, 0 rows affected, 1 warning (0.028 sec) Query OK, 0 rows affected, 1 warning (0.001 sec) Query OK, 0 rows affected, 1 warning (0.033 sec) Query OK, 0 rows affected, 1 warning (0.001 sec) Query OK, 0 rows affected, 1 warning (0.033 sec) Query OK, 0 rows affected, 1 warning (0.001 sec) Query OK, 0 rows affected, 1 warning (0.030 sec) Query OK, 0 rows affected, 1 warning (0.001 sec) Query OK, 0 rows affected, 1 warning (0.033 sec) Query OK, 0 rows affected, 1 warning (0.001 sec) Query OK, 0 rows affected, 1 warning (0.035 sec) Query OK, 0 rows affected, 1 warning (0.000 sec) Query OK, 0 rows affected, 1 warning (0.028 sec) Query OK, 0 rows affected, 1 warning (0.000 sec) Query OK, 0 rows affected, 1 warning (0.040 sec) Query OK, 0 rows affected, 1 warning (0.000 sec) Query OK, 0 rows affected, 1 warning (0.030 sec) Query OK, 0 rows affected, 1 warning (0.000 sec) Query OK, 0 rows affected, 1 warning (0.034 sec) Query OK, 0 rows affected, 1 warning (0.001 sec) Query OK, 0 rows affected, 1 warning (0.040 sec) Query OK, 5 rows affected (0.004 sec) Records: 5 Duplicates: 0 Warnings: 0 Query OK, 6 rows affected (0.011 sec) Records: 6 Duplicates: 0 Warnings: 0 Query OK, 10 rows affected (0.005 sec) Records: 10 Duplicates: 0 Warnings: 0 Query OK, 3 rows affected (0.006 sec) Records: 3 Duplicates: 0 Warnings: 0 Query OK, 6 rows affected (0.004 sec) Records: 6 Duplicates: 0 Warnings: 0 Query OK, 6 rows affected (0.010 sec) Records: 6 Duplicates: 0 Warnings: 0 Query OK, 9 rows affected (0.006 sec) Records: 9 Duplicates: 0 Warnings: 0 Query OK, 5 rows affected (0.005 sec) Records: 5 Duplicates: 0 Warnings: 0 Query OK, 3 rows affected (0.006 sec) Records: 3 Duplicates: 0 Warnings: 0 Query OK, 11 rows affected (0.008 sec) Records: 11 Duplicates: 0 Warnings: 0 Query OK, 1 row affected (0.005 sec) Query OK, 1 row affected (0.005 sec) Query OK, 1 row affected (0.005 sec) Query OK, 1 row affected (0.003 sec) Query OK, 1 row affected (0.004 sec) Query OK, 1 row affected (0.003 sec) Query OK, 1 row affected (0.003 sec) Query OK, 1 row affected (0.003 sec) Query OK, 1 row affected (0.003 sec) Query OK, 1 row affected (0.004 sec) Query OK, 1 row affected (0.004 sec) Query OK, 1 row affected (0.005 sec) Query OK, 1 row affected (0.003 sec) Query OK, 1 row affected (0.003 sec) Query OK, 1 row affected (0.003 sec) Query OK, 1 row affected (0.010 sec) Query OK, 1 row affected (0.034 sec) Query OK, 1 row affected (0.003 sec) Query OK, 1 row affected (0.004 sec) Query OK, 1 row affected (0.003 sec) Query OK, 4 rows affected (0.004 sec) Records: 4 Duplicates: 0 Warnings: 0 Query OK, 1 row affected (0.007 sec) Query OK, 1 row affected (0.004 sec) Query OK, 1 row affected (0.003 sec) Query OK, 1 row affected (0.004 sec) Query OK, 1 row affected (0.003 sec) Query OK, 1 row affected (0.003 sec) Query OK, 1 row affected (0.003 sec) Query OK, 1 row affected (0.003 sec) Query OK, 1 row affected (0.005 sec) Query OK, 6 rows affected (0.005 sec) Records: 6 Duplicates: 0 Warnings: 0 Query OK, 3 rows affected (0.004 sec) Records: 3 Duplicates: 0 Warnings: 0 Query OK, 2 rows affected (0.003 sec) Records: 2 Duplicates: 0 Warnings: 0 Query OK, 2 rows affected (0.004 sec) Records: 2 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 [SWIM]> MariaDB [SWIM]> CREATE TEMPORARY TABLE t1 -> (A INT); Query OK, 0 rows affected (0.008 sec) MariaDB [SWIM]> CREATE TEMPORARY TABLE t1 -> (A NOT NULL INT); 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 'NOT NULL INT)' at line 2 MariaDB [SWIM]> CREATE TEMPORARY TABLE IF NOT EXISTS t1 -> (A INT NOT NULL); Query OK, 0 rows affected, 1 warning (0.000 sec) MariaDB [SWIM]> SET @dept = 'CSCI'; Query OK, 0 rows affected (0.001 sec) MariaDB [SWIM]> SELECT @dept; +-------+ | @dept | +-------+ | CSCI | +-------+ 1 row in set (0.000 sec) MariaDB [SWIM]> SELECT * -> FROM student -> WHERE major = @dept; ERROR 1146 (42S02): Table 'swim.student' doesn't exist MariaDB [SWIM]> use toyu Database changed MariaDB [toyu]> SELECT * -> FROM student -> WHERE major = @dept; +--------+-------+-------+-------+-------+------+---------+ | 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 | +--------+-------+-------+-------+-------+------+---------+ 3 rows in set (0.002 sec) MariaDB [toyu]> select * from t1; ERROR 1146 (42S02): Table 'toyu.t1' doesn't exist MariaDB [toyu]> CREATE TEMPORARY TABLE t1 -> (A INT); Query OK, 0 rows affected (0.005 sec) MariaDB [toyu]> select * from t1; Empty set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> CREATE TABLE s1 -> AS SELECT * FROM student; Query OK, 11 rows affected (0.038 sec) Records: 11 Duplicates: 0 Warnings: 0 MariaDB [toyu]> SELECT * FROM s1; +--------+-----------+----------+-------+-------+------+---------+ | 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]> DESC s1; +---------+-------------+------+-----+---------+-------+ | 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) | YES | | 0 | | | advisor | int(11) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 7 rows in set (0.013 sec) MariaDB [toyu]> DESC 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) | YES | | 0 | | | advisor | int(11) | YES | MUL | NULL | | +---------+-------------+------+-----+---------+-------+ 7 rows in set (0.011 sec) MariaDB [toyu]> CREATE TABLE s2 -> LIKE student; Query OK, 0 rows affected (0.032 sec) MariaDB [toyu]> DESC s2; +---------+-------------+------+-----+---------+-------+ | 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) | YES | | 0 | | | advisor | int(11) | YES | MUL | NULL | | +---------+-------------+------+-----+---------+-------+ 7 rows in set (0.035 sec) MariaDB [toyu]> SELECT * FROM s2; Empty set (0.001 sec) MariaDB [toyu]> INSERT INTO s2 -> SELECT * FROM student; Query OK, 11 rows affected (0.007 sec) Records: 11 Duplicates: 0 Warnings: 0 MariaDB [toyu]> 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.000 sec) MariaDB [toyu]> UPDATE ENROLL -> SET grade = 'A' -> WHERE stuId = 100000 -> AND classId = 10002; Query OK, 1 row affected (0.005 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [toyu]> UPDATE ENROLL -> SET grade = 'A'; Query OK, 17 rows affected (0.013 sec) Rows matched: 22 Changed: 17 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, 13 rows affected (0.199 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.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.000 sec) Query OK, 0 rows affected, 1 warning (0.000 sec) Query OK, 0 rows affected, 1 warning (0.030 sec) Query OK, 0 rows affected, 1 warning (0.029 sec) Query OK, 0 rows affected, 1 warning (0.035 sec) Query OK, 0 rows affected, 1 warning (0.027 sec) Query OK, 0 rows affected, 1 warning (0.032 sec) Query OK, 0 rows affected, 1 warning (0.030 sec) Query OK, 0 rows affected, 1 warning (0.037 sec) Query OK, 0 rows affected, 1 warning (0.036 sec) Query OK, 15 rows affected (0.004 sec) Records: 15 Duplicates: 0 Warnings: 0 Query OK, 4 rows affected (0.007 sec) Records: 4 Duplicates: 0 Warnings: 0 Query OK, 7 rows affected (0.005 sec) Records: 7 Duplicates: 0 Warnings: 0 Query OK, 11 rows affected (0.004 sec) Records: 11 Duplicates: 0 Warnings: 0 Query OK, 11 rows affected (0.010 sec) Records: 11 Duplicates: 0 Warnings: 0 Query OK, 14 rows affected (0.012 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.001 sec) Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> SELECT * -> FROM student -> WHERE ach >= 30 -> AND ach <= 60; +--------+-------+-------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+-------+-------+-------+-------+------+---------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | +--------+-------+-------+-------+-------+------+---------+ 2 rows in set (0.001 sec) MariaDB [toyu]> SELECT * -> FROM student -> WHERE ach BETWEEN 30 AND 60; +--------+-------+-------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+-------+-------+-------+-------+------+---------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | +--------+-------+-------+-------+-------+------+---------+ 2 rows in set (0.001 sec) MariaDB [toyu]> SELECT * -> FROM student -> WHERE ach BETWEEN 13 AND 18; +--------+---------+---------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+---------+---------+-------+-------+------+---------+ | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | +--------+---------+---------+-------+-------+------+---------+ 3 rows in set (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.005 sec) MariaDB [toyu]> SELECT DISTINCT * -> FROM student AS s; +--------+-----------+----------+-------+-------+------+---------+ | 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]> 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.001 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.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 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 -> WHERE s.ach + 60 >= t1.max -> 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 | +--------+-------------+------------------------------+ 4 rows in set (0.000 sec) MariaDB [toyu]> WITH cte (col1, col2) AS -> ( -> SELECT 1, 2 -> UNION ALL -> SELECT 3, 4 -> ) -> SELECT col1, col2 FROM cte; +------+------+ | col1 | col2 | +------+------+ | 1 | 2 | | 3 | 4 | +------+------+ 2 rows in set (0.000 sec) MariaDB [toyu]> CREATE SCHEMA CTETinker; Query OK, 1 row affected (0.003 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.046 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.003 sec) MariaDB [toyu]> INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES -> ('E75', 'E50'); Query OK, 1 row affected (0.003 sec) MariaDB [toyu]> INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES -> ('E100', 'E75'); Query OK, 1 row affected (0.003 sec) MariaDB [toyu]> INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES -> ('E102', 'E75'); Query OK, 1 row affected (0.003 sec) MariaDB [toyu]> INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES -> ('E70', 'E50'); Query OK, 1 row affected (0.004 sec) MariaDB [toyu]> INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES -> ('E103', 'E70'); Query OK, 1 row affected (0.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.002 sec) MariaDB [toyu]> SELECT SupervisorEmpId AS SEId FROM Employee AS e WHERE e.EmpId = 'E100'; +------+ | SEId | +------+ | E75 | +------+ 1 row in set (0.000 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 DISTINCT s.major -> FROM student AS s -> WHERE s.ach > 15 -> GROUP BY s.major; +-------+ | major | +-------+ | NULL | | ARTS | | CINF | | CSCI | | ITEC | +-------+ 5 rows in set (0.001 sec) MariaDB [toyu]> SELECT DISTINCT s.major, COUNT(s.stuId) AS `Number of students` -> FROM student AS s -> WHERE s.ach > 15 -> GROUP BY s.major; +-------+--------------------+ | major | Number of students | +-------+--------------------+ | NULL | 1 | | ARTS | 2 | | CINF | 1 | | CSCI | 3 | | ITEC | 2 | +-------+--------------------+ 5 rows in set (0.001 sec) MariaDB [toyu]> SELECT DISTINCT s.major, COUNT(s.stuId) AS `Number of students` -> FROM student AS s -> WHERE s.ach > 15 -> GROUP BY s.major -> HAVING `Number of students` >= 2; +-------+--------------------+ | major | Number of students | +-------+--------------------+ | ARTS | 2 | | CSCI | 3 | | ITEC | 2 | +-------+--------------------+ 3 rows in set (0.001 sec) MariaDB [toyu]> SELECT * -> FROM student AS s LEFT JOIN enroll AS e USING (stuId); +--------+-----------+----------+-------+-------+------+---------+---------+-------+----------+ | stuId | fname | lname | major | minor | ach | advisor | classId | grade | n_alerts | +--------+-----------+----------+-------+-------+------+---------+---------+-------+----------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 10000 | A | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 10001 | A | 2 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 10002 | B+ | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 10003 | C | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 10004 | A- | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 11001 | D | 4 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 10000 | NULL | NULL | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 10001 | A- | 0 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 10000 | B- | 3 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 10002 | B+ | 2 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 10003 | D | 4 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | NULL | NULL | NULL | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 10003 | A | 0 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 10004 | B+ | NULL | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 10003 | NULL | NULL | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 10004 | A- | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 10005 | A- | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 10006 | B+ | NULL | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 10004 | C+ | NULL | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 10005 | A | NULL | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 10007 | F | 4 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 10008 | A- | 0 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | 10007 | C- | 0 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | NULL | NULL | NULL | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | NULL | NULL | NULL | +--------+-----------+----------+-------+-------+------+---------+---------+-------+----------+ 25 rows in set (0.001 sec) MariaDB [toyu]> SELECT * -> FROM student AS s LEFT JOIN enroll AS e USING (stuId) -> GROUP BY s.stuId; +--------+-----------+----------+-------+-------+------+---------+---------+-------+----------+ | stuId | fname | lname | major | minor | ach | advisor | classId | grade | n_alerts | +--------+-----------+----------+-------+-------+------+---------+---------+-------+----------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 10000 | A | 0 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 10000 | NULL | NULL | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 10000 | B- | 3 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | NULL | NULL | NULL | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 10003 | A | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 10003 | NULL | NULL | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 10004 | C+ | NULL | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 10007 | F | 4 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | 10007 | C- | 0 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | NULL | NULL | NULL | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | NULL | NULL | NULL | +--------+-----------+----------+-------+-------+------+---------+---------+-------+----------+ 11 rows in set (0.001 sec) MariaDB [toyu]> SELECT s.StuId, COUNT(e.classId) AS `Number of classes` -> FROM student AS s LEFT JOIN enroll AS e USING (stuId) -> GROUP BY s.stuId; +--------+-------------------+ | StuId | Number of classes | +--------+-------------------+ | 100000 | 6 | | 100001 | 2 | | 100002 | 3 | | 100003 | 0 | | 100004 | 2 | | 100005 | 4 | | 100006 | 2 | | 100007 | 2 | | 100008 | 1 | | 100009 | 0 | | 100111 | 0 | +--------+-------------------+ 11 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT s.StuId, COUNT(e.classId) AS `Number of classes` -> FROM student AS s LEFT JOIN enroll AS e USING (stuId) -> GROUP BY s.stuId -> HAVING `Number of classes` >= 1 -> ORDER `Number of classes` DESC; 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 '`Number of classes` DESC' at line 5 MariaDB [toyu]> SELECT s.StuId, COUNT(e.classId) AS `Number of classes` -> FROM student AS s LEFT JOIN enroll AS e USING (stuId) -> GROUP BY s.stuId -> HAVING `Number of classes` >= 1 -> ORDER BY`Number of classes` DESC; +--------+-------------------+ | StuId | Number of classes | +--------+-------------------+ | 100000 | 6 | | 100005 | 4 | | 100002 | 3 | | 100006 | 2 | | 100007 | 2 | | 100001 | 2 | | 100004 | 2 | | 100008 | 1 | +--------+-------------------+ 8 rows in set (0.001 sec) MariaDB [toyu]> SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS student -> FROM student AS s INNER JOIN enroll AS e1 USING (stuId) -> INNER JOIN enroll AS e2 USING (stuId) -> WHERE e1.grade = 'A' -> AND e2.grade = 'A'; +-----------------+ | student | +-----------------+ | Tony Hawk | | Larry Johnson | | Lillian Johnson | +-----------------+ 3 rows in set (0.003 sec) MariaDB [toyu]>