mysql -u yue -p

SELECT * FROM student;

-- 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');