Thanks to M++.
高手无视。
http://www.sqlzoo.cn/1a.htm
bbc(name, region, area, population, gdp)
3a. 给出每个地区人口最大的国家：
- SELECT region,name,m_p from (select max(population) as m_p,name,region from bbc group by region) x
- SELECT region, name, population FROM bbc x
- WHERE population >= ALL
- (SELECT population FROM bbc y
- WHERE y.region=x.region)
3b. 给出地区中所有国家的人口总数为０的地区．
- select c.region from (select sum(population) total,region from bbc
- group by region) c where c.total = 0
- select distinct(region) from bbc x where 0>=ALL(SELECT sum(population) FROM bbc y where y.region=x.region)
3c. 有些国家的人口数比她的周边国家（周边国家指在同一地区的国家）要多三倍，列出这些国家和地区．
- select name,region from bbc x where population>=ALL(SELECT population*3 from bbc y where y.name<>x.name and y.region=x.region)
http://www.sqlzoo.cn/3a.htm
album(asin, title, artist, price, release, label, rank)
track(album, dsk, posn, song)
3a. 找出在两张以上专辑中出现过的歌曲．同时包含出现的次数．
- select song,count(*) as c from album join track on (asin=album) group by song having c>2
3b. A "good value" album （一张物超所值的专辑）就是专辑中每首歌曲的定价低于 50 pence（便士）的专辑．请找出这些专辑，并显示 title（专辑标题）, the price（专辑价格）和 the number of tracks（曲目总数）
- select title,price as `the price`, count(*) from album join track on (asin=album) group by asin having price*100/count(*)<50
3c. Wagner's Ring cycle 的专辑有 173 tracks（曲目）, Bing Crosby 的专辑包含101 tracks（曲目）．
请根据专辑中包含曲目的多少，从多到少依次列出这些专辑的名称和包含的总曲目数
- select title,count(song) as num from album join track on (asin=album) group by title order by num desc
http://www.sqlzoo.cn/3.htm
4b. 列出有'Julie Andrews'参演的所有电影名称以及该片的主角．
- select tmp.title,name from (select movie.id as movie_id,title from movie join casting on movie.id=casting.movieid join actor on actor.id=casting.actorid where name='Julie Andrews') tmp join casting on tmp.movie_id=casting.movieid join actor on actor.id=casting.actorid where casting.ord=1
4e. 列出所有和'Art Garfunkel'合作过的演员（不要列出'Art Garfunkel'他自己）.
- select actor.name from (select movieid from movie join casting on movie.id=casting.movieid join actor on actor.id=casting.actorid where name='Art Garfunkel') tmp join casting on tmp.movieid=casting.movieid join actor on casting.actorid=actor.id where name<>'Art Garfunkel'
鄙视啊鄙视，基础啊基础
这口气怎么那么熟悉……