-- 3/25
tee 2024_3_25_sql_log.txt
use toyu


SELECT s.major, COUNT(s.stuId) AS `Number of major`
FROM student AS s
WHERE s.ach > 15 -- 9 intial rows
GROUP BY s.major -- 5 groups: 5 new rows
HAVING `Number of major` > 1;

SELECT s.major, d.deptName AS department, '= ',
	COUNT(s.stuId) AS `Number of major`
FROM student AS s INNER JOIN department AS d ON (s.major = d.deptCode)
WHERE s.ach > 15 -- 9 intial rows
GROUP BY s.major, department -- 5 groups: 5 new rows
HAVING `Number of major` > 1;


SELECT s.major, d.deptName AS department, '= ',
	COUNT(s.stuId) AS `Number of major`,
	CONCAT(s.fname, ' ', s.lname) AS students
FROM student AS s INNER JOIN department AS d ON (s.major = d.deptCode)
WHERE s.ach > 15 -- 9 intial rows
GROUP BY s.major, department -- 5 groups: 5 new rows
HAVING `Number of major` > 1;


-- GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])
			 
SELECT s.major, d.deptName AS department, '= ',
	COUNT(s.stuId) AS `Number of major`,
	GROUP_CONCAT(CONCAT(s.fname, ' ', s.lname) ORDER BY s.fname ASC SEPARATOR '; ') AS students
FROM student AS s INNER JOIN department AS d ON (s.major = d.deptCode)
WHERE s.ach > 15 -- 9 intial rows
GROUP BY s.major, department -- 5 groups: 5 new rows
HAVING `Number of major` > 1;
			 
SELECT s.major, d.deptName AS department, '= ',
	COUNT(s.stuId) AS `Number of major`,
	GROUP_CONCAT(CONCAT(s.fname, ' ', s.lname) ORDER BY s.fname ASC SEPARATOR '; ') AS students
FROM student AS s INNER JOIN department AS d ON (s.major = d.deptCode)
WHERE s.ach > 15 -- 9 intial rows
GROUP BY s.major, department -- 5 groups: 5 new rows
HAVING `Number of major` > 1
ORDER BY `Number of major` DESC;

SELECT s.major, d.deptName AS department, '= ',
	COUNT(s.stuId) AS `Number of major`,
	GROUP_CONCAT(CONCAT(s.fname, ' ', s.lname) ORDER BY s.fname ASC SEPARATOR '; ') AS students
FROM student AS s INNER JOIN department AS d ON (s.major = d.deptCode)
WHERE s.ach > 15 -- 9 intial rows
GROUP BY 1, 2-- 5 groups: 5 new rows
HAVING `Number of major` > 1
ORDER BY `Number of major` DESC;

-- Q2: just numFaculty;
SELECT d.deptCode, d.deptName,
	COUNT(f.facId) AS numFaculty
FROM department AS d LEFT JOIN faculty AS f ON (f.deptCode = d.deptCode)
GROUP BY d.deptCode, d.deptName;


			 
-- 3/20
tee 2024_3_20_sql_log.txt

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

-- [1] stuId enrolled in one or more class.
SELECT DISTINCT e.stuID FROM enroll AS e;

-- [2] stuId not enrolled in any class.
SELECT DISTINCT s.stuId
FROM student AS s
WHERE s.stuId NOT IN (SELECT DISTINCT e.stuID FROM enroll AS e);

-- [3] answer using [2] 
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)
FROM student;

SELECT DISTINCT COUNT(stuId)
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) -- {(125)} -- implicit type conversion -> 125
   
 ;

-- subqueries as derived tables.
SELECT DISTINCT s.stuId,  -- [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]
WHERE s.ach + 60 >= m.max;

-- CTE common table expression
WITH m AS (SELECT DISTINCT MAX(ach) AS max FROM student) -- [1]
SELECT DISTINCT s.stuId, -- [2]
   CONCAT(s.fname, ' ', s.lname) AS student,
   s.ach AS credits
FROM student AS s INNER JOIN m -- an alias is required.
WHERE s.ach + 60 >= m.max;

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;


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

WITH RECURSIVE Super(SEId) AS
(  SELECT SupervisorEmpId AS SEId FROM Employee AS e WHERE e.EmpId = 'E100' -- initial condition/action
   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;



SELECT DISTINCT <<result_columns>> -- [3] 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


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.*
FROM student AS s
WHERE s.ach > 15;

SELECT s.major
FROM student AS s
WHERE s.ach > 15;

SELECT DISTINCT s.major
FROM student AS s
WHERE s.ach > 15;

SELECT s.major
FROM student AS s
WHERE s.ach > 15 -- 9 intial rows
GROUP BY s.major; -- 5 groups: 5 new rows

SELECT s.major, COUNT(s.stuId)
FROM student AS s
WHERE s.ach > 15 -- 9 intial rows
GROUP BY s.major; -- 5 groups: 5 new rows

SELECT s.major, COUNT(s.stuId) AS `Number of major`
FROM student AS s
WHERE s.ach > 15 -- 9 intial rows
GROUP BY s.major -- 5 groups: 5 new rows
HAVING `Number of major` > 1;


SELECT d.deptCode, d.deptName, COUNT(s.stuId) AS `Number of major`
FROM department AS d LEFT JOIN student AS s ON (d.deptCode = s.major)
WHERE s.ach > 15 -- 9 intial rows
GROUP BY d.deptCode, d.deptName -- 5 groups: 5 new rows
HAVING `Number of major` > 1;

-- 3/18
CREATE TABET t1
(x INT);

CREATE SCHEMA tinker;
use tinker;


CREATE TABLE s2
SELECT * FROM toyu.student;

SELECT *
FROM s2;

CREATE TEMPORARY TABLE s3
SELECT * FROM toyu.student;


SET @major = 'CSCI';

SELECT *
FROM s3;

CREATE TABLE s4 LIKE student;


SELECT s.*
FROM toyu.student AS s
WHERE s.major = @major;


SELECT *
FROM s4;

INSERT INTO s4
SELECT * FROM student;

SELECT *
FROM s4;

SHOW TABLES;

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

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

SHOW TABLES;

use toyu;

SELECT * FROM student;

DELETE FROM student
WHERE 1;

UPDATE enroll
SET grade = 'A'
WHERE stuId = 100000 
AND classId = 10003;

UPDATE enroll
SET grade = 'A';