sqlzoo习题答案-Select within Select Tutorial

This tutorial looks at how we can use SELECT statements within SELECT statements to perform more complex queries.

name	continent	area	population	gdp
Afghanistan	Asia	652230	25500100	20343000000
Albania	Europe	28748	2831741	12960000000
Algeria	Africa	2381741	37100000	188681000000
Andorra	Europe	468	78115	3712000000
Angola	Africa	1246700	20609294	100990000000
...

8.List each continent and the name of the country that comes first alphabetically.

*******方法一*******

select continent,min(name)
   from world
   group by continent
   order by continent

*******方法二*******

select continent,name from world
group by continent）

(1)为什么group by可以这么用？

*******方法三*******

select continent,name from world x where x.name=(select y.name from world y where y.continent=x.continent order by name limit 1)

continent	name
Africa	Algeria
Asia	Afghanistan
Caribbean	Antigua and Barbuda
Eurasia	Armenia
Europe	Albania
North America	Belize
Oceania	Australia
South America	Argentina

解析：给同一个表命名两个别名x和y，今天查了一天资料，都没有查到对应的解释，于是自己在纸上写代码，测试会呈现的结果，并且sqlzoo运行的结果也跟自己预设的一致。

(1)为什么用where y.continent=x.contient,如果不用

select continent,name from world x where x.name=(select y.name from world y order by name limit 1)

执行结果是：

continent	name
Asia	Afghanistan

(2)=如果换成in,执行错误

9.Find the continents where all countries have a population <= 25000000. Then find the names of the countries associated with these continents. Show name, continent and population.

*******方法一*******

select name,continent,population from world
where continent in(select continent from world
group by continent
having max(population)<=25000000)

*******方法二*******

SELECT name, continent, population FROM world x  
WHERE 25000000>=ALL (SELECT population FROM world y 
WHERE x.continent=y.continent  
AND population>0) 

10.Some countries have populations more than three times that of any of their neighbours (in the same continent). Give the countries and continents.

select name,continent from world x
where x.population/3> all(select population from world y
where x.continent=y.continent and x.name!=y.name and y.population>0)