SQLZOO 4 select within select

4 select within select

1.List each country name where the population is larger than that of ‘Russia’.

列出每个人口比“俄罗斯”人口多的国家的名字。

select name from world
where population >
(select population from world where name ='Russia') 

2.Show the countries in Europe with a per capita GDP greater than ‘United Kingdom’.

显示人均gdp大于联合王国的欧洲国家

select  name from world where 
continent ='Europe' and gdp/population >
(select gdp from world where name='United Kingdom') 
/(select population from world where name ='United Kingdom')

3.List the name and continent of countries in the continents containing either Argentina or Australia. Order by name of the country.

列出包含阿根廷或澳大利亚的大洲中国家的名称和大洲。按国家名称排序。

select name,continent from world where continent=
(select continent from world where name = 'Argentina ') or
continent = (select continent from world where name = 'Australia')
order by name

4.Which country has a population that is more than United Kingom but less than Germany? Show the name and the population.

哪个国家人口比联合王国多，但是比德国少？显示国家名字和人口

select name,population from world where
population > (select population from world where name = 'United Kingdom')
and
population < (select population from world where name = 'Germany')

5.Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.

显示欧洲每个国家的名称和人口。显示人口占德国人口的百分比。

select name,
concat(round(population/(select population from world where name='Germany')*100,0),'%')
as percentage from world where continent = 'Europe

6.Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values)

哪些国家的GDP超过欧洲所有国家?只给出名字。(有些国家的gdp值可能为零)

select name from world where gdp > 
all(select gdp from world where continent= 'Europe' and gdp> 0)

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 x.continent = y.continent and area > 0)

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

按字母顺序列出每个大洲和国家的名字。

select continent,name from world x 
where name <= 
all(select name from world y where x.continent = y.continent)

名字为最小的国家就是该国家所在大陆所该显示的国家

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.

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

select name,continent,population from world x
 where 25000000 >= all(select population from world y where x.continent = y.continent)

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

有些国家的人口是其邻国(在同一大陆)人口的三倍多。给出国家和大洲。

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

为什么要除掉它本身呢？因为它自己的三分之一人口肯定是小于它自身人口的，不排它本身的人口的话不可能有国家符合比同大陆国家人口都大三倍这个条件的



总结：第四部分中比价难的是all的使用，all是将all里面子查询每一次的查询结果都拿出来与外部的条件对比，全部为真最终结果才为真。