sqlzoo Self_join_Quiz 练习题

最新推荐文章于 2023-09-12 10:12:56 发布

原创最新推荐文章于 2023-09-12 10:12:56 发布 · 481 阅读

0 ·

CC 4.0 BY-SA版权

本文提供了SQLZoo网站上教程测验的解答代码，包括如何查询从Craiglockhart到Haymarket的路线，查找从Haymarket出发可以乘坐一次公交车到达的站点，以及展示从Tollcross出发的服务信息。

https://sqlzoo.net/wiki/Tutorial_Quizzes
https://sqlzoo.net/wiki/Self_join_Quiz

在这里插入图片描述

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 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 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 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)
 WHERE stopa.name='Tollcross'