tee 2026_3_2_sql_log.txt

SELECT DISTINCT *
FROM student AS s
WHERE s.stuId NOT IN (SELECT DISTINCT e.stuID FROM enroll AS e);

SELECT CONCAT(fname, ' ',lname)
FROM Student;

SELECT DISTINCT MAX(ach),  -- the group is the entire student table.	
	COUNT(*)
FROM student;

-- student within 60 credits of the maximum number of ach any student may have.
SELECT DISTINCT s.stuId,
   CONCAT(s.fname, ' ', s.lname) AS student,
   s.ach AS credits
FROM student AS s
WHERE s.ach + 60 >=
   (SELECT DISTINCT MAX(ach) FROM student);
   
-- s.ach + 60 >=
   (SELECT DISTINCT MAX(ach) FROM student)
+----------+
| MAX(ach) |
+----------+
|      125 |
+----------+

-- TYpe mismatch
-- implcit type conversion: a relation of 1 row and 1 column of int -> int. 

-- subqueries as derived tables.
SELECT DISTINCT s.stuId,
   CONCAT(s.fname, ' ', s.lname) AS student,
   s.ach AS credits
FROM student AS s INNER JOIN
   (SELECT DISTINCT MAX(ach) AS max FROM student) AS m -- an alias is required.
WHERE s.ach + 60 >= m.max;
 
-- CTE
WITH t1 AS
   (SELECT MAX(ach) AS max FROM student)
SELECT s.stuId,
   s.ach AS `ach credits`,
   t1.max - s.ach AS `diff from max credits of all`
FROM student AS s, t1
WHERE s.ach + 60 >= t1.max
ORDER BY `ach credits` DESC;

-- multiple common tables (not efficient; used as demonstration.)
WITH t1 AS -- step 1
   (SELECT MAX(ach) AS max FROM student),
t2 AS -- step 2
   (SELECT s.stuId,
      s.ach AS `ach credits`,
      t1.max - s.ach AS diff,
      s.major
    FROM student AS s, t1)
SELECT t2.stuId, t2.`ach credits`, -- step 3
   t2.diff AS `diff from max credits of all`,
   d.deptName AS department
FROM t2 LEFT JOIN department d ON (t2.major = d.deptCode)
ORDER BY t2.`ach credits` DESC;
-- scope of Common table is in the containing SELECT statement.

SELECT *
FROM t1;

-- step 1
CREATE TEMPORARY TABLE tt1
(SELECT MAX(ach) AS max FROM student);

-- scope of temp table is the client session.

SELECT *
FROM tt1;

-- step 2
CREATE TEMPORARY TABLE tt2
   (SELECT s.stuId,
      s.ach AS `ach credits`,
      tt1.max - s.ach AS diff,
      s.major
    FROM student AS s, tt1);

SELECT *
FROM tt2;

-- step 3
SELECT tt2.stuId, tt2.`ach credits`,
   tt2.diff AS `diff from max credits of all`,
   d.deptName AS department
FROM tt2 LEFT JOIN department d ON (tt2.major = d.deptCode)
ORDER BY tt2.`ach credits` DESC;


SELECT DISTINCT MAX(ach)  -- the group is the entire student table.
FROM student;

SELECT AVG(d.numStaff) -- group fcn: AVG applies to the group: department table (7 rows)
FROM department AS d; 

SELECT d.*  -- step [3] prepare the output columns from [2]
FROM department AS d -- step [1]: create a bit source table with raw rows.
WHERE d.numStaff >= 5 -- step [2]: filter the row.
;

SELECT d.schoolCode, AVG(d.numStaff)  -- step [4] prepare the output columns from [3]
FROM department AS d -- step [1]: create a bit source table with raw rows.
WHERE d.numStaff >= 5 -- step [2]: filter the row.
GROUP BY d.schoolCode -- step [3] form groups using distinct values of the group by columns: d.schoolCode. A group -> new row. Original row will no longer be accessible.
;

SELECT d.schoolCode, 
	AVG(d.numStaff) AS `Average staff in departments`,
	COUNT(d.deptCode) AS `Number of departments` -- step [4] prepare the output columns from [3]
FROM department AS d -- step [1]: create a bit source table with raw rows.
WHERE d.numStaff >= 5 -- step [2]: filter the row.
GROUP BY d.schoolCode -- step [3] form groups using distinct values of the group by columns: d.schoolCode. A group -> new row. Original row will no longer be accessible.
;

SELECT d.schoolCode, 
	AVG(d.numStaff) AS `Average staff in departments`,
	COUNT(d.deptCode) AS `Number of departments` -- step [5] prepare the output columns from [4]
FROM department AS d -- step [1]: create a bit source table with raw rows.
WHERE d.numStaff >= 5 -- step [2]: filter the row.
GROUP BY d.schoolCode -- step [3] form groups using distinct values of the group by columns: d.schoolCode. A group -> new row. Original row will no longer be accessible.
HAVING `Number of departments` >= 2 -- step [3] filter out the group/new row.
ORDER BY `Number of departments` ASC -- step [6] order the result of [5]

;