MariaDB [toyu]> SELECT DISTINCT MAX(ach) FROM student; +----------+ | MAX(ach) | +----------+ | 125 | +----------+ 1 row in set (0.014 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.003 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]> WHERE major = 'CSCI';SELECT CONCAT(fname, ' ', lname) FROM student; 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 'WHERE major = 'CSCI'' at line 1 +---------------------------+ | 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.000 sec) MariaDB [toyu]> WHERE major = 'CSCI';SELECT CONCAT(fname, ' ', lname) FROM student 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 'WHERE major = 'CSCI'' at line 1 -> WHERE major = 'CSCI'; +---------------------------+ | CONCAT(fname, ' ', lname) | +---------------------------+ | Tony Hawk | | Mary Hawk | | David Hawk | +---------------------------+ 3 rows in set (0.005 sec) MariaDB [toyu]> SELECT CONCAT(fname, ' ', lname) FROM student -> WHERE major = 'CSCI'; +---------------------------+ | CONCAT(fname, ' ', lname) | +---------------------------+ | Tony Hawk | | Mary Hawk | | David Hawk | +---------------------------+ 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 MAX(ach), COUNT(*) FROM student; +----------+----------+ | MAX(ach) | COUNT(*) | +----------+----------+ | 125 | 11 | +----------+----------+ 1 row in set (0.002 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.004 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), COUNT(*) FROM student); ERROR 4078 (HY000): Illegal parameter data types bigint and row for operation '>=' 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.005 sec) MariaDB [toyu]> SELECT DISTINCT s.stuId, -> CONCAT(s.fname, ' ', s.lname) AS student, -> s.ach AS credits, m.max -> 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 | max | +--------+---------------+---------+------+ | 100002 | David Hawk | 66 | 125 | | 100004 | Larry Johnson | 66 | 125 | | 100008 | Bill Ching | 90 | 125 | | 100009 | Linda King | 125 | 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 | | 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]> 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.003 sec) MariaDB [toyu]> SELECT MAX(ach) AS max FROM student; +------+ | max | +------+ | 125 | +------+ 1 row in set (0.000 sec) MariaDB [toyu]> WITH t1 AS -> (SELECT MAX(ach) AS max FROM student), -> SELECT s.stuId, -> s.ach AS `ach credits`, -> t1.max - s.ach AS diff, -> s.major -> FROM student AS s, t1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT s.stuId, s.ach AS `ach credits`, t1.max - s.ach AS diff, ...' at line 3 MariaDB [toyu]> WITH t1 AS -> (SELECT MAX(ach) AS max FROM student) -> SELECT s.stuId, -> s.ach AS `ach credits`, -> t1.max - s.ach AS diff, -> s.major -> FROM student AS s, t1; +--------+-------------+------+-------+ | stuId | ach credits | diff | major | +--------+-------------+------+-------+ | 100000 | 40 | 85 | CSCI | | 100001 | 35 | 90 | CSCI | | 100002 | 66 | 59 | CSCI | | 100003 | 20 | 105 | ITEC | | 100004 | 66 | 59 | ITEC | | 100005 | 13 | 112 | CINF | | 100006 | 18 | 107 | CINF | | 100007 | 16 | 109 | NULL | | 100008 | 90 | 35 | ARTS | | 100009 | 125 | 0 | ARTS | | 100111 | 0 | 125 | NULL | +--------+-------------+------+-------+ 11 rows in set (0.001 sec) MariaDB [toyu]> WITH t1 AS -> (SELECT MAX(ach) AS max FROM student), -> t2 AS -> (SELECT s.stuId, -> s.ach AS `ach credits`, -> t1.max - s.ach AS diff, -> s.major -> FROM student AS s, t1) -> SELECT t2.stuId, t2.`ach credits`, -> t2.diff AS `diff from max credits of all`, -> d.deptName AS department -> FROM t2 LEFT JOIN department d ON (t2.major = d.deptCode) -> ORDER BY t2.`ach credits` DESC; +--------+-------------+------------------------------+------------------------------+ | stuId | ach credits | diff from max credits of all | department | +--------+-------------+------------------------------+------------------------------+ | 100009 | 125 | 0 | Arts | | 100008 | 90 | 35 | Arts | | 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.001 sec) MariaDB [toyu]> CREATE SCHEMA CTETinker; Query OK, 1 row affected (0.004 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.020 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.001 sec) MariaDB [toyu]> INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES -> ('E50', 'E15'); Query OK, 1 row affected (0.001 sec) MariaDB [toyu]> INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES -> ('E75', 'E50'); Query OK, 1 row affected (0.002 sec) MariaDB [toyu]> INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES -> ('E100', 'E75'); Query OK, 1 row affected (0.002 sec) MariaDB [toyu]> INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES -> ('E102', 'E75'); Query OK, 1 row affected (0.001 sec) MariaDB [toyu]> INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES -> ('E70', 'E50'); Query OK, 1 row affected (0.001 sec) MariaDB [toyu]> INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES -> ('E103', 'E70'); Query OK, 1 row affected (0.002 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT * FROM Employee; +-------+-----------------+ | EmpId | SupervisorEmpId | +-------+-----------------+ | E3 | NULL | | E50 | E15 | | E15 | E3 | | E70 | E50 | | E75 | E50 | | E103 | E70 | | E100 | E75 | | E102 | E75 | +-------+-----------------+ 8 rows in set (0.001 sec) MariaDB [toyu]> 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.stuId, s.major -> FROM student AS s -> WHERE s.ach > 20; +--------+-------+ | stuId | major | +--------+-------+ | 100000 | CSCI | | 100001 | CSCI | | 100002 | CSCI | | 100004 | ITEC | | 100008 | ARTS | | 100009 | ARTS | +--------+-------+ 6 rows in set (0.000 sec) MariaDB [toyu]> SELECT DISTINCT s.stuId, s.major -> FROM student AS s -- [1] Big table -> WHERE s.ach > 20 -- [2] filter the row in the big table -> GROUP BY s.major -- [3] form group: each s.major value -> 1 group -> -- group becomes the new row. -> ; +--------+-------+ | stuId | major | +--------+-------+ | 100008 | ARTS | | 100000 | CSCI | | 100004 | ITEC | +--------+-------+ 3 rows in set (0.000 sec) MariaDB [toyu]> SELECT DISTINCT COUNT(s.stuId), s.major -- COUNT: group function -> FROM student AS s -- [1] Big table -> WHERE s.ach > 20 -- [2] filter the row in the big table -> GROUP BY s.major -- [3] form group: each s.major value -> 1 group -> -- group becomes the new row. -> ; +----------------+-------+ | COUNT(s.stuId) | major | +----------------+-------+ | 2 | ARTS | | 3 | CSCI | | 1 | ITEC | +----------------+-------+ 3 rows in set (0.001 sec) MariaDB [toyu]> SELECT DISTINCT s.stuId, s.major -- not good as stuId is for the old raw row, not the new row (group) -> FROM student AS s -- [1] Big table -> WHERE s.ach > 20 ; +--------+-------+ | stuId | major | +--------+-------+ | 100000 | CSCI | | 100001 | CSCI | | 100002 | CSCI | | 100004 | ITEC | | 100008 | ARTS | | 100009 | ARTS | +--------+-------+ 6 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT DISTINCT COUNT(s.stuId), s.major -- COUNT: group function -> FROM student AS s -- [1] Big table -> WHERE s.ach > 20 -- [2] filter the row in the big table -> GROUP BY s.major -- [3] form group: each s.major value -> 1 group -> -- group becomes the new row. -> ; +----------------+-------+ | COUNT(s.stuId) | major | +----------------+-------+ | 2 | ARTS | | 3 | CSCI | | 1 | ITEC | +----------------+-------+ 3 rows in set (0.000 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT DISTINCT s.major, COUNT(s.stuId) AS `Number of majors` -- COUNT: group function -> FROM student AS s -- [1] Big table -> WHERE s.ach > 20 -- [2] filter the row in the big table -> GROUP BY s.major -- [3] form group: each s.major value -> 1 group -> -- group becomes the new row. -> ; +-------+------------------+ | major | Number of majors | +-------+------------------+ | ARTS | 2 | | CSCI | 3 | | ITEC | 1 | +-------+------------------+ 3 rows in set (0.000 sec) MariaDB [toyu]> SELECT MAX(ach) AS max FROM student; +------+ | max | +------+ | 125 | +------+ 1 row in set (0.001 sec) MariaDB [toyu]> -- MAX: group function: need a group. THe group is the entire student table. MariaDB [toyu]> SELECT MAX(ach) AS max FROM student; +------+ | max | +------+ | 125 | +------+ 1 row in set (0.000 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT DISTINCT s.stuId, s.major -- not good as stuId is for the old raw row, not the new row (group) -> FROM student AS s -- [1] Big table -> WHERE s.ach > 20 ; +--------+-------+ | stuId | major | +--------+-------+ | 100000 | CSCI | | 100001 | CSCI | | 100002 | CSCI | | 100004 | ITEC | | 100008 | ARTS | | 100009 | ARTS | +--------+-------+ 6 rows in set (0.000 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT DISTINCT s.major, COUNT(s.stuId) AS `Number of majors`, -> GROUP_CONCAT(s.stuId) AS `ids of students in the major` -> -- COUNT: group function; can only use Group functions -> FROM student AS s -- [1] Big table -> WHERE s.ach > 20 -- [2] filter the row in the big table -> GROUP BY s.major -- [3] form group: each s.major value -> 1 group -> -- group becomes the new row. -> ; +-------+------------------+------------------------------+ | major | Number of majors | ids of students in the major | +-------+------------------+------------------------------+ | ARTS | 2 | 100008,100009 | | CSCI | 3 | 100000,100001,100002 | | ITEC | 1 | 100004 | +-------+------------------+------------------------------+ 3 rows in set (0.001 sec) MariaDB [toyu]> SELECT DISTINCT s.major, COUNT(s.stuId) AS `Number of majors`, -> GROUP_CONCAT(s.stuId) AS `ids of students in the major` -> -- COUNT: group function; can only use Group functions -> FROM student AS s -- [1] Big table -> WHERE s.ach > 20 -- [2] filter the row in the big table -> GROUP BY s.major -- [3] form group: each s.major value -> 1 group -> -- group becomes the new row. -> ORDER BY `Number of majors`; +-------+------------------+------------------------------+ | major | Number of majors | ids of students in the major | +-------+------------------+------------------------------+ | ITEC | 1 | 100004 | | ARTS | 2 | 100008,100009 | | CSCI | 3 | 100000,100001,100002 | +-------+------------------+------------------------------+ 3 rows in set (0.011 sec) MariaDB [toyu]> SELECT DISTINCT s.major, COUNT(s.stuId) AS `Number of majors`, -> GROUP_CONCAT(s.stuId) AS `ids of students in the major` -> -- COUNT: group function; can only use Group functions -> FROM student AS s -- [1] Big table -> WHERE s.ach > 20 -- [2] filter the row in the big table -> GROUP BY s.major -- [3] form group: each s.major value -> 1 group -> -- group becomes the new row. -> ORDER BY `Number of majors` DESC; +-------+------------------+------------------------------+ | major | Number of majors | ids of students in the major | +-------+------------------+------------------------------+ | CSCI | 3 | 100000,100001,100002 | | ARTS | 2 | 100008,100009 | | ITEC | 1 | 100004 | +-------+------------------+------------------------------+ 3 rows in set (0.009 sec) MariaDB [toyu]> SELECT DISTINCT s.major, COUNT(s.stuId) AS `Number of majors`, -> GROUP_CONCAT(s.stuId) AS `ids of students in the major` -> -- COUNT: group function; can only use Group functions -> FROM student AS s -- [1] Big table -> WHERE s.ach > 20 -- [2] filter the row in the big table -> GROUP BY s.major -- [3] form group: each s.major value -> 1 group -> -- group becomes the new row. -> HAVING `Number of majors` > 1 -> ORDER BY `Number of majors` DESC; +-------+------------------+------------------------------+ | major | Number of majors | ids of students in the major | +-------+------------------+------------------------------+ | CSCI | 3 | 100000,100001,100002 | | ARTS | 2 | 100008,100009 | +-------+------------------+------------------------------+ 2 rows in set (0.008 sec) MariaDB [toyu]>