SQL Zooを試してみた ~ Using NULL

概要

SQL ZooのUsing NULLはわりと簡単な問題が多かった。一応、回答例を残しておく。

1. 

SELECT name
FROM teacher
WHERE dept IS NULL

2.

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

3.

SELECT teacher.name, dept.name "Dept"
FROM teacher
LEFT JOIN dept ON dept = dept.id

4.

SELECT teacher.name, dept.name "dept"
FROM teacher
RIGHT JOIN dept ON dept = dept.id

5.

SELECT name, COALESCE(mobile, '07986 444 2266') "Mobile"
FROM teacher

6.

SELECT teacher.name, COALESCE(dept.name, 'None') "Dept"
FROM teacher
LEFT JOIN dept ON dept = dept.id

7.

SELECT COUNT(name) "# of Teacher", COUNT(mobile) "Mobile"
FROM teacher

8.

SELECT dept.name "Dept", COUNT(teacher.name) "# of Teacher"
FROM teacher
RIGHT JOIN dept ON dept = dept.id
GROUP BY dept.name

9.

SELECT name,
  CASE dept
   WHEN 1 THEN 'Sci'
   WHEN 2 THEN 'Sci'
   ELSE 'Art'
  END "Type of Dept"
FROM teacher

10.

SELECT name,
  CASE dept
   WHEN 1 THEN 'Sci'
   WHEN 2 THEN 'Sci'
   WHEN 3 THEN 'Art'
   ELSE 'None'
  END "Type of Dept"
FROM teacher