SQL ZOO练习记录（Using Null）

这一篇主要练习的内容是is null、left/right join的区别，case when 的用法，和第一次看见的coalesce(x,y,z)的函数用法。
练习入口
coalesce(x,y,z)的用法讲解在这个网址，在文章最后我也直接贴出来了。

表格字段：

NULL, INNER JOIN, LEFT JOIN, RIGHT JOIN

题1：List the teachers who have NULL for their department.

select t.name
from teacher t
left join dept d on t.dept = d.id
where d.name is null;

题2：Note the INNER JOIN misses the teachers with no department and the departments with no teacher.

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.

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.

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

Using the COALESCE function

题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',null)
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 t.name, coalesce(d.name, 'None')
from teacher t
left join dept d
on t.dept = d.id;

Using the COUNT function

题7：Use COUNT to show the number of teachers and the number of mobile phones.

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

题8：Use COUNT and GROUP BY dept.name to show each department and the number of staff. Use a RIGHT JOIN to ensure that the Engineering department is listed.

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

Using CASE

题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 dept in (1,2) then 'Sci' else 'Art' end) type
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.

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

9和10题都重新命名了~不然列名太长，看着难受😫

coalesce的用法我直接贴出来了: