MySQL练习9

来源于zoo sql，联系表自连接操作

表结构图如下：

stops表，id为站点序号，name为站点名字

route为线路表：num为线路序号，表示公交车顶上的线路，如13路公交车，num就为13，company为运作该线路的公司名字，pos为顺序，stop为站点编号，关联stops表的id字段

重点看route表，部分数据如下：

1路公交车，第1个站点为137，第2个站点为99，依次类推

练习题目：

1. How many stops are in the database.
    select count(*) 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 stops.id, stops.name from stops, route where num = '4' and company = 'LRT' and stops.id = route.stop;
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) and a.stop = 53 and b.stop = 149 
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'
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, route b where a.num = b.num and a.company = b.company and 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, route b, stops c, stops d where a.num = b.num and a.company = b.company and a.stop = c.id and b.stop = d.id and c.name = 'Craiglockhart' and d.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 c.name, b.company, a.num from route a, route b, stops c,stops d where a.num = b.num and a.company = b.company and b.stop = c.id and a.stop = 53 and a.company = 'LRT' and d.id = a.stop and d.name = 'Craiglockhart';

10. Find the routes involving two buses that can go from Craiglockhart to Sighthill.
    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.
    -- 从53站可以通过哪些线路做到哪些站
    select a.num, a.company, b.stop from route a, route b where a.stop = 53 and a.num = b.num and a.company = b.company
    -- 从哪些站可以坐哪些线路到213站
    select c.num, c.company, c.stop from route c, route d where d.stop = 213 and c.num = d.num and c.company = d.company
    -- 以上两张表联立，只需要stop一样
   select distinct e.num, e.company, g.name,  f.num, f.company from
   (select a.num as num, a.company as company, b.stop as stop from route a, route b where a.stop = 53 and a.num = b.num and a.company = b.company) e, (select c.num as num, c.company as company, c.stop as stop from route c, route d where d.stop = 213 and c.num = d.num and c.company = d.company) f, stops g where e.stop = f.stop and e.stop = g.id 

创建对应的本地数据库脚本下载地址：https://download.youkuaiyun.com/download/hielw/10387763