知り合いが、SQLの勉強の為に SQLZoo というサイトを参考にしていました。
ただ、Self Join のページがわからないとの事で、この記事を作成することにしました。

ネット上には同様な解説サイトが多数ありますが、今回は以下のサイトを参考にさせていただきました。

では、問題を考えていきます。

Edinburgh Buses. (エジンバラのバス)

テーブル定義は以下のページにあります。
https://sqlzoo.net/wiki/Edinburgh_Buses.

テーブル：stops

カラム名	型	備考
id	INT	任意の値
name	CHAR(30)	バスが運行しているエリア・地域
※バスが運用しているエリアのリスト。
実際のバス停名は含まれない。

テーブル：route

カラム名	型	備考
num	CHAR(5)	バス系統番号
company	CHAR(3)	バス会社名
pos	INT	ルート内停止順※1
stop	INT	stopsテーブルのid

※バスで街を通過する記録
※1、一部のルートでは、停車地を再訪する場合があります。
　　　ほとんどのバスは両方向に行きます。

問題１

テーブル：stopsのレコード数は？

 select count(*) from stops;

答え
　246個

では、テーブル：routeのレコード数は？

 select count(*) from route;

1174個

問題２

バスが止まるエリア'Craiglockhart'のidは？

　select id from stops where name = 'Craiglockhart'

答えは

53

では、id が 246 の停車エリアは？

  select name from stops where id = 246;

答えは
Whitburn

問題３

原文
Give the id and the name for the stops on the '4' 'LRT' service.
概約
サービス(バス会社とバス系統番号)が LRT と 4 の止まる id とエリア名は？

ここでは、routeテーブルからバス会社とバス系統番号を検索し止まる番号を求める。
その結果を stopsテーブルのidとnameを求める。

よって、先ずテーブルを結合することを考える。
routeテーブルのstopは、stopsテーブルのid になっているので以下の様に結合(join)してみる。

select * from route join stops on id = stop;

num	company	pos	stop	id	name
124	SMT	9	1	1	Aberlady
100	MAC	6	2	2	Abington
106	SMT	8	3	3	Amisfield Park
29	LOW	8	4	4	Ancrum

ここから、問題３の条件を当てはめてSQLを作る。

select id, name from stops join route on id = stop
 where num = 4 and company = 'LRT';

と書くと答えが正解にならない。
正しくは、

select id, name from stops join route on id = stop
 where num = '4' and company = 'LRT';

となる。
routeテーブルのnumは数値と思って ’’ でくくらないと答えはでるが正解にはならない。

バスの路線と停車エリア

問題４

回答欄に示されているクエリは、London Road(149)または Craiglockhart(53)のいずれかを訪れるルートの数を示しています。

SELECT company, num, COUNT(*)
 FROM route WHERE stop=149 OR stop=53
 GROUP BY company, num

クエリを修正し、これらの停車エリアを通過する 2つのサービス(company と num )のカウントが2であるものを抽出してください。
尚、HAVING句を追加して、出力をこれら2つのルートにあるものに制限します。

ちなみに、回答欄に示されているクエリをそのまま実行すると

company	num	COUNT(*)
LRT	10	1
LRT	15	1
LRT	20	1
LRT	26A	1
LRT	27	1
LRT	34	1
LRT	35	1
LRT	4	2
LRT	42	1
LRT	43	1
LRT	44	1
LRT	45	2
LRT	46A	1
LRT	47	1
LRT	5	1
LRT	51	1
LRT	63	1
LRT	65	1
LRT	75	1
LRT	87	1
LRT	87A	1
SMT	106	1
SMT	113	1
SMT	124	1
SMT	66	1
SMT	66A	1
SMT	C5	1
SMT	C55	1

と表示される。

では、いったい何行あるのかと思ってクエリを作成します。
compayの数を副問合せ(in)を使用してみます。

SELECT count(*) from route 
 where company in
( select company
FROM route WHERE stop=149 OR stop=53
GROUP BY company, num )

1009行あることが確認できる。

既にcount(*)が 2 であるものが目視できるが他にもある可能性があるのでクエリを修正すると

SELECT company, num, COUNT(*)
FROM route WHERE stop=149 OR stop=53
GROUP BY company, num
having count(*) = 2;

