sqlzoo刷题笔记-06 | SELECT within SELECT Tutorial

网址：https://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial

1、List each country name where the population is larger than that of 'Russia'.         列出人口大于"俄罗斯"的每个国家名称。

world(name, continent, area, population, gdp)

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 大于'United Kingdom'的国家。

Per Capita GDP = The per capita GDP is the gdp/population

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.

列出包含Argentina或Australia各大洲的国家的名称和大陆。按国家名称排列。

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 Canada but less than Poland? Show the name and the population.

哪个国家的人口比加拿大多，但比波兰少？显示名称和人口。

SELECT name,population
FROM world
WHERE population > (SELECT population FROM world WHERE name ='Canada')
  AND population < (SELECT population FROM world WHERE name ='Poland')

解析：注意不能用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.

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

SELECT name, CONCAT(ROUND(100*population/(SELECT population FROM world WHERE name='Germany')),'%')
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高于欧洲的每个国家？仅给出名称。（某些国家可能有 NULL gdp 值）

SELECT name
FROM world
WHERE gdp > ALL(SELECT gdp
                FROM world
                WHERE continent ='Europe' AND gdp IS NOT NULL)

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 y.continent=x.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 x.name <= ALL(SELECT name FROM world y WHERE y.continent = x.continent)
ORDER BY continent, name

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.

查找所有国家的人口 <= 250000000 的大洲，然后找到与这些大洲相关的国家的名称。

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 any of their neighbours (in the same continent). Give the countries and continents.

某些国家的人口是其同大洲的其他国家的三倍以上，列出满足条件的国家和大洲。

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)