Difference between revisions of "Self join Quiz"

From SQLZOO
Jump to: navigation, search
 
Line 15: Line 15:
 
<div class=quiz>
 
<div class=quiz>
 
<div class=q>Select the code that would show it is possible to get from Craiglockhart to Haymarket
 
<div class=q>Select the code that would show it is possible to get from Craiglockhart to Haymarket
<syntaxhighlight class=d lang='sql'>SELECT DISTINCT a.name, b.name from stops a JOIN route z IN a.id=z.stop JOIN route y ON y.num = z.num JOIN stops b IN y.stop=b.id where a.name='Craiglockhart' AND b.name ='Haymarket'</syntaxhighlight>
+
<syntaxhighlight class=d lang='sql'>
<syntaxhighlight class=d lang='sql'>SELECT DISTINCT a.name, b.name from stops a JOIN route z ON a.id=z.stop JOIN route y JOIN stops b ON y.stop=b.id where a.name='Craiglockhart' AND b.name ='Haymarket'</syntaxhighlight>
+
SELECT DISTINCT a.name, b.name
<syntaxhighlight class='d y' lang='sql'>SELECT DISTINCT a.name, b.name from stops a JOIN route z ON a.id=z.stop JOIN route y ON y.num = z.num JOIN stops b ON y.stop=b.id where a.name='Craiglockhart' AND b.name ='Haymarket'</syntaxhighlight>
+
  FROM stops a JOIN route z IN a.id=z.stop
<syntaxhighlight class=d lang='sql'>SELECT DISTINCT a.name, b.name from stops a JOIN route z ON a.id=z.stop JOIN route y ON y.num = z.num JOIN stops b ON y.stop=b.id where a.name='Craiglockhart' AND b.name ='Sighthill'</syntaxhighlight>
+
  JOIN route y ON y.num = z.num
<syntaxhighlight class=d lang='sql'>SELECT DISTINCT a.name, b.name from stops a JOIN route z ON a.id=z.stop JOIN route y ON y.num = z.num JOIN stops b ON y.stop=b.id where y.name='Craiglockhart' AND z.name ='Haymarket'</syntaxhighlight>
+
  JOIN stops b IN y.stop=b.id
 +
WHERE a.name='Craiglockhart' AND b.name ='Haymarket'</syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
SELECT DISTINCT a.name, b.name
 +
  FROM stops a JOIN route z ON a.id=z.stop
 +
  JOIN route y JOIN stops b ON y.stop=b.id
 +
WHERE a.name='Craiglockhart' AND b.name ='Haymarket'</syntaxhighlight>
 +
<syntaxhighlight class='d y' lang='sql'>
 +
SELECT DISTINCT a.name, b.name
 +
  FROM stops a JOIN route z ON a.id=z.stop
 +
  JOIN route y ON y.num = z.num
 +
  JOIN stops b ON y.stop=b.id
 +
WHERE a.name='Craiglockhart' AND b.name ='Haymarket'</syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
SELECT DISTINCT a.name, b.name from stops a
 +
  JOIN route z ON a.id=z.stop
 +
  JOIN route y ON y.num = z.num
 +
  JOIN stops b ON y.stop=b.id
 +
WHERE a.name='Craiglockhart' AND b.name ='Sighthill'</syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
SELECT DISTINCT a.name, b.name
 +
  FROM stops a JOIN route z ON a.id=z.stop
 +
  JOIN route y ON y.num = z.num
 +
  JOIN stops b ON y.stop=b.id
 +
WHERE y.name='Craiglockhart' AND z.name ='Haymarket'</syntaxhighlight>
 
</div>
 
</div>
  
 
<div class=q>Select the code that shows the stops that are on route.num '2A' which can be reached with one bus from Haymarket?
 
