tee 2025_9_3_sql_log.txt +-----------+----------------------------+---------+----------+------+------------+------------+-------+ | 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 -- declarative analysis [1] Output: label/name: value (value AS label/name) 1. CONCAT(co.rubric, ' ', co.number) AS `course id` 2. co.title AS coourse 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 Sourcea: 1. course AS co 2. class AS c 3. faculty AS f 4. student AS s 5. enroll AS e -- to join class and student [3] Conditions: [3a] problem conditions: CSCI courses co.rubric = 'CSCI' [3b] join conditions (n tables usually (n-1) join conditions 1. c.courseId = co.courseId 2. c.facId = f.facId 3. c.classId = e.classId 4. e.stuId = s.stuId [3] List the enrollment information of all CSCI courses that have been offered in the following manner. SELECT DISTINCT CONCAT(co.rubric, ' ', co.number) AS `course id`, co.title AS coourse, 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 (c.classId = e.classId) INNER JOIN student AS s ON (e.stuId = s.stuId) WHERE co.rubric = 'CSCI'; [4] Repeat [3], show only those entries with a grade of B or above. +-----------+----------------------------+---------+----------+------+------------+------------+-------+ | course id | course | credits | semester | year | instructor | student | grade | +-----------+----------------------------+---------+----------+------+------------+------------+-------+ | CSCI 3333 | Data Structures | 3 | Fall | 2019 | Paul Smith | Tony Hawk | A | | 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 5333 | DBMS | 3 | Fall | 2019 | Mary Tran | Tony Hawk | B+ | | CSCI 5333 | DBMS | 3 | Fall | 2019 | Mary Tran | David Hawk | B+ | +-----------+----------------------------+---------+----------+------+------------+------------+-------+ 5 rows in set -- declarative analysis [1] Output: label/name: value (value AS label/name) 1. CONCAT(co.rubric, ' ', co.number) AS `course id` 2. co.title AS coourse 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 Sourcea: 1. course AS co 2. class AS c 3. faculty AS f 4. student AS s 5. enroll AS e -- to join class and student [3] Conditions: [3a] problem conditions: CSCI courses and a grade of B or above. co.rubric = 'CSCI' ... [3b] join conditions (n tables usually (n-1) join conditions 1. c.courseId = co.courseId 2. c.facId = f.facId 3. c.classId = e.classId 4. e.stuId = s.stuId SELECT DISTINCT CONCAT(co.rubric, ' ', co.number) AS `course id`, co.title AS coourse, 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 (c.classId = e.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'; -- grade of B or above. -- mindful of opeator precedences. -- e.g. 3 + 6 * 7 -> 45 -- e.g. 5 - 3 - 2 -> 0 SELECT DISTINCT CONCAT(co.rubric, ' ', co.number) AS `course id`, co.title AS coourse, 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 (c.classId = e.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'; -- grade of B or above. SELECT DISTINCT CONCAT(co.rubric, ' ', co.number) AS `course id`, co.title AS coourse, 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 (c.classId = e.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'); -- grade of B or above. -- use explicit parentheses to state your intended precedence orders. SELECT DISTINCT CONCAT(co.rubric, ' ', co.number) AS `course id`, co.title AS coourse, 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 (c.classId = e.classId) INNER JOIN student AS s ON (e.stuId = s.stuId) WHERE co.rubric = 'CSCI' AND e.grade IN ('A', 'A-', 'B+', 'B'); -- IN: set membership test operator; binary 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' (string) -> 0 (INT): false. -- it may not be what you want. -- strong typed language: few implicit type conversions. -- use explicit type conversions generously in weakly typed language. 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 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; -- 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; -- Q. List all enrollment records without a value in n_alerts. -- incorrect answer. SELECT e.* FROM enroll AS e WHERE e.n_alerts <> NULL; -- Q. List all enrollment records without a value in n_alerts. -- correct answer. SELECT e.* FROM enroll AS e WHERE e.n_alerts IS NOT NULL;