概要

10番目の問題がわからなかった。。。。

1.

SELECT count(1) "# of Stops"  
FROM stops  

2.

SELECT id  
FROM stops  
WHERE name = 'Craiglockhart'  

3.

SELECT id, name  
FROM stops  
JOIN route ON stops.id = route.stop  
WHERE num = '4' AND company = '[LRT](<http://d.hatena.ne.jp/keyword/LRT>)'  

5.

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.

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.

SELECT DISTINCT a.company, a.num  
FROM route a  
JOIN route b ON a.num = b.num AND a.company = b.company  
WHERE a.stop=115 AND b.stop=137  

SELECT a.company, a.num  
FROM route a  
JOIN route b ON a.num = b.num AND a.company = b.company  
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.

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

10.

わからなかった。。。。