-- logging.
-- 1/28/25

mysql -u yue -p

use toyu;

SELECT * FROM student;

tee 2015_1_28_sql_log.txt



show databases;

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. (expected output)
+--------+-----------------+------------------------------+-------+
| 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] just major, minor, deptCode, stuId, fname, lanme
[1] Ouptut: stuId, fname, lname, major, minor
[2] Sources: student
[3] Condition: no

SELECT DISTINCT <<result_columns>> -- select clause
FROM <<source_tables>> -- conceptually joined to form a large table
    -- from clause
WHERE <<conditions_for_inclusion>> -- where clause

SELECT DISTINCT stuId, fname, lname, major, minor
FROM student;

-- 1b. same as 1a but fname and lname in a column

[1b] stuId, student (e.g. Tony Hawk) major, minor, deptCode, 

[1] Ouptut: label: value
    stuId, 
    student: Concatenatnion of fname, ' ', lname
    major, minor
[2] Sources: student
[3] Condition: no

SELECT DISTINCT stuId, 
    CONCAT(fname, ' ', lname), 
    major, minor
FROM student;

SELECT DISTINCT stuId, 
    CONCAT(fname, ' ', lname) AS student, -- alias (column label)
    major, minor
FROM student;

-- Declarative analysis: what (not how)
-- algorithm: how

[1c] Output columns: label: value
1.	s.stuId
2.	student: fname concatenate with ‘space’ and lname, CONCAT(s.fname, ' ', s.lname), e.g: Tony Hawk: 
3.	major: d.deptName
4.	s.minor
[2] Sources: 
1.	student AS s
2.	department AS d
[3] Conditions: 
•	Join condition: s.major = d.deptCode
    equality of common attributes (same meaning)

SELECT DISTINCT s.stuId, 
    CONCAT(s.fname, ' ', s.lname) AS student, -- alias (column label)
    d.deptName AS major, s.minor
FROM student AS s, department AS d; -- an intermediate 'big' table containing all necessary data.
-- 77 rows in the result

SELECT DISTINCT s.*, d.* -- * matches any column
FROM student AS s, department AS d;


SELECT DISTINCT s.stuId, 
    CONCAT(s.fname, ' ', s.lname) AS student, -- alias (column label)
    d.deptName AS major, s.minor
FROM student AS s, department AS d
WHERE s.major = d.deptCode; -- join condition.

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

Expected stuId: ...00, 01, 02, 03, 06, 09
[1c] Output columns: label: value
1.	s.stuId
2.	student: fname concatenate with ‘space’ and lname, CONCAT(s.fname, ' ', s.lname), e.g: Tony Hawk: 
3.	major: d.deptName
4.	s.minor
[2] Sources: 
1.	student AS s
2.	department AS d
[3] Conditions: 
.   Problem condition: minoring in CSCI, CINF or ITEC 
    s.minor = 'CSCI' OR 'CINF' OR 'ITEC'
•	Join condition: s.major = d.deptCode
    equality of common attributes (same meaning)

SELECT DISTINCT s.stuId, 
    CONCAT(s.fname, ' ', s.lname) AS student, -- alias (column label)
    d.deptName AS major, s.minor
FROM student AS s, department AS d
WHERE s.minor = 'CSCI' OR 'CINF' OR 'ITEC' -- problem conditions
AND s.major = d.deptCode; -- join condition.


SELECT DISTINCT s.stuId, 
    CONCAT(s.fname, ' ', s.lname) AS student, -- alias (column label)
    d.deptName AS major, s.minor
FROM student AS s, department AS d
WHERE (s.minor = 'CSCI') OR (s.minor = 'CINF')
    OR (s.minor = 'ITEC') -- problem conditions
AND s.major = d.deptCode; -- join condition.

-- (a)
SELECT DISTINCT s.stuId, 
    CONCAT(s.fname, ' ', s.lname) AS student, -- alias (column label)
    d.deptName AS major, s.minor
FROM student AS s, department AS d
WHERE ((s.minor = 'CSCI') OR (s.minor = 'CINF')
    OR (s.minor = 'ITEC')) -- problem conditions
AND s.major = d.deptCode; -- join condition.

-- be mindful of the data types and the order of execution precedence.

-- alternative solutions:

SELECT 2 IN (0,3,5,7); -- tinkering
+----------------+
| 2 IN (0,3,5,7) |
+----------------+
|              0 |
+----------------+
1 row in set (0.001 sec)

