SQLzoo 4 SELECT within SELECT

4.7 Find the largest country (by area) in each continent, show the continent, the name and the area:

查询每个洲面积最大的国家，列出洲名，国名和面积

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

 

 

 

 涉及关联子查询概念参考如何正确理解SQL关联子查询 - 何大卫 - 博客园 (cnblogs.com)

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

列出每个洲按首字母顺序排第一的国家名

select continent,name from world a
where name <= all(select name from world b where b.continent = a.continent)
order by continent

 

 

4.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.

找出所有国家的人口都小于2500亿的洲。然后找出与这些洲相关的国家的名字。显示姓名，大陆和人口。

select name,continent,population from world a
where  25000000 >= all(select population from world b where b.continent = a.continent) 

 

 

 4.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 a
where  population > all(select population*3 from world b where b.name!= a.name and b.continent = a.continent)

 

 

 关于相关子查询的个人理解：

以4.10为例，先把整表作为表a，取一条数据【select name,continent from world a】，判定条件是a中population的值大于某个条件【where population >】；

该条件为：将整表作为表b，取所有【all】洲名与表a所取数据洲名相同【b.continent = a.continent】且不同国名【b.name!= a.name】的人口的三倍【population*3】