MariaDB [toyu]> MariaDB [toyu]> MariaDB [toyu]> -- operators: MariaDB [toyu]> -- student with credits in a range. MariaDB [toyu]> SELECT DISTINCT * -> FROM Student -> WHERE credits BETWEEN 30 AND 70; ERROR 1054 (42S22): Unknown column 'credits' in 'where clause' MariaDB [toyu]> 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.005 sec) MariaDB [toyu]> 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.000 sec) 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]> 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]> SELECT DISTINCT S.StuId, IF(s.ach <= 60, 'lower', 'upper') AS level -> FROM student AS s; +--------+-------+ | StuId | level | +--------+-------+ | 100000 | lower | | 100001 | lower | | 100002 | upper | | 100003 | lower | | 100004 | upper | | 100005 | lower | | 100006 | lower | | 100007 | lower | | 100008 | upper | | 100009 | upper | | 100111 | lower | +--------+-------+ 11 rows in set (0.003 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.007 sec) MariaDB [toyu]> 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]> -- List the names of the students with their minors (in full name). MariaDB [toyu]> -- Student with no department not listed. MariaDB [toyu]> SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS student, -> d.deptName AS `minor department` -> -> ; ERROR 1109 (42S02): Unknown table 's' in field list MariaDB [toyu]> SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS student, -> d.deptName AS `minor department`; ERROR 1109 (42S02): Unknown table 's' in field list 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.002 sec) MariaDB [toyu]> -- List the names of the students with their minors (in full name). 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.002 sec) MariaDB [toyu]> -- subqueries in the WHERE course MariaDB [toyu]> -- students not enrolled in any class. MariaDB [toyu]> SELECT DISTINCT * -> FROM student AS s -> WHERE s.stuId NOT IN (SELECT DISTINCT e.stuID FROM enroll AS e); +--------+-----------+----------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+-----------+----------+-------+-------+------+---------+ | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | +--------+-----------+----------+-------+-------+------+---------+ 3 rows in set (0.008 sec) MariaDB [toyu]> MariaDB [toyu]> -- student with the maximum number of ach. MariaDB [toyu]> SELECT DISTINCT MAX(ach) -> FROM student; +----------+ | MAX(ach) | +----------+ | 125 | +----------+ 1 row in set (0.002 sec) MariaDB [toyu]> MariaDB [toyu]> -- student within 60 credits of the maximum number of ach any student may have. MariaDB [toyu]> SELECT DISTINCT s.stuId, -> CONCAT(s.fname, ' ', s.lname) AS student, -> s.ach AS credits -> FROM student AS s -> WHERE s.ach + 60 >= -> (SELECT DISTINCT MAX(ach) FROM student); +--------+---------------+---------+ | stuId | student | credits | +--------+---------------+---------+ | 100002 | David Hawk | 66 | | 100004 | Larry Johnson | 66 | | 100008 | Bill Ching | 90 | | 100009 | Linda King | 125 | +--------+---------------+---------+ 4 rows in set (0.002 sec) MariaDB [toyu]> MariaDB [toyu]> -- subqueries as derived tables. MariaDB [toyu]> SELECT DISTINCT s.stuId, -> CONCAT(s.fname, ' ', s.lname) AS student, -> s.ach AS credits -> FROM student AS s INNER JOIN -> (SELECT DISTINCT MAX(ach) AS max FROM student) AS m -- an alias is required. -> WHERE s.ach + 60 >= m.max; +--------+---------------+---------+ | stuId | student | credits | +--------+---------------+---------+ | 100002 | David Hawk | 66 | | 100004 | Larry Johnson | 66 | | 100008 | Bill Ching | 90 | | 100009 | Linda King | 125 | +--------+---------------+---------+ 4 rows in set (0.003 sec) MariaDB [toyu]> notee