-- 8/29

tee 2024_8_28_sql_log.txt

source h2sol.sql

3. All student names enrolled in the class with id 10003.

[1] source
    Student AS s
    Enroll AS e
    
    join conditions:
    [1] s.stuId = e.stuId

[2] condition: enrolled in the class with id 10003
    e.classId = 10003

[3] output
    All student names : s.fname, s.lname
    
SELECT DISTINCT s.fname, s.lname
FROM Student AS s INNER JOIN Enroll AS e ON (s.stuId = e.stuId)
WHERE e.classId = 10003;


13. Show the student names and their major names for all students who have received a grade A in a class offered by a faculty from the CSCI department.

[1] Source:
    Student AS s
    department AS d
    enroll AS e
    class AS c
    faculty AS f
    
    Join conditions:
    [1] s.major = d.deptCode
    [2] s.stuId = e.stuId
    [3] e.classId = c.classId
    [4] c.facId = f.facId

[2] COndition: all students who have received a grade A in a class offered by a faculty from the CSCI department.
    who have received a grade A in a class: e.grade = 'A'
        a class offered by a faculty facId: c.facId
        in a class offered by a faculty from the CSCI department: f.deptCode = 'CSCI'
        
[3] Output: the student names and their major names 
        All student names : s.fname, s.lname
        their major names  (e.g. Computer Science): d.deptName

SELECT DISTINCT s.fname, s.lname, d.deptName AS major
FROM Student AS s INNER JOIN department AS d ON (s.major = d.deptCode) 
    INNER JOIN enroll AS e ON (s.stuId = e.stuId)
    INNER JOIN class AS c ON (e.classId = c.classId)
    INNER JOIN faculty AS f ON (c.facId = f.facId)
WHERE e.grade = 'A' AND f.deptCode = 'CSCI';
        
14. Show the student names who have enrolled in at least two classes.      
   Expected result: 100000, ..1, ..2, ..4, ..5, ..6, ..7

14a. stuId of students who have enrolled in at least one class.  

SELECT DISTINCT e.stuId 
FROM enroll AS e;    
        
 14b. stuId and names of students who have enrolled in at least one class.  

SELECT DISTINCT s.stuId, s.fname, s.lname
FROM STudent AS s INNER JOIN enroll AS e ON (s.stuId = e.stuId);

        
14. Show the student names who have enrolled in at least two classes.      
   Expected result: 100000, ..1, ..2, ..4, ..5, ..6, ..7
               
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);
    
    ERROR 1066 (42000): Not unique table/alias: 'e'
    
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);   

100008 incorrectly included in the result.

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;  

 
-- 8/26

tee 2024_8_26_sql_log.txt
source createtoyu.sql


mysql -u yue -p

SELECT * FROM student;

SELECT * FROM student
WHERE stuId = 100000;

SELECT * FROM student
WHERE major = 'CSCI';

INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (100000,'Bun','YUe','CSCI','MATH',40,1011);
ERROR 1062 (23000): Duplicate entry '100000' for key 'PRIMARY'
INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (200000,'Bun','YUe','CSCI','MATH',40,1011);

SELECT * FROM student
WHERE stuId = 100000;

SELECT * FROM enroll
WHERE stuId = 100000;
 
INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (300000,'Bun_C1','Yue','MAGI','MATH',40,1011);  
    
INSERT INTO Department(deptCode, deptName, schoolCode, numStaff) VALUES 
    ('MAGI','Magic','CSE',4);
    
INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (300000,'Bun_C1','Yue','MAGI','MATH',40,1011);  
    
INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (500000,'Bun_C1123','Yue','MAGI','MUSI',40,1565);  
    
INSERT INTO Department(deptCode, deptName, schoolCode, numStaff) VALUES 
    ('MUSI','Music','HSH',14);

    
INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (500000,'Bun_C1123','Yue','MAGI','MUSI',40,1565); 
    
INSERT INTO Faculty(facId, fname, lname, deptCode, `rank`) VALUES
    (1565,'Joe','Smith','MUSI','Professor');
    

INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (500000,'Bun_C1123','Yue','MAGI','MUSI',40,1565); 
    

Spring 2024:
[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

[1] Sources: create a big/raw table from sources
    student AS s
    enroll AS e

[2] COnditions: filter the raw table from [1]
    Problem condition: minoring in CINF or ITEC 
        minor = 'CINF' OR minor = 'ITEC'
    Join condition: s.stuId = e.stuId
    
[3] Output columns: column name: column value -- declare your output columns frm [2]
    s.fname
    s.lname
    e.classId
    e.grade
    
    -- Conceptual model
SELECT DISTINCT s.*, e.*  -- [3]
FROM student AS s, enroll AS e; -- [1]

SELECT DISTINCT s.*, e.*  -- [3]
FROM student AS s, enroll AS e -- [1]
WHERE s.stuId = e.stuId;

SELECT DISTINCT s.*, e.*  -- [3]
FROM student AS s INNER JOIN enroll AS e ON (s.stuId = e.stuId)-- [1]
;
    
 
SELECT DISTINCT s.*, e.*  -- [3]
FROM student AS s INNER JOIN enroll AS e ON (s.stuId = e.stuId)-- [1]
WHERE (minor = 'CINF' OR minor = 'ITEC'); -- [2]
    
SELECT DISTINCT s.fname, s.lname, e.classId, e.grade  -- [3]
FROM student AS s INNER JOIN enroll AS e ON (s.stuId = e.stuId)-- [1]
WHERE (minor = 'CINF' OR minor = 'ITEC'); -- [2]   

    
SELECT DISTINCT s.stuId,
    CONCAT(s.fname, ' ', s.lname, '[', s.major, ']') AS student, e.classId, e.grade  -- [3] 
FROM student AS s INNER JOIN enroll AS e ON (s.stuId = e.stuId)-- [1]
WHERE (minor = 'CINF' OR minor = 'ITEC'); -- [2]