-- logging.
-- 1/29

tee 2025_1_29_sql_log.txt

mysql -u yue -p -h dcm.uhcl.edu

use toyu;

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

ERROR 1062 (23000): Duplicate entry '100000' for key 'PRIMARY'

INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (500000,'Bun','Yue','CSCI','CINF',4,1011);
    
INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (500099,'Bunlo','Yue','MUSI','CINF',45,3011);
    
    
INSERT INTO Department(deptCode, deptName, schoolCode, numStaff) VALUES 
    ('MUSI','Music','CSE',8);


INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES
    (500099,'Bunlo','Yue','MUSI','CINF',45,3011);



INSERT INTO Faculty(facId, fname, lname, deptCode, `rank`) VALUES
    (3011,'Paula','Smithson','MUSI','Professor');
    
    


-- 1/15
SELECT * FROM student;

tee 2025_1_15_sql_log.txt

SHOW databases;

-- MySQL: scheme = database
DROP SCHEMA IF EXISTS toyu;

source Createtoyu.sql

[1] List the stuId, names, major departments and minor code of all students minoring in CSCI, CINF or ITEC in the following manner.
+--------+-----------------+------------------------------+-------+
| stuId | student | major | minor |
+--------+-----------------+------------------------------+-------+
| 100000 | Tony Hawk | Computer Science | CINF |
| 100001 | Mary Hawk | Computer Science | CINF |
| 100002 | David Hawk | Computer Science | ITEC |
| 100003 | Catherine Lim | Information Technology | CINF |
| 100006 | Lillian Johnson | Computer Information Systems | ITEC |
| 100009 | Linda King | Arts | CSCI |
+--------+-----------------+------------------------------+-------+
6 rows in set)

[1a] List the stuid, fname, lname, major, and minor of all students.

Declarative Analysis:
[1] Output:
[2] Sources:
[3] Conditions:

Analysis:
[1] Output:
       stuid, fname, lname, major, minor
[2] Sources:
    student
[3] Conditions: 
    none
    
SELECT DISTINCT  -- [1] select clause
FROM  -- [2] from clause
WHERE -- [3] conditions
;

SELECT DISTINCT stuid, fname, lname, major, minor  -- [1]
FROM student -- [2] 
;

[1b] List the stuid, name (e.g. 'Tony Hawk'), major, and minor of all students.

[1] Output: label: value
       stuid, 
       name: concatenate fname, ' ', lname.
       major, minor
[2] Sources:
    student
[3] Conditions: 
    none
    
    
SELECT DISTINCT stuid, 
    CONCAT(student.fname, ' ', lname) AS name -- alias: label
    , major, minor  -- [1]
FROM student -- [2] 
;

    
SELECT DISTINCT stuid, 
    CONCAT(student.fname, ' ' lname) AS name -- alias: label
    , major, minor  -- [1]
FROM student -- [2] 
;

-- give: ERROR 1583 (42000): Incorrect parameters in the call to native function 'CONCAT'

[1] List the stuId, names, major departments and minor code of all students minoring in CSCI, CINF or ITEC in the following manner.


[1] Output: label: value
       stuid, 
       name: concatenate fname, ' ', lname.
       major, minor
[2] Sources:
    student
[3] Conditions: minoring in CSCI, CINF or ITEC
       minor = 'CSCI' OR 'CINF' OR 'ITEC'  
    
   -- expected results: 00, 01, 02, 03, 06, 09 
SELECT DISTINCT stuid, 
    CONCAT(student.fname, ' ', lname) AS name -- alias: label
    , major, minor  -- [1]
FROM student -- [2] 
WHERE minor = 'CSCI' OR 'CINF' OR 'ITEC'  
;
-- mind your data types; mind your operators (types of the arguments)
-- (a)
SELECT DISTINCT stuid, 
    CONCAT(student.fname, ' ', lname) AS name -- alias: label
    , major, minor  -- [1]
FROM student -- [2] 
WHERE minor = 'CSCI' OR minor = 'CINF' OR minor = 'ITEC'  
;

-- alternative:
SELECT 2 IN (0,3,5,7);

--
SELECT 2 IN (2,3,5,7);
-- SQL: 0: false; 1: true

-- SEt membership function: whether an element is in a set or not.

-- (b)
SELECT DISTINCT stuid, 
    CONCAT(student.fname, ' ', lname) AS name -- alias: label
    , major, minor  -- [1]
FROM student -- [2] 
WHERE minor IN ('CSCI','CINF', 'ITEC')
;

[1d] Include the full name of the department.

[1] Output: label: value
       s.stuid, 
       name: concatenate s.fname, ' ', s.lname.
       major: d.deptName (e.g. Computer Science), 
       minor
