-- 3/26

use toyu
tee 2024_3_26_sql_log.txt

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

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].

-- Also list the student names 

SELECT s.StuId, -- group by column
	CONCAT(s.fname, ' ', s.lname) AS student, -- group by columns
	' => ', 
	COUNT(e.classId) AS `Number of classes` -- group function
FROM student AS s LEFT JOIN enroll AS e USING (stuId)
WHERE e.grade IS NOT NULL
GROUP BY s.stuId, student  -- group by columns
HAVING `Number of classes` >= 1
ORDER  BY`Number of classes` DESC;

--
SELECT s.StuId, -- group by column
	CONCAT(s.fname, ' ', s.lname) AS student, -- group by columns
	' => ', 
	COUNT(e.classId) AS `Number of classes`, -- group function
	CONCAT(e.classId, ': ', e.grade) AS classes -- semantic error: not a group by column, not a group function.
FROM student AS s LEFT JOIN enroll AS e USING (stuId)
WHERE e.grade IS NOT NULL
GROUP BY s.stuId, student  -- group by columns
HAVING `Number of classes` >= 1
ORDER  BY`Number of classes` DESC;

SELECT s.StuId, -- group by column
	CONCAT(s.fname, ' ', s.lname) AS student, -- group by columns
	' => ', 
	COUNT(e.classId) AS `Number of classes`, -- group function
	CONCAT(e.classId, ': ', e.grade) AS classes -- semantic error: not a group by column, not a group function.
FROM student AS s LEFT JOIN enroll AS e USING (stuId)
WHERE e.grade IS NOT NULL
GROUP BY s.stuId, student  -- group by columns
HAVING `Number of classes` >= 1
ORDER  BY`Number of classes` DESC;

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

SELECT s.StuId, -- group by column
	CONCAT(s.fname, ' ', s.lname) AS student, -- group by columns
	' => ', 
	COUNT(e.classId) AS `Number of classes`, -- group function
	GROUP_CONCAT(CONCAT(e.classId, ': ', e.grade)) AS classes -- semantic error: not a group by column, not a group function.
FROM student AS s LEFT JOIN enroll AS e USING (stuId)
WHERE e.grade IS NOT NULL
GROUP BY s.stuId, student  -- group by columns
HAVING `Number of classes` >= 1
ORDER  BY`Number of classes` DESC;			 
			 
SELECT s.StuId, -- group by column
	CONCAT(s.fname, ' ', s.lname) AS student, -- group by columns
	COUNT(e.classId) AS `Number of classes`, -- group function
	GROUP_CONCAT(CONCAT(e.classId, ': ', e.grade) ORDER BY e.classId DESC SEPARATOR '; ') AS classes -- semantic error: not a group by column, not a group function.
FROM student AS s LEFT JOIN enroll AS e USING (stuId)
WHERE e.grade IS NOT NULL
GROUP BY s.stuId, student  -- group by columns (named group by columns)
HAVING `Number of classes` >= 1
ORDER  BY`Number of classes` DESC;

SELECT s.StuId, -- group by column
	CONCAT(s.fname, ' ', s.lname) AS student, -- group by columns
	COUNT(e.classId) AS `Number of classes`, -- group function
	GROUP_CONCAT(CONCAT(e.classId, ': ', e.grade) ORDER BY e.classId DESC SEPARATOR '; ') AS classes -- semantic error: not a group by column, not a group function.
FROM student AS s LEFT JOIN enroll AS e USING (stuId)
WHERE e.grade IS NOT NULL
GROUP BY 1, 2  -- group by columns (positional colum ns)
HAVING `Number of classes` >= 1
ORDER  BY`Number of classes` DESC;			

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])
			 
			 
[2] Can you write a query to generate the following output?
+----------+------------------------------+------------+----------+----------+
| deptCode | deptName                     | numFaculty | numMajor | numMinor |
+----------+------------------------------+------------+----------+----------+
| ACCT     | Accounting                   |          1 |        0 |        0 |
| ARTS     | Arts                         |          1 |        2 |        0 |
| CINF     | Computer Information Systems |          2 |        2 |        3 |
| CSCI     | Computer Science             |          4 |        3 |        1 |
| ENGL     | English                      |          1 |        0 |        2 |
| ITEC     | Information Technology       |          2 |        2 |        2 |
| MATH     | Mathematics                  |          0 |        0 |        0 |
+----------+------------------------------+------------+----------+----------+
7 rows in set (0.00 sec)


[2.1] deptCode + numFaculty

SELECT f.deptCode, 
	COUNT(f.facId) AS numFaculty
FROM faculty AS f
GROUP BY f.deptCode;


[2.2] deptCode + numMajor
SELECT s.major AS deptCode, COUNT(s.stuId) AS numMajor
    FROM student AS s
    GROUP BY s.major;
	
[2.3]

SELECT s.minor AS deptCode, COUNT(s.stuId) AS numMinor
    FROM student AS s
    GROUP BY s.minor

[2.4]

WITH ma AS -- scope of ma is within the current SQL
   (SELECT s.major AS deptCode, COUNT(s.stuId) AS numMajor
    FROM student AS s
    GROUP BY s.major),
mi AS
   (SELECT s.minor AS deptCode, COUNT(s.stuId) AS numMinor
    FROM student AS s
    GROUP BY s.minor),
f AS
   (SELECT f.deptCode, COUNT(f.facId) AS numFaculty
    FROM faculty AS f
    GROUP BY f.deptCode)
SELECT d.deptCode,
   d.deptName,
   IFNULL(f.numFaculty, 0) AS numFaculty,
   IFNULL(ma.numMajor, 0) AS numMajor,
   IFNULL(mi.numMinor, 0) AS numMinor
FROM department AS d LEFT JOIN ma USING (deptCode)
   LEFT JOIN mi USING (deptCode)
   LEFT JOIN f USING (deptCode);
-- ma not defined.

-- 3/19

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;