SQLZOOのUsing Nullを解いてみた

さいきんSQLを勉強していまして、Gunosyの「非エンジニアが最速でSQLをマスターする方法」に書いてある『10年戦えるデータ分析入門』→SQLZOO (https://sqlzoo.net/wiki/SQL_Tutorial) という順番で学習を進めています。

　SQLZOOは出力が合っているのか違っているのかは教えてくれるのですが、どんなSQL文を書けば正解できるのか教えてくれません。すでに日本語でSQLZOOの解答を公開してくださっている方もおられますが、私も自分の勉強のアウトプットとして、解答例を公開したいと思います。

　SQLZOOは問題文が英語なので、あまり自信はありませんが問題文の和訳も用意しました。意訳なので正確さに欠けるかもしれませんが……

• SQLZOOのMore JOIN operationsの解答例はこちら　→　https://rnsr0371.boy.jp/2020/12/21/sqlzoomore_join_operations/

NULL, INNER JOIN, LEFT JOIN, RIGHT JOIN

1

所属する学部が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
    teacher.name
    , dept.name
FROM teacher
LEFT JOIN dept ON teacher.dept = dept.id;

4

すべての学部を列挙するように、様々なJOINを使ってください。

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

5

COALESCEを電話番号を表示するために使ってください。電話番号が与えられない場合には、07986 444 2266を使ってください。教員の名前と電話番号または07986 444 2266を表示してください。

SELECT
    name
    , COALESCE(mobile, '07986 444 2266') AS mobile_number
FROM teacher;

6

COALESCE関数とLEFT JOINを使って、教員の名前と学部の名前を表示してください。学部がない場合はNoneという文字列を使ってください。

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

7

COUNTを使って教員の数と携帯電話の数を表示してください。

SELECT
    COUNT(name)
    , COUNT(mobile)
FROM teacher;

8

COUNTとdept.nameによるGROUP BYを使って各学部とスタッフの数を表示してください。工学部が列挙されるように、RIGHT JOINを使ってください。

SELECT
    dept.name
    , COUNT(teacher.name)
FROM teacher
RIGHT JOIN dept ON teacher.dept = dept.id
GROUP BY dept.name;

9

CASEを使って、学部が1か2の教員の名前の後ろにSciをつけて、教員の名前を表示してください。学部がそれ以外の場合は名前の後ろにArtをつけてください。

SELECT
    name
    , CASE WHEN dept = 1 THEN 'Sci' 
        WHEN dept = 2 THEN 'Sci' 
        ELSE 'Art' END
FROM teacher;

10

CASEを使って学部が1か2の教員の名前の後ろにSci、学部が3の教員にはArt、学部がそれ以外の場合にはNoneをつけて表示してください。

SELECT
    name
    , CASE WHEN dept = 1 THEN 'Sci' 
        WHEN dept = 2 THEN 'Sci' 
        WHEN dept = 3 THEN 'Art'
        ELSE 'None' END
FROM teacher;