[2] Sources:
    student AS s  -- alias
    department AS d -- alias
[3] Conditions: minoring in CSCI, CINF or ITEC
       minor = 'CSCI' OR 'CINF' OR 'ITEC'

SELECT DISTINCT s.stuid, 
    CONCAT(s.fname, ' ', s.lname) AS name -- alias: label
    , d.deptName AS major, minor  -- [1]
FROM student AS s,
     department AS d -- [2] 
WHERE minor IN ('CSCI','CINF', 'ITEC')
;   

-- 42 row.  

[1] Output: label: value
       s.stuid, 
       name: concatenate s.fname, ' ', s.lname.
       major: d.deptName (e.g. Computer Science), 
       minor
[2] Sources:
    student AS s  -- alias
    department AS d -- alias
[3] Conditions: 
    [3a] problem conditions: minoring in CSCI, CINF or ITEC
       minor = 'CSCI' OR 'CINF' OR 'ITEC'
    [3b] join conditions: usually: equality of the common attributes.
        s.major = d.deptCode -- (e.g. CSCI for Tony Hawk)
  
SELECT DISTINCT s.stuid, 
    CONCAT(s.fname, ' ', s.lname) AS name -- alias: label
    , d.deptName AS major, minor  -- [1]
FROM student AS s,
     department AS d -- [2] 
WHERE s.major = d.deptCode 
     AND minor IN ('CSCI','CINF', 'ITEC')
;  

-- alternative using explicit inner join
SELECT DISTINCT s.stuid, 
    CONCAT(s.fname, ' ', s.lname) AS name -- alias: label
    , d.deptName AS major, minor  -- [1]
FROM student AS s INNER JOIN department AS d 
    ON (s.major = d.deptCode) -- [2] 
WHERE minor IN ('CSCI','CINF', 'ITEC')
;  

. Include only students with a faculty advisor, and list also their
faculty advisor names in the following manner.
+--------+-----------------+------------------------------+-------+--------------+
| stuId | student | major | minor | advisor |
+--------+-----------------+------------------------------+-------+--------------+
| 100000 | Tony Hawk | Computer Science | CINF | Paul Smith |
| 100001 | Mary Hawk | Computer Science | CINF | Paul Smith |
| 100002 | David Hawk | Computer Science | ITEC | Mary Tran |
| 100006 | Lillian Johnson | Computer Information Systems | ITEC | Andrew Byre |
| 100009 | Linda King | Arts | CSCI | Art Allister |
+--------+-----------------+------------------------------+-------+--------------+
5 rows in set

[1] Output: label: value
       s.stuid, 
       name: concatenate s.fname, ' ', s.lname.
       major: d.deptName (e.g. Computer Science), 
       minor
       advsior: (e.g. Paul Smith) concatenate f.fname, ' ', f.lname.
[2] Sources:
    student AS s  -- alias
    department AS d -- alias
    faculty AS f
[3] Conditions: 
    [3a] problem conditions: minoring in CSCI, CINF or ITEC
       minor = 'CSCI' OR 'CINF' OR 'ITEC'
    [3b] join conditions: usually: equality of the common attributes.
        s.major = d.deptCode -- (e.g. CSCI for Tony Hawk)
        s.advisor = f.facId
 
SELECT DISTINCT s.stuid, 
    CONCAT(s.fname, ' ', s.lname) AS name -- alias: label
    , d.deptName AS major, minor,
    CONCAT(f.fname, ' ', f.lname) AS advsior  -- [1]
FROM student AS s INNER JOIN department AS d 
        ON (s.major = d.deptCode) 
    INNER JOIN faculty AS f ON (s.advisor = f.facId)-- [2] 
WHERE minor IN ('CSCI','CINF', 'ITEC')
; 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'faculty AS f ON (s.advisor = f.facId)
WHERE minor IN ('CSCI','CINF', 'ITEC')' at line 7  

[3] Repeat [2] but add the condition to show all students majoring or minoring in
'CSCI', 'CINF' or 'ITEC';
+--------+-----------------+------------------------------+-------+--------------+
| stuId | student | major | minor | advisor |
+--------+-----------------+------------------------------+-------+--------------+
| 100009 | Linda King | Arts | CSCI | Art Allister |
| 100005 | Linda Johnson | Computer Information Systems | ENGL | Daniel Kim |
| 100006 | Lillian Johnson | Computer Information Systems | ITEC | Andrew Byre |
| 100000 | Tony Hawk | Computer Science | CINF | Paul Smith |
| 100001 | Mary Hawk | Computer Science | CINF | Paul Smith |
| 100002 | David Hawk | Computer Science | ITEC | Mary Tran |
| 100004 | Larry Johnson | Information Technology | NULL | Deborah Gump |
+--------+-----------------+------------------------------+-------+--------------+
7 rows in set



