SQLZOO 英文第四套（困难版） SELECT within SELECT

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(ROUND((population / 
(SELECT population 
FROM world
WHERE name = 'Germany'
)), 2)*100,0), '%')
FROM world
WHERE continent = 'Europe'

 

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

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
order by name)
order by 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.

select name, continent, population
from world x
where x.continent in (
select continent from world y
group by continent
having max(y.population) <= 25000000)

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 x
where name in (
select name from world y
where population > all(
select 3 * population from world z
where z.continent = y.continent and z.name != y.name)
)