mysql -u yue -p
use toyu

SELECT *  -- *: wild card character matching all columns
FROM student;

SELECT stuId, fname, lname -- columns in the table student
FROM student;

source Createtoyu.sql

tee 2026_1_21_sql_log.txt

Declarative Analysis
1.	<<source_tables>>: the source tables to gather the result data
2.	<<conditions_for_inclusion>>: the conditions to be satisfied for results to be included and the conditions the tables should be connected together.
	2a. Problem condition
	2b. Join condition: PK to Fk pair to join the tables.
3.	<<result_columns>>: the result columns or expressions desired to be displayed.

F25, HW #1:

[1] Show the stuId, name, major, and minor of all students who are majoring in
CSCI, CINF or ITEC.
+--------+-----------------+-------+-------+
| stuId | name | major | minor |
+--------+-----------------+-------+-------+
| 100000 | Tony Hawk | CSCI | CINF |
| 100001 | Mary Hawk | CSCI | CINF |
| 100002 | David Hawk | CSCI | ITEC |
| 100003 | Catherine Lim | ITEC | CINF |
| 100004 | Larry Johnson | ITEC | NULL |
| 100005 | Linda Johnson | CINF | ENGL |
| 100006 | Lillian Johnson | CINF | ITEC |
+--------+-----------------+-------+-------+
7 rows in set

[1] Table Source: student AS s -- alias (good)
[2] Conditions:
   [2.a] Problem condition: majoring in CSCI, CINF or ITEC
	s.major = 'CSCI' OR s.major = 'CINF' OR s.major = 'ITEC'
   [2.b] join condition: none
[3] Output columns: label/name: value
	stuId, 
	name: s.fname + + ' ' + s.lname (+: string concatenation); CONCAT(s.fname, ' ', s.lname)
	major, 
	minor

-- Select statement
SELECT DISTINCT  -- Select clause: [3]
FROM  -- From clause: [1]
WHERE ;  -- Where clause: [2]

SELECT DISTINCT s.stuId, s.name, s.major, s.minor -- Select clause: [3]
FROM student AS s -- From clause: [1]
WHERE s.major = 'CSCI' OR s.major = 'CINF' OR s.major = 'ITEC'
;  -- Where clause: [2]

SELECT DISTINCT s.stuId, 
	CONCAT(s.fname, ' ', s.lname) AS name,  -- value AS label/alias
	s.major, 
	s.minor -- Select clause: [3]
FROM student AS s -- From clause: [1]
WHERE s.major = 'CSCI' OR s.major = 'CINF' OR s.major = 'ITEC'
;  -- Where clause: [2]

SELECT DISTINCT s.stuId, 
	CONCAT(s.fname, ' ', s.lname) AS name,  -- value AS label/alias
	s.major 
	s.minor -- Select clause: [3]
FROM student AS s -- From clause: [1]
WHERE s.major = 'CSCI' OR s.major = 'CINF' OR s.major = 'ITEC'
;  -- Where clause: [2]

[2] List the names of all departments together with their faculty members' names
and ranks of the School 'Science and Engineering' in the following format. You
should not use 'CSE' in your query.
+---------------+---------------------+------------------------------+
| faculty | rank | department |
+---------------+---------------------+------------------------------+
| Daniel Kim | Professor | Computer Information Systems |
| Andrew Byre | Associate Professor | Computer Information Systems |
| Paul Smith | Professor | Computer Science |
| Mary Tran | Associate Professor | Computer Science |
| David Love | NULL | Computer Science |
| Sharon Mannes | Assistant Professor | Computer Science |
| Deborah Gump | Professor | Information Technology |
| Benjamin Yu | Lecturer | Information Technology |
+---------------+---------------------+------------------------------+
8 rows in set

Example:
   Daniel Kim: CONCAT(f.fname, ' ', f.lname) (where f is faculty)
   Professor: f.rank
   Computer Information Systems: department.deptName
   
[1] Source:
	faculty AS f
	department AS d
	school AS s
[2] Output:
	[1] faculty: CONCAT(f.fname, ' ', f.lname)	
	[2] rank: f.rank
	[3] department: d.deptName
[3] Condition:
	[3a] Join conditions
		[1] f.deptCode (FK) = d.deptCode (PK; d: parent table)
		[2] d.schoolCode (FK) = s.schoolCode (PK: parent table: s)
	[3.b] Problem condition: the School 'Science and Engineering'
		[1] Look up School Code of 'Science and Engineering': s.schoolCode (e.g., 'CSE')
		School AS s: s.schoolName = 'Science and Engineering'

SELECT DISTINCT CONCAT(f.fname, ' ', f.lname) AS faculty,
	f.rank,
	d.deptName AS department
FROM faculty AS f,
	department AS d,
	school AS s
WHERE f.deptCode = d.deptCode -- join condition #1
AND d.schoolCode = s.schoolCode -- join condition #2
AND s.schoolName = 'Science and Engineering';  -- problem condition

SELECT DISTINCT CONCAT(f.fname, ' ', f.lname) AS faculty,
	f.rank,
	d.deptName AS department
FROM faculty AS f INNER JOIN department AS d
		ON (f.deptCode = d.deptCode)
	JOIN school AS s ON (d.schoolCode = s.schoolCode )
WHERE s.schoolName = 'Science and Engineering';