第 4关 SELECT within SELECT Tutorial - SQLZOO

练习select嵌套select，也就是子查询。

-- List each country name where the population is larger than that of 'Russia'.
-- 练习where子查询, 从子查询里筛选数值。

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

-- Show the countries in Europe with a per capita GDP greater than 'United Kingdom'.
-- 练习 where +and + 子查询 ， 从子查询里筛选数值。


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


-- List the name and continent of countries in the continents containing either Argentina or Australia. Order by name of the country.
-- 练习 where in +子查询，从子查询里筛选字符串。

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



-- Which country has a population that is more than Canada but less than Poland? Show the name and the population.
-- 练习 where子查询 and 子查询，筛选数值。 
select name,population
from world 
where population>
	(select population
	from world 
	where name= 'Canada')
and population<
	(select population
	from world 
	where name= 'Poland')


-- Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.
-- 练习 select + 子查询 

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

-- Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values) 
-- 练习 where + all(子查询)

-- 写法1 
select name 
from world 
where gdp >
	all(select gdp
	from world
	where continent = 'Europe'
	and gdp is not null)

-- 写法2 
select name 
from world 
where gdp >
	all(select gdp
	from world
	where continent = 'Europe'
	and gdp > 0)	
	
-- 写法3 
select name
from world
where gdp >
	(select max(gdp) 
	from world
	where continent = 'Europe')

-- Find the largest country (by area) in each continent, show the continent, the name and the area:
-- 练习关联子查询

-- 写法1 
select continent, name, area 
from world x 
where area >= all
	(select area from world y
	where y.continent = x.continent
	and area >0)
	
-- 写法2 

select continent, name, area
from world
where area in 
	(select max(area)
	from world
	group by continent)


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

-- 练习关联子查询，字符串比较，按字母顺序排名取出第一个国家。 

-- 写法1 
select continent,name 
from world x 
where name <= all
	(select name from world y
	where y.continent= x.continent)

-- 写法2 
select continent,name
from world
where name in 
	(select min(name)
	from world
	group by continent)
	
-- 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.
-- 双重嵌套，先写一个嵌套，筛选出洲，再写一个嵌套，筛选该洲的国家，洲名和人口。

-- 写法1 

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

-- 写法2 

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

-- Some countries have populations more than three times that of any of their neighbours (in the same continent). Give the countries and continents.
-- 如何表达3倍的人口和排除最大国家自身是关键


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