<div class=q>Select the code that shows the stops that are on route.num '2A' which can be reached with one bus from Haymarket?
<syntaxhighlight class=d lang='sql'>SELECT S2.id, S2.name, R2.company, R2.num FROM stops S1, stops S2, route R1, route R2 WHERE S1.name='Haymarket' AND S1.id=R1.stop AND R1.company=R2.company AND R1.num=R2.num AND R2.stop=S2.id AND R1.num='2A'</syntaxhighlight>
+
<syntaxhighlight class=d lang='sql'>
<syntaxhighlight class=d lang='sql'>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 AND R2.num='2A'</syntaxhighlight>
+
SELECT S2.id, S2.name, R2.company, R2.num
<syntaxhighlight class=d lang='sql'>SELECT S2.id, S2.name, R2.company, R2.num FROM stops S1, stops S2, route R1, route R2 WHERE S1.name='Haymarket' AND S1.id=R1.stop AND R1.company=R2.company AND R1.num=R2.num AND R2.stop=S2.id</syntaxhighlight>
+
  FROM stops S1, stops S2, route R1, route R2
<syntaxhighlight class=d lang='sql'>SELECT S2.id, S2.name, R2.company, R2.num FROM stops S1, stops S2, route R1, route R2 WHERE S1.name='Haymarket' AND S1.id=R1.stop AND R1.company=R2.company AND R1.num=R2.num AND R2.stop=S2.id AND R2.num='2'</syntaxhighlight>
+
WHERE S1.name='Haymarket' AND S1.id=R1.stop
<syntaxhighlight class='d y' lang='sql'>SELECT S2.id, S2.name, R2.company, R2.num FROM stops S1, stops S2, route R1, route R2 WHERE S1.name='Haymarket' AND S1.id=R1.stop AND R1.company=R2.company AND R1.num=R2.num AND R2.stop=S2.id AND R2.num='2A'</syntaxhighlight>
+
  AND R1.company=R2.company AND R1.num=R2.num
 +
  AND R2.stop=S2.id AND R1.num='2A'</syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
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 AND R2.num='2A'</syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
SELECT S2.id, S2.name, R2.company, R2.num
 +
  FROM stops S1, stops S2, route R1, route R2
 +
WHERE S1.name='Haymarket' AND S1.id=R1.stop
 +
  AND R1.company=R2.company AND R1.num=R2.num
 +
  AND R2.stop=S2.id</syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
SELECT S2.id, S2.name, R2.company, R2.num
 +
  FROM stops S1, stops S2, route R1, route R2
 +
WHERE S1.name='Haymarket' AND S1.id=R1.stop
 +
  AND R1.company=R2.company AND R1.num=R2.num
 +
  AND R2.stop=S2.id AND R2.num='2'</syntaxhighlight>
 +
<syntaxhighlight class='d y' lang='sql'>
 +
SELECT S2.id, S2.name, R2.company, R2.num
 +
  FROM stops S1, stops S2, route R1, route R2
 +
WHERE S1.name='Haymarket' AND S1.id=R1.stop
 +
  AND R1.company=R2.company AND R1.num=R2.num
 +
  AND R2.stop=S2.id AND R2.num='2A'</syntaxhighlight>
 
</div>
 
</div>
  
 
<div class=q>Select the code that shows the services available from Tollcross?
 
<div class=q>Select the code that shows the services available from Tollcross?
<syntaxhighlight class=d lang='sql'>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)</syntaxhighlight>
+
<syntaxhighlight class=d lang='sql'>
<syntaxhighlight class=d lang='sql'>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='Sighthill'</syntaxhighlight>
+
SELECT a.company, a.num, stopa.name, stopb.name
<syntaxhighlight class=d lang='sql'>SELECT a.company, a.num, stopa.name, stopb.name FROM route a JOIN route b IN (a.company=b.company AND a.num=b.num) JOIN stops stopa IN (a.stop=stopa.id) JOIN stops stopb ON (b.stop=stopb.id) WHERE stopa.name='Tollcross'</syntaxhighlight>
+
  FROM route a JOIN route b ON (a.company=b.company AND a.num=b.num)
<syntaxhighlight class='d y' lang='sql'>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='Tollcross'</syntaxhighlight>
+
  JOIN stops stopa ON (a.stop=stopa.id)
