-- 9/3

tee 2024_9_3_sql_log.txt


SELECT *
FROM student
WHERE 0;

SELECT *
FROM student
WHERE 1;

SELECT *
FROM student
WHERE 2697;

SELECT *
FROM student
WHERE '0';

SELECT *
FROM student
WHERE '145';

SELECT FALSE IS NULL,  
   TRUE IS NULL,
   0 IS NULL,
   1 IS NULL,
   "" IS NULL,
   "Hey" IS NULL,
   NULL IS NULL,
   NULL IS NOT NULL;

SELECT *
FROM Student
WHERE '1 = 2';

SELECT *
FROM Student
WHERE 1 = '0';

SELECT *
FROM Student
WHERE 0 = '0';



-- Q. List all enrollment records without 2 or more n_alerts.
-- Naive solution
SELECT e.*
FROM enroll AS e
WHERE e.n_alerts < 2;


-- 8/27

tee 2024_8_27_sql_log.txt

source Createtoyu.sql

SELECT *
FROM student
WHERE stuId = 100000;

SELECT *
FROM enroll
WHERE stuId = 100000;

INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (100000,'Bun','Yue','CSCI','CITEC',22,1011);
    
INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (222000,'Bun','Yue','CSCI','ITEC',22,1011);
    
    SELECT *
FROM enroll
WHERE classId =10000;

INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (333000,'Bun007','Yue','SECR','ITEC',32,1011);
    
INSERT INTO Department(deptCode, deptName, schoolCode, numStaff) VALUES 
    ('SECR','Secret Agent','CSE',100);

INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (333000,'Bun007','Yue','SECR','ITEC',32,1011);
    
INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (444000,'Bun117','Yue','SECR','MUSI',32,2222);

INSERT INTO Department(deptCode, deptName, schoolCode, numStaff) VALUES 
    ('MUSI','Music','COB',1);
  
INSERT INTO Department(deptCode, deptName, schoolCode, numStaff) VALUES 
    ('MUSI','Music','BUS',1);
  
    
INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (444000,'Bun117','Yue','SECR','MUSI',32,2222);
    
    
INSERT INTO Faculty(facId, fname, lname, deptCode, `rank`) VALUES
    (2222,'Linda','Smith','MUSI','Professor');
    
   INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (444000,'Bun117','Yue','SECR','MUSI',32,2222); 
    
  
 [ 2] List the student names of minoring in CINF or ITEC with classes (classId) and
grades they have taken in the following manner.
+---------+---------+---------+-------+
| fname | lname | classId | grade |
+---------+---------+---------+-------+
| Tony | Hawk | 10000 | A |
| Tony | Hawk | 10001 | A |
| Tony | Hawk | 10002 | B+ |
| Tony | Hawk | 10003 | C |
| Tony | Hawk | 10004 | A- |
| Tony | Hawk | 11001 | D |
| Mary | Hawk | 10000 | NULL |
| Mary | Hawk | 10001 | A- |
| David | Hawk | 10000 | B- |
| David | Hawk | 10002 | B+ |
| David | Hawk | 10003 | D |
| Lillian | Johnson | 10004 | C+ |
| Lillian | Johnson | 10005 | A |
+---------+---------+---------+-------+
13 rows in set (0.001 sec)


Declarative analysis (Conceptual model of SQL)
   
[1] Source:  -- form a raw/large/big table containing all needed from the sourec.
    student AS s  -- alias
    enroll AS e
 
SELECT DISTINCT s.*, e.* -- [2]
FROM student AS s, enroll AS e;  -- [1] create the 'big' table

SELECT DISTINCT s.*, e.* -- [2]
FROM student AS s, enroll AS e  -- [1] create the 'big' table
WHERE s.stuId = e.stuId; 

SELECT DISTINCT s.*, e.* -- [2]
FROM student AS s INNER JOIN enroll AS e ON (s.stuId = e.stuId)
;  -- [1] create the 'big' table



    
[2] COnditions: filter rows from [1] the big table
   problem conditions: minoring in CINF or ITEC 
    s.minor = 'CINF' OR s.minor = 'ITEC'
   join condition: s.stuId = e.stuId
   
SELECT DISTINCT s.*, e.* -- [2]
FROM student AS s INNER JOIN enroll AS e ON (s.stuId = e.stuId)  -- [1] create the 'big' table
WHERE s.minor = 'CINF' OR s.minor = 'ITEC'; -- [2]
   
[3] Output columns: construct output from the filtered table.
    student.fname
    student.lname
    enroll.classId
    enroll.grade
    
SELECT DISTINCT s.fname, s.lname, e.classId, e.grade-- [3] construct from [2] filtered table
FROM student AS s INNER JOIN enroll AS e ON (s.stuId = e.stuId)  -- [1] create the 'big' table
WHERE s.minor = 'CINF' OR s.minor = 'ITEC'; -- [2] filter the big table
       

SELECT DISTINCT s.stuId,
    CONCAT(s.fname, ' ', s.lname) AS student, e.classId, e.grade-- [3] construct from [2] filtered table
FROM student AS s INNER JOIN enroll AS e ON (s.stuId = e.stuId)  -- [1] create the 'big' table
WHERE s.minor = 'CINF' OR s.minor = 'ITEC'; -- [2] filter the big table
       
Q14. Show the student names who have enrolled in at least two classes.

100000, ..1, ..2, ..4, ..5, ..6, ..7
  
Q14a. Show the student id who have enrolled in one class.
SELECT DISTINCT e.stuId
FROM enroll AS e;

Q14b. Show the student name who have enrolled in one class.
SELECT DISTINCT s.stuId, s.fname, s.lname
FROM student AS s INNER JOIN enroll AS e ON (s.stuId = e.stuId);

-- Answer:
SELECT DISTINCT s.stuId, s.fname, s.lname
FROM student AS s INNER JOIN enroll AS e ON (s.stuId = e.stuId)
    INNER JOIN enroll AS e ON (s.stuId = e.stuId);


SELECT DISTINCT s.stuId, s.fname, s.lname
FROM student AS s INNER JOIN enroll AS e1 ON (s.stuId = e1.stuId)
    INNER JOIN enroll AS e2 ON (s.stuId = e2.stuId);

SELECT DISTINCT s.*, e1.*, e2.*
FROM student AS s INNER JOIN enroll AS e1 ON (s.stuId = e1.stuId)
    INNER JOIN enroll AS e2 ON (s.stuId = e2.stuId);
    
SELECT DISTINCT s.stuId, s.fname, s.lname
FROM student AS s INNER JOIN enroll AS e1 ON (s.stuId = e1.stuId)
    INNER JOIN enroll AS e2 ON (s.stuId = e2.stuId)
WHERE e1.classId <> e2.classId;  
    
    
   SELECT DISTINCT s.stuId, s.fname, s.lname
FROM student AS s INNER JOIN enroll AS e ON (s.stuId = e.stuId AND e.classId >= 2)
;