Self join
| Language: Project:Language policy | English • 日本語 • 中文 |
|---|
Edinburgh Buses
Details of the database Looking at the data
stops(id, name) route(num, company, pos, stop)
| stops |
|---|
| id |
| name |
| route |
|---|
| num |
| company |
| pos |
| stop |
How many stops are in the database.
SELECTCOUNT(*) FROMstops
Find the id value for the stop 'Craiglockhart'
SELECTid FROMstops WHEREname='Craiglockhart'
Give the id and the name for the stops on the '4' 'LRT' service.
SELECTid,nameFROMstops,route WHEREid=stop ANDcompany='LRT' ANDnum='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.
SELECTcompany,num,COUNT(*) FROMrouteWHEREstop=149ORstop=53 GROUPBYcompany,num
SELECTcompany,num,COUNT(*) FROMrouteWHEREstop=149ORstop=53 GROUPBYcompany,num HAVINGCOUNT(*)=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.
SELECTa.company,a.num,a.stop,b.stop FROMrouteaJOINroutebON (a.company=b.companyANDa.num=b.num) WHEREa.stop=53
SELECTa.company,a.num,a.stop,b.stop FROMrouteaJOINroutebON (a.company=b.companyANDa.num=b.num) WHEREa.stop=53ANDb.stop=149
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'
SELECTa.company,a.num,stopa.name,stopb.name FROMrouteaJOINroutebON (a.company=b.companyANDa.num=b.num) JOINstopsstopaON(a.stop=stopa.id) JOINstopsstopbON(b.stop=stopb.id) WHEREstopa.name='Craiglockhart'
SELECTa.company,a.num,stopa.name,stopb.name FROMrouteaJOINroutebON (a.company=b.companyANDa.num=b.num) JOINstopsstopaON(a.stop=stopa.id) JOINstopsstopbON(b.stop=stopb.id) WHEREstopa.name='Craiglockhart' ANDstopb.name='London Road'
Give a list of all the services which connect stops 115 and 137 ('Haymarket' and 'Leith')
SELECTDISTINCTR1.company,R1.num FROMrouteR1,routeR2 WHERER1.num=R2.numANDR1.company=R2.company ANDR1.stop=115ANDR2.stop=137
Give a list of the services which connect the stops 'Craiglockhart' and 'Tollcross'
SELECTR1.company,R1.num FROMrouteR1,routeR2,stopsS1,stopsS2 WHERER1.num=R2.numANDR1.company=R2.company ANDR1.stop=S1.idANDR2.stop=S2.id ANDS1.name='Craiglockhart' ANDS2.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.
SELECTDISTINCTS2.name,R2.company,R2.num FROMstopsS1,stopsS2,routeR1,routeR2 WHERES1.name='Craiglockhart' ANDS1.id=R1.stop ANDR1.company=R2.companyANDR1.num=R2.num ANDR2.stop=S2.id
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.
SELECTbus1.num,bus1.company,name,bus2.num,bus2.companyFROM(SELECTstart1.num,start1.company,stop1.stopFROMrouteASstart1JOINrouteASstop1ONstart1.num=stop1.numANDstart1.company=stop1.companyANDstart1.stop!=stop1.stopWHEREstart1.stop=(SELECTidFROMstopsWHEREname='Craiglockhart'))ASbus1JOIN(SELECTstart2.num,start2.company,start2.stopFROMrouteASstart2JOINrouteASstop2ONstart2.num=stop2.numANDstart2.company=stop2.companyandstart2.stop!=stop2.stopWHEREstop2.stop=(SELECTidFROMstopsWHEREname='Lochend'))ASbus2ONbus1.stop=bus2.stopJOINstopsONbus1.stop=stops.idORDERBYbus1.num,bus1.company,name,bus2.num,bus2.company