SQLZOO:Using Null 习题答案

1.列出學系department是NULL值的老師。

select name
from teacher
where dept is null

2.注意INNER JOIN 不理會沒有學系的老師及沒有老師的學系。

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

3.使用不同的JOIN(外連接)，來列出全部老師。

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

4.使用不同的JOIN(外連接)，來列出全部學系。

select t.name,d.name
from teacher t right join dept d on (t.dept = d.id)

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 t.name,
case
when mobile is null then '07986 444 2266' 
else mobile
end
from teacher t

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 t.name,
case
when d.name is null then 'None'
else d.name
end
from teacher t left join dept d on (t.dept = d.id)

7.使用COUNT來數算老師和流動電話數目。

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

8.使用COUNT 和 GROUP BY dept.name來顯示每一學系的老師數目。 使用 RIGHT JOIN 以確保工程系Engineering 是在當中。

select d.name,count(t.id)
from teacher t right join dept d on(t.dept = d.id)
group by d.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 t.name,
case
when d.id = 1 or d.id = 2 then 'Sci'
else 'Art'
end
from teacher t left join dept d on t.dept = d.id

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.

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