【SQL練習】【SQLZOO】 9 Self join

9 Self join

問題はこちらです。
Self join - SQLZOO


以下が解答となります。

1.Summary

【問題】
How many stops are in the database.

データベース内にいくつ停留所がありますか。

【解答】

SELECT COUNT(*) FROM stops;

2.

【問題】
Find the id value for the stop 'Craiglockhart'

停留所「Craiglockhart」のIDを見つけてください。

【解答】

SELECT id FROM stops WHERE name = 'Craiglockhart';

3.

【問題】
Give the id and the name for the stops on the '4' 'LRT' service.

「4」,「LRT」サービスに関してIDと停留所の名前を伝えてください。

【解答】

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

4.Routes and stops

【問題】
The query shown gives the number of routes that visit either London Road (149) or Craiglockhart (53). Run the query and notice the two services that link these stops have a count of 2. Add a HAVING clause to restrict the output to these two routes.

表示されているクエリは、「London Road（149）」または「Craiglockhart （53）」のいずれかを訪れるルートの数を示しています。クエリを実行し、これらの停留所をリンクする2つのサービスのカウントが2であることに注意してください。HAVING句を追加して、出力をこれら2つのルートに制限してください。

【解答】

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

5.

【問題】
Execute the self join shown and observe that b.stop gives all the places you can get to from Craiglockhart, without changing routes. Change the query so that it shows the services from Craiglockhart to London Road.

ルートを変えることなく、示される自身接点を実行して、b.stopがあなたがCraiglockhartからに得ることができるすべての場所を与えると述べてください。
それがCraiglockhartからロンドン通りまでサービスを示すように、問合わせを変えてください。

【解答】

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;

6.

【問題】
The query shown is similar to the previous one, however by joining two copies of the stops table we can refer to stops by name rather than by number. Change the query so that the services between 'Craiglockhart' and 'London Road' are shown. If you are tired of these places try 'Fairmilehead' against 'Tollcross'

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

【解答】

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';

7.Using a self join

【問題】
Give a list of all the services which connect stops 115 and 137 ('Haymarket' and 'Leith')

停留所115と137（「Haymarket」と「Leith」）をつなぐすべてのサービスのリストを伝えてください。

【解答】

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';

8.

【問題】
Give a list of the services which connect the stops 'Craiglockhart' and 'Tollcross'

停留所「Craiglockhart」と「Tollcross」をつなぐサービスのリストを伝えてください。

【解答】

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='Craiglockhart' and stopb.name='Tollcross';

9.

【問題】
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台のバス（「Craiglockhart」自体を含む）に乗ることによって「Craiglockhart」から達するかもしれない停留所の異なったリストを伝えてください。
関連したサービスの会社とバスno.を含めてください。

【解答】

SELECT 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' and a.company = 'LRT';

10.

【問題】
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.

ルートが「Craiglockhart」から「Lochend」へ行くことができる2台のバスを含んでいるのを発見してください。
2台目のバスのために最初のバス（移動のための停止の名前）とバスno.と会社のためにバスno.と会社を示してください。

【解答】

SELECT 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 stopd.name='Lochend' 
  AND stopb.name = stopc.name
ORDER BY a.num, a.company, stopb.name, c.num;

「SQLZOO」につきまして、SQLの記述問題はこれで終わりですので、一旦終了とします。