-- logging.
-- 2/4/25

mysql -u yue -p

use toyu;

SELECT * FROM student;

tee 2015_2_4_sql_log.txt

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

-- 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 FALSE,
   TRUE;
   -- no native Boolean type; 0 is False,
  
SELECT *
FROM student
WHERE 0;

SELECT *
FROM student
WHERE 1;

SELECT *
FROM student
WHERE 2697;

SELECT *
FROM student
WHERE '0';

SELECT *
FROM student
WHERE CONVERT('0', INT);  -- explicit type conversion.

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;



SELECT *
FROM student
WHERE '145';

-- warning: '' cannot be converted to a number.
-- "Warning 1292 Truncated incorrect DOUBLE value: ''"
SELECT *
FROM student
WHERE '';

-- warning: '' cannot be converted to a number.
-- "Warning 1292 Truncated incorrect DOUBLE value: ''"
SELECT *
FROM student
WHERE 'Hello world';

SELECT *
FROM student
WHERE 1.49;

SELECT DISTINCT s.*
FROM student AS s
WHERE s.minor IS NOT NULL;

SELECT DISTINCT s.*
FROM student AS s
WHERE s.minor IS NULL;

SELECT DISTINCT s.*
FROM student AS s
WHERE s.minor = 'NULL';

SELECT DISTINCT s.*
FROM student AS s
WHERE s.minor <> 'NULL';

-- WHERE s.minor <> 'NULL'; when s.minor IS NULL ->null --> (implicitly conversion) to false.

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

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

SELECT DISTINCT e.*
FROM enroll AS e
WHERE e.n_alerts <= 2
OR e.n_alerts > 2; -- may expect getting all rows in enroll. No, no.

SELECT DISTINCT e.*
FROM enroll AS e
WHERE e.n_alerts IS NULL
OR e.n_alerts <= 2
OR e.n_alerts > 2; -- may expect getting all rows in enroll.