【SQLZOO答え】８．Using Null

SQL入門を勉強するため、友人にSQLの練習問題ないかと聞いたら、
【SQLZOO】というサイトを教えてもらいました。
ただし、問題を解いてるうちに、わからない問題に関して、クエリの答えがおらず、結果しか教えてくれないので、答えをアウトプットしようと思い、noteを始めました。　

０．SQLZOO練習問題

Using Null

１．NULL

List the teachers who have NULL for their department.
Why we cannot use =
You might think that the phrase dept=NULL would work here but it doesn't - you can use the phrase dept IS NULL
That's not a proper explanation.

select
 name
from
 teacher
where
 dept is null;

２．INNER JOIN

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

select
 teacher.name Teacher, dept.name Dept
from
 teacher 
inner join
 dept
 on (teacher.dept=dept.id);

３．LEFT JOIN

Use a different JOIN so that all teachers are listed.

select
 t.name Teacher, d.name Dept
from
 teacher t
left join
 dept d
 on t.dept=d.id

4．RIGHT JOIN

Use a different JOIN so that all departments are listed.

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

５．COALESCE

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')
from
 teacher;

６．COALESCE LEFT JOIN

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')
from teacher
left join
 dept
 on
 teacher.dept=dept.id;

７．COUNT

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

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

８．COUNT RIGHT JOIN

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
 dept.name, count(teacher.name)
from
 teacher
right join
 dept
 on
 teacher.dept=dept.id
group by
 dept.name

9．CASE WHEN

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=1 then 'Sci'
when dept=2 then 'Sci' 
else 'Art' end
from
 teacher;

10．CASE WHEN2

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=1 then 'Sci'
when dept=2 then 'Sci'
when dept=3 then 'Art'
else 'None' end
from
 teacher

その他の答へ　　　　　　　　

０．SELECT basics
１．SELECT name
２．SELECT from World
３．SELECT from Nobel
４．SELECT within SELECT
５．SUM and COUNT
６．JOIN
７．More JOIN operations
８．Using Null
８+ Numeric Examples
９．Self join
１０．Tutorial Quizzes
１１．Tutorial Student Records
１２．Tutorial DDL

※問題を攻略でき次第、随時更新いたします。