-- 2/12/2024

"
[4] List the ids, names, major names and faculty advisor name of every student in the following manner.

+--------+-----------------+------------------------------+--------------+
| stuId  | student         | major                        | advisor      |
+--------+-----------------+------------------------------+--------------+
| 100000 | Tony Hawk       | Computer Science             | Paul Smith   |
| 100001 | Mary Hawk       | Computer Science             | Paul Smith   |
| 100002 | David Hawk      | Computer Science             | Mary Tran    |
| 100003 | Catherine Lim   | Information Technology       | NULL         |
| 100004 | Larry Johnson   | Information Technology       | Deborah Gump |
| 100005 | Linda Johnson   | Computer Information Systems | Daniel Kim   |
| 100006 | Lillian Johnson | Computer Information Systems | Andrew Byre  |
| 100007 | Ben Zico        | NULL                         | NULL         |
| 100008 | Bill Ching      | Arts                         | NULL         |
| 100009 | Linda King      | Arts                         | Art Allister |
| 100111 | Cathy Johanson  | NULL                         | Art Allister |
+--------+-----------------+------------------------------+--------------+
11 rows in set
"

"
Output:
   s.stuId,
   CONCAT(s.fname, ' ', s.lname) AS student,
   d.deptName AS major,
   CONCAT(f.fname, ' ', f.lname) AS advisor 


Source:
	student AS s, (left join: keep every student in the result)
	department AS d, (left join: keep every student from the intermediate result into the  final result)
	faculty AS f
   
Conditions:
   Join conditions:
		s.major = d.deptCode
		s.advisor = f.facId

   Problem conditions: none 
" 

SELECT DISTINCT s.stuId,
   CONCAT(s.fname, ' ', s.lname) AS student,
   d.deptName AS major,
   CONCAT(f.fname, ' ', f.lname) AS advisor 
FROM student AS s INNER JOIN department AS d ON (s.major = d.deptCode)
	INNER JOIN faculty AS f ON (s.advisor = f.facId);


SELECT DISTINCT s.stuId,
   CONCAT(s.fname, ' ', s.lname) AS student,
   d.deptName AS major,
   CONCAT(f.fname, ' ', f.lname) AS advisor 
FROM student AS s LEFT JOIN department AS d ON (s.major = d.deptCode)
	INNER JOIN faculty AS f ON (s.advisor = f.facId);

SELECT DISTINCT s.stuId,
   CONCAT(s.fname, ' ', s.lname) AS student,
   d.deptName AS major,
   CONCAT(f.fname, ' ', f.lname) AS advisor 
FROM student AS s LEFT JOIN department AS d ON (s.major = d.deptCode)
	LEFT JOIN faculty AS f ON (s.advisor = f.facId);


SELECT s.*, d.*
FROM student AS s, department AS d;

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

-- major informaton
SELECT s.*, d.*
FROM student AS s LEFT JOIN department AS d
	ON (s.major = d.deptCode);
	
-- 1. Boolean values are TINYINT. FALSE is 0.
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/2024
mysql -u yue -p

mysql -u s1 -p

tee 2024_2_12_sql_log.txt

[1] Output columns: label: value
•	stuId: s.stuId
•	student: st.fname & ' ' & s.lname (string concatenation)
•	major: s.major
•	advisorId: s.advisor
[2] Source tables:
•	student AS s -- alias
[3] Conditions: None
SQL:

SELECT DISTINCT s.stuId,
	CONCAT(st.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, 'No major'),
	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

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 -- conditions
	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 (f.deptCode = d.deptCode )
	INNER JOIN school AS s ON (d.schoolCode = s.schoolCode)
   -- sources
WHERE  s.schoolName = 'Science and Engineering' ;
-- conditions


-- faculty of department
SELECT DISTINCT d.deptCode, d.deptName,
	CONCAT(f.fname, ' ', f.lname) AS faculty 
FROM department AS d INNER JOIN faculty AS f ON (d.deptCode = f.deptCode);

SELECT DISTINCT d.deptCode, d.deptName,
	CONCAT(f.fname, ' ', f.lname) AS faculty 
FROM department AS d INNER JOIN faculty AS f ON (d.deptCode = f.deptCode)
ORDER BY d.deptCode ASC;

SELECT DISTINCT d.deptCode, d.deptName,
	IFNULL(CONCAT(f.fname, ' ', f.lname), 'Santa Claus') AS faculty 
FROM department AS d LEFT JOIN faculty AS f ON (d.deptCode = f.deptCode)
ORDER BY d.deptCode ASC;



SELECT DISTINCT -- output
FROM -- sources
WHERE -- conditions