sqlzoo use null 题目及答案

最新推荐文章于 2022-10-22 16:02:26 发布

舟晓南

最新推荐文章于 2022-10-22 16:02:26 发布

阅读量311

点赞数

CC 4.0 BY-SA版权

文章标签： mysql sql 数据库

本文链接：https://blog.youkuaiyun.com/zxn1996csdn/article/details/105164139

本文深入探讨了SQL查询的多种高级技巧，包括使用NULL、JOIN、COALESCE、COUNT、CASE等函数进行复杂数据处理的方法。通过实例展示了如何处理部门与教师之间的关系，如列出所有教师及其所属部门，即使部门信息缺失也能妥善处理。此外，还介绍了如何统计各部门教师数量，以及根据不同条件对教师进行分类。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

List the teachers who have NULL for their department.

select name
from teacher
where dept is null

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

SELECT teacher.name, dept.name
FROM teacher 
INNER JOIN dept ON (teacher.dept=dept.id)

Use a different JOIN so that all teachers are listed.

select teacher.name, dept.name
from teacher 
left join dept on teacher.dept = dept.id

Use a different JOIN so that all departments are listed.

select teacher.name, dept.name
from teacher
right join dept on teacher.dept = dept.id

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’

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.

select teacher.name, coalesce(dept.name, 'None')
from teacher
left join dept on teacher.dept = dept.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.

select dept.name, count(teacher.name)
from teacher
right join dept on teacher.dept = dept.id
group by dept.name

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

select name,
case 
when dept = 1 or dept = 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.

select name, (
	case
	when dept = 1 or dept = 2 then 'Sci'
	when dept = 3 then 'Art'
	else 'None'
	end
)
from teacher