Difference between revisions of "Self join Quiz"

From SQLZOO
Jump to navigation Jump to search
Line 3: Line 3:
{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="()"}
-A. 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'
-B. 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'
+C. 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'
-D. 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'
-E. 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="()"}
-A. 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'
-B. 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'
-C. 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
-D. 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='2'
+E. 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='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 the code that shows the services available from Tollcross?
|type="()"}
|type="()"}
-A. 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)
-B. 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 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'
-C. 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 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'
+D. 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 stopa.name='Tollcross'
-E. 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'
- 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>
</quiz>


[[Category:Quizzes]]
[[Category:Quizzes]]

Revision as of 14:44, 7 August 2012

SELF JOIN quiz <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>