-- logging.
-- 2/3

tee 2025_2_3_sql_log.txt

mysql -u yue -p -h dcm.uhcl.edu



-- students with no advisor
SELECT s.*
FROM student AS s
WHERE s.advisor IS NULL;

SELECT s.*
FROM student AS s
WHERE s.advisor <> 'NULL'; 
-- if s.advisor is null, s.advisor <> 'NULL' -> null implicitly converted --> false

+--------+---------+----------+-------+-------+------+---------+
| 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 |
| 100005 | Linda   | Johnson  | CINF  | ENGL  |   13 |    1015 |
| 100006 | Lillian | Johnson  | CINF  | ITEC  |   18 |    1016 |
| 100009 | Linda   | King     | ARTS  | CSCI  |  125 |    1018 |
| 100111 | Cathy   | Johanson | NULL  | NULL  |    0 |    1018 |
+--------+---------+----------+-------+-------+------+---------+
8 rows in set, 3 warnings (0.002 sec)

-- Show all students with a declared minor.
SELECT DISTINCT s.*
FROM student AS s
WHERE s.minor IS NOT NULL;

-- Show enrollment without a n_alerts value.
SELECT e.*
FROM enroll AS e
WHERE e.n_alerts IS NULL;

SELECT e.*
FROM enroll AS e
WHERE e.n_alerts < 2;

SELECT e.*
FROM enroll AS e
WHERE e.n_alerts >= 2;

SELECT e.*
FROM enroll AS e
WHERE (e.n_alerts < 2)
OR (e.n_alerts >= 2);

-- Q. List all enrollment records without 2 or more n_alerts.
-- Naive solution
SELECT e.*
FROM enroll AS e
WHERE e.n_alerts < 2;

-- Q. List all enrollment records without 2 or more n_alerts.
-- More likely solution
SELECT e.*
FROM enroll AS e
WHERE e.n_alerts IS NULL
OR e.n_alerts < 2;

SELECT * FROM student;