SQL Zoo 4.SELECT within SELECT Tutorial

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 gdp/population > 
(select gdp/population from world where name = 'united kingdom') 
and continent = 'Europe'

 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 in 
(select continent from world where name in ('Argentina','Australia')) 
order by name

4.Which country has a population that is more than United Kingdom 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')

注：这里不能包含英国和德国，所以说不能使用between and

5.Germany (population 80 million) has the largest population of the countries in Europe. Austria (population 8.5 million) has 11% of the population of Germany.Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.（德国(人口8000万)是欧洲人口最多的国家。奥地利人口850万，占德国人口的11%。显示欧洲每个国家的名称和人口。显示人口占德国人口的百分比）

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

6.Which countries have a GDP greater than every country in Europe? (哪个国家的GDP比欧洲所有国家都高)

select name from world where gdp > (
select max(gdp) from world where continent = 'Europe')

解：先求出欧洲gdp的最大值，再去求gdp比欧洲最大值大的

7.Find the largest country (by area) in each continent, show the continent, the name and the area.(查找每个大陆中最大的国家(按地区)，显示大陆、名称和地区)

select continent,name,area from (
select *,rank()over(partition by continent order by area desc) posn from world) as rk 
where rk.posn = 1 order by name

8.List each continent and the name of the country that comes first alphabetically.(列出每个大洲和国家的名字第一个字母顺序排列)

select continent,name from (
select *,rank()over(partition by continent order by name)
 as rk from world) as t where t.rk=1

9.Find the continents where all countries have a population <= 25000000. Then find the names of the countries associated with these continents. (找出所有国家人口<= 25000000的大陆。然后找出与这些大陆相关的国家的名字)

select name,continent,population from world where continent not in (
select distinct continent from world where population>25000000)

10.Some countries have populations more than three times that of all of their neighbours (in the same continent). (一些国家的人口是其所有邻国人口的三倍以上(在同一大陆))

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