MySQL [(none)]> -- students with no advisor MySQL [(none)]> SELECT s.* -> FROM student AS s -> WHERE s.advisor IS NULL; ERROR 1046 (3D000): No database selected MySQL [(none)]> MySQL [(none)]> -- Show all students with a declared minor. MySQL [(none)]> SELECT DISTINCT s.* -> FROM student AS s -> WHERE s.minor IS NOT NULL; ERROR 1046 (3D000): No database selected MySQL [(none)]> MySQL [(none)]> -- Show enrollment without a n_alerts value. MySQL [(none)]> SELECT e.* -> FROM enroll AS e -> WHERE e.n_alerts IS NULL; ERROR 1046 (3D000): No database selected MySQL [(none)]> use toyu; Database changed MySQL [toyu]> -- students with no advisor MySQL [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.037 sec) MySQL [toyu]> MySQL [toyu]> -- Show all students with a declared minor. MySQL [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 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | +--------+-----------+---------+-------+-------+------+---------+ 7 rows in set (0.011 sec) MySQL [toyu]> MySQL [toyu]> -- Show enrollment without a n_alerts value. MySQL [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.003 sec) MySQL [toyu]> SELECT FALSE, -> TRUE; +-------+------+ | FALSE | TRUE | +-------+------+ | 0 | 1 | +-------+------+ 1 row in set (0.002 sec) MySQL [toyu]> MySQL [toyu]> SELECT * -> FROM student -> WHERE 0; Empty set (0.002 sec) MySQL [toyu]> MySQL [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.002 sec) MySQL [toyu]> MySQL [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.002 sec) MySQL [toyu]> MySQL [toyu]> SELECT * -> FROM student -> WHERE '0'; Empty set (0.011 sec) MySQL [toyu]> MySQL [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.004 sec) MySQL [toyu]> MySQL [toyu]> -- warning: '' cannot be converted to a number. MySQL [toyu]> -- "Warning 1292 Truncated incorrect DOUBLE value: ''" MySQL [toyu]> SELECT * -> FROM student -> WHERE ''; Empty set (0.002 sec) MySQL [toyu]> MySQL [toyu]> -- warning: '' cannot be converted to a number. MySQL [toyu]> -- "Warning 1292 Truncated incorrect DOUBLE value: ''" MySQL [toyu]> SELECT * -> FROM student -> WHERE 'Hello world'; Empty set, 1 warning (0.015 sec) MySQL [toyu]> MySQL [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) MySQL [toyu]> SELECT * -> FROM student -> WHERE CAST('0' AS INT); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INT)' at line 3 MySQL [toyu]> SELECT * -> FROM student -> WHERE CAST('0') AS INTEGER; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AS INTEGER' at line 3 MySQL [toyu]> SELECT * -> FROM student -> WHERE CONVERT('0', INT); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INT)' at line 3 MySQL [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.002 sec) MySQL [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 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | +--------+-----------+---------+-------+-------+------+---------+ 7 rows in set (0.002 sec) MySQL [toyu]> SELECT DISTINCT s.* -> FROM student AS s -> WHERE s.minor IS NULL; +--------+-------+----------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+-------+----------+-------+-------+------+---------+ | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | +--------+-------+----------+-------+-------+------+---------+ 4 rows in set (0.008 sec) MySQL [toyu]> SELECT DISTINCT s.* -> FROM student AS s -> WHERE s.minor = 'NULL'; Empty set (0.003 sec) MySQL [toyu]> SELECT DISTINCT s.* -> FROM student AS s -> WHERE s.minor <> 'NULL'; +--------+-----------+---------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+-----------+---------+-------+-------+------+---------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | +--------+-----------+---------+-------+-------+------+---------+ 7 rows in set (0.003 sec) MySQL [toyu]> SELECT DISTINCT e.* -> FROM enroll AS e -> WHERE e.n_alerts <= 2; +--------+---------+-------+----------+ | stuId | classId | grade | n_alerts | +--------+---------+-------+----------+ | 100000 | 10000 | A | 0 | | 100000 | 10001 | A | 2 | | 100001 | 10001 | A- | 0 | | 100000 | 10002 | B+ | 1 | | 100002 | 10002 | B+ | 2 | | 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 | +--------+---------+-------+----------+ 12 rows in set (0.002 sec) MySQL [toyu]> SELECT DISTINCT e.* -> FROM enroll AS e -> WHERE e.n_alerts > 2; +--------+---------+-------+----------+ | stuId | classId | grade | n_alerts | +--------+---------+-------+----------+ | 100002 | 10000 | B- | 3 | | 100002 | 10003 | D | 4 | | 100007 | 10007 | F | 4 | | 100000 | 11001 | D | 4 | +--------+---------+-------+----------+ 4 rows in set (0.003 sec) MySQL [toyu]> SELECT DISTINCT e.* -> FROM enroll AS e -> WHERE e.n_alerts <= 2 -> OR e.n_alerts > 2; -- may expect getting all rows in enroll. +--------+---------+-------+----------+ | 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.002 sec) MySQL [toyu]> SELECT DISTINCT e.* -> FROM enroll AS e -> WHERE e.n_alerts IS NULL -> OR e.n_alerts <= 2 -> OR e.n_alerts > 2; +--------+---------+-------+----------+ | 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.005 sec) MySQL [toyu]> notee