tee 2026_3_2_sql_log.txt



-- older
SELECT DISTINCT s.fname AS first name
FROM Student AS s;

SELECT DISTINCT s.fname AS `first name`
FROM Student AS s;

UPDATE Student
SET major = 'ITEC'
WHERE StuId = 100000;

UPDATE Student
SET major = 'CSCI'
WHERE StuId = 100000;

UPDATE Student
SET major = 'CSCI';

-- students wiht a 'k' in their last name.
SELECT DISTINCT s.*
FROM student AS s
WHERE s.lname LIKE '%k%';

SELECT DISTINCT s.fname, s.lname, c.classId, e.grade
FROM student AS s, enroll AS e, class AS c
WHERE s.stuId = e.stuId -- Join condition
AND e.classId = c.classId -- Join condition
AND c.semester = 'Fall' -- problem condition
AND c.year = 2019; -- problem condition

SELECT DISTINCT s.fname, s.lname, c.classId, e.grade
FROM student AS s INNER JOIN enroll e ON (s.stuId = e.stuId) -- Join condition
    INNER JOIN class AS c ON (e.classId = c.classId) -- Join condition
WHERE c.semester = 'Fall' -- Problem condition
AND c.year = 2019; -- Problem condition

-- alternative: using the USING clause.
SELECT DISTINCT s.fname, s.lname, c.classId, e.grade
FROM student AS s INNER JOIN enroll e USING (stuId) -- Join condition
    INNER JOIN class AS c USING (classId) -- Join condition
WHERE c.semester = 'Fall' -- Problem condition
AND c.year = 2019; -- Problem condition


-- List the names of the students with their minors (in full name).
-- Student with no department not listed.
SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS student,
    d.deptName AS `minor department`
FROM student AS s INNER JOIN department AS d ON (s.minor = d.deptCode);

-- List the names of the students with their minors (in full name).
SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS student,
    d.deptName AS `minor department`
FROM student AS s LEFT JOIN department AS d ON (s.minor = d.deptCode);

-- List the names of the students with their minors (in full name).
-- more readable form.
SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS student,
    IFNULL (d.deptName, 'N/A') AS `minor department`
FROM student s LEFT JOIN department d ON (s.minor = d.deptCode);

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;

-- 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.
   -- no join condition (exception to the rule n tables join, (n-1) join conditions.
WHERE s.ach + 60 >= m.max;

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
ORDER BY `ach credits` DESC;

WITH t1 AS
   (SELECT MAX(ach) AS max FROM student),
t2 AS
   (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`,
   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 tables: only within the SELECT statement.

-- scope of temp tables within the current client session.
-- step 1
CREATE TEMPORARY TABLE tt1
AS (SELECT MAX(ach) AS max FROM student);

SELECT *
FROM tt1;

-- step 2
CREATE TEMPORARY TABLE tt2
AS (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;


-- the average number of staff of a department.
SELECT AVG(d.numStaff)
FROM department AS d;

SELECT d.numStaff
FROM department AS d;

SELECT d.*
FROM department AS d;

SELECT d.schoolCode, 
	COUNT(d.deptCode) AS `Number of departments`,
	AVG(d.numStaff) AS `Average # of staff`
FROM department AS d
GROUP BY d.schoolCode;

SELECT d.schoolCode, -- step [4] prepare columns using [3]
	COUNT(d.deptCode) AS `Number of departments`,
	AVG(d.numStaff) AS `Average # of staff`
FROM department AS d  -- step [1] big source table
WHERE d.numStaff >= 5 -- step [2] filter the original row in the table in [1]
GROUP BY d.schoolCode  -- step [3] form groups by distinct values of the group by columns. The group -> the new row.
;

SELECT d.schoolCode, -- step [5] prepare columns using [4]
	COUNT(d.deptCode) AS `Number of departments`,
	AVG(d.numStaff) AS `Average # of staff`
FROM department AS d  -- step [1] big source table
WHERE d.numStaff >= 5 -- step [2] filter the original row in the table in [1]
GROUP BY d.schoolCode  -- step [3] form groups by distinct values of the group by columns. The group -> the new row.
HAVING `Number of departments` >= 2 -- step [4]: filter the group/new row.
;

SELECT d.schoolCode, -- step [45] prepare columns using [4]
	COUNT(d.deptCode) AS `Number of departments`,
	AVG(d.numStaff) AS `Average # of staff`
FROM department AS d  -- step [1] big source table
WHERE d.numStaff >= 5 -- step [2] filter the original row in the table in [1]
GROUP BY d.schoolCode  -- step [3] form groups by distinct values of the group by columns. The group -> the new row.
HAVING `Number of departments` >= 2 -- step [4]: filter the group/new row.
ORDER BY `Number of departments` ASC -- step [6] provide order to [5]
;

SELECT CONCAT(s.fname, ' ', s.lname) AS student,
    COUNT(e.classId) AS `Enrolled classes`
FROM student AS s LEFT JOIN enroll e ON (s.stuId = e.stuId)
GROUP BY student 
HAVING `Enrolled classes` >= 2
ORDER BY `Enrolled classes` DESC; 

SELECT CONCAT(s.fname, ' ', s.lname) AS student,
    COUNT(e.classId) AS `Enrolled classes`
FROM student AS s LEFT JOIN enroll e ON (s.stuId = e.stuId)
GROUP BY 1 
HAVING `Enrolled classes` >= 2
ORDER BY `Enrolled classes` DESC;