mysql -u yue -p

show databases;

SELECT * FROM student;

SELECT stuId, fname, lname, major
FROM student;

tee 2024_8_20_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. (problem condition)
+--------+-------+-------+---------+
| stuId | fname | lname | credits |
+--------+-------+-------+---------+
| 100000 | Tony | Hawk | 40 |
| 100001 | Mary | Hawk | 35 |
+--------+-------+-------+---------+
2 rows in set (0.001 sec)

SELECT DISTINCT -- output columns
FROM -- source tables
WHERE -- conditions for inclusion in the result.


Declarative analysis:

[1] Output columns:
    stuId: stuId
    fname
    lname
    credits (label, column name): ach (column value)
    
[2] Source: student
[3] COnditions: students majoring in CSCI and have 30 to
60 credits (ach) in the following format. (problem condition)

SELECT DISTINCT stuId, fname, lname, ach -- output columns
FROM student -- source tables
WHERE -- conditions for inclusion in the result.
    major = 'CSCI'
AND ach >= 30
AND ach <= 60;

SELECT DISTINCT stuId, fname, lname, 
    ach AS credits -- output columns: alias
FROM student -- source tables
WHERE -- conditions for inclusion in the result.
    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:
    student.fname
    student.lname
    enroll.classId
    enroll.grade
    
[2] Source: 
    student AS s  -- alias
    enroll AS e
[3] COnditions:
   problem conditions: 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 -- join condition
AND (s.minor = 'CINF' OR s.minor = 'ITEC');