tee 2025_9_3_sql_log.txt

[3] List the enrollment information of all CSCI courses that have been offered in the following manner.

+-----------+----------------------------+---------+----------+------+------------+------------+-------+
| course id | course                     | credits | semester | year | instructor | student    | grade |
+-----------+----------------------------+---------+----------+------+------------+------------+-------+
| CSCI 3333 | Data Structures            |       3 | Fall     | 2019 | Paul Smith | Tony Hawk  | A     |
| CSCI 3333 | Data Structures            |       3 | Fall     | 2019 | Paul Smith | Mary Hawk  | NULL  |
| CSCI 3333 | Data Structures            |       3 | Fall     | 2019 | Paul Smith | David Hawk | B-    |
| CSCI 4333 | Design of Database Systems |       3 | Fall     | 2019 | Paul Smith | Tony Hawk  | A     |
| CSCI 4333 | Design of Database Systems |       3 | Fall     | 2019 | Paul Smith | Mary Hawk  | A-    |
| CSCI 4333 | Design of Database Systems |       3 | Spring   | 2020 | Mary Tran  | Tony Hawk  | D     |
| CSCI 5333 | DBMS                       |       3 | Fall     | 2019 | Mary Tran  | Tony Hawk  | B+    |
| CSCI 5333 | DBMS                       |       3 | Fall     | 2019 | Mary Tran  | David Hawk | B+    |
+-----------+----------------------------+---------+----------+------+------------+------------+-------+
8 rows in set

Decalarative analysis:

[1] output columns: label: value AS label
    1) CONCAT(co.rubric, ' ', co.number) AS `course id`
    2) co.title AS courses
    3} co.credits
    4) c.semester
    5) c.year
    6) CONCAT(f.fname, ' ', f.lname) AS instructor
    7) CONCAT(s.fname, ' ', s.lname) AS student
    8) e.grade
[2] data sources: 
    1) course AS co
    2) class AS c
    3) faculty AS f
    4) student AS s
    5) enroll AS e
[3] conditions: 
    [3a] problem condition: CSCI courses
        co.rubric = 'CSCI'
    [3b] join conditions:    n tables -> usually n - 1 tables.
        c.courseId = co.courseId
        c.facId= f.facId
        e.stuId = s.stuId
        e.classId = c.classId
        
SELECT DISTINCT
    CONCAT(co.rubric, ' ', co.number) AS `course id`,
    co.title AS courses,
    co.credits,
    c.semester,
    c.year,
    CONCAT(f.fname, ' ', f.lname) AS instructor,
    CONCAT(s.fname, ' ', s.lname) AS student,
    e.grade
FROM course AS co INNER JOIN class AS c ON (c.courseId = co.courseId)
    INNER JOIN faculty AS f ON (c.facId= f.facId)
    INNER JOIN enroll AS e ON (e.classId = c.classId)
    INNER JOIN student AS s ON (e.stuId = s.stuId)
WHERE co.rubric = 'CSCI';


-- Spring 2025 Q4:
SELECT DISTINCT
    CONCAT(co.rubric, ' ', co.number) AS `course id`,
    co.title AS courses,
    co.credits,
    c.semester,
    c.year,
    CONCAT(f.fname, ' ', f.lname) AS instructor,
    CONCAT(s.fname, ' ', s.lname) AS student,
    e.grade
FROM course AS co INNER JOIN class AS c ON (c.courseId = co.courseId)
    INNER JOIN faculty AS f ON (c.facId= f.facId)
    INNER JOIN enroll AS e ON (e.classId = c.classId)
    INNER JOIN student AS s ON (e.stuId = s.stuId)
WHERE co.rubric = 'CSCI'
AND e.grade = 'A'
OR e.grade = 'A-'
OR e.grade = 'B+'
OR e.grade = 'B'; -- B or above
   
-- mind the operator precedence. E.g. 5 + 2 * 3   

SELECT DISTINCT
    CONCAT(co.rubric, ' ', co.number) AS `course id`,
    co.title AS courses,
    co.credits,
    c.semester,
    c.year,
    CONCAT(f.fname, ' ', f.lname) AS instructor,
    CONCAT(s.fname, ' ', s.lname) AS student,
    e.grade
FROM course AS co INNER JOIN class AS c ON (c.courseId = co.courseId)
    INNER JOIN faculty AS f ON (c.facId= f.facId)
    INNER JOIN enroll AS e ON (e.classId = c.classId)
    INNER JOIN student AS s ON (e.stuId = s.stuId)
WHERE (co.rubric = 'CSCI'
AND e.grade = 'A')
OR e.grade = 'A-'
OR e.grade = 'B+'
OR e.grade = 'B'; -- B or above

SELECT DISTINCT
    CONCAT(co.rubric, ' ', co.number) AS `course id`,
    co.title AS courses,
    co.credits,
    c.semester,
    c.year,
    CONCAT(f.fname, ' ', f.lname) AS instructor,
    CONCAT(s.fname, ' ', s.lname) AS student,
    e.grade
FROM course AS co INNER JOIN class AS c ON (c.courseId = co.courseId)
    INNER JOIN faculty AS f ON (c.facId= f.facId)
    INNER JOIN enroll AS e ON (e.classId = c.classId)
    INNER JOIN student AS s ON (e.stuId = s.stuId)
WHERE co.rubric = 'CSCI'
AND (e.grade = 'A'
OR e.grade = 'A-'
OR e.grade = 'B+'
OR e.grade = 'B'); -- B or above

SELECT DISTINCT
    CONCAT(co.rubric, ' ', co.number) AS `course id`,
    co.title AS courses,
    co.credits,
    c.semester,
    c.year,
    CONCAT(f.fname, ' ', f.lname) AS instructor,
    CONCAT(s.fname, ' ', s.lname) AS student,
    e.grade
FROM course AS co INNER JOIN class AS c ON (c.courseId = co.courseId)
    INNER JOIN faculty AS f ON (c.facId= f.facId)
    INNER JOIN enroll AS e ON (e.classId = c.classId)
    INNER JOIN student AS s ON (e.stuId = s.stuId)
WHERE co.rubric = 'CSCI'
AND e.grade IN ('A', 'A-', 'B+', 'B'); -- B or above; IN; set membership test.

SELECT s.*
FROM student AS s
WHERE s.advisor IS NULL;

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

-- 1. Boolean values are TINYINT. FALSE is 0.
SELECT FALSE,
   TRUE;
  
SELECT *
FROM student
WHERE 0;

SELECT *
FROM student
WHERE 1;

SELECT *
FROM student
WHERE 2697;

SELECT *
FROM student
WHERE '0';
-- implicit type conversion: '0' -> 0 -> interpreted as FALSE

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 e.*
FROM enroll AS e
WHERE e.n_alerts; 

-- 3. null is a special value different with 0 or empty string.
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;
-- 4. Comparing null to other values return null, which is converted to false.
SELECT NULL > 3,
   NULL <= 3,
   5 >= NULL,
   5 < NULL,
   NULL > NULL,
   NULL <= NULL;
  
SELECT *
FROM student
WHERE NULL > 3;

-- Comparisons must be mindful of 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;