sqlzoo-Nested SELECT Quiz记录

Nested SELECT Quiz

表格：

1. Select the code that shows the name, region and population of the smallest country in each region

-- 查找每个地区最小的国家（按人口排序），显示其名字、所属地区和人口
 SELECT region, name, population 
 FROM bbc x 
 WHERE population <= ALL (SELECT population FROM bbc y WHERE y.region=x.region AND population>0)

2. Select the code that shows the countries belonging to regions with all populations over 50000

-- 找出某地区所有人数大于50000的地区，显示其中国家
SELECT name,region,population 
FROM bbc x 
WHERE 50000 < ALL (SELECT population FROM bbc y WHERE x.region=y.region AND y.population>0)

3. Select the code that shows the countries with a less than a third of the population of the countries around it

-- 找出人口数小于其邻国（同一地区）三分之一人口的国家
SELECT name, region 
FROM bbc x
WHERE population < ALL (SELECT population/3 FROM bbc y WHERE y.region = x.region AND y.name != x.name)

4. Select the result that would be obtained from the following code:

-- 代码运行题
-- 查找与英国在同一区域且人口数大于英国的国家
SELECT name 
FROM bbc
WHERE population >
       (SELECT population
          FROM bbc
         WHERE name='United Kingdom')
   AND region IN
       (SELECT region
          FROM bbc
         WHERE name = 'United Kingdom')

结果：

5. Select the code that would show the countries with a greater GDP than any country in Africa (some countries may have NULL gdp values).

-- 找出gdp大于所有亚洲国家的国家
SELECT name 
FROM bbc
WHERE gdp > (SELECT MAX(gdp) FROM bbc WHERE region = 'Africa')

-- 易错：
SELECT name 
FROM bbc
WHERE gdp > ALL (SELECT gdp FROM bbc WHERE region = 'Africa' AND gdp<>NULL)
-- 本答案错在gdp<>NULL,null不能用于数值比较，应改为gdp is not null

6. Select the code that shows the countries with population smaller than Russia but bigger than Denmark

-- 找出人口数少于俄罗斯，大于丹麦的国家
SELECT name 
FROM bbc
WHERE population < (SELECT population FROM bbc WHERE name='Russia')
   AND population > (SELECT population FROM bbc WHERE name='Denmark')

7.Select the result that would be obtained from the following code:

-- 代码运行结果题：
-- 找出南亚区域内人口数大于所有欧洲国家人口数的国家
SELECT name 
FROM bbc
WHERE population > ALL
       (SELECT MAX(population)
          FROM bbc
         WHERE region = 'Europe')
   AND region = 'South Asia'

结果：