company	num	COUNT(*)
LRT	4	2
LRT	45	2

となる。

問題５

示されている自己結合を実行し、b.stopがルートを変更せずにCraiglockhartから到達できるすべての場所を提供することを確認します。
クエリを変更して、CraiglockhartからLondonRoadまでのサービスが表示されるようにします。
（Google翻訳より）

※提示されているクエリ
SELECT a.company, a.num, a.stop, b.stop
FROM route a JOIN route b ON
  (a.company=b.company AND a.num=b.num)
WHERE a.stop=53

を実行すると

company	num	stop	stop
LRT	10	53	215
LRT	10	53	162
　：
　：
　：
LRT	27	53	59
LRT	27	53	37
　：
　：

となる。

自己結合(1つ目のrouteを a とし 2つ目のrouteを b とする )の要件を確認すると、aとbのバス会社(company) と aとbのバス系統番号(num)が同じものの表を作成している。
結果として、同じ会社で同じバス系統番号のバスが止まる表になる。
ここで、where句を付けないと、停車エリア(stop)の組み合わせが大量にできて大きな表になる。
　問題にある停車エリア(Craiglockhart)は、問題４から、stop = 53 と判明しているので、例題の絞り込みに指定することで、停車エリア(Craiglockhart)に関連する一覧を表示する。

さて、問題の停車エリア(LondonRoad)は stop = 149 なので自己結合した b テーブルの stop を絞り込めばよい。

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

結果は

company	num	stop	stop
LRT	4	53	149
LRT	45	53	149

となり、バス会社 LRT のバス系統番号の4番と45番が停車エリア(Craiglockhart)と停車エリア(LondonRoad)を通過することがわかる。

問題６

示されているクエリは前のクエリと似ていますが、ストップテーブルの2つのコピーを結合することにより、番号ではなく名前でストップを参照できます。
'Craiglockhart'と 'London Road'の間のサービスが表示されるようにクエリを変更します。
これらの場所にうんざりしている場合は、「Tollcross」に対して「Fairmilehead」を試してください。
（Google翻訳より）

※提示されているクエリ
SELECT a.company, a.num, stopa.name, stopb.name
FROM route a JOIN route b ON
  (a.company=b.company AND a.num=b.num)
  JOIN stops stopa ON (a.stop=stopa.id)
  JOIN stops stopb ON (b.stop=stopb.id)
WHERE stopa.name='Craiglockhart'

これは問題５の routeテーブルの自己結合に stopテーブルをさらに結合する。
　1つ目のroute a テーブルには、stopsテーブルを stopsa として結合する。
　2つ目のtoute b テーブルには、stopsテーブルを stopsb として結合する。
これで、route a b それぞれの停車エリアの番号であったものを停車エリア名で表示する。

提示されているクエリをそのまま実行すると、

company	num	name	name
LRT	10	Craiglockhart	Silverknowes
LRT	10	Craiglockhart	Muirhouse
　：
　：
LRT	27	Craiglockhart	Silverknowes
LRT	27	Craiglockhart	Crewe Toll
　：
　：

となる。

問題にあるように、'Craiglockhart'と 'London Road'の間のサービス(バス会社とバス系統番号)が表示されるよう変更するには、問題5と同じ様に絞り込みの条件を追加すればよい。

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

結果は

company	num	name	name
LRT	4	Craiglockhart	London Road
LRT	45	Craiglockhart	London Road

問題７

原文
Give a list of all the services which connect stops 115 and 137 ('Haymarket' and 'Leith')
概約
停車エリア番号 115と137（「Haymarket」と「Leith」）を接続するすべてのサービス（バス会社とバス系統番号）のリストを提供します。
(Google翻訳より)

問題６を利用して、

SELECT a.company, a.num
FROM route a JOIN route b ON
  (a.company=b.company AND a.num=b.num)
  JOIN stops stopa ON (a.stop=stopa.id)
  JOIN stops stopb ON (b.stop=stopb.id)
WHERE
  stopa.name='Haymarket'
  and 
  stopb.name='Leith';

結果は
company	num
LRT	12
LRT	12
LRT	2
LRT	2
LRT	22
LRT	25
LRT	2A
LRT	2A
SMT	C5

となる。
重複しているレコードがあるので問題のリストとしては不適切になる。
重複を排除するために、DISTINCT 句を使用します。

