Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /* Self join * /
- /* How many stops are in the database */
- SELECT COUNT(*)
- FROM stops
- /* Find the id value for the stop Craiglockhart */
- SELECT id
- FROM stops
- WHERE name = 'Craiglockhart'
- /* Give the id and the name for the stops on the '4' 'LRT' service. */
- SELECT id, name
- FROM stops JOIN route ON id = stop
- WHERE company = 'LRT' AND num = 4
- /* Restricting output to two routes (London Road or Craiglockhart) */
- SELECT company, num, COUNT(*)
- FROM route
- WHERE stop = 149 or stop = 53
- GROUP BY company, num
- HAVING COUNT(*) = 2
- /* 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. */
- 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 = (SELECT id FROM stops WHERE name = 'London Road')
- /* 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. */
- 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'
- /* Give a list of all the services which connect stops 115 and 137 */
- 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'
- /* Give a list of the service which connects 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'
- /* 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. */
- SELECT stopA.name, 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 stopB.name = 'Craiglockhart'
Add Comment
Please, Sign In to add comment