tee 2025_8_27_sql_log.txt

SELECT * 
FROM student;

INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (100000,'Ben','Yue','CSCI','ITEC',55,1011);
    
INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (200000,'Ben','Yue','CSCI','ITEC',55,1011);
    
INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (200000,'Min','Yue','CINF','HIST',45,3011);

INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES (200011,'Min','Yue','CINF','HIST',45,3011);    
   
INSERT INTO Department(deptCode, deptName, schoolCode, numStaff) VALUES ('HIST','History','BHSH',2);

   
INSERT INTO Department(deptCode, deptName, schoolCode, numStaff) VALUES ('HIST','History','HSH',2);

SELECT * FROM department;

INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES (200011,'Min','Yue','CINF','HIST',45,3011);  

INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES (200011,'Min','Yue','CINF','HIST',45,1018);  

-- find all information of students majoring in CSCI.


-- declarative analysis

[1] output columns
[2] data sources
[3] conditions: 
    [3a] problem condition
    [3b] join conditions: FK-PK mostly
    
    
[1] output columns: all columns about students:
[2] data sources: student
[3] conditions: 
    [3a] problem condition: majoring in CSCI.
    [3b] join conditions: FK-PK mostly   
    
SELECT DISTINCT -- [1] output
FROM  -- [2] data sources
WHERE ; -- [3] conditions

    
SELECT DISTINCT * -- [1] output (* matches all columns
FROM student -- [2] data sources
WHERE major = 'CSCI'; -- [3] conditions

-- SPring 2025 HW #1.
[1] List the stuId, names, and majors of students minoring in ITEC and having 1016
as their faculty advisors in the following manner.
+--------+-----------------+------------------------------+
| stuId | student | major |
+--------+-----------------+------------------------------+
| 100006 | Lillian Johnson | Computer Information Systems |
+--------+-----------------+------------------------------+
1 row in set

[1] output columns (label: value)
    [1] s.stuId
    [2] student: s.fname + ' ' + s.lname (last name)
    [3] major: d.deptName
[2] data sources:
    [1] student AS s (alias)
    [2] department AS d
[3] conditions: 
    [3a] problem condition: minoring in ITEC and having 1016:
        minor = 'ITEC' AND advisor = 1016
    [3b] join conditions: FK-PK mostly:
        s.major = d.deptCode (e.g., CINF for Lillian Johnson.

SELECT DISTINCT s.stuId,
    CONCAT(s.fname,' ', s.lname) AS student,
    d.deptName AS major
    -- [1] output
FROM student AS s,
    department AS d
    -- [2] data sources
WHERE s.major = d.deptCode
AND minor = 'ITEC' 
AND advisor = 1016; -- [3] conditions

-- SELECT statement
SELECT DISTINCT s.stuId, -- SELECT clause
    CONCAT(s.fname,' ', s.lname) AS student,
    d.deptName AS major
    -- [1] output
FROM student AS s INNER JOIN department AS d -- FROM clause
        ON (s.major = d.deptCode)
    -- [2] data sources
WHERE minor = 'ITEC' -- WHERE clause 
AND advisor = 1016; -- [3] conditions