Difference between revisions of "Self join Quiz"

From SQLZOO
Jump to: navigation, search
(2 intermediate revisions by one other user not shown)
Line 1: Line 1:
 
SELF JOIN quiz
 
SELF JOIN quiz
 +
 +
<div class='schema'></div>
 +
<div class = 'ref_section'>
 +
<table class = 'db_ref'>
 +
<tr><th>'''stops'''</th><th>'''route'''</th></tr>
 +
<tr><td>id</td><td>num</td></tr>
 +
<tr><td>name</td><td>company</td></tr>
 +
<tr><td></td><td>pos</td></tr>
 +
<tr><td></td><td>stop</td></tr>
 +
<tr><td></td><td></td></tr>
 +
</table>
 +
</div>
 +
 
<quiz shuffle=none display=simple>
 
<quiz shuffle=none display=simple>
 
{Select the code that would show it is possible to get from Craiglockhart to Haymarket
 
{Select the code that would show it is possible to get from Craiglockhart to Haymarket
 
|type="()"}
 
|type="()"}
- 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 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 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 ='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 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 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 the code that shows the stops that are on route.num '2A' which can be reached with one bus from Haymarket?
 
|type="()"}
 
|type="()"}
- 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 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 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='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

Revision as of 10:02, 30 September 2012

SELF JOIN quiz

stopsroute
idnum
namecompany
pos
stop

<quiz shuffle=none display=simple> {Select the code that would show it is possible to get from Craiglockhart to Haymarket |type="()"} - 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? |type="()"} - 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 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? |type="()"} - 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'

</quiz>