MariaDB [(none)]> MariaDB [(none)]> use toyu; Database changed MariaDB [toyu]> 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.008 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 | | 100004 | 66 | 59 | | 100002 | 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]> SELECT MAX(ach) AS max FROM student; +------+ | max | +------+ | 125 | +------+ 1 row 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 `diff from max credits of all` DESC, s.stuId ASC; +--------+-------------+------------------------------+ | stuId | ach credits | diff from max credits of all | +--------+-------------+------------------------------+ | 100111 | 0 | 125 | | 100005 | 13 | 112 | | 100007 | 16 | 109 | | 100006 | 18 | 107 | | 100003 | 20 | 105 | | 100001 | 35 | 90 | | 100000 | 40 | 85 | | 100002 | 66 | 59 | | 100004 | 66 | 59 | | 100008 | 90 | 35 | | 100009 | 125 | 0 | +--------+-------------+------------------------------+ 11 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 | | 100004 | 66 | 59 | Information Technology | | 100002 | 66 | 59 | Computer Science | | 100000 | 40 | 85 | Computer Science | | 100001 | 35 | 90 | Computer Science | | 100003 | 20 | 105 | Information Technology | | 100006 | 18 | 107 | Computer Information Systems | | 100007 | 16 | 109 | NULL | | 100005 | 13 | 112 | Computer Information Systems | | 100111 | 0 | 125 | NULL | +--------+-------------+------------------------------+------------------------------+ 11 rows in set (0.002 sec) MariaDB [toyu]> CREATE TABLE tt1 AS -> SELECT MAX(ach) AS max FROM student; Query OK, 1 row affected (0.029 sec) Records: 1 Duplicates: 0 Warnings: 0 MariaDB [toyu]> SELECT * FROM tt1; +------+ | max | +------+ | 125 | +------+ 1 row in set (0.001 sec) MariaDB [toyu]> CREATE 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.045 sec) Records: 11 Duplicates: 0 Warnings: 0 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.000 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]> SELECT 1 UNION SELECT 5; +---+ | 1 | +---+ | 1 | | 5 | +---+ 2 rows in set (0.001 sec) MariaDB [toyu]> DROP SCHEMA IF EXISTS CTEtinker; Query OK, 0 rows affected (0.004 sec) MariaDB [toyu]> CREATE SCHEMA CTETinker; Query OK, 1 row affected (0.006 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.040 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.004 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.004 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.001 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 f.deptCode, f.facId, f.rank -> FROM faculty AS f -> WHERE f.deptCode IN ('CSCI', 'CINF', 'ITEC'); +----------+-------+---------------------+ | deptCode | facId | rank | +----------+-------+---------------------+ | CSCI | 1011 | Professor | | CSCI | 1012 | Associate Professor | | CSCI | 1013 | NULL | | CSCI | 1014 | Assistant Professor | | CINF | 1015 | Professor | | CINF | 1016 | Associate Professor | | ITEC | 1017 | Professor | | ITEC | 1019 | Lecturer | +----------+-------+---------------------+ 8 rows in set (0.002 sec) MariaDB [toyu]> SELECT DISTINCT f.deptCode, f.facId, f.rank -- [4] -> FROM faculty AS f -- [1] -> WHERE f.deptCode IN ('CSCI', 'CINF', 'ITEC') -- [2] -> GROUP BY f.deptCode -- [3] -> ; +----------+-------+-----------+ | deptCode | facId | rank | +----------+-------+-----------+ | CINF | 1015 | Professor | | CSCI | 1011 | Professor | | ITEC | 1017 | Professor | +----------+-------+-----------+ 3 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT DISTINCT f.deptCode, COUNT(f.facId) AS numFaculty-- [4] -> FROM faculty AS f -- [1] -> WHERE f.deptCode IN ('CSCI', 'CINF', 'ITEC') -- [2] -> GROUP BY f.deptCode -- [3] -> ; +----------+------------+ | deptCode | numFaculty | +----------+------------+ | CINF | 2 | | CSCI | 4 | | ITEC | 2 | +----------+------------+ 3 rows in set (0.001 sec) MariaDB [toyu]> SELECT DISTINCT 'I am ready to go', f.deptCode, COUNT(f.facId) AS numFaculty-- [4] COUNT: group function -> FROM faculty AS f -- [1] -> WHERE f.deptCode IN ('CSCI', 'CINF', 'ITEC') -- [2] -> GROUP BY f.deptCode -- [3] -> ; +------------------+----------+------------+ | I am ready to go | deptCode | numFaculty | +------------------+----------+------------+ | I am ready to go | CINF | 2 | | I am ready to go | CSCI | 4 | | I am ready to go | ITEC | 2 | +------------------+----------+------------+ 3 rows in set (0.001 sec) MariaDB [toyu]> notee