-- SQL workarea

-- 3/3

tee 2025_3_3_sql_log.txt

SELECT DISTINCT s.stuId,
   CONCAT(s.fname, ' ', s.lname) AS student,
   IFNULL(e.classId, '') AS classId,
   IFNULL(CONCAT(co.rubric, ' ', co.number), '') AS `CSCI course`,
   IFNULL(e.grade, '') AS grade
FROM enroll AS e INNER JOIN class AS c USING (classId)
   INNER JOIN course AS co ON (c.courseId = co.courseId AND co.rubric = 'CSCI' )
   RIGHT JOIN student AS s USING (stuId);

-- student with the maximum number of ach.
SELECT DISTINCT MAX(ach)
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);

-- subqueries as derived tables.
SELECT DISTINCT s.stuId,  -- step [2]
   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. step [1]
   -- m cannot be used here to define another subqueries.
WHERE s.ach + 60 >= m.max; -- m can be used here.

-- CTE
WITH m AS
(SELECT DISTINCT MAX(ach) AS max FROM student)  -- step [1]
   -- may use m here to define other common tables
SELECT DISTINCT s.stuId, -- step [2]
   CONCAT(s.fname, ' ', s.lname) AS student, -- regular function applies to one row
   s.ach AS credits
FROM student AS s INNER JOIN m
WHERE s.ach + 60 >= m.max;

-- multiple common tables (not efficient; used as demonstration.)
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;

SELECT MAX(ach) AS max FROM student;

WITH t1 AS
   (SELECT MAX(ach) AS max FROM student) -- max: group function applying to a group of rows.
SELECT s.stuId,
      s.ach AS `ach credits`,
      t1.max - s.ach AS diff,
      s.major
    FROM student AS s, t1;

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;


SELECT DISTINCT <<result_columns>> -- [5] construct result columns
FROM <<source_tables>> -- [1] conceptually join tables to form a large table to produce initial rows
WHERE <<conditions_for_inclusion>> -- [2] Filter initial rows
GROUP BY <<group_by_columns>>
         --[3] group initial rows into groups by values of the group_by_column. A group becomes a new row.
HAVING <<conditions for filtering group>> -- [4] filter groups
ORDER BY <<columns>>; -- [6] Order the result of [5].


SELECT s.stuId, s.major
FROM student AS s
WHERE s.ach >= 10
ORDER BY s.major;

SELECT s.stuId, s.major
FROM student AS s
WHERE s.ach >= 10
GROUP BY s.major  -- form group according to distinct values in s.major
ORDER BY s.major;


SELECT s.major, COUNT(s.stuId) AS `# major`
FROM student AS s
WHERE s.ach >= 10
GROUP BY s.major  -- form group according to distinct values in s.major
ORDER BY s.major;

CONCAT(s.fname, ' ', s.lname)


SELECT s.major, d.deptName,
    COUNT(s.stuId) AS `# major`
FROM student AS s LEFT JOIN department AS d ON (s.major = d.deptCode)
WHERE s.ach >= 10
GROUP BY s.major, d.deptName -- form group according to distinct values in s.major
ORDER BY s.major;

SELECT s.major, d.deptName,
    COUNT(s.stuId) AS `# major`
FROM student AS s LEFT JOIN department AS d ON (s.major = d.deptCode)
WHERE s.ach >= 10
GROUP BY s.major, d.deptName -- form group according to distinct values in s.major
HAVING `# major` > 1
ORDER BY s.major;


-- older


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

-- All students will be majoring in CSCI
UPDATE Student
SET major = 'CSCI';

-- operators:
-- student with credits in a range.
SELECT DISTINCT *
FROM Student
WHERE ach BETWEEN 30 AND 70;

-- a
SELECT DISTINCT *
FROM Student
WHERE ach BETWEEN 35 AND 66;

-- b
SELECT DISTINCT *
FROM Student
WHERE ach >= 35 
AND ach <= 66;




SELECT s.stuId, d.deptCode, d.deptName AS department
FROM Student AS s INNER JOIN department AS d ON (s.major = d.deptCode);


SELECT s.stuId, d.deptCode, d.deptName AS department
FROM Student AS s LEFT JOIN department AS d ON (s.major = d.deptCode);


SELECT s.stuId, d.deptCode, d.deptName AS department
FROM department AS d RIGHT JOIN  Student AS s  ON (s.major = d.deptCode);

-- subqueries in the WHERE course
-- students not enrolled in any class.
SELECT DISTINCT *
FROM student AS s
WHERE s.stuId NOT IN (SELECT DISTINCT e.stuID FROM enroll AS e);