mysql -u yue -p

use toyu

SELECT *  -- wild card match all columns
FROM student;


SELECT stuId, fname, lname  -- wild card match all columns
FROM student;
source createtoyu.sql

tee 2026_1_21_sql_log.txt

[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

Declarative analysis:

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

-- Select statement
SELECT DISTINCT s.stuId, -- select clause: [2]
	CONCAT(s.fname, ' ', s.lname) AS name, -- alias: label
	s.major,
	s.minor
FROM student AS s  -- from clause: [1]
WHERE -- where clause: conditions
	s.major = 'CSCI' OR s.major = 'CINF' OR s.major = 'ITEC'
;

SELECT DISTINCT s.stuId, -- select clause: [2]
	CONCAT(s.fname, ' ', s.lname) AS name, -- alias: label
	s.major,
	s.minor
FROM student AS s  -- from clause: [1]
WHERE -- where clause: conditions
	s.major = 'CSCI' OR s.major = 'CINF' OR s.major = 'ITEC'
;

SELECT DISTINCT s.stuId, -- select clause: [2]
	CONCAT(s.fname, ' ', s.lname) AS name -- alias: label
	s.major,
	s.minor
FROM student AS s  -- from clause: [1]
WHERE -- where clause: conditions
	s.major = 'CSCI' OR s.major = 'CINF' OR s.major = 'ITEC'
;

[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:
| faculty | rank | department |
+---------------+---------------------+------------------------------+
| Daniel Kim | Professor | Computer Information Systems |

Daniel Kim:
CONCAT(f.fname, ' ', f.lname) AS faculty (faculty AS f)

Professor:
f.rank

department: d.deptName (department AS d)

[1] Source tables:
	[1] faculty AS f
	[2] department AS d
	[3] school AS s
[2] Output columns:
	[1] CONCAT(f.fname, ' ', f.lname) AS faculty
	[2] f.rank
	[3] d.deptName AS department

[3] Conditions:
	[3a] join conditions
		[1] f.deptCode (FK) = d.deptCode (PK)
		[2] d.schoolCode (FK) = s.schoolCode
	[3b] problem conditions: School 'Science and Engineering'
	-- 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, department AS d, school AS s
WHERE f.deptCode = d.deptCode,
	d.schoolCode = s.schoolCode
	s.schoolName = 'Science and Engineering';