mysql -u yue -p

show databases;

tee 2024_2_7_sql_log.txt

DROP SCHEMA IF EXISTS toyu;

source createtoyu.sql

use toyu;

SELECT *
FROM student;

SELECT *
FROM toyu.student;

mysql -u s3 -p
mysql -u s2 -p

"
[1] Output columns: label: value
1.	stuId: s.stuId
2.	student: CONCAT(fs.fname, ' ', s.lname)  (&: string concatentation)
3.	major: s.major
4.	advisorId: s.advisor
[2] Source tables:
1.	student AS s  -- AS: alias
[3] Conditions: none

"

SELECT DISTINCT s.stuId,
	s.fname & ' ' & s.lname AS student,
	s.major,
	s.advisor AS advisorId,
	-- output
FROM student AS s;

SELECT DISTINCT s.stuId,
	CONCAT(s.fname, ' ', s.lname)  AS student,
	s.major,
	s.advisor AS advisorId
	-- output
FROM student AS s;


SELECT DISTINCT s.stuId,
	CONCAT(s.fname, ' ', s.lname)  AS student,
	IFNULL(s.major, '') AS major,
	IFNULL(s.advisor, '') AS advisorId
	-- output
FROM student AS s;

	-- sources
WHERE
	-- conditions
	
"
[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                   |
+---------------+---------------------+------------------------------+
| Paul Smith    | Professor           | Computer Science             |
| Mary Tran     | Associate Professor | Computer Science             |
| David Love    | NULL                | Computer Science             |
| Sharon Mannes | Assistant Professor | Computer Science             |
| Daniel Kim    | Professor           | Computer Information Systems |
| Andrew Byre   | Associate Professor | Computer Information Systems |
| Deborah Gump  | Professor           | Information Technology       |
| Benjamin Yu   | Lecturer            | Information Technology       |
+---------------+---------------------+------------------------------+
8 rows in set

Analysis:

Output:
	CONCAT(f.fname, ' ', f.lname) AS faculty
	f.rank
	d.deptName AS department
	
SOurce:
	faculty AS f
	department AS d
	school AS s
	
Conditions:
	Join conditions:
		(FK) f.deptCode = d.deptCode (PK)
		(FK) d.schoolCode = s.schoolCode (PK)
	PRoblem conditions: the School 'Science and Engineering' in the following format. You should not use 'CSE' in your query.
	    s.schoolName = 'Science and Engineering' 
"

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

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


	-- conditions