SELECT DISTINCT a.company, a.num
FROM route a JOIN route b ON
  (a.company=b.company AND a.num=b.num)
  JOIN stops stopa ON (a.stop=stopa.id)
  JOIN stops stopb ON (b.stop=stopb.id)
WHERE
  stopa.name='Haymarket'
  and 
  stopb.name='Leith';

結果
company	num
LRT	12
LRT	2
LRT	22
LRT	25
LRT	2A
SMT	C5

問題８

原文
　Give a list of the services which connect the stops 'Craiglockhart' and 'Tollcross'
概約
　ストップ「クレイグロックハート」と「トールクロス」を結ぶサービスのリストを提供します。

これも問題７を利用してクエリーを作成すると

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

結果は

company	num
LRT	10
LRT	27
LRT	45
LRT	47

となります。

問題９

原文
　Give a distinct list of the stops which may be reached from 'Craiglockhart' by taking one bus, including 'Craiglockhart' itself, offered by the LRT company. Include the company and bus no of the relevant services.
概約
LRT会社が提供する「クレイグロックハート」自体を含む1つのバスに乗ることで、「クレイグロックハート」から到達できる停車地の明確なリストを提供します。関連するサービスの会社とバス番号を含めます。
(Google翻訳より)

これも、前回のクエリーを利用して考えますが、前回と違うところは、
　必要なリストは、
　「クレイグロックハート」から到達できる停車地の明確なリスト、サービス（バス会社名とバス系統番号）
　絞り込み条件は
　・バス会社はLRT
　・バス停車エリア'Craiglockhart'を通過すること
　・ 'Craiglockhart'を通過するバス停車エリアをすべて

上記の内容をクエリーにすると

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

結果は

name	company	num
Silverknowes	LRT	10
Muirhouse	LRT	10
Newhaven	LRT	10
Leith	LRT	10
Leith Walk	LRT	10
Princes Street	LRT	10
Tollcross	LRT	10
Craiglockhart	LRT	10
Colinton	LRT	10
Torphin	LRT	10
Silverknowes	LRT	27
Crewe Toll	LRT	27
Canonmills	LRT	27
Hanover Street	LRT	27
Tollcross	LRT	27
Craiglockhart	LRT	27
Oxgangs	LRT	27
Hunters Tryst	LRT	27
Bingham	LRT	4
Northfield	LRT	4
London Road	LRT	4
Princes Street	LRT	4
Haymarket	LRT	4
Craiglockhart	LRT	4
Oxgangs	LRT	4
Fairmilehead	LRT	4
Hillend	LRT	4
Brunstane	LRT	45
Duddingston	LRT	45
Northfield	LRT	45
London Road	LRT	45
Hanover Street	LRT	45
Tollcross	LRT	45
Craiglockhart	LRT	45
Colinton	LRT	45
Currie	LRT	45
Riccarton Campus	LRT	45
Canonmills	LRT	47
Hanover Street	LRT	47
Tollcross	LRT	47
Craiglockhart	LRT	47
Colinton	LRT	47
Currie	LRT	47
Balerno	LRT	47
Cockburn Crescent	LRT	47
Balerno Church	LRT	47

問題１０

原文
Find the routes involving two buses that can go from Craiglockhart to Lochend.
Show the bus no. and company for the first bus, the name of the stop for the transfer,
and the bus no. and company for the second bus.

Hint
Self-join twice to find buses that visit Craiglockhart and Lochend, then join those on matching stops.

概約
クレイグロックハートからロッヘンドまで行くことができる2つのバスを含むルートを見つけます。
最初のバスのバス番号と会社、乗り換えの停留所の名前、2番目のバスのバス番号と会社を表示します。

ヒント
クレイグロックハートとロッヘンドを訪れるバスを見つけるために2回自己参加してから、一致する停留所でそれらに参加します。
(Google翻訳より)

