MariaDB [toyu]> desc class; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | classId | int(11) | NO | PRI | NULL | | | courseId | int(11) | NO | MUL | NULL | | | semester | varchar(10) | NO | | NULL | | | year | decimal(4,0) | NO | | NULL | | | facId | int(11) | NO | MUL | NULL | | | room | varchar(6) | YES | | NULL | | +----------+--------------+------+-----+---------+-------+ 6 rows in set (0.023 sec) MariaDB [toyu]> SELECT * FROM class; +---------+----------+----------+------+-------+------+ | classId | courseId | semester | year | facId | room | +---------+----------+----------+------+-------+------+ | 10000 | 2000 | Fall | 2019 | 1011 | D241 | | 10001 | 2001 | Fall | 2019 | 1011 | D242 | | 10002 | 2002 | Fall | 2019 | 1012 | D136 | | 10003 | 2020 | Fall | 2019 | 1014 | D241 | | 10004 | 2021 | Fall | 2019 | 1014 | D241 | | 10005 | 2040 | Fall | 2019 | 1015 | D237 | | 10006 | 2041 | Fall | 2019 | 1019 | D217 | | 10007 | 2060 | Fall | 2019 | 1020 | B101 | | 10008 | 2080 | Fall | 2019 | 1018 | D241 | | 11000 | 2000 | Spring | 2020 | 1011 | D241 | | 11001 | 2001 | Spring | 2020 | 1012 | D242 | | 11002 | 2002 | Spring | 2020 | 1013 | D136 | | 11003 | 2020 | Spring | 2020 | 1016 | D217 | | 11004 | 2061 | Spring | 2020 | 1018 | B101 | +---------+----------+----------+------+-------+------+ 14 rows in set (0.000 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.014 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 * -> FROM student -> WHERE 0; Empty set (0.001 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.001 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.001 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]> SELECT FALSE IS NULL, -> TRUE IS NULL, -> 0 IS NULL, -> 1 IS NULL, -> "" IS NULL, -> "Hey" IS NULL, -> NULL IS NULL, -> NULL IS NOT NULL; +---------------+--------------+-----------+-----------+------------+---------------+--------------+------------------+ | FALSE IS NULL | TRUE IS NULL | 0 IS NULL | 1 IS NULL | "" IS NULL | "Hey" IS NULL | NULL IS NULL | NULL IS NOT NULL | +---------------+--------------+-----------+-----------+------------+---------------+--------------+------------------+ | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | +---------------+--------------+-----------+-----------+------------+---------------+--------------+------------------+ 1 row 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]> SELECT * -> FROM Student -> WHERE '1 = 2'; +--------+-----------+----------+-------+-------+------+---------+ | 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, 1 warning (0.001 sec) MariaDB [toyu]> SELECT * -> FROM Student -> WHERE '1 = 2'; +--------+-----------+----------+-------+-------+------+---------+ | 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, 1 warning (0.000 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT * -> FROM Student -> WHERE 1 = '0'; Empty set (0.001 sec) MariaDB [toyu]> SELECT * -> FROM Student -> WHERE 0 = '0'; +--------+-----------+----------+-------+-------+------+---------+ | 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]> -- 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.001 sec) 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.001 sec) 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]>