mysql -u yue -p

show databases;

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

tee 2024_2_6_sql_log.txt

DROP SCHEMA IF EXISTS toyu;

-- schema = database

use toyu;

-- default db to toyu.

source createtoyu.sql

SELECT *
FROM student;


SELECT *
FROM toyu.student;

[1] Output columns (label: value)
1.	stuId: s.stuId
2.	student: s.fname & ' ' & s.lname (expression: +, &: string concatenation.)
3.	major: s.major
4.	AdvisorId: s.advisor
[2] Source tables:
1.	student AS s -- s: alias
[3] Conditions: no condition

SQL:
SELECT DISTINCT s.stuId, 
	CONCAT(s.fname, ' ', s.lname) AS student,
	s.major,
	s.advisor AS AdvisorId
-- columns
FROM student AS s;
-- source tables

WHERE 
-- condition

Declarative Analysis:
[1] Column:

2.	CONCAT(f.fname, ' ', f.lname) AS faulty
3.	f.rank
4.	d.deptName AS department
[2] Source:
5.	faculty AS f
6.	department AS d
7.	school AS s
[3] Conditions:
1.	Join conditions:
a.	(FK) f.deptCode = d.deptCode (PK)
b.	(FK) d.schoolCode = s.schoolCode (PK)
2.	Problem conditions: the School 'Science and Engineering' in the following format. You should not use 'CSE' in your query.
a.	S.schoolName = 'Science and Engineering'

SELECT DISTINCT CONCAT(f.fname, ' ', f.lname) AS faculty,
	f.rank,
	d.deptName AS department
-- columns
FROM faculty AS f,
	department AS d,
	school AS s
-- source tables
WHERE f.deptCode = d.deptCode -- Join conditions
AND d.schoolCode = s.schoolCode
AND s.schoolName = 'Science and Engineering'; -- problem conditions

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