Self_join -SQLZOO - 练习答案

Self_join -SQLZOO

题目源自 ： https://sqlzoo.net/wiki/Self_join

4

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

5

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 = 149

6

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' 

7

select a.company,a.num
  from route a join route b on (a.num = b.num and a.company = b.company)
 where a.stop = 115 and b.stop = 137
 group by a.num,a.company

8

SELECT 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

SELECT 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

SELECT a.num,a.company,stopb.name,c.num,c.company
FROM route a JOIN route b ON
  (a.company=b.company AND a.num=b.num)
  JOIN route c ON (b.stop=c.stop)
  JOIN route d ON (c.company=d.company AND c.num=d.num)
  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'