sqlzoo

**

sqlzoo

**

select from nobel

q14

The expression subject IN (‘Chemistry’,‘Physics’) can be used as a value - it will be 0 or 1.
Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.

SELECT winner, subject
  FROM nobel
 WHERE yr=1984
 ORDER BY case when subject IN ('Physics','Chemistry') then 1 else 0 end asc ,subject,winner

升序排列0 在1 前面，所以subject not in (‘Physics’,‘Chemistry’) 的排在subject IN (‘Physics’,‘Chemistry’)的前面

Using Null

q9:Use CASE to show the name of each teacher followed by ‘Sci’ if the teacher is in dept 1 or 2 and ‘Art’ otherwise.

select name,case when dept in (1,2) then 'Sci' else 'Art' end from teacher

self join

q5: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 in (select id from stops where name='Craiglockhart') and b.stop in (select id from stops where name='London Road')

接下来几道题答案都很相似，改动一点点就行
q6: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"

q7: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 

q8: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"

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

q10:
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.
思路：首先挑选出从Craiglockhart 出发 到达的站点，公交车号和对应公司，然后挑选出能到达Lochend的公交车号，对应公司，和出发的站点，最后将这两个表联合，使站点重合即能从Craiglockhart 到达Lochend的线路，join stops 是为了把中转站点的名字输出（不join就是输出stop的id了）
order by 纯属因为和给的正确答案顺序不一样，不过以后可以注意一下还是按某种顺序更好发现规律。

select distinct c.num,c.company,g.name,d.num,d.company from
(select a.num as num ,a.company as company ,b.stop as stop from route a join route b on (a.company=b.company and a.num=b.num) join stops on stops.id=a.stop where stops.name='Craiglockhart') c join
(select e.num as num ,e.company as company ,e.stop as stop from route e join route f on (e.company=f.company and e.num=f.num) join stops on stops.id=f.stop where stops.name='Lochend') d on c.stop=d.stop 
join stops g on g.id=c.stop order by c.num,g.name,d.num