-- 10/14
tee 2024_10_14_sql_log.txt
use toyu


SELECT s.major, COUNT(stuId) AS numMajor,
    GROUP_CONCAT(CONCAT(s.fname, ' ', s.lname)
        ORDER BY s.lname ASC, s.fname ASC ) AS students
FROM Student AS s
GROUP BY s.major;



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


SELECT *
FROM student AS s CROSS JOIN enroll AS e;

-- equi-join

SELECT s.*, e.*
FROM student AS s CROSS JOIN enroll AS e USING (stuId);

SELECT *
FROM student NATURAL JOIN enroll;
















-- 10/7

tee 2024_10_7_sql_log.txt
use toyu;

SELECT DISTINCT 'I am ready to go', f.deptCode, COUNT(f.facId) AS numFaculty-- [4] COUNT: group function
FROM faculty AS f -- [1]
WHERE f.deptCode IN ('CSCI', 'CINF', 'ITEC') -- [2]
GROUP BY f.deptCode  -- [3]
;

SELECT DISTINCT 'I am ready to go', f.deptCode, f.facId, CONCAT(f.fname, ' ', f.lname) -- [4] COUNT: group function
FROM faculty AS f -- [1]
WHERE f.deptCode IN ('CSCI', 'CINF', 'ITEC'); -- [2]

SELECT DISTINCT 'I am ready to go', f.deptCode, COUNT(f.facId) AS numFaculty, -- [4] COUNT: group function
    GROUP_CONCAT(CONCAT(f.fname, ' ', f.lname) ORDER BY f.lname ASC SEPARATOR '; ') AS faculty
FROM faculty AS f -- [1]
WHERE f.deptCode IN ('CSCI', 'CINF', 'ITEC') -- [2]
HAVING numFaculty < = 3
GROUP BY f.deptCode  -- [3]
;

SELECT DISTINCT 'I am ready to go', f.deptCode, COUNT(f.facId) AS numFaculty, -- [4] COUNT: group function
    GROUP_CONCAT(CONCAT(f.fname, ' ', f.lname) ORDER BY f.lname ASC SEPARATOR '; ') AS faculty
FROM faculty AS f -- [1]
WHERE f.deptCode IN ('CSCI', 'CINF', 'ITEC') -- [2]
GROUP BY f.deptCode  -- [3]
HAVING numFaculty <= 3
;


SELECT CONCAT(s.fname, ' ', s.lname) AS student,
    COUNT(e.classId) AS `Enrolled classes`
FROM student AS s LEFT JOIN enroll e ON (s.stuId = e.stuId)
GROUP BY student 
ORDER BY `Enrolled classes` DESC; 

