-- 2/12
tee 2024_2_12_sql_log.txt

-- list students and major department information.
SELECT s.*, d.*
FROM student AS s, department AS d;
-- 77 rows

-- list students and major department information.
SELECT s.*, d.*
FROM student AS s INNER JOIN department AS d
	ON (s.major = d.deptCode);


-- list students and minor department information.
SELECT s.*, d.deptName AS `minor department`
FROM student AS s INNER JOIN department AS d
	ON (s.minor = d.deptCode);
	
	
	-- list students and minor department information with less than 11 staff in the minor department.
SELECT s.*, d.deptName AS `minor department`
FROM student AS s INNER JOIN department AS d
	ON (s.minor = d.deptCode)
WHERE d.numStaff < 11;

SELECT s.*, d.deptName AS `minor department`
FROM student AS s, department AS d
WHERE s.minor = d.deptCode
AND  d.numStaff < 11;


-- list students and major department information.
SELECT s.*, d.*
FROM student AS s INNER JOIN department AS d
	ON (s.major = d.deptCode);
	
-- list students and major department information if available.
SELECT s.*, d.*
FROM student AS s LEFT JOIN department AS d
	ON (s.major = d.deptCode);

SELECT s.*, d.*
FROM department AS d RIGHT JOIN  student AS s
	ON (s.major = d.deptCode);

SELECT s.*, d.*
FROM department AS d  LEFT JOIN  student AS s
	ON (s.major = d.deptCode);

-- A LEFT JOIN B LEFT JOIN C = (A LEFT JOIN B) LEFT JOIN C;
-- JOIN: left associative
-- A LEFT JOIN (B LEFT JOIN C);

-- TIps: be as explicit and specific as possible.
	
-- Tips: decoupling your logic
	
-- 7 department rows and 11 student rows

-- students with no advisor
SELECT s.*
FROM student AS s
WHERE s.advisor IS NULL;

-- students with no advisor
SELECT s.*
FROM student AS s
WHERE s.advisor = NULL;


SELECT FALSE,
   TRUE;
  
SELECT *
FROM student
WHERE 0;

SELECT *
FROM student
WHERE 1;

SELECT *
FROM student
WHERE 2697;

SELECT *
FROM student
WHERE '0';

SELECT *
FROM student
WHERE '145';

-- warning: '' cannot be converted to a number.
-- "Warning 1292 Truncated incorrect DOUBLE value: ''"
SELECT *
FROM student
WHERE '';

-- warning: '' cannot be converted to a number.
-- "Warning 1292 Truncated incorrect DOUBLE value: ''"
SELECT *
FROM student
WHERE 'Hello world';

SELECT *
FROM student
WHERE 1.49;





-- 2/7
mysql -u yue -p

show databases;

tee 2024_2_12_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