Overview

I couldn't figure out problem 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'

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.

I couldn't figure this one out....