SQLZOO: Using Null--COALESCE( )

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'

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

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.

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

注意coalesce函数的用法：返回参数中第一个不是NULL的值，因此可以用这个函数来对NULL值做替换

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.

select teacher.name,
case dept 
when 1 then 'Sci'
when 2 then 'Sci'
else 'Art' end
from teacher left join dept on teacher.dept=dept.id

注意 case when 的两种用法(别再记错了！！！)：

1. case 字段

   when 值 then 取值

   else 取值 end

2. case 

   when 字段=值 then 取值

   else 取值 end