Eine Einführung in

SQL

Self Joins

stops (id, name)
route (num, company, pos, stop)

Details zur Buslinien-Datenbank.

1a	Wieviele Haltestellen (`stops`) gibt es in der Datenbank? SELECT COUNT(*) FROM stops

1b	Ermittle die `id` für die Haltestelle `'Craiglockhart'`. SELECT id FROM stops WHERE name='Craiglockhart'

1c	Zeige die `id` und den Namen der Haltestellen der `'LRT'`-Linie `'4'`. SELECT id, name FROM stops, route WHERE id=stop AND company='LRT' AND num='4'

Routen und Haltestellen

2a	Die Abfrage zeigt die Nummern der Routen, die entweder über London Road (`149`) oder Craiglockhart (`53`) gehen. Führe die Abfrage aus und notiere die Linien, die zweimal (COUNT = 2) diese Haltestellen kreuzen. Füge eine `HAVING`-Klausel hinzu, um die Ausgabe auf diese zwei Routen zu beschränken. SELECT company, num, COUNT() FROM route WHERE stop=149 OR stop=53 GROUP BY company, num HAVING COUNT()=2	SELECT company, num, COUNT(*) FROM route WHERE stop=149 OR stop=53 GROUP BY company, num

2b	Führe den rechts stehenden Self Join aus. Du kannst erkennen, daß `b.stop` alle Stationen enthält, die von Craiglockhart aus erreichbar sind. Ändere die Abfrage so ab, daß es die Linien von Craiglockhart nach London Road zeigt. 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 = 149 AND b.stop=53	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

2c	Die Abfrage ist ähnlich der Vorigen. Durch einen Join der Haltestellen-Tabelle mit einer (virtuellen) Kopie von sich selbst können wir uns auf die Haltestellennamen anstelle der Nummern beziehen. Ändere die Abfrage, so daß die Linien zwischen `'Craiglockhart'` und `'London Road'` gezeigt werden. Alternativ können auch die Stationen zwischen `'Fairmilehead'` und `'Tollcross'` angezeigt werden. 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'	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'

Hinweise zum Anwenden von Self Joins.

3a	Ermittle alle Buslinien, die die Haltestellen `115` und `137` ('Haymarket' und 'Leith') verbinden. SELECT R1.company, R1.num FROM route R1, route R2 WHERE R1.num=R2.num AND R1.company=R2.company AND R1.stop=115 AND R2.stop=137

3b	Finde die Linien, die die Haltestellen `'Craiglockhart'` und `'Tollcross'` verbinden. SELECT R1.company, R1.num FROM route R1, route R2, stops S1, stops S2 WHERE R1.num=R2.num AND R1.company=R2.company AND R1.stop=S1.id AND R2.stop=S2.id AND S1.name='Craiglockhart' AND S2.name='Tollcross'

Finde die Linien, die die Haltestellen 'Craiglockhart' und 'Tollcross' verbinden.

SELECT R1.company, R1.num FROM route R1, route R2, stops S1, stops S2 WHERE R1.num=R2.num AND R1.company=R2.company AND R1.stop=S1.id AND R2.stop=S2.id AND S1.name='Craiglockhart' AND S2.name='Tollcross'

3c	Ermittle die Haltestellen, die von `'Craiglockhart'` ohne Umsteigen erreicht werden können. Es interessieren auch die Details der entsprechenden Linie(n). SELECT S2.id, S2.name, R2.company, R2.num FROM stops S1, stops S2, route R1, route R2 WHERE S1.name='Craiglockhart' AND S1.id=R1.stop AND R1.company=R2.company AND R1.num=R2.num AND R2.stop=S2.id

3d	Zeige, wie man von `'Sighthill'` nach `'Craiglockhart'` kommt. "Thanks to Sam Lafferty for solution SELECT a.name Start_At, b.num FirstBus, b.company FBC, e.name Change_At, f.num SecondBus, f.company SBC, g.name Arive_At FROM stops a, route b, stops c, route d, stops e, route f, stops g, route h WHERE a.id = b.stop AND c.id = d.stop AND e.id = f.stop AND g.id = h.stop AND b.num = d.num AND b.company = d.company AND f.num = h.num AND f.company = h.company AND c.name = e.name AND a.name = 'Craiglockhart' AND g.name = 'Sighthill' AND d.pos > b.pos AND h.pos > f.pos

Zeige, wie man von 'Sighthill' nach 'Craiglockhart' kommt.

"Thanks to Sam Lafferty for solution
SELECT a.name Start_At, b.num FirstBus, b.company FBC,
       e.name Change_At, f.num SecondBus, f.company SBC,
       g.name Arive_At
 FROM  stops a, route b,
       stops c, route d,
       stops e, route f,
       stops g, route h
WHERE  a.id = b.stop                 
  AND  c.id = d.stop
  AND  e.id = f.stop
  AND  g.id = h.stop
  
  AND  b.num = d.num
  AND  b.company = d.company   

  AND  f.num = h.num
  AND  f.company = h.company   
  AND  c.name = e.name

  AND  a.name = 'Craiglockhart'
  AND  g.name = 'Sighthill'

  AND d.pos > b.pos
  AND h.pos > f.pos

Eine Einführung in SQL

Self Joins

Routen und Haltestellen

Eine Einführung in

SQL