sqlzoo练习14-using NULL
本文中讲解的是当数据库的表中的数据存在缺失值NULL
的时候,该如何进行处理。下面的数据含有两个表teacher
和dept
The school includes many departments. Most teachers work exclusively for a single department. Some teachers have no department.
练习
- List the teachers who have NULL for their department.
找出dept为空的老师
1 | select name |
- Note the INNER JOIN misses the teachers with no department and the departments with no teacher.
inner join能够排除老师为空的系或者没有系的老师
1 | select teacher.name, dept.name |
- Use a different JOIN so that all teachers are listed.
1 | select teacher.name. dept.name |
1 | select teacher.name, dept.name |
- Use a different JOIN so that all departments are listed.
1 | -- 方法1 |
- 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’
1 | select name, coalesce(mobile, '07986 444 2266') |
- 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 | select teacher.name, coalesce(dept.name, 'None') |
- Use COUNT to show the number of teachers and the number of mobile phones.
1 | select count(name), count(mobile) |
- 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.
1 | select dept.name, count(teacher.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.
1 | select teacher.name, |
- 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.
1 | select teacher.name, |
About coalesce函数
COALESCE takes any number of arguments and returns the first value that is not null.
笔记:取第一个不是NULL值的数据
About case function
case
表达式的两种写法
1 | case sex -- 1. 简单表达式 |