[1] Output: label: value
       s.stuid, 
       name: concatenate s.fname, ' ', s.lname.
       major: d.deptName (e.g. Computer Science), 
       minor
       advsior: (e.g. Paul Smith) concatenate f.fname, ' ', f.lname.
[2] Sources:
    student AS s  -- alias
    department AS d -- alias
    faculty AS f
[3] Conditions: 
    [3a] problem conditions: ll students majoring or minoring in
'CSCI', 'CINF' or 'ITEC';
       minor = 'CSCI' OR 'CINF' OR 'ITEC'
    [3b] join conditions: usually: equality of the common attributes.
        s.major = d.deptCode -- (e.g. CSCI for Tony Hawk)
        s.advisor = f.facId
 
SELECT DISTINCT s.stuid, 
    CONCAT(s.fname, ' ', s.lname) AS name -- alias: label
    , d.deptName AS major, minor,
    CONCAT(f.fname, ' ', f.lname) AS advsior  -- [1]
FROM student AS s INNER JOIN department AS d 
        ON (s.major = d.deptCode) 
    INNER JOIN faculty AS f ON (s.advisor = f.facId)-- [2] 
WHERE minor IN ('CSCI','CINF', 'ITEC')
OR major IN ('CSCI','CINF', 'ITEC')
;

[4] List the class grade information of all courses taught by a CSCI faculty member
in the following manner.
+--------+-----------------+---------+-----------+-------------------------------------+-------+---------------+
| stuId | student | classId | course | course title | grade | faculty |
+--------+-----------------+---------+-----------+-------------------------------------+-------+---------------+
| 100000 | Tony Hawk | 10000 | CSCI 3333 | Data Structures | A | Paul Smith |
| 100001 | Mary Hawk | 10000 | CSCI 3333 | Data Structures | NULL | Paul Smith |
| 100002 | David Hawk | 10000 | CSCI 3333 | Data Structures | B- | Paul Smith |
| 100000 | Tony Hawk | 10001 | CSCI 4333 | Design of Database Systems | A | Paul Smith |
| 100001 | Mary Hawk | 10001 | CSCI 4333 | Design of Database Systems | A- | Paul Smith |
| 100000 | Tony Hawk | 10002 | CSCI 5333 | DBMS | B+ | Mary Tran |
| 100002 | David Hawk | 10002 | CSCI 5333 | DBMS | B+ | Mary Tran |
| 100000 | Tony Hawk | 11001 | CSCI 4333 | Design of Database Systems | D | Mary Tran |
| 100000 | Tony Hawk | 10003 | CINF 3321 | Introduction to Information Systems | C | Sharon Mannes |
| 100002 | David Hawk | 10003 | CINF 3321 | Introduction to Information Systems | D | Sharon Mannes |
| 100004 | Larry Johnson | 10003 | CINF 3321 | Introduction to Information Systems | A | Sharon Mannes |
| 100005 | Linda Johnson | 10003 | CINF 3321 | Introduction to Information Systems | NULL | Sharon Mannes |
| 100000 | Tony Hawk | 10004 | CINF 4320 | Web Application Development | A- | Sharon Mannes |
| 100004 | Larry Johnson | 10004 | CINF 4320 | Web Application Development | B+ | Sharon Mannes |
| 100005 | Linda Johnson | 10004 | CINF 4320 | Web Application Development | A- | Sharon Mannes |
| 100006 | Lillian Johnson | 10004 | CINF 4320 | Web Application Development | C+ | Sharon Mannes |
+--------+-----------------+---------+-----------+-------------------------------------+-------+---------------+
16 rows in set

[1] Output: label: value
       s.stuid, 
       name: concatenate s.fname, ' ', s.lname.
        classId: e.classId (e.g. 100000 Tony Hawk | 10000)
        course: concat(co.rubic, ' ', co.number)
        course title: co.title
        grade: e.grade
        faculty: concatenate f.fname, ' ', f.lname.(instructor)
        
[2] Sources:
    student AS s  -- alias
    enroll AS e
    class AS c
    course AS co
[3] Conditions: 
    [3a] problem conditions: ll students majoring or minoring in
'CSCI', 'CINF' or 'ITEC';
       minor = 'CSCI' OR 'CINF' OR 'ITEC'
    [3b] join conditions: usually: equality of the common attributes.
        s.stuId = e.stuId
        e.classId = c.classId
        c.courseId = co.courseId
        c.facId = f.facId