CREATE TEMPORARY TABLE t1
(A INT);

-- Context Free Grammar (CFG)
-- Backus Naur Form (BNF)

CREATE TEMPORARY TABLE IF NOT EXISTS t1
(A INT NOT NULL);

-- session variable.

SET @dept = 'CSCI';

SELECT @dept;

SELECT *
FROM student
WHERE major = @dept;

SELECT * from t1;


CREATE TABLE s1
AS SELECT * FROM student;

SELECT * FROM s1;

DESC s1;

DESC Student;

CREATE TABLE s2 
LIKE student;


INSERT INTO s2
SELECT * FROM student;

SELECT * FROM s2;


UPDATE ENROLL
SET grade = 'A'
WHERE stuId = 100000 
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 *
FROM student
WHERE ach >= 30
AND ach <= 60;

SELECT *
FROM student
WHERE ach BETWEEN 13 AND 18;

-- 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 at least one  class.

SELECT DISTINCT e.stuID FROM enroll AS e;

-- [2] All student info
SELECT DISTINCT *
FROM student AS s;

-- [3] Remove [1] as stuId from [2]
SELECT DISTINCT *
FROM student AS s
WHERE s.stuId NOT IN (SELECT DISTINCT e.stuID FROM enroll AS e);


-- 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)
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 >=   -- arithmetic comparison.
   (SELECT DISTINCT MAX(ach) FROM student);
   -- (SELECT DISTINCT MAX(ach) FROM student) -> {(125)} -- implicit data conversion -> 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;

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
WHERE s.ach + 60 >= t1.max
ORDER BY `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;


-- for 

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

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

SELECT DISTINCT s.major, COUNT(s.stuId) AS `Number of students`
FROM student AS s
WHERE s.ach > 15
GROUP BY s.major;

SELECT DISTINCT s.major, COUNT(s.stuId) AS `Number of students`
FROM student AS s
WHERE s.ach > 15
GROUP BY s.major
HAVING `Number of students` >= 2;

SELECT s.StuId, COUNT(e.classId) AS `Number of classes`
FROM student AS s LEFT JOIN enroll AS e USING (stuId)
GROUP BY s.stuId
HAVING `Number of classes` >= 1
ORDER  BY`Number of classes` DESC;