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

-- 8/21
use toyu;

SELECT * FROM student;

-- logging.

tee 2024_8_21_sql_log.txt

source createtoyu.sql

[1] List the stuId, names and credits of students majoring in CSCI and have 30 to
60 credits (ach) in the following format.
+--------+-------+-------+---------+
| stuId | fname | lname | credits |
+--------+-------+-------+---------+
| 100000 | Tony | Hawk | 40 |
| 100001 | Mary | Hawk | 35 |
+--------+-------+-------+---------+
2 rows in set (0.001 sec)


-- declarative analysis

[1] Output: column name/label: column value
    stuId
    fname
    lname
    credit:ach

[2] Source:
    student
    
[3] Condition: (problem condition) majoring in CSCI and have 30 to 60 credits (ach)
    major = 'CSCI'
    ach >= 30 AND ach <= 60

SELECT DISTINCT -- output
FROM -- source
WHERE -- conditions

SELECT DISTINCT stuId, fname, lname, 
    ach AS credits -- alias
-- output
FROM student
-- source
WHERE -- conditions
     major = 'CSCI'
AND ach >= 30 AND ach <= 60;

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

[1] Output
    s.fname
    s.lname
    e.classId
    e.grade

[2] SOurce:
   student AS s
   enroll AS e

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