MariaDB [(none)]> use toyu; Database changed 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.003 sec) 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.002 sec) MariaDB [toyu]> -- Show enrollment without a n_alerts value. MariaDB [toyu]> SELECT e.* -> FROM enroll AS e -> WHERE e.n_alerts IS NULL; +--------+---------+-------+----------+ | stuId | classId | grade | n_alerts | +--------+---------+-------+----------+ | 100001 | 10000 | NULL | NULL | | 100005 | 10003 | NULL | NULL | | 100004 | 10004 | B+ | NULL | | 100006 | 10004 | C+ | NULL | | 100006 | 10005 | A | NULL | | 100005 | 10006 | B+ | NULL | +--------+---------+-------+----------+ 6 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.000 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.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 ''; Empty set, 1 warning (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 '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.002 sec) MariaDB [toyu]> 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.001 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT * -> FROM student -> WHERE NULL > 3; Empty set (0.001 sec) 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.000 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.000 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]> 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]> notee