-- 10/1

tee 2024_10_1_sql_log.txt

use toyu;

SELECT StuId
FROM enroll;


SELECT DISTINCT StuId
FROM enroll;

SELECT DISTINCT d.deptCode, d.deptName AS department
FROM Department AS d
WHERE d.schoolCode = 'CSE';


SELECT DISTINCT d.deptCode, d.deptName AS department
WHERE d.schoolCode = 'CSE'
FROM Department AS d;



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

CREATE TABLE s2
SELECT * FROM toyu.student;

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [IGNORE | REPLACE]
    [AS] query_expression

SELECT *
FROM s2;

CREATE TEMPORARY TABLE s3
SELECT * FROM toyu.student;

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 toyu.student;
DESC s2;
DESC s3;
DESC s4;

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

SHOW TABLES;

DROP SCHEMA IF EXISTS tinker;

UPDATE student
SET major = 'CSCI';

UPDATE student
SET major = 'CINF'
WHERE lname = 'Hawk';


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

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

SELECT DISTINCT *
FROM Student AS s
WHERE EXISTS
(SELECT *  -- a subquery
FROM Enroll AS e
WHERE e.stuId = s.stuId);

SELECT DISTINCT s.*
FROM Student AS s INNER JOIN Enroll AS e USING (stuId);


SELECT DISTINCT s.*
FROM student AS s
WHERE s.lname LIKE '%k%';

SELECT 'Michael!' REGEXP '.*';

SELECT DISTINCT s.*
FROM student AS s
WHERE s.lname REGEXP '.*k.*';

SELECT DISTINCT s.*
FROM student AS s
WHERE s.lname REGEXP '.*[jkl].*';

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

SELECT DISTINCT s.fname, s.lname, c.classId, e.grade
FROM student AS s INNER JOIN enroll e USING (stuId) -- Join condition
    INNER JOIN class AS c USING (classId) -- Join condition
WHERE c.semester = 'Fall' -- Problem condition
AND c.year = 2019; -- Problem condition

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

SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS student,
    d.deptName AS `minor department`,
    d.numStaff
FROM student AS s LEFT JOIN department AS d ON (s.minor = d.deptCode);

SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS student,
    IFNULL(d.deptName, 'Undeclared') AS `minor department`
FROM student AS s LEFT JOIN department AS d ON (s.minor = d.deptCode);

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

WITH m AS  -- CTE
(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;

-- using CTE: scope is the current SQL
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;
-- CT t1 is no longer defined (outside scope)

-- using temporary tables. Good if tt1 is reused. Scope of tt1 is the entire session.
CREATE TEMPORARY TABLE  tt1 AS
   (SELECT MAX(ach) AS max FROM student);
   
SELECT s.stuId,
   s.ach AS `ach credits`,
   tt1.max - s.ach AS `diff from max credits of all`
FROM student AS s, tt1
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;

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

WITH RECURSIVE Super(SEId) AS
(  SELECT SupervisorEmpId AS SEId FROM Employee AS e WHERE e.EmpId = 'E100' -- initial condition/action
   -- initially, add E75 to CT Super(SEId)
   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 s.major, s.stuId, s.ach -- [3] 
FROM Student AS s  -- [1]
WHERE s.ach >= 20;  -- [2] 


-- not good. MySQL gives no error
SELECT s.major, s.stuId, s.ach -- [4] 
FROM Student AS s  -- [1]
WHERE s.ach >= 20 -- [2]
GROUP BY s.major;  -- [3] 

SELECT s.major, s.stuId, s.ach -- [3] 
FROM Student AS s  -- [1]
WHERE s.ach >= 20;  -- [2] 

SELECT s.major, COUNT(s.stuId) AS `Number of students`, 
    AVG(s.ach) AS `Average ach` -- [4] 
FROM Student AS s  -- [1]
WHERE s.ach >= 20 -- [2]
GROUP BY s.major;  -- [3] 

SELECT s.major, s.stuId, s.ach -- [3] 
FROM Student AS s  -- [1]
WHERE s.ach >= 20;  -- [2] 

SELECT 'Hi', s.major, COUNT(s.stuId) AS `Number of students`, 
    AVG(s.ach) AS `Average ach` -- [4] 
FROM Student AS s  -- [1]
WHERE s.ach >= 20 -- [2]
GROUP BY s.major;  -- [3] 

SELECT 'Hi', s.major, COUNT(s.stuId) AS `Number of students`, 
    AVG(s.ach) AS `Average ach` -- [4] 
FROM Student AS s  -- [1]
WHERE s.ach >= 20 -- [2]
GROUP BY s.major  -- [3] 
ORDER BY `Number of students` DESC, `Average ach` ASC;  -- [5]

SELECT 'Hi', s.major, COUNT(s.stuId) AS `Number of students`, 
    AVG(s.ach) AS `Average ach` -- [5] 
FROM Student AS s  -- [1]
WHERE s.ach >= 20 -- [2]
GROUP BY s.major  -- [3] 
HAVING `Average ach` > 45 -- [4]
ORDER BY `Number of students` DESC, `Average ach` ASC;  -- [6]

SELECT s.major, COUNT(s.stuId) AS `Number of students`, 
    AVG(s.ach) AS `Average ach` -- [5] 
FROM Student AS s  -- [1]
WHERE s.ach >= 20 
AND `Average ach` > 45 -- [2]
GROUP BY s.major  -- [3] 
-- [4]
ORDER BY `Number of students` DESC, `Average ach` ASC;  -- [6]


SELECT s.major, COUNT(s.stuId) AS `Number of students`, 
    AVG(s.ach) AS `Average ach` -- [5] 
FROM Student AS s  -- [1]
WHERE s.ach >= 20 
AND COUNT(s.stuId) > 45 -- [2]
GROUP BY s.major  -- [3] 
-- [4]
ORDER BY `Number of students` DESC, `Average ach` ASC;  -- [6]


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

COlumns 1 and 3:

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

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

Columns 1 and 4:

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

Columns 1 and 5:

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