Difference between revisions of "Self join Quiz"

Revision as of 11:02, 30 September 2012

SELF JOIN quiz

	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 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 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'

Your score is 0 / 0

@@ Line 1: / Line 1: @@
 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>
 {Select the code that would show it is possible to get from Craiglockhart to Haymarket