Quick Ref.
Functions
date
number
string
Data Types
date
number
string

Edinburgh Buses

Details of the database. Looking at the data.

stops(id, name)
route(num,company,pos, stop)
1a. How many stops are in the database.

Results
1b. Find the id value for the stop 'Craiglockhart'

Results
1c. Give the id and the name for the stops on the '4' 'LRT' service.

Results

Routes and stops

2a. 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.

Results
2b. Execute the self join shown and observe that b.stop gives all the places you can get to from Craiglockhart.
Change the query so that it shows the services from Craiglockhart to London Road.

Results
2c. 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'

Results

Using a self join

3a. Give a list of all the services which connect stops 115 and 137 ('Haymarket' and 'Leith')

Results
3b. Give a list of the services which connect the stops 'Craiglockhart' and 'Tollcross'

Results
3c. Give a list of the stops which may be reached from 'Craiglockhart' by taking one bus. Include the details of the appropriate service.

Results
3d. Show how it possible to get from Sighthill to Craiglockhart.

Results