teacher表：
iddeptnamephonemobile 101 1 Shrivell 2753 07986 555 1234 102 1 Throd 2754 07122 555 1920 103 1 Splint 2293 104 Spiregrain 3287 105 2 Cutflower 3212 07996 555 6574 106 Deadyawn 3345
dept表：
idname 1 Computing 2 Design 3 Engineering
1. List the teachers who have NULL for their department.
解题思路： left join，以teacher左表为主表，向左连接。和inner join的区别在于，inner join公共列出现null值时，将忽略null值。
- Use a different JOIN so that all departments are listed.
解题思路，right join，以dept右表为主表，向右连接。
Using Coalesce Fuction
5. 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’
使用coalesce函数，显示教师姓名和电话号码，如果电话号码是空值，用’07986 444 2266’填充
解题思路，colaesce函数是缺失值处理，针对的是null的情况，注意，coalesce函数对空格不起作用。
6. 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.
7. Use COUNT to show the number of teachers and the number of mobile phones.
- 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.
- Use CASE to show the name of each teacher followed by ‘Sci’ if the teacher is in dept 1 or 2 and ‘Art’ otherwise.
使用 CASE 显示每位教师的姓名，如果教师在部门 1 或 2，则显示”Sci”，否则显示”Art”。
解题思路：考察case when
10.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.
使用 CASE 显示每位教师的姓名，如果教师在部门 1 或 2，则显示”Sci”，如果教师的部门是 3，则显示”Art”，否则显示”None”。
解题思路：考察case when 多个条件。
Original: https://www.cnblogs.com/ruoli-121288/p/16310507.html
Author: 徐若离
Title: SQLZOO练习7–Using NULL
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/621702/
转载文章受原作者版权保护。转载请注明原作者出处！