[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 |
+----------+------------------------------+------------+----------+----------+

--  [2.a] columns 1 and 3.

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

SELECT DISTINCT  f.deptCode, COUNT(f.facId) AS numFaculty 
FROM department AS d INNER JOIN faculty AS f ON (f.deptCode = d.deptCode)
GROUP BY f.deptCode;

SELECT DISTINCT  f.deptCode, COUNT(f.facId) AS numFaculty 
FROM department AS d LEFT JOIN faculty AS f ON (f.deptCode = d.deptCode)
GROUP BY f.deptCode;

SELECT DISTINCT  d.deptCode, COUNT(f.facId) AS numFaculty 
FROM department AS d LEFT JOIN faculty AS f ON (f.deptCode = d.deptCode)
GROUP BY d.deptCode;


-- [2.b] columns 1 and 4.

SELECT DISTINCT  d.deptCode, COUNT(s.stuId) AS numMajor 
FROM department AS d LEFT JOIN student AS s ON (d.deptCode = s.major)
GROUP BY d.deptCode;


-- [2.c] columns 1 and 5.

SELECT DISTINCT  d.deptCode, COUNT(s.stuId) AS numMinor 
FROM department AS d LEFT JOIN student AS s ON (d.deptCode = s.minor)
GROUP BY d.deptCode;

-- [2]
WITH t1 AS
(SELECT DISTINCT  d.deptCode, COUNT(f.facId) AS numFaculty 
FROM department AS d LEFT JOIN faculty AS f ON (f.deptCode = d.deptCode)
GROUP BY d.deptCode),
t2 AS
(SELECT DISTINCT  d.deptCode, COUNT(s.stuId) AS numMajor 
FROM department AS d LEFT JOIN student AS s ON (d.deptCode = s.major)
GROUP BY d.deptCode),
t3 AS
(SELECT DISTINCT  d.deptCode, COUNT(s.stuId) AS numMinor 
FROM department AS d LEFT JOIN student AS s ON (d.deptCode = s.minor)
GROUP BY d.deptCode)
SELECT d.deptCode, d.deptName, t1.numFaculty, t2.numMajor, t3.numMinor
FROM department AS d INNER JOIN t1 USING (deptCode)
    INNER JOIN t2 USING (deptCode)
    INNER JOIN t3 USING (deptCode);
    
WITH temp AS
(SELECT DISTINCT sc.schoolName AS college, d.deptName AS department,
   COUNT(s.stuId) As deptMajor
FROM school AS sc INNER JOIN department AS d ON (sc.schoolCode = d.schoolCode)
   LEFT JOIN student AS s ON (s.major = d.deptCode)
GROUP BY college, department)
SELECT temp.college, temp.department,
   temp.deptMajor AS `major in department`,
   SUM(deptMajor) OVER(PARTITION BY college) AS `major in college`,
   SUM(deptMajor) OVER() AS `major in university`
FROM temp;  
 
    
    
    
    
    
    
    
-- Student names and number of classes enrolled.
-- More than 2 classes to be included in the result.
SELECT CONCAT(s.fname, ' ', s.lname) AS student,
   COUNT(e.classId) AS `Enrolled classes`
FROM student AS s INNER JOIN enroll e ON (s.stuId = e.stuId)
GROUP BY student
HAVING `Enrolled classes` > 2
ORDER BY `Enrolled classes` DESC; 



-- Student names and number of classes enrolled.
-- More than 2 classes to be included in the result.
SELECT CONCAT(s.fname, ' ', s.lname) AS student,
   COUNT(e.classId) AS `Enrolled classes`
FROM student AS s INNER JOIN enroll e ON (s.stuId = e.stuId)
GROUP BY student
HAVING `Enrolled classes` > 2
ORDER BY `Enrolled classes` DESC; 







-- 10/2
use toyu;

SELECT stuId
FROM enroll;

SELECT DISTINCT stuId
FROM enroll;

SELECT DISTINCT s.stuId, s.fname
FROM student AS s
WHERE s.lname = 'Hawk';

SELECT DISTINCT s.stuId, s.fname
WHERE s.lname = 'Hawk'
FROM student AS s; -- error


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

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

SELECT *
FROM s2;

CREATE TEMPORARY TABLE s3
SELECT * FROM toyu.student;

SELECT *
FROM s3;

CREATE TABLE s4 LIKE toyu.student; -- creation

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

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

SHOW TABLES;

DROP SCHEMA IF EXISTS tinker;

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

UPDATE Student
SET major = 'CSCI';


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

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


SELECT DISTINCT S.StuId, IF(s.ach <= 60, 'lower', 'upper') AS level
FROM student AS s;


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


-- student in selected majors
SELECT DISTINCT *
FROM Student
WHERE major IN ('CSCI', 'CINF', 'ITEC');

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

-- List the names of the students with their minors (in full name).
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);

-- 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 >=
   (SELECT DISTINCT MAX(ach) FROM student);

-- 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
(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 -- 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
ORDER BY `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 from max credits of all`
FROM student AS s, t1
ORDER BY `diff from max credits of all` DESC, s.stuId ASC;



-- CTE
-- multiple common tables (not efficient; used as demonstration.)
WITH t1 AS -- scope of CT is just the current SELECT statement.
   (SELECT MAX(ach) AS max FROM student),
   -- scope should be as small as possible.
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;


-- temporary tables: scopes: the session.

CREATE TABLE tt1 AS
SELECT MAX(ach) AS max FROM student;

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

-- supervisors of employee 'E100':  -- E75
SELECT DISTINCT SupervisorEmpId
FROM Employee
WHERE EmpId = 'E100';


-- all (direct and indirect) supervisors of employee 'E100':

WITH RECURSIVE Super(SEId) AS
(  SELECT SupervisorEmpId AS SEId FROM Employee AS e WHERE e.EmpId = 'E100' -- initial condition/action
   -- initial condition -> E75
   UNION ALL -- union all: add rows created by the recursive action to the result, table Super.
   -- recursive part:
   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 f.deptCode, f.facId, f.rank
FROM faculty AS f
WHERE f.deptCode IN ('CSCI', 'CINF', 'ITEC');

-- smeantic error. Executed by MySQL. Errors in Oracle.
SELECT DISTINCT f.deptCode, f.facId, f.rank -- [4]
FROM faculty AS f -- [1]
WHERE f.deptCode IN ('CSCI', 'CINF', 'ITEC') -- [2]
GROUP BY f.deptCode  -- [3]
;

SELECT DISTINCT 'I am ready to go', f.deptCode, COUNT(f.facId) AS numFaculty-- [4] COUNT: group function
FROM faculty AS f -- [1]
WHERE f.deptCode IN ('CSCI', 'CINF', 'ITEC') -- [2]
GROUP BY f.deptCode  -- [3]
;