-- 10/2
tee 2024_10_2_sql_log.txt


SELECT stuId
FROM enroll;


SELECT DISTINCT stuId
FROM enroll;

SELECT DISTINCT f.facId, f.lname
FROM faculty AS f
WHERE rank = 'Professor';

SELECT DISTINCT f.facId, f.lname
WHERE rank = 'Professor'
FROM faculty AS f;



DROP SCHEMA IF EXISTS tinker;
CREATE SCHEMA tinker;
USE tinker;

CREATE TABLE s2  -- creation + population.
SELECT * FROM toyu.student;

SELECT *
FROM s2;

CREATE TEMPORARY TABLE s3
SELECT * FROM toyu.student;

SELECT *
FROM s3;

-- CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
 --   { LIKE old_tbl_name | (LIKE old_tbl_name) }
CREATE TABLE s4 LIKE toyu.student; -- creation of table

SELECT *
FROM s4;

INSERT INTO s4  -- population.
SELECT * FROM toyu.student;

SELECT *
FROM s4;

SHOW TABLES;

-- Note that keys and constraints of student are missing in s2 and S3.
DESC toyu.student;
DESC s2;
DESC s4;

DESC s3;

DROP TABLE s2;
DROP TABLE s3;
DROP TABLE s4;

SHOW TABLES;

DROP SCHEMA IF EXISTS tinker;

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]
    
UPDATE Student
SET major = 'ITEC'
WHERE StuId = 100000;

UPDATE Student
SET major = 'CSCI';

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

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


-- student with credits in a range.
SELECT DISTINCT *
FROM Student
WHERE ach>= 30 AND ach <= 70;

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

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


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


SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS student,
    IFNULL(d.deptName, 'Undeclared') AS `minor department`
FROM student AS s LEFT JOIN department AS d ON (s.minor = 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);

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


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

-- 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); -- implicit type converstion: a table -> a number: 125

-- 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 m AS -- step [1]
(SELECT DISTINCT MAX(ach) AS max FROM student)
SELECT DISTINCT s.stuId,
   CONCAT(s.fname, ' ', s.lname) AS student,
   s.ach AS credits
FROM student AS s INNER JOIN m 
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
ORDER BY `ach credits` DESC;

-- CTE solution
WITH t1 AS -- Common table scope: just the current SQL statement.
    -- scope should be as small as possible.
   (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;
-- t1 and t2 no longer defined.

-- temporary tables;
-- scope: client session
CREATE TEMPORARY TABLE tt1 AS
SELECT MAX(ach) AS max FROM student;

SELECT * FROM tt1;

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;

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;

DROP SCHEMA IF EXISTS CTEtinker;

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 DISTINCT SupervisorEmpId
FROM Employee
WHERE EmpId = 'E100';

-- All (direct/indirect supervisor of E100: E75, E50, E15, E3.
WITH RECURSIVE Super(SEId) AS
(  SELECT SupervisorEmpId AS SEId FROM Employee AS e WHERE e.EmpId = 'E100' -- initial condition/action
   -- init: Super: 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 -- negation of exit condition.
      -- exit condition: when the recursive action returns an empty table.
)
SELECT *
FROM Super;


SELECT DISTINCT s.major, s.stuId, s.ach
FROM student AS s
WHERE s.major IN ('CSCI', 'CINF', 'ITEC');

-- Bad meaning but MySQL allows it.
SELECT DISTINCT s.major, s.stuId, s.ach
FROM student AS s
WHERE s.major IN ('CSCI', 'CINF', 'ITEC')
GROUP BY s.major;

SELECT DISTINCT 'ready to go in 4 minutes.', s.major, COUNT(s.stuId) AS numStudent, 
    AVG(s.ach) AS `average ach` -- group functions, group by columns, constants
FROM student AS s
WHERE s.major IN ('CSCI', 'CINF', 'ITEC')
GROUP BY s.major;