sql在线练习网站（http://sqlzoo.cn）答案解析（4）

七：The OUTER JOIN: （http://sqlzoo.cn/4.htm）
    1a. 一个议员被开除出党，看看他是谁
        SELECT Name FROM msp WHERE Party IS NULL
    1b. 列出所有政党和领导者
        SELECT Name, Leader FROM party
    1c. 列出所有有领导者的政党
        SELECT Name,Leader FROM party WHERE Leader IS NOT NULL
    1d. 列出至少有一个议员的政党
        SELECT party.Name FROM party JOIN msp ON (msp.Party = party.Code)
      GROUP BY msp.Party HAVING COUNT(msp.Name)>0
    2a. 列出所有议员的名字和他所属政党的名称（如果有的话）
        SELECT msp.Name,party.Name From msp LEFT JOIN party ON (msp.Party = party.Code)
    2b. 列出包含议员的政党，包含每个政党的议员人数
        SELECT party.Name, COUNT(msp.Name) From party LEFT JOIN msp ON (msp.Party = party.Code)
      GROUP BY party.Name HAVING COUNT(msp.Name)>0
    or:  SELECT party.name, COUNT(msp.name)   FROM msp, party 
        WHERE msp.party=party.code GROUP BY party.name
    2c. 列出所有政党和每个政党议员人数．包含那些没有议员的政党
        SELECT party.Name, COUNT(msp.Name) From party 
      LEFT JOIN msp ON (msp.Party = party.Code) GROUP BY party.Name

八：SELF JOIN: （http://sqlzoo.cn/6.htm）
    1a. 显示数据库中包含多少车站
        SELECT COUNT(name) FROM stops
    1b. 找出站名为'Craiglockhart'的id 号
        SELECT id FROM stops WHERE name = 'Craiglockhart'
    1c. 给出 'LRT'公司经营的'4'号线路的所有车站的id(站号)和name(站名）
        SELECT id,name From stops JOIN route ON(id = stop) WHERE company = 'LRT' AND num = '4'
    2a. 请看一下已经给出的查询，它将列出所有公交线路经过车站London Road (149)或Craiglockhart (53)的次数．　修改一下该查询，使得该查询仅列出两次经过这些车站的两条线路
        SELECT company, num, COUNT(*) FROM route WHERE stop=149 OR stop=53 
      GROUP BY company, num HAVING COUNT(*) = 2
    2b. 给出从Craiglockhart(stop=53)到London Road(stop=149)的线路
        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 b.stop=53 AND a.stop=149
    2c. 下面的查询和上例中的比较类似．只是通过两次连接stops表，让我们可以通过站名而不是站号来查询公交线路．
请调整一下该查询，让它可以显示'Craiglockhart'和'London Road'间的公交线路
        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(stopa.id=a.stop)
      JOIN stops stopb ON(stopb.id=b.stop)
      WHERE stopa.name='Craiglockhart' AND stopb.name='London Road'
    3a. 给出连接车站号为115和137公交线路
        SELECT ra.company, ra.num FROM route ra, route rb
  WHERE ra.num=rb.num AND ra.company=rb.company AND ra.stop=115 AND rb.stop=137
    or:  SELECT ra.company,ra.num FROM route ra 
      JOIN route rb ON(ra.company=rb.company AND ra.num=rb.num) WHERE ra.stop=115 AND rb.stop=137
    3b. 给出连接车站 'Craiglockhart' and 'Tollcross'的公交线路
        SELECT ra.company, ra.num FROM route ra 
      JOIN route rb ON(ra.company=rb.company AND ra.num=rb.num)
      JOIN stops sa ON(sa.id=ra.stop) JOIN stops sb ON(sb.id=rb.stop)
      WHERE sa.name='Craiglockhart' AND sb.name='Tollcross'
    3c. 给出可以从'Craiglockhart' 车站乘坐一辆汽车就能到达的车站．显示的结果包含到达车站号，车站名，公交线路的公司和公交线路号
        SELECT sa.id,sa.name,ra.company,ra.num FROM route ra 
      JOIN route rb ON(ra.company=rb.company AND ra.num=rb.num)
      JOIN stops sa ON(sa.id=ra.stop) JOIN stops sb ON(sb.id=rb.stop)
      WHERE sb.name = 'Craiglockhart'
    or:  SELECT sa.id, sa.name, ra.company, ra.num FROM stops sa, stops sb, route ra, route rb
      WHERE sb.name='Craiglockhart' AND sa.id=ra.stop AND sb.id=rb.stop AND ra.company=rb.company AND ra.num=rb.num
    3d. 给出从Sighthill 到 Craiglockhart的所有可能的交通线路
        此题暂未解决。。。希望高人指点

sql在线练习网站（http://sqlzoo.cn）答案解析（1）
sql在线练习网站（http://sqlzoo.cn）答案解析（2）
sql在线练习网站（http://sqlzoo.cn）答案解析（3）