SQLZOO:Self join

1.How many stops are in the database.

select count(name)
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 inner join route on id=stop
where company='lrt' and num=4

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.

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.

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

SELECT a.company, a.num, a.stop as 'from', b.stop as 'to'
FROM route a JOIN route b 
			 ON (a.company=b.company AND a.num=b.num)
WHERE a.stop=(select id 
				from stops 
				where name='Craiglockhart') 
and b.stop=(select id 
			from stops 
			where name='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’

SELECT a.company,a.num,c.name,d.name
FROM route a JOIN route b 
			 ON (a.company=b.company AND a.num=b.num) 
			 left join stops c 
			 on c.id=a.stop 
			 left join stops d 
			 on d.id=b.stop
where c.name='Craiglockhart' 
and d.name='London Road'

7.Give a list of all the services which connect stops 115 and 137 (‘Haymarket’ and ‘Leith’)

select a.company,a.num
from route a,route b
where a.company=b.company AND a.num=b.num
and a.stop=115 and b.stop=137
group by a.num

——因为有pos不同但其他属性相同的车次，用group by语句组合以剔除重复组

select distinct a.company,a.num
from route a,route b
where a.company=b.company AND a.num=b.num 
and a.stop=115 and b.stop=137

——用distinct语句剔除重复组

• 因为题前有Using a self join的链接，所以代码如上采用的是self join的形式进行解题。实际上直接用join语句代码会更简练,因为join语句的on条件限制了只有company和num相同的车次，能省去剔除因pos不同而重复的车次的步骤：

select 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 a.company,a.num
from route a,route b,stops c,stops d
where a.company=b.company 
AND a.num=b.num 
and c.id=a.stop 
and d.id=b.stop
and c.name='Craiglockhart' 
and d.name='Tollcross'

• 相较上题变成了四表联结，以体型练习依旧采用自联结语法写代码。恰好从’Craiglockhart’ 开往’Tollcross’的车次没有有不同pos的车次所以没有用distinct语句。但实际应用中为防止第7题的情况也应加上。

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 c.name,a.company,a.num
from route a,route b,stops c,stops d
where a.company=b.company 
	  AND a.num=b.num 
	  and c.id=a.stop 
	  and d.id=b.stop 
	  and d.name='Craiglockhart' 
	  and a.company='LRT'

• 核对过结果和答案是一致的，但是系统会显示是错误答案（似乎是因为顺序不同，但不清楚SQLZOO的顺序是怎么出来的）。
  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, trans1.name ,  c.num,  c.company
FROM route a JOIN route b ON (a.company = b.company AND a.num = b.num)
	JOIN ( route c JOIN route d ON (c.company = d.company AND c.num= d.num))
	JOIN stops start ON (a.stop = start.id)
	JOIN stops trans1 ON (b.stop = trans1.id)
	JOIN stops trans2 ON (c.stop = trans2.id)
	JOIN stops end ON (d.stop =  end.id)
WHERE start.name = 'Craiglockhart' 
 	  AND end.name = 'Lochend' 
	  AND  trans1.name = trans2.name 
ORDER BY a.num ASC,trans1.name

• 很绕，这个题。如果能把几个join换成creat view，我这个代码，将决杀。可是换不得。
• 代码想了半天还是没理出个头绪，写了几次发现都是不对的，上网找了别人的发现最后也是wrong anwser，但是和正确答案对比其实数据是一致的，只是顺序问题。