-- 8/28


tee 2024_8_28_sql_log.txt

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.

Declarative analysis:

[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] Conditions: have received a grade A in a class offered by a faculty from the CSCI department.
    a grade A in a class: e.grade = 'A'
    offered by a faculty from the CSCI department:
        offered by faculty id: c.facId
        facId is a faculty in CSCI: f.deptCode = 'CSCI'

[3] Output: student names (fname, lname) and their major names (e.g. Computer Science)
    s.fname
    s.lname
    d.deptName

SELECT DISTINCT s.fname, s.lname,
    d.deptName AS `Major department`
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 c.facId AND f.deptCode = 'CSCI';


14. Show the student names who have enrolled in at least two classes.
    Result: 100000, ..1, ..2, ..4, ..5, ..6, ..7

14.a Show the stuId of students enrolled in one or more classes.
SELECT DISTINCT e.stuId
FROM enroll AS e;

14.a Show the student name, and stuId of students enrolled in one or more classes
SELECT DISTINCT s.stuId, CONCAT(s.fname, ' ', s.lname) AS student
FROM student AS s INNER JOIN enroll AS e ON (s.stuId = e.stuId);

14.
SELECT DISTINCT s.stuId, CONCAT(s.fname, ' ', s.lname) AS student
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, CONCAT(s.fname, ' ', s.lname) AS student
FROM student AS s INNER JOIN enroll AS e1 ON (s.stuId = e1.stuId)
    INNER JOIN enroll AS e2 ON (s.stuId = e2.stuId);
    -- 000008 is incrrectly present 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, CONCAT(s.fname, ' ', s.lname) AS student
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;
    -- 000008 is incrrectly present in the result    

-- 8/26

mysql -u yue -p

tee 2024_8_26_sql_log.txt

SELECT DISTINCT *
FROM Student
WHERE StuId = 100000;


SELECT DISTINCT *
FROM Student
WHERE lname = 'Hawk';


INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (2200000,'Bun','Yue','ITEC','CINF',25,1011);
    
INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (2200000,'Bunny','Yue','CINF','ITEC',15,1011);    

ERROR 1062 (23000): Duplicate entry '2200000' for key 'PRIMARY'

    
INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (3330000,'Bunny','Yue','CINF','ITEC',15,1011);    


    
INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (4440000,'Funny','Yue','COME','ITEC',55,1011);   

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`toyu`.`student`, CONSTRAINT `Student_major_fk` FOREIGN KEY (`major`) REFERENCES `department` (`deptCode`) ON DELETE CASCADE)
MariaDB [toyu]>

INSERT INTO Department(deptCode, deptName, schoolCode, numStaff) VALUES 
    ('COME','Comedy','CSE',3);
    
INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (4440000,'Funny','Yue','COME','ITEC',55,1011);   
    
 INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (5550000,'Grumpy','Yue','COME','MUSI',75,1221);   
    
INSERT INTO Department(deptCode, deptName, schoolCode, numStaff) VALUES 
    ('MUSI','Music','HSH',2);
    
INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (5550000,'Grumpy','Yue','COME','MUSI',75,1221);   
    
 INSERT INTO Faculty(facId, fname, lname, deptCode, `rank`) VALUES
    (1221,'Pauline','Smithson','MUSI','Professor');
    
        
 INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (5550000,'Grumpy','Yue','COME','MUSI',75,1221);   
    
-- 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)

Delcarative analysis


[1] SOurce:
   student AS s
   enroll AS e
   
[2] COndition
    Problem condition: minoring in CINF or ITEC
        s.minor = 'CINF' OR s.minor = 'ITEC'
    JOIN condition: s.stuId = e.stuId
     
[3] Output
    s.fname
    s.lname
    e.classId
    e.grade
    
SELECT DISTINCT s.fname, s.lname, e.classId, e.grade  -- [3]
FROM    student AS s, enroll AS e  -- [1]
WHERE s.stuId = e.stuId -- [2]
AND (s.minor = 'CINF' OR s.minor = 'ITEC');

Conceptual model:
[1] SOurce: form a table from the sources.

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 -- [2]  
    ;
    
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 s.minor = 'CINF' OR s.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 s.minor = 'CINF' OR s.minor = 'ITEC' -- [2]       
;


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