-- logging.
-- 2/25/25

mysql -u yue -p

use toyu;

SELECT * FROM student;

tee 2025_2_25_sql_log.txt

CREATE SCHEMA tinker;

SHOW TABLE;

CREATE TABLE p
( A CHAR(2));

CREATE TABLE IF NOT EXISTS q
( A CHAR(2) NOT NULL);

CREATE TABLE r IF NOT EXISTS 
( A CHAR(2) NOT NULL);

CREATE TABLE s2
SELECT * FROM toyu.student;
-- create a permanent table in the MySQL server.

SELECT *
FROM s2;

CREATE TEMPORARY TABLE s3
SELECT * FROM toyu.student;
-- create as a temporate table in the current client session; like a session variable.

SELECT *
FROM s3;

CREATE TABLE s4 LIKE toyu.student;

SELECT *
FROM s4;

INSERT INTO s4
SELECT * FROM toyu.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;

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

UPDATE Student
SET major = 'ITEC';

-- a
SELECT DISTINCT *
FROM Student
WHERE ach BETWEEN 30 AND 70;

-- b
SELECT DISTINCT *
FROM Student
WHERE ach >= 30 AND 
ach <= 70;


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



SHOW TABLES;


SELECT 1;

SELECT s.stuId, d.deptName AS major
FROM Student AS s INNER JOIN department AS d 
    ON (s.major = d.deptCode);
    
-- show all students
SELECT s.stuId, IF(d.deptName IS NULL, 'No major', d.deptName) AS major
FROM Student AS s LEFT JOIN department AS d 
    ON (s.major = d.deptCode);

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

-- subqueries in the WHERE course

-- students not enrolled in any class.

-- [1] stuId enrolled some classes.
SELECT DISTINCT e.stuID FROM enroll AS e;

-- [2] student info for those not enrolled in any class.

SELECT DISTINCT *
FROM student AS s
WHERE s.stuId NOT IN (SELECT DISTINCT e.stuID FROM enroll AS e);

    
-- subqueries as derived tables.

-- 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 INNER JOIN
   (SELECT DISTINCT MAX(ach) AS max FROM student) AS m -- an alias is required. [1] find max ach: 125
WHERE s.ach + 60 >= m.max;

-- 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);
   -- SQL: impicit type conversion of a table with 1 column  of 1 number value -> number)
   
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] find max ach: 125
   -- m cannot be used here.
WHERE -- m can be used from here on.
    s.ach + 60 >= m.max;

WITH m AS
    (SELECT DISTINCT MAX(ach) AS max FROM student) -- step [1]
    -- use m from here on.
    -- can do recursion.
SELECT DISTINCT s.stuId, -- step [2]
   CONCAT(s.fname, ' ', s.lname) AS student,
   s.ach AS credits
FROM student AS s, m
WHERE s.ach + 60 >= m.max;

SELECT MAX(ach) AS max FROM student;

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;



SELECT s.*, s.major
FROM student AS s;


SELECT s.stuId, s.major -- [3] construct columns
FROM student AS s -- [1] big raw table: raw rows
WHERE ach > 0; -- [2] filter out the rows.


SELECT s.stuId, s.major -- [3] construct columns
FROM student AS s -- [1] big raw table: raw rows
WHERE ach > 0; -- [2] filter out the rows.

SELECT s.stuId, s.major -- [4] construct output columns
FROM student AS s -- [1] big raw table: old raw rows
WHERE ach > 0 -- [2] filter out the rows.
GROUP BY s.major; -- [3] From group with the same group by column: s.major; each value of s.major defines a group. New group -> new row.


SELECT s.stuId, s.major -- [3] construct columns
FROM student AS s -- [1] big raw table: raw rows
WHERE ach > 0; -- [2] filter out the rows.

SELECT s.major, COUNT(s.stuId) AS `no of major` -- [4] construct output columns
FROM student AS s -- [1] big raw table: old raw rows
WHERE ach > 0 -- [2] filter out the rows.
GROUP BY s.major; -- [3] From group with the same group by column: s.major; each value of s.major defines a group. New group -> new row.


SELECT s.stuId, s.major -- [3] construct columns
FROM student AS s -- [1] big raw table: raw rows
WHERE ach > 0; -- [2] filter out the rows.

SELECT s.major, COUNT(s.stuId) AS `no of major` -- [5] construct output columns
FROM student AS s -- [1] big raw table: old raw rows
WHERE ach > 0 -- [2] filter out the rows.
GROUP BY s.major -- [3] From group with the same group by column: s.major; each value of s.major defines a group. New group -> new row.
HAVING `no of major`  > 1;  [4] filter out the group/new row.