SQLZOOのSelf joinを解いてみた

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

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

　問題文の和訳が面倒になってきたので、不自然な部分はちょっとだけ手を加えていますが、ほとんどDEEPLを使って翻訳しました。

• SQLZOOのCovid 19の解答例はこちら　→　https://rnsr0371.boy.jp/2020/12/25/sqlzoocovid19/

Summary

1.

データベースの中にバス停はいくつありますか？

SELECT
    COUNT(*)
FROM stops;

2.

バス停Craiglockhartのidを見つけてください。

SELECT
    id
FROM stops
WHERE name = 'Craiglockhart';

3.

numが4でLRT社のバス停のidと名前を表示してください。

SELECT
    id
    , name
FROM route
    JOIN stops ON stop = id
WHERE num = '4' AND company = 'LRT';

Routes and stops

4.

このクエリはLondon RoadかCraiglockhartを訪れる経路の数を表示します。クエリを実行して、これらのバス停をつなげる2社のカウントが2であることに注目してください。HAVING節を追加して、これら2つの路線に出力を限定してください。

SELECT
    company
    , num
    , COUNT(*)
FROM route
    JOIN stops ON id = stop
WHERE name = 'London Road' OR name = 'Craiglockhart'
GROUP BY company, num
HAVING COUNT(*) = 2;

5.

表示されているセルフジョインを実行して、b.stopではCraiglockhartからルートを変更せずに行ける場所をすべて表示していることを確認してください。クエリを変更して、CraiglockhartからLondon Roadまでのサービスを表示するようにしてください。

SELECT
    a.company
    , a.num
    , a.stop
    , b.stop
FROM route AS a
    JOIN route AS b ON(a.company = b.company AND a.num = b.num)
WHERE a.stop = 53 AND b.stop = 149;

6.

表示されているクエリは前のものと似ていますが、停止駅テーブルの2つのコピーを結合することで、停止駅を番号ではなく名前で参照することができます。クエリを変更して、’Craiglockhart’ と ‘London Road’ の間のサービスが表示されるようにします。これらの停留所に飽きたら、’Tollcross’ と ‘Fairmilehead’ を比較してみてください。

SELECT
    a.company
    , a.num
    , stopa.name
    , stopb.name
FROM route AS a 
    JOIN route AS b ON(a.company=b.company AND a.num=b.num)
    JOIN stops AS stopa ON (a.stop=stopa.id)
    JOIN stops AS stopb ON (b.stop=stopb.id)
WHERE stopa.name = 'Craiglockhart' AND stopb.name = 'London Road';

Using a self join

7.

停留所115と137を繋ぐ会社のリストを作ってください。

SELECT
    a.company
    , a.num
FROM route AS a
    JOIN route AS b ON (a.num = b.num AND a.company = b.company)
WHERE a.stop = 115 AND b.stop = 137
GROUP BY a.company, a.num;

8.

CraiglockhartとTollcrossを繋ぐ会社のリストを作ってください。

SELECT
    a.company
    , a.num
FROM route AS a
    JOIN route AS b ON (a.num = b.num AND a.company = b.company)
    JOIN stops AS c ON a.stop = c.id
    JOIN stops AS d ON b.stop = d.id
WHERE c.name = 'Craiglockhart' AND d.name = 'Tollcross';

9.

LRT会社が提供する’Craiglockhart’自体も含めて、’Craiglockhart’から1本のバスに乗って’Craiglockhart’に行くことができる停留所をDISTINCTを使ってリストアップしてください。関連するサービスの会社とバスの番号を含めてください。

SELECT
    d.name
    , a.company
    , a.num
FROM route AS a
    JOIN route AS b ON (a.num = b.num AND a.company = b.company)
    JOIN stops AS c ON a.stop = c.id
    JOIN stops AS d ON b.stop = d.id
WHERE (a.company = 'LRT' AND c.name = 'Craiglockhart')
    OR (a.company = 'LRT' AND c.name = 'Craiglockhart' AND d.name = 'Craiglockhart') ;

10.

Craiglockhart から Lochendへ行くことができる2つのバスのルートを見つけてください。最初のバスのバス番号と会社名、乗り換えの停留所名を表示してください。さらに、２台目のバスの番号とバスの会社を表示してください。

→（問題文と解答例の補足）Craiglockhartから出発して、一回の乗り換えを挟んで、Lochendに到達するルートを聞いているのだと思います。要求されている出力は、出発地のnum、出発地の会社名、乗り換えするバス停の名前、Lochendへ向かうバスのnum、Lochendへ向かうバスの会社名です。Craiglockhartから到着したバス停で、（おそらく徒歩で）違う会社のバス停だが同じ名前ではあるバス停に移動しているカラムが存在するので、SELECT文ではm1.num, m1.companyではなく、m2.num, m2.companyを呼んでくる必要があるようです。あと、問題文には書いてありませんが、出力はソートする必要があるようです。

SET SQL_BIG_SELECTS=1;
SELECT
    DISTINCT(start.num)
    , start.company
    , b.name
    , m2.num
    , m2.company
FROM route AS start
    JOIN route AS m1 ON (start.num = m1.num AND start.company = m1.company)
    JOIN (route AS m2 JOIN route AS goal ON (m2.num = goal.num AND m2 .company = goal.company))
    JOIN stops AS a ON start.stop = a.id
    JOIN stops AS b ON m1.stop = b.id
    JOIN stops AS c ON m2.stop = c.id
    JOIN stops AS d ON goal.stop = d.id
WHERE a.name = 'Craiglockhart' AND d.name = 'Lochend'
    AND b.name = c.name
ORDER BY start.num, b.name, m2.num;