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