-- 8/27

tee 2024_8_27_sql_log.txt

source Createtoyu.sql

SELECT *
FROM student
WHERE stuId = 100000;

SELECT *
FROM enroll
WHERE stuId = 100000;

INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (100000,'Bun','Yue','CSCI','CITEC',22,1011);
    
INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (222000,'Bun','Yue','CSCI','ITEC',22,1011);
    
    SELECT *
FROM enroll
WHERE classId =10000;

INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (333000,'Bun007','Yue','SECR','ITEC',32,1011);
    
INSERT INTO Department(deptCode, deptName, schoolCode, numStaff) VALUES 
    ('SECR','Secret Agent','CSE',100);

INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (333000,'Bun007','Yue','SECR','ITEC',32,1011);
    
INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (444000,'Bun117','Yue','SECR','MUSI',32,2222);

INSERT INTO Department(deptCode, deptName, schoolCode, numStaff) VALUES 
    ('MUSI','Music','COB',1);
  
INSERT INTO Department(deptCode, deptName, schoolCode, numStaff) VALUES 
    ('MUSI','Music','BUS',1);
  
    
INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (444000,'Bun117','Yue','SECR','MUSI',32,2222);
    
    
INSERT INTO Faculty(facId, fname, lname, deptCode, `rank`) VALUES
    (2222,'Linda','Smith','MUSI','Professor');
    
   INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (444000,'Bun117','Yue','SECR','MUSI',32,2222); 
    
  
 [ 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 (Conceptual model of SQL)
   
[1] Source:  -- form a raw/large/big table containing all needed from the sourec.
    student AS s  -- alias
    enroll AS e
 
SELECT DISTINCT s.*, e.* -- [2]
FROM student AS s, enroll AS e;  -- [1] create the 'big' table

SELECT DISTINCT s.*, e.* -- [2]
FROM student AS s, enroll AS e  -- [1] create the 'big' table
WHERE s.stuId = e.stuId; 

SELECT DISTINCT s.*, e.* -- [2]
FROM student AS s INNER JOIN enroll AS e ON (s.stuId = e.stuId)
;  -- [1] create the 'big' table



    
[2] COnditions: filter rows from [1] the big table
   problem conditions: minoring in CINF or ITEC 
    s.minor = 'CINF' OR s.minor = 'ITEC'
   join condition: s.stuId = e.stuId
   
SELECT DISTINCT s.*, e.* -- [2]
FROM student AS s INNER JOIN enroll AS e ON (s.stuId = e.stuId)  -- [1] create the 'big' table
WHERE s.minor = 'CINF' OR s.minor = 'ITEC'; -- [2]
   
[3] Output columns: construct output from the filtered table.
    student.fname
    student.lname
    enroll.classId
    enroll.grade
    
SELECT DISTINCT s.fname, s.lname, e.classId, e.grade-- [3] construct from [2] filtered table
FROM student AS s INNER JOIN enroll AS e ON (s.stuId = e.stuId)  -- [1] create the 'big' table
WHERE s.minor = 'CINF' OR s.minor = 'ITEC'; -- [2] filter the big table
       

SELECT DISTINCT s.stuId,
    CONCAT(s.fname, ' ', s.lname) AS student, e.classId, e.grade-- [3] construct from [2] filtered table
FROM student AS s INNER JOIN enroll AS e ON (s.stuId = e.stuId)  -- [1] create the 'big' table
WHERE s.minor = 'CINF' OR s.minor = 'ITEC'; -- [2] filter the big table
       
Q14. Show the student names who have enrolled in at least two classes.

100000, ..1, ..2, ..4, ..5, ..6, ..7
  
Q14a. Show the student id who have enrolled in one class.
SELECT DISTINCT e.stuId
FROM enroll AS e;

Q14b. Show the student name who have enrolled in one class.
SELECT DISTINCT s.stuId, s.fname, s.lname
FROM student AS s INNER JOIN enroll AS e ON (s.stuId = e.stuId);

-- Answer:
SELECT DISTINCT s.stuId, s.fname, s.lname
FROM student AS s INNER JOIN enroll AS e ON (s.stuId = e.stuId)
    INNER JOIN enroll AS e ON (s.stuId = e.stuId);


SELECT DISTINCT s.stuId, s.fname, s.lname
FROM student AS s INNER JOIN enroll AS e1 ON (s.stuId = e1.stuId)
    INNER JOIN enroll AS e2 ON (s.stuId = e2.stuId);

SELECT DISTINCT s.*, e1.*, e2.*
FROM student AS s INNER JOIN enroll AS e1 ON (s.stuId = e1.stuId)
    INNER JOIN enroll AS e2 ON (s.stuId = e2.stuId);
    
SELECT DISTINCT s.stuId, s.fname, s.lname
FROM student AS s INNER JOIN enroll AS e1 ON (s.stuId = e1.stuId)
    INNER JOIN enroll AS e2 ON (s.stuId = e2.stuId)
WHERE e1.classId <> e2.classId;  
    
    
   SELECT DISTINCT s.stuId, s.fname, s.lname
FROM student AS s INNER JOIN enroll AS e ON (s.stuId = e.stuId AND e.classId >= 2)
;

 
    
    
-- 8/20
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');