SQLzoo 习题记录09-Self join & Quiz

最新推荐文章于 2024-01-06 21:51:23 发布

Moist_Grass

最新推荐文章于 2024-01-06 21:51:23 发布

阅读量739

点赞数

分类专栏： SQLzoo笔记/习题文章标签： sql

本文链接：https://blog.csdn.net/Moist_Grass/article/details/119191603

版权

SQLzoo笔记/习题专栏收录该内容

9 篇文章

订阅专栏

本文详细介绍了如何使用SQL进行数据查询，特别是自我连接的应用。从Edinburgh Buses数据库出发，展示了如何查询公交路线、停靠站，以及如何找到从一个站点到另一个站点的换乘服务。通过一系列实例，解释了如何找出特定路线上的停靠站，同一公司服务的连接站点，以及涉及两次换乘的旅行路径。此外，还提供了自我连接的练习题，帮助读者巩固自我连接的概念和实践。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Self Join

网址：Self join - SQLZOO

Edinburgh Buses

Details of the database Looking at the data

stops(id, name)
route(num, company, pos, stop)

stops
id
name

route
num
company
pos
stop

1.

How many stops are in the database.

SELECT COUNT(DISTINCT id) AS Num_stops
FROM stops

2.

Find the id value for the stop 'Craiglockhart'

SELECT id FROM stops
WHERE name = 'Craiglockhart'

3.

Give the id and the name for the stops on the '4' 'LRT' service.

SELECT id,name
FROM stops JOIN route ON id=stop
WHERE num='4' AND company='LRT'

Routes and stops

4.

The query shown gives the number of routes that visit either London Road (149) or Craiglockhart (53). Run the query and notice the two services that link these stops have a count of 2. Add a HAVING clause to restrict the output to these two routes.

P.S.这题是重复考察Having可以使用聚合函数，而Where不能。

SELECT company, num, COUNT(*)
FROM route WHERE stop=149 OR stop=53
GROUP BY company, num
HAVING COUNT(*)=2

5.

Execute the self join shown and observe that b.stop gives all the places you can get to from Craiglockhart, without changing routes. Change the query so that it shows the services from Craiglockhart to London Road.

题2中有Craiglockhart的站点号，也可以同London Road一样使用子查询。

SELECT a.company, a.num, a.stop, b.stop
FROM route a JOIN route b ON
(a.company=b.company AND a.num=b.num)
WHERE a.stop=53 And b.stop = (Select id From stops Where name Like 'London Road')

6.

The query shown is similar to the previous one, however by joining two copies of the stops table we can refer to stops by name rather than by number. Change the query so that the services between 'Craiglockhart' and 'London Road' are shown. If you are tired of these places try 'Fairmilehead' against '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='Craiglockhart' AND stopb.name='London Road'

Using a self join

7.

Give a list of all the services which connect stops 115 and 137 ('Haymarket' and 'Leith')

SELECT DISTINCT a.company, a.num
FROM route a JOIN route b ON
(a.company=b.company AND a.num=b.num)
WHERE a.stop=115 And b.stop = 137

8.

Give a list of the services which connect the stops 'Craiglockhart' and 'Tollcross'

SELECT DISTINCT a.company, a.num
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='Craiglockhart' AND stopb.name='Tollcross'

9.

Give a distinct list of the stops which may be reached from 'Craiglockhart' by taking one bus, including 'Craiglockhart' itself, offered by the LRT company. Include the company and bus no. of the relevant services.

SELECT DISTINCT stopb.name,a.company, a.num
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='Craiglockhart' AND a.company = 'LRT'

10.

Find the routes involving two buses that can go from Craiglockhart to Lochend.
Show the bus no. and company for the first bus, the name of the stop for the transfer,
and the bus no. and company for the second bus.

Hint

Self-join twice to find buses that visit Craiglockhart and Lochend, then join those on matching stops.

分别找到所有涉及起点和终点的路线号后，通过两条路线中重复的站点确认可换乘的站点。

关联站点名，方便直接通过站点名查找。

SELECT a.num,a.company,stopb.name,d.num,d.company
FROM
route a JOIN route b ON(a.num=b.num AND a.company=b.company)
JOIN route c ON(b.stop=c.stop)
JOIN route d ON(c.num=d.num AND c.company=d.company)
JOIN stops stopa ON(a.stop=stopa.id)
JOIN stops stopb ON(b.stop=stopb.id)
JOIN stops stopd ON(d.stop=stopd.id)
WHERE stopa.name='Craiglockhart' AND stopd.name='Lochend'

Self join Quiz

(Quiz好简单，感觉就是考验细心了）

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

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

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