<syntaxhighlight class=d lang='sql'> 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 stopz.name='Tollcross'</syntaxhighlight>
+
  JOIN stops stopb ON (b.stop=stopb.id)</syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
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='Sighthill'</syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
SELECT a.company, a.num, stopa.name, stopb.name
 +
  FROM route a JOIN route b IN (a.company=b.company AND a.num=b.num)
 +
  JOIN stops stopa IN (a.stop=stopa.id)
 +
  JOIN stops stopb ON (b.stop=stopb.id)
 +
WHERE stopa.name='Tollcross'</syntaxhighlight>
 +
<syntaxhighlight class='d y' lang='sql'>
 +
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='Tollcross'</syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
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 stopz.name='Tollcross'</syntaxhighlight>
 
</div>
 
</div>
  

Latest revision as of 12:48, 23 April 2015

SELF JOIN quiz

stopsroute
idnum
namecompany
pos
stop
Select the code that would show it is possible to get from Craiglockhart to Haymarket
SELECT DISTINCT a.name, b.name
  FROM stops a JOIN route z IN a.id=z.stop
  JOIN route y ON y.num = z.num
  JOIN stops b IN y.stop=b.id
 WHERE a.name='Craiglockhart' AND b.name ='Haymarket'
SELECT DISTINCT a.name, b.name
  FROM stops a JOIN route z ON a.id=z.stop
  JOIN route y JOIN stops b ON y.stop=b.id
 WHERE a.name='Craiglockhart' AND b.name ='Haymarket'
SELECT DISTINCT a.name, b.name
  FROM stops a JOIN route z ON a.id=z.stop
  JOIN route y ON y.num = z.num
  JOIN stops b ON y.stop=b.id
 WHERE a.name='Craiglockhart' AND b.name ='Haymarket'
SELECT DISTINCT a.name, b.name from stops a
  JOIN route z ON a.id=z.stop
  JOIN route y ON y.num = z.num
  JOIN stops b ON y.stop=b.id
 WHERE a.name='Craiglockhart' AND b.name ='Sighthill'
SELECT DISTINCT a.name, b.name
  FROM stops a JOIN route z ON a.id=z.stop
  JOIN route y ON y.num = z.num
  JOIN stops b ON y.stop=b.id
 WHERE y.name='Craiglockhart' AND z.name ='Haymarket'
Select the code that shows the stops that are on route.num '2A' which can be reached with one bus from Haymarket?
SELECT S2.id, S2.name, R2.company, R2.num
  FROM stops S1, stops S2, route R1, route R2
 WHERE S1.name='Haymarket' AND S1.id=R1.stop
   AND R1.company=R2.company AND R1.num=R2.num
   AND R2.stop=S2.id AND R1.num='2A'
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 AND R2.num='2A'
SELECT S2.id, S2.name, R2.company, R2.num
  FROM stops S1, stops S2, route R1, route R2
 WHERE S1.name='Haymarket' AND S1.id=R1.stop
   AND R1.company=R2.company AND R1.num=R2.num
   AND R2.stop=S2.id
SELECT S2.id, S2.name, R2.company, R2.num
  FROM stops S1, stops S2, route R1, route R2
 WHERE S1.name='Haymarket' AND S1.id=R1.stop
   AND R1.company=R2.company AND R1.num=R2.num
   AND R2.stop=S2.id AND R2.num='2'
SELECT S2.id, S2.name, R2.company, R2.num
  FROM stops S1, stops S2, route R1, route R2
 WHERE S1.name='Haymarket' AND S1.id=R1.stop
   AND R1.company=R2.company AND R1.num=R2.num
   AND R2.stop=S2.id AND R2.num='2A'
Select the code that shows the services available from 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)
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='Sighthill'
SELECT a.company, a.num, stopa.name, stopb.name
  FROM route a JOIN route b IN (a.company=b.company AND a.num=b.num)
  JOIN stops stopa IN (a.stop=stopa.id)
  JOIN stops stopb ON (b.stop=stopb.id)
 WHERE stopa.name='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='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 stopz.name='Tollcross'