-- 3/20

tee 2024_3_20_sql_log.txt

UPDATE enroll
SET grade = 'A'
WHERE stuId = 100002
AND classId - 10002;

UPDATE enroll
SET grade = 'A'
WHERE stuId = 100002
AND classId = 10002;

UPDATE enroll
SET grade = 'A';

SELECT DISTINCT <<result_columns>> -- [3] construct result columns
FROM <<source_tables>> -- [1] conceptually join sources to form a large table
WHERE <<conditions_for_inclusion>> -- [2] Filter rows from [1]

SELECT s.*  -- all swimmers with first name starting with B.
FROM Swimmer AS s
WHERE s.FName LIKE 'B%';


-- 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);

-- student with the maximum number of ach.
SELECT DISTINCT MAX(ach) -- MAX: group function
FROM student;

-- 
SELECT CONCAT(fname, ' ', lname) -- regular function
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 >=  -- number >= number
   (SELECT DISTINCT MAX(ach) FROM student) -- subqery -> {(125)} -- implicit type conversion -> 125
;

-- 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: a derived table from a subquery.
   -- m cannot be used here
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]
-- m can be used immediately
SELECT DISTINCT s.stuId, -- [2]
   CONCAT(s.fname, ' ', s.lname) AS student,
   s.ach AS credits
FROM student AS s INNER JOIN m 
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,
      s.major
    FROM student AS s, t1;
	
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;

CREATE SCHEMA CTETinker;
USE SCHEMA CTEtinker;
CREATE OR REPLACE TABLE Employee (
    EmpId CHAR(7) NOT NULL,
    SupervisorEmpId CHAR(7) NULL,
    CONSTRAINT Emp_EmpId_pk PRIMARY KEY (EmpId),
    CONSTRAINT Emp_SupervisorEmpId_fk FOREIGN KEY (SupervisorEmpId)
        REFERENCES Employee(EmpId)
);

INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES
   ('E3', null);
INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES
   ('E15', 'E3');
INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES
   ('E50', 'E15');
INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES
   ('E75', 'E50');
INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES
   ('E100', 'E75');
INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES
   ('E102', 'E75');
INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES
   ('E70', 'E50');
INSERT INTO Employee(EmpId, SupervisorEmpId) VALUES
   ('E103', 'E70');
  
SELECT * FROM Employee;

-- supervisor of E100:
SELECT SupervisorEmpId AS SEId FROM Employee AS e WHERE e.EmpId = 'E100';

-- all direct and indirect supervisors of E100.

WITH RECURSIVE Super(SEId) AS
(  SELECT SupervisorEmpId AS SEId FROM Employee AS e WHERE e.EmpId = 'E100' -- initial condition/action: SEId: E75
   UNION ALL -- union all: add rows created by the recursive action to the result, table Super.
   SELECT e.SupervisorEmpId AS SEId -- recursive action
      FROM Employee AS e INNER JOIN Super
      WHERE e.EmpId = Super.SEId
      AND e.SupervisorEmpId IS NOT NULL
      -- exit condition: when the recursive action returns an empty table.
)
SELECT *
FROM Super;





-- 3/18
SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS student
FROM student AS s INNER JOIN enroll AS e USING (stuId)
WHERE e.grade = 'A'
AND s.stuId IN (SELECT DISTINCT stuId FROM enroll WHERE grade = 'B+');

SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS student
FROM student AS s INNER JOIN enroll AS e USING (stuId)
WHERE e.grade = 'A'

INTERSECT

SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS student
FROM student AS s INNER JOIN enroll AS e USING (stuId)
WHERE e.grade = 'B+'

CREATE TABLE t10
(A INT);


CREATE MUSIC t10 (A INT);

CREATE TABLE t10 hello (A INT);

CREATE TEMPORARY TABLE  t3
AS SELECT * FROM student;

SELECT * 
FROM t3;

SET @minor = 'CSCI';

SELECT @minor;

SELECT *
FROM student
WHERE minor = @minor;


CREATE  TABLE  t5
AS SELECT * FROM student;


CREATE  TABLE  t8
LIKE student;

INSERT INTO t8
SELECT * FROM student;