http://sqlzoo.net/wiki/Using_Null/zh
Teacher(id,dept,name,phone,mobile)
dept(id,name)
dept.id=teacher.dept
语句：select dept.name from dept
显示结果为：name 【Computing，Design，Engineerin】
语句：select teacher.name from teacher。从表格teacher中列出所有的老师名称。
显示结果为：
name
Shrivell
Throd
Splint
Spiregrain
Cutflower
Deadyawn
语句：从表格teacher中查询显示所有学系名称name。根据结果可知学系名称相比于表格dept中的学系名称要少一种。
Select dept.name from dept
Join teacher on dept.id=teacher.dept
name
Computing
Computing
Computing
Design
1.列出學系department是NULL值的老師。
Select teacher.name from teacher
Where dept is null
2.注意INNER JOIN 不理會沒有學系的老師及沒有老師的學系。
selectteacher.name,dept.name
fromteacher join dept
on dept.id=teacher.dept
或者
SELECT teacher.name,dept.name
FROM teacher INNER JOIN dept
ON (teacher.dept=dept.id)
3.使用不同的JOIN(外連接)，來列出全部老師。
Teacher(id,dept,name,phone,mobile)
dept(id,name)
dept.id=teacher.dept
根据原题意，有些院系中可能没有老师，有些老师并没有列入到相应的院系中。所以依据teacher表格为主，即选用left join 或者right join 列出全部老师名称，name。
select teacher.name,dept.name
from teacher left join dept
on dept.id=teacher.dept
4.使用不同的JOIN(外連接)，來列出全部學系。
select teacher.name,dept.name from dept
left join teacher
on dept.id=teacher.dept
5.Use COALESCE to print the mobile number. Use the number'07986 444 2266' if there is no number given. Show teacher name and mobilenumber or '07986 444 2266'
select teacher.name,mobile,coalesce(mobile,’07986 4442266’)AS N_MOBILR
from teacher
说明：函数coalesce为对空值进行处理，可认为是对null的替换函数。
Coalesce（expression，value1，value2……，value）
例如coalesce（mobile，phone，110）“mobile”为待检测参数，如果mobile为空值，则检测参数“phone”，参数phone不为空值则显示结果为“phone”，倘若参数“phone”也为空值，则继续检测参数“110”，因为此处“110”不是空值，所以此处函数结果显示为“110”。
6.Use the COALESCE function and a LEFT JOIN to print theteacher name and department name. Use the string 'None' where there is nodepartment.
解答：同上一题，使用函数coalesce（）
select teacher.name,coalesce(dept.name,’None’) fromteacher
left join dept on teacher.dept=dept.id
7.使用COUNT來數算老師和流動電話數目。
select count(teacher.id),count(mobile)
from teacher
8.使用COUNT 和 GROUP
BY dept.name來顯示每一學系的老師數目。 使用 RIGHT JOIN 以確保工程系Engineering 是在當中。
select dept.name,count(teacher.name)
from dept left join teacher
on dept.id=teacher.dept
group by dept.name
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 name,
case when deptin(1,2) then 'Sci'
else 'Art' end
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.
selectteacher.name,
case whenteacher.dept in(1,2) then 'Sci'
when teacher.dept=3then 'Art'
else 'None' end
from teacher fromteacher
解答：函数case（），语句为
case when 条件1 then 结果1
when 条件2 then 结果2
else 结果3
end
CASE WHEN condition1THEN value1
WHEN condition2 THEN value2
ELSE def_value
END
原题中若teacher.dept=1或teacher.dept=2，则teacher.dept一栏显示“SCI”;
若teacher.dept=3，则teacher.dept一栏显示“Art”，若为其他值，则显示为“None”。所以case语句为
case whenteacher.dept in(1,2) then 'Sci'
when teacher.dept=3then 'Art'
else 'None' end
