tee 2025_8_27_sql_log.txt

INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES (100000,'Bun','Yue','CSCI','MATH',64,1011);

SELECT *
FROM student;

INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES (333000,'Bun','Yue','CSCI','MATH',64,1011);

INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES (333000,'Susan','Yue','ECON','MATH',22,2314);


INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES (355601,'Susan','Yue','ECON','MATH',22,2314);

INSERT INTO Department(deptCode, deptName, schoolCode, numStaff) VALUES ('ECON','Economics','HHS',2);

INSERT INTO Department(deptCode, deptName, schoolCode, numStaff) VALUES ('ECON','Economics','HSH',2);

INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES (355601,'Susan','Yue','ECON','MATH',22,2314);

INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES (355601,'Susan','Yue','ECON','MATH',22,1018);


Q1. stuId, names, major and minor of all students majoring in CSCI.

-- declarative analysis
[1] Output:

[2] Data Sourcea:

[3] Conditions:
    [3a] problem conditions
    [3b] join conditions


-- basic declarative analysis
[1] Output: stuId, fname, lname, major, minor

[2] Data Sources: student

[3] Conditions:
    [3a] problem conditions: majoring in CSCI: major = 'CSCI'
    [3b] join conditions: none

-- SELECT 
SELECT DISTINCT stuId, fname, lname, major, minor 
    -- output; select clause
FROM student -- sources; from clause
WHERE major = 'CSCI'; -- conditions; where clause

-- SELECT 
SELECT DISTINCT  -- output; select clause
FROM -- sources; from clause
WHERE -- conditions; where clause



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

-- declarative analysis
[1] Output: label (name): value
    s.stuId
    student: s.fname + ' ' + s.lname
    major: d.deptName
[2] Data Sources:
    student AS s (alias)
    department AS d (alias)
[3] Conditions:
    [3a] problem conditions: minoring in ITEC and having 1016
as their faculty advisors: s.minor = 'ITEC' AND s.advisor = 1016
    [3b] join conditions: s.major (FK) = d.deptCode (PK of the parent table).
    
SELECT DISTINCT s.stuId,
    CONCAT(s.fname, ' ' , s.lname) AS student,
    d.deptName AS major
    -- output; select clause
FROM student AS s, department AS d 
    -- sources; from clause
WHERE s.major = d.deptCode
AND s.minor = 'ITEC' 
AND s.advisor = 1016; 

-- conditions; where clause

SELECT DISTINCT s.stuId,
    CONCAT(s.fname, ' ' , s.lname) AS student,
    d.deptName AS major
    -- output; select clause
FROM student AS s INNER JOIN department AS d 
    ON (s.major = d.deptCode)
    -- sources; from clause
WHERE s.minor = 'ITEC' 
AND s.advisor = 1016;