SQL Zoo 8.Using Null

以下数据均来自SQL Zoo

1.List the teachers who have NULL for their department.（列出所属部门为NULL的教师）

select name from teacher where dept is null

2.Note the INNER JOIN misses the teachers with no department and the departments with no teacher.(INNER JOIN遗漏了没有部门的教师和没有教师的部门)

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

3.Use a different JOIN so that all teachers are listed.（使用不同的JOIN，以便列出所有教师）

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

4.Use a different JOIN so that all departments are listed.（使用不同的JOIN，以便列出所有部门）

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

5.Use COALESCE to print the mobile number. Show teacher name and mobile number or '07986 444 2266'.(使用COALESCE打印手机号码。显示老师的姓名和手机号码或'07986 444 2266')

select name,coalesce(mobile,'07986 444 2266') from teacher

注：coalesce替换结果集中的 NULL 值

6.Use the COALESCE function and a LEFT JOIN to print the teacher name and department name. （使用COALESCE函数和LEFT JOIN来打印教师姓名和系名）

select teacher.name,coalesce(dept.name,'None') from teacher 
left join dept on teacher.dept = dept.id

7.Use COUNT to show the number of teachers and the number of mobile phones.(使用COUNT显示教师数量和手机数量)

select count(name),count(mobile) from teacher

8.Use COUNT and GROUP BY dept.name to show each department and the number of staff. (使用COUNT和GROUP BY department .name显示每个部门和员工人数)

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

9.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或2部门，后面跟着“Sci”，否则是“Art”)

select name,if(dept = 1 or dept = 2,'Sci','Art') from teacher

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.(用例显示每个教师的姓名，如果教师在部门1或2后面跟着'Sci'，如果教师的部门是3则显示'Art'，否则显示'None')

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