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
顯示結(jié)果為：name 【Computing，Design，Engineerin】
語句：select teacher.name from teacher。從表格teacher中列出所有的老師名稱。
顯示結(jié)果為：
name
Shrivell
Throd
Splint
Spiregrain
Cutflower
Deadyawn
語句：從表格teacher中查詢顯示所有學系名稱name。根據(jù)結(jié)果可知學系名稱相比于表格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
根據(jù)原題意，有些院系中可能沒有老師，有些老師并沒有列入到相應的院系中。所以依據(jù)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
說明：函數(shù)coalesce為對空值進行處理，可認為是對null的替換函數(shù)。
Coalesce（expression，value1，value2……，value）
例如coalesce（mobile，phone，110）“mobile”為待檢測參數(shù)，如果mobile為空值，則檢測參數(shù)“phone”，參數(shù)phone不為空值則顯示結(jié)果為“phone”，倘若參數(shù)“phone”也為空值，則繼續(xù)檢測參數(shù)“110”，因為此處“110”不是空值，所以此處函數(shù)結(jié)果顯示為“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.
解答：同上一題，使用函數(shù)coalesce（）
select teacher.name,coalesce(dept.name,’None’) fromteacher
left join dept on teacher.dept=dept.id
7.使用COUNT來數(shù)算老師和流動電話數(shù)目。
select count(teacher.id),count(mobile)
from teacher
8.使用COUNT 和 GROUP
BY dept.name來顯示每一學系的老師數(shù)目。 使用 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
?
解答：函數(shù)case（），語句為
case when 條件1 then 結(jié)果1
when 條件2 then 結(jié)果2
else? 結(jié)果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?
?
