SQL第 4关 SELECT within SELECT Tut

第 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)