mysql -u yue -p

mysql -u s1 -p

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