-- IN: set membership test: set (0,3,5,7); 0 is false.
-- SQL: no native Boolean type (no T or F, but 0 or 1)

SELECT 5 IN (0,3,5,7); 

-- (b)
SELECT DISTINCT s.stuId, 
    CONCAT(s.fname, ' ', s.lname) AS student, -- alias (column label)
    d.deptName AS major, s.minor
FROM student AS s, department AS d
WHERE s.minor IN ('CSCI', 'CINF', 'ITEC') -- problem conditions
AND s.major = d.deptCode; -- join condition.

-- (c)
SELECT DISTINCT s.stuId, 
    CONCAT(s.fname, ' ', s.lname) AS student, -- alias (column label)
    d.deptName AS major, s.minor
FROM student AS s INNER JOIN department AS d
    ON (s.major = d.deptCode) -- join condition.
WHERE s.minor IN ('CSCI', 'CINF', 'ITEC'); -- problem conditions

-- (d) List the class grade information of all courses taught by a CSCI faculty member in the following manner.

SELECT DISTINCT s.stuId,
	CONCAT(s.fname, ' ', s.lname) AS student,
	c.classId,
	CONCAT(co.rubric, ' ', co.number) AS course,
	co.title AS `course title`,
	e.grade,
	CONCAT(f.fname, ' ', f.lname) AS instructor
FROM student AS s INNER JOIN enroll AS e USING (stuId)
	INNER JOIN class AS c USING (classId)
	INNER JOIN faculty AS f ON (f.facId  = c.facId)
	INNER JOIN course AS co USING (courseId)
WHERE f.deptCode = 'CSCI';


SELECT DISTINCT s.stuId,
	CONCAT(s.fname, ' ', s.lname) AS student,
	c.classId,
	CONCAT(co.rubric, ' ', co.number) AS course,
	co.title AS `course title`,
	e.grade,
	CONCAT(f.fname, ' ', f.lname) AS faculty
FROM student AS s INNER JOIN enroll AS e USING (stuId)
	INNER JOIN class AS c USING (classId)
	INNER JOIN faculty AS f ON (f.facId  = s.major)
	INNER JOIN course AS co USING (courseId)
WHERE f.deptCode = 'CSCI';
-- incorrect




[2] Repeat [1]. 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

[1c] Output columns: label: value
1.	s.stuId
2.	student: fname concatenate with ‘space’ and lname, CONCAT(s.fname, ' ', s.lname), e.g: Tony Hawk: 
3.	major: d.deptName
4.	s.minor
5.  advsior: (e.g. Paul Smith) CONCAT(f.fname, ' ', f.lname)
[2] Sources: 
1.	student AS s
2.	department AS d
3.  faculty AS f
[3] Conditions: 
.   Problem condition: minoring in CSCI, CINF or ITEC 
    s.minor = 'CSCI' OR 'CINF' OR 'ITEC'
•	Join conditions: (1) s.major = d.deptCode; (2) s.advisor = f.facId
    equality of common attributes (same meaning)
    
SELECT DISTINCT s.stuId, 
    CONCAT(s.fname, ' ', s.lname) AS student, -- alias (column label)
    d.deptName AS major, s.minor,
    CONCAT(f.fname, ' ', f.lname) AS advisor
FROM student AS s, department AS d, faculty AS f
WHERE s.minor IN ('CSCI', 'CINF', 'ITEC') -- problem conditions
AND s.major = d.deptCode
AND s.advisor = f.facId; -- join condition.  

INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES (100000,'Bun','Yue','CSCI','ITEC',22,1011);
    
   ERROR 1062 (23000): Duplicate entry '100000' for key 'PRIMARY'
-- NO two rows can have the same PK's values (CK's values)

INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES (200000,'Bun','Yue','CSCI','ITEC',22,1011);

SELECT * FROM student;

INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES (300000,'Buny','Yue','CINF','MAGI',55,2033);

INSERT INTO Department(deptCode, deptName, schoolCode, numStaff) VALUES 
    ('MAGI', 'Magic','BUS',8);
    
INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES (300000,'Buny','Yue','CINF','MAGI',55,2033);   

INSERT INTO Faculty(facId, fname, lname, deptCode, `rank`) VALUES
    (2033,'Harry','Porter','MAGI','Professor');
 
INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES (300000,'Buny','Yue','CINF','MAGI',55,2033);