SQLZOO Using Null 参考答案（含表格样式和解题思路）_list the teachers who have null for their departme-CSDN博客

本文详细解析了如何在SQL中处理NULL值，包括使用IS NULL查询空值，LEFT JOIN显示所有教师，COALESCE函数填充NULL，以及COUNT和GROUP BY统计部门教师数量。还介绍了CASE语句在条件判断中的应用，加深了对SQL中处理NULL和条件表达式的理解。

在这里插入图片描述

List the teachers who have NULL for their department.

思路：这个题是简单查询
考点：测定空值用 IS NULL 而不是 = NULL

SELECT name
FROM teacher
WHERE dept IS NULL

Note the INNER JOIN misses the teachers with no department and the departments with no teacher.

思路：INNER JOIN 就是求交集，会去掉含有空值行

SELECT t.name, de.name
FROM Teacher t INNER JOIN dept de
ON t.dept=de.id

3.Use a different JOIN so that all teachers are listed.

思路：用TEACHER表作为主表连接（想要哪个显示all 就用哪个作为主表）
考点：RIGHT JOIN & LEFT JOIN

SELECT t.name ,de.name
FROM teacher t LEFT JOIN dept de
ON t.dept=de.id

4.Use a different JOIN so that all departments are listed.

SELECT t.name ,de.name
FROM teacher t RIGHT JOIN dept de
ON t.dept=de.id

Using the COALESCE function
COALESCE：
格式：COALESCE(x,y,z）
IF X 非空输出：X
IF X IS NULL ,Y 非空输出：Y
IF X IS NULL,Y IS NULL ,Z 非空输出：Z
IF X,Y,Z 都为空值输出：NULL
应用：当你想替换NULL值。

Use COALESCE to print the mobile number.
Use the number ‘07986 444 2266’ if there is no number given. Show teacher name and mobile number or ‘07986 444 2266’

思路：当mobile为非空，输出mobile,为空则输出号码

SELECT name,COALESCE(mobile,'07986 444 2266')
FROM teacher

Use the COALESCE function and a LEFT JOIN to print the teacher name and department name. Use the string ‘None’ where there is no department.

思路：
1:题目说可能会没有dept.name 说明所有的TEACHER（有没有dept）都会被打出来，即teacher为主表（teacher left join）
2:要清除空值，用COALESCE方法

SELECT t.name, COALESCE(de.name,'None')
FROM teacher t LEFT JOIN dept de
ON t.dept=de.id

Use COUNT to show the number of teachers and the number of mobile phones.

SELECT count(name),count(mobile)
FROM teacher

Use COUNT and GROUP BY dept.name to show each department and the number of staff. Use a RIGHT JOIN to ensure that the Engineering department is listed.

思路：从题目出发：
a. show each department 看到each 就分组
b. number of staff 分组后计数
c. ensure that the Engineering department is listed 说明要用RIGHT JOIN显示所有的dept.name

如我上一篇博文所说，Group by经常会对SELECT后的显示有影响，所以最好先用right join 将你说需要的信息全部汇总，在分组排序等进行下一步:所以我采用的实现顺序为cab

SELECT de.name,count(t.name)
FROM teacher t RIGHT JOIN dept de
ON t.dept=de.id
GROUP BY de.name

Using CASE

Use CASE to show the name of each teacher followed by ‘Sci’ if the teacher is in dept 1 or 2 and ‘Art’ otherwise.

考点：

1 OR 2 的写法是 IN (a,b)[如果只有一个值，用‘=’就可以]

SELECT name,
CASE WHEN dept IN (1 , 2) THEN  "Sci"
	ELSE "Art"
	END
FROM teacher

Use CASE to show the name of each teacher followed by ‘Sci’ if the teacher is in dept 1 or 2, show ‘Art’ if the teacher’s dept is 3 and ‘None’ otherwise.

思路：if 的条件多了一种可能，只要多加一个WHEN THEN 就可以

SELECT name
CASE WHEN dept IN (1 , 2) THEN  "Sci"
     WHEN dept IN (3) THEN  "Art"
     ELSE "None"
FROM teacher

总结：

掌握了coalsce 来用特殊值去替代空值，如果设计数分一开始的洗数据是个不错的方法
CASE 的用法就像是IF，之前看了SQL进阶却还是不太明白，这次做题有所加深。