これも、前回のクエリーを利用して考えますが、前回と違うところは、
　必要なリストは、
　　最初のバス系統番号とバス会社、乗り換えのバス停車エリア、その次のバス系統番号とバス会社
　絞り込み条件は
　　最初のスタートエリアは 'Craiglockhart'
　　目的地点のエリアは 'Lochend'
　　中継エリアがある、
　　　・'Craiglockhart' から中継エリアまで
　　　・中継エリアから 'Lochend' まで
　　＝＝＞
　　前問はスタートからゴールまで乗り換えなしですが、今回は乗り換えがあるため2回考える必要がある。
　　ただし、1回目のゴールと2回目のスタートが同じとすればいい。

感じとしては、問題８を2回分まとめて条件づけする感じです。

一つ目は

SELECT DISTINCT a.company, a.num
FROM route a JOIN route b ON
  (a.company=b.company AND a.num=b.num)
  JOIN stops stopa ON (a.stop=stopa.id)
  JOIN stops stopb ON (b.stop=stopb.id)
WHERE
  stopa.name='Craiglockhart'
  and 
  stopb.name= 中継エリア

二つ目は

SELECT DISTINCT c.company, c.num
FROM route c JOIN route d ON
  (c.company=d.company AND c.num=d.num)
  JOIN stops stopc ON (c.stop=stopc.id)
  JOIN stops stopd ON (d.stop=stopd.id)
WHERE
  stopc.name= 中継エリア
  and 
  stopd.name='Lochend'

を一つにまとめると

SELECT DISTINCT a.num, a.company, stopb.name, c.num, c.company 
FROM route a 
  JOIN route b ON (a.company=b.company AND a.num=b.num)
  JOIN (route c join route d ON (c.company=d.company AND c.num=d.num))
  JOIN stops stopa ON (a.stop=stopa.id)
  JOIN stops stopb ON (b.stop=stopb.id)
  JOIN stops stopc ON (c.stop=stopc.id)
  JOIN stops stopd ON (d.stop=stopd.id)
WHERE
  stopa.name='Craiglockhart'
  and stopb.name=stopc.name;
  and stopd.name='Lochend'

結果は

num	company	name	num	company
10	LRT	Leith	34	LRT
10	LRT	Leith	35	LRT
10	LRT	Leith	87	LRT
10	LRT	Leith	C5	SMT
10	LRT	Princes Street	65	LRT
10	LRT	Princes Street	C5	SMT
27	LRT	Crewe Toll	20	LRT
27	LRT	Canonmills	34	LRT
27	LRT	Canonmills	35	LRT
4	LRT	London Road	20	LRT
4	LRT	London Road	34	LRT
4	LRT	London Road	35	LRT
4	LRT	London Road	42	LRT
4	LRT	London Road	46A	LRT
4	LRT	London Road	65	LRT
4	LRT	London Road	87	LRT
4	LRT	London Road	87A	LRT
4	LRT	London Road	C5	SMT
4	LRT	Princes Street	65	LRT
4	LRT	Princes Street	C5	SMT
4	LRT	Haymarket	65	LRT
4	LRT	Haymarket	C5	SMT
45	LRT	Duddingston	42	LRT
45	LRT	Duddingston	46A	LRT
45	LRT	London Road	20	LRT
45	LRT	London Road	34	LRT
45	LRT	London Road	35	LRT
45	LRT	London Road	42	LRT
45	LRT	London Road	46A	LRT
45	LRT	London Road	65	LRT
45	LRT	London Road	87	LRT
45	LRT	London Road	87A	LRT
45	LRT	London Road	C5	SMT
45	LRT	Riccarton Campus	65	LRT
47	LRT	Canonmills	34	LRT
47	LRT	Canonmills	35	LRT

となりますが、正解にはなりません。
正解と内容は同じで並びが合っていないので、

ORDER BY a.num, stopb.name, c.num;

を付けて

SELECT DISTINCT a.num, a.company, stopb.name, c.num, c.company 
FROM route a 
  JOIN route b ON (a.company=b.company AND a.num=b.num)
  JOIN (route c join route d ON (c.company=d.company AND c.num=d.num))
  JOIN stops stopa ON (a.stop=stopa.id)
  JOIN stops stopb ON (b.stop=stopb.id)
  JOIN stops stopc ON (c.stop=stopc.id)
  JOIN stops stopd ON (d.stop=stopd.id)
WHERE
  stopa.name='Craiglockhart'
  and stopb.name=stopc.name;
  and stopd.name='Lochend'
ORDER BY a.num, stopb.name, c.num;

とすることで、正解になる。