MariaDB [toyu]> SELECT DISTINCT -> CONCAT(co.rubric, ' ', co.number) AS `course id`, -> co.title AS coourse, -> co.credits, -> c.semester, -> c.year, -> CONCAT(f.fname, ' ', f.lname) AS instructor, -> CONCAT(s.fname, ' ', s.lname) AS student, -> e.grade -> FROM course AS co INNER JOIN class AS c ON (c.courseId = co.courseId) -> INNER JOIN faculty AS f ON (c.facId = f.facId) -> INNER JOIN enroll AS e ON (c.classId = e.classId) -> INNER JOIN student AS s ON (e.stuId = s.stuId) -> WHERE co.rubric = 'CSCI'; +-----------+----------------------------+---------+----------+------+------------+------------+-------+ | course id | coourse | credits | semester | year | instructor | student | grade | +-----------+----------------------------+---------+----------+------+------------+------------+-------+ | CSCI 3333 | Data Structures | 3 | Fall | 2019 | Paul Smith | Tony Hawk | A | | CSCI 3333 | Data Structures | 3 | Fall | 2019 | Paul Smith | Mary Hawk | NULL | | CSCI 3333 | Data Structures | 3 | Fall | 2019 | Paul Smith | David Hawk | B- | | CSCI 4333 | Design of Database Systems | 3 | Fall | 2019 | Paul Smith | Tony Hawk | A | | CSCI 4333 | Design of Database Systems | 3 | Fall | 2019 | Paul Smith | Mary Hawk | A- | | CSCI 4333 | Design of Database Systems | 3 | Spring | 2020 | Mary Tran | Tony Hawk | D | | CSCI 5333 | DBMS | 3 | Fall | 2019 | Mary Tran | Tony Hawk | B+ | | CSCI 5333 | DBMS | 3 | Fall | 2019 | Mary Tran | David Hawk | B+ | +-----------+----------------------------+---------+----------+------+------------+------------+-------+ 8 rows in set (0.005 sec) MariaDB [toyu]> SELECT DISTINCT -> CONCAT(co.rubric, ' ', co.number) AS `course id`, -> co.title AS coourse, -> co.credits, -> c.semester, -> c.year, -> CONCAT(f.fname, ' ', f.lname) AS instructor, -> CONCAT(s.fname, ' ', s.lname) AS student, -> e.grade -> FROM course AS co INNER JOIN class AS c ON (c.courseId = co.courseId) -> INNER JOIN faculty AS f ON (c.facId = f.facId) -> INNER JOIN enroll AS e ON (c.classId = e.classId) -> INNER JOIN student AS s ON (e.stuId = s.stuId) -> WHERE co.rubric = 'CSCI' -> AND e.grade = 'A'; +-----------+----------------------------+---------+----------+------+------------+-----------+-------+ | course id | coourse | credits | semester | year | instructor | student | grade | +-----------+----------------------------+---------+----------+------+------------+-----------+-------+ | CSCI 3333 | Data Structures | 3 | Fall | 2019 | Paul Smith | Tony Hawk | A | | CSCI 4333 | Design of Database Systems | 3 | Fall | 2019 | Paul Smith | Tony Hawk | A | +-----------+----------------------------+---------+----------+------+------------+-----------+-------+ 2 rows in set (0.003 sec) MariaDB [toyu]> SELECT DISTINCT -> CONCAT(co.rubric, ' ', co.number) AS `course id`, -> co.title AS coourse, -> co.credits, -> c.semester, -> c.year, -> CONCAT(f.fname, ' ', f.lname) AS instructor, -> CONCAT(s.fname, ' ', s.lname) AS student, -> e.grade -> FROM course AS co INNER JOIN class AS c ON (c.courseId = co.courseId) -> INNER JOIN faculty AS f ON (c.facId = f.facId) -> INNER JOIN enroll AS e ON (c.classId = e.classId) -> INNER JOIN student AS s ON (e.stuId = s.stuId) -> WHERE co.rubric = 'CSCI' -> AND e.grade = 'A' -> OR e.grade = 'A-' -> OR e.grade = 'B+' -> OR e.grade = 'B'; -- grade of B or above. +-----------+-----------------------------+---------+----------+------+---------------+---------------+-------+ | course id | coourse | credits | semester | year | instructor | student | grade | +-----------+-----------------------------+---------+----------+------+---------------+---------------+-------+ | CSCI 3333 | Data Structures | 3 | Fall | 2019 | Paul Smith | Tony Hawk | A | | CSCI 4333 | Design of Database Systems | 3 | Fall | 2019 | Paul Smith | Tony Hawk | A | | CSCI 5333 | DBMS | 3 | Fall | 2019 | Mary Tran | Tony Hawk | B+ | | CINF 4320 | Web Application Development | 3 | Fall | 2019 | Sharon Mannes | Tony Hawk | A- | | CSCI 4333 | Design of Database Systems | 3 | Fall | 2019 | Paul Smith | Mary Hawk | A- | | CSCI 5333 | DBMS | 3 | Fall | 2019 | Mary Tran | David Hawk | B+ | | CINF 4320 | Web Application Development | 3 | Fall | 2019 | Sharon Mannes | Larry Johnson | B+ | | CINF 4320 | Web Application Development | 3 | Fall | 2019 | Sharon Mannes | Linda Johnson | A- | | ITEC 3335 | Database Development | 3 | Fall | 2019 | Daniel Kim | Linda Johnson | A- | | ITEC 3312 | Introduction to Scripting | 3 | Fall | 2019 | Benjamin Yu | Linda Johnson | B+ | | ARTS 3311 | Hindu Arts | 3 | Fall | 2019 | Art Allister | Ben Zico | A- | +-----------+-----------------------------+---------+----------+------+---------------+---------------+-------+ 11 rows in set (0.001 sec) MariaDB [toyu]> SELECT DISTINCT -> CONCAT(co.rubric, ' ', co.number) AS `course id`, -> co.title AS coourse, -> co.credits, -> c.semester, -> c.year, -> CONCAT(f.fname, ' ', f.lname) AS instructor, -> CONCAT(s.fname, ' ', s.lname) AS student, -> e.grade -> FROM course AS co INNER JOIN class AS c ON (c.courseId = co.courseId) -> INNER JOIN faculty AS f ON (c.facId = f.facId) -> INNER JOIN enroll AS e ON (c.classId = e.classId) -> INNER JOIN student AS s ON (e.stuId = s.stuId) -> WHERE (co.rubric = 'CSCI' -> AND e.grade = 'A') -> OR e.grade = 'A-' -> OR e.grade = 'B+' -> OR e.grade = 'B'; -- grade of B or above. +-----------+-----------------------------+---------+----------+------+---------------+---------------+-------+ | course id | coourse | credits | semester | year | instructor | student | grade | +-----------+-----------------------------+---------+----------+------+---------------+---------------+-------+ | CSCI 3333 | Data Structures | 3 | Fall | 2019 | Paul Smith | Tony Hawk | A | | CSCI 4333 | Design of Database Systems | 3 | Fall | 2019 | Paul Smith | Tony Hawk | A | | CSCI 5333 | DBMS | 3 | Fall | 2019 | Mary Tran | Tony Hawk | B+ | | CINF 4320 | Web Application Development | 3 | Fall | 2019 | Sharon Mannes | Tony Hawk | A- | | CSCI 4333 | Design of Database Systems | 3 | Fall | 2019 | Paul Smith | Mary Hawk | A- | | CSCI 5333 | DBMS | 3 | Fall | 2019 | Mary Tran | David Hawk | B+ | | CINF 4320 | Web Application Development | 3 | Fall | 2019 | Sharon Mannes | Larry Johnson | B+ | | CINF 4320 | Web Application Development | 3 | Fall | 2019 | Sharon Mannes | Linda Johnson | A- | | ITEC 3335 | Database Development | 3 | Fall | 2019 | Daniel Kim | Linda Johnson | A- | | ITEC 3312 | Introduction to Scripting | 3 | Fall | 2019 | Benjamin Yu | Linda Johnson | B+ | | ARTS 3311 | Hindu Arts | 3 | Fall | 2019 | Art Allister | Ben Zico | A- | +-----------+-----------------------------+---------+----------+------+---------------+---------------+-------+ 11 rows in set (0.001 sec) MariaDB [toyu]> SELECT DISTINCT -> CONCAT(co.rubric, ' ', co.number) AS `course id`, -> co.title AS coourse, -> co.credits, -> c.semester, -> c.year, -> CONCAT(f.fname, ' ', f.lname) AS instructor, -> CONCAT(s.fname, ' ', s.lname) AS student, -> e.grade -> FROM course AS co INNER JOIN class AS c ON (c.courseId = co.courseId) -> INNER JOIN faculty AS f ON (c.facId = f.facId) -> INNER JOIN enroll AS e ON (c.classId = e.classId) -> INNER JOIN student AS s ON (e.stuId = s.stuId) -> WHERE co.rubric = 'CSCI' -> AND (e.grade = 'A' -> OR e.grade = 'A-' -> OR e.grade = 'B+' -> OR e.grade = 'B'); -- grade of B or above. +-----------+----------------------------+---------+----------+------+------------+------------+-------+ | course id | coourse | credits | semester | year | instructor | student | grade | +-----------+----------------------------+---------+----------+------+------------+------------+-------+ | CSCI 3333 | Data Structures | 3 | Fall | 2019 | Paul Smith | Tony Hawk | A | | CSCI 4333 | Design of Database Systems | 3 | Fall | 2019 | Paul Smith | Tony Hawk | A | | CSCI 4333 | Design of Database Systems | 3 | Fall | 2019 | Paul Smith | Mary Hawk | A- | | CSCI 5333 | DBMS | 3 | Fall | 2019 | Mary Tran | Tony Hawk | B+ | | CSCI 5333 | DBMS | 3 | Fall | 2019 | Mary Tran | David Hawk | B+ | +-----------+----------------------------+---------+----------+------+------------+------------+-------+ 5 rows in set (0.001 sec) MariaDB [toyu]> -- students with no advisor MariaDB [toyu]> SELECT s.* -> FROM student AS s -> WHERE s.advisor IS NULL; +--------+-----------+-------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+-----------+-------+-------+-------+------+---------+ | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | +--------+-----------+-------+-------+-------+------+---------+ 3 rows in set (0.004 sec) MariaDB [toyu]> MariaDB [toyu]> -- Show all students with a declared minor. MariaDB [toyu]> SELECT DISTINCT s.* -> FROM student AS s -> WHERE s.minor IS NOT NULL; +--------+-----------+---------+-------+-------+------+---------+ | 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 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | +--------+-----------+---------+-------+-------+------+---------+ 7 rows in set (0.001 sec) MariaDB [toyu]> SELECT FALSE, -> TRUE; +-------+------+ | FALSE | TRUE | +-------+------+ | 0 | 1 | +-------+------+ 1 row in set (0.000 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT * -> FROM student -> WHERE 0; Empty set (0.000 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT * -> FROM student -> WHERE 1; +--------+-----------+----------+-------+-------+------+---------+ | 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]> MariaDB [toyu]> SELECT * -> FROM student -> WHERE 2697; +--------+-----------+----------+-------+-------+------+---------+ | 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 [toyu]> MariaDB [toyu]> SELECT * -> FROM student -> WHERE '0'; Empty set (0.000 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT * -> FROM student -> WHERE '145'; +--------+-----------+----------+-------+-------+------+---------+ | 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 [toyu]> MariaDB [toyu]> -- warning: '' cannot be converted to a number. MariaDB [toyu]> -- "Warning 1292 Truncated incorrect DOUBLE value: ''" MariaDB [toyu]> SELECT * -> FROM student -> WHERE ''; Empty set, 1 warning (0.000 sec) MariaDB [toyu]> MariaDB [toyu]> -- warning: '' cannot be converted to a number. MariaDB [toyu]> -- "Warning 1292 Truncated incorrect DOUBLE value: ''" MariaDB [toyu]> SELECT * -> FROM student -> WHERE 'Hello world'; Empty set, 1 warning (0.000 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT * -> FROM student -> WHERE 1.49; +--------+-----------+----------+-------+-------+------+---------+ | 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 [toyu]> SELECT NULL > 3, -> NULL <= 3, -> 5 >= NULL, -> 5 < NULL, -> NULL > NULL, -> NULL <= NULL; +----------+-----------+-----------+----------+-------------+--------------+ | NULL > 3 | NULL <= 3 | 5 >= NULL | 5 < NULL | NULL > NULL | NULL <= NULL | +----------+-----------+-----------+----------+-------------+--------------+ | NULL | NULL | NULL | NULL | NULL | NULL | +----------+-----------+-----------+----------+-------------+--------------+ 1 row in set (0.002 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT * -> FROM student -> WHERE NULL > 3; Empty set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> -- Comparisons must be mindful of null. MariaDB [toyu]> SELECT e.* -> FROM enroll AS e -> WHERE e.n_alerts >= 2; +--------+---------+-------+----------+ | stuId | classId | grade | n_alerts | +--------+---------+-------+----------+ | 100002 | 10000 | B- | 3 | | 100000 | 10001 | A | 2 | | 100002 | 10002 | B+ | 2 | | 100002 | 10003 | D | 4 | | 100007 | 10007 | F | 4 | | 100000 | 11001 | D | 4 | +--------+---------+-------+----------+ 6 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT e.* -> FROM enroll AS e -> WHERE e.n_alerts < 2; +--------+---------+-------+----------+ | stuId | classId | grade | n_alerts | +--------+---------+-------+----------+ | 100000 | 10000 | A | 0 | | 100001 | 10001 | A- | 0 | | 100000 | 10002 | B+ | 1 | | 100000 | 10003 | C | 0 | | 100004 | 10003 | A | 0 | | 100000 | 10004 | A- | 1 | | 100005 | 10004 | A- | 0 | | 100005 | 10005 | A- | 0 | | 100008 | 10007 | C- | 0 | | 100007 | 10008 | A- | 0 | +--------+---------+-------+----------+ 10 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT e.* -> FROM enroll AS e; +--------+---------+-------+----------+ | stuId | classId | grade | n_alerts | +--------+---------+-------+----------+ | 100000 | 10000 | A | 0 | | 100001 | 10000 | NULL | NULL | | 100002 | 10000 | B- | 3 | | 100000 | 10001 | A | 2 | | 100001 | 10001 | A- | 0 | | 100000 | 10002 | B+ | 1 | | 100002 | 10002 | B+ | 2 | | 100000 | 10003 | C | 0 | | 100002 | 10003 | D | 4 | | 100004 | 10003 | A | 0 | | 100005 | 10003 | NULL | NULL | | 100000 | 10004 | A- | 1 | | 100004 | 10004 | B+ | NULL | | 100005 | 10004 | A- | 0 | | 100006 | 10004 | C+ | NULL | | 100005 | 10005 | A- | 0 | | 100006 | 10005 | A | NULL | | 100005 | 10006 | B+ | NULL | | 100007 | 10007 | F | 4 | | 100008 | 10007 | C- | 0 | | 100007 | 10008 | A- | 0 | | 100000 | 11001 | D | 4 | +--------+---------+-------+----------+ 22 rows in set (0.000 sec) MariaDB [toyu]> MariaDB [toyu]> -- Q. List all enrollment records without 2 or more n_alerts. MariaDB [toyu]> -- Naive solution MariaDB [toyu]> SELECT e.* -> FROM enroll AS e -> WHERE e.n_alerts < 2; +--------+---------+-------+----------+ | stuId | classId | grade | n_alerts | +--------+---------+-------+----------+ | 100000 | 10000 | A | 0 | | 100001 | 10001 | A- | 0 | | 100000 | 10002 | B+ | 1 | | 100000 | 10003 | C | 0 | | 100004 | 10003 | A | 0 | | 100000 | 10004 | A- | 1 | | 100005 | 10004 | A- | 0 | | 100005 | 10005 | A- | 0 | | 100008 | 10007 | C- | 0 | | 100007 | 10008 | A- | 0 | +--------+---------+-------+----------+ 10 rows in set (0.000 sec) MariaDB [toyu]> MariaDB [toyu]> -- Q. List all enrollment records without 2 or more n_alerts. MariaDB [toyu]> -- More likely solution MariaDB [toyu]> SELECT e.* -> FROM enroll AS e -> WHERE e.n_alerts IS NULL -> OR e.n_alerts < 2; +--------+---------+-------+----------+ | stuId | classId | grade | n_alerts | +--------+---------+-------+----------+ | 100000 | 10000 | A | 0 | | 100001 | 10000 | NULL | NULL | | 100001 | 10001 | A- | 0 | | 100000 | 10002 | B+ | 1 | | 100000 | 10003 | C | 0 | | 100004 | 10003 | A | 0 | | 100005 | 10003 | NULL | NULL | | 100000 | 10004 | A- | 1 | | 100004 | 10004 | B+ | NULL | | 100005 | 10004 | A- | 0 | | 100006 | 10004 | C+ | NULL | | 100005 | 10005 | A- | 0 | | 100006 | 10005 | A | NULL | | 100005 | 10006 | B+ | NULL | | 100008 | 10007 | C- | 0 | | 100007 | 10008 | A- | 0 | +--------+---------+-------+----------+ 16 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> -- Q. List all enrollment records without a value in n_alerts. MariaDB [toyu]> -- incorrect answer. MariaDB [toyu]> SELECT e.* -> FROM enroll AS e -> WHERE e.n_alerts <> NULL; Empty set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> -- Q. List all enrollment records without a value in n_alerts. MariaDB [toyu]> -- correct answer. MariaDB [toyu]> SELECT e.* -> FROM enroll AS e -> WHERE e.n_alerts IS NOT NULL; +--------+---------+-------+----------+ | stuId | classId | grade | n_alerts | +--------+---------+-------+----------+ | 100000 | 10000 | A | 0 | | 100002 | 10000 | B- | 3 | | 100000 | 10001 | A | 2 | | 100001 | 10001 | A- | 0 | | 100000 | 10002 | B+ | 1 | | 100002 | 10002 | B+ | 2 | | 100000 | 10003 | C | 0 | | 100002 | 10003 | D | 4 | | 100004 | 10003 | A | 0 | | 100000 | 10004 | A- | 1 | | 100005 | 10004 | A- | 0 | | 100005 | 10005 | A- | 0 | | 100007 | 10007 | F | 4 | | 100008 | 10007 | C- | 0 | | 100007 | 10008 | A- | 0 | | 100000 | 11001 | D | 4 | +--------+---------+-------+----------+ 16 rows in set (0.001 sec) MariaDB [toyu]> notee