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




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

   
-- logging.

tee 2024_8_21_sql_log.txt

source createtoyu.sql

mysql -u s2 -p

[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 columns: column name: column value
    stuId:stuId
    fname
    lname
    credit:ach

[1] Sources
    student

[3] COnditions: students majoring in CSCI and have 30 to
60 credits (ach)
   major = 'CSCI'
   ach >= 30
   ach <= 60
   
SELECT DISTINCT-- output columns
FROM -- sources
WHERE -- conditions

SELECT DISTINCT stuId, fname, lname, ach AS credits -- alias
-- output columns
FROM student
-- sources
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)

-- Declarative Analysis

[1] Output columns: column name: column value
    s.fname
    s.lname
    e.classId
    e.grade

[1] Sources
    student AS s
    enroll AS e

[3] COnditions: 
    Problem condition: minoring in CINF or ITEC 
        minor = 'CINF' OR 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 -- join condition
AND (minor = 'CINF' OR minor = 'ITEC');