SQLZOO刷题记录4——SELECT within SELECT Tutorial

本章节主要训练SELECT语句的嵌套使用。

题目3：Neighbours of Argentina and Australia

List the name and continent of countries in the continents containing either Argentina or Australia. Order by name of the country.

解题3：

SELECT name, continent 
FROM world 
WHERE continent IN 
  (SELECT continent 
  FROM world 
  WHERE name IN ('Argentina','Australia')) 
ORDER BY name 

题目5：Percentages of Germany

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.

The format should be Name, Percentage for example:

Decimal places

You can use the function ROUND to remove the decimal places.

Percent symbol %

You can use the function CONCAT to add the percentage symbol.

解题5：

SELECT name, 
  CONCAT(ROUND(population*100/(
  SELECT population 
  FROM world 
  WHERE name = 'Germany'),0), '%') 
  AS percentage 
FROM world 
WHERE continent = 'Europe' 

注意：（1）本题查询的结果是欧洲地区的国家，但是计算百分比的却以德国为准；

（2）注意CONCAT函数、ROUND函数的嵌套顺序；CONCAT函数主要是拼接%，要知道给数字后面加入%后，数字部分的值应该扩大100倍，即*100；另外ROUND函数参数取值为0时，表示删除小数点部分，以符合题目要求。

（3）SQL子句中，查询德国的人口数，是因为结果集中每条查询记录的人口数都需要与之比较。

题目6：Bigger than every country in Europe

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

(哪些国家的GDP比欧洲所有国家都高?只给出名字。(有些国家的gdp值可能为零))

解题6：

SELECT name 
FROM world 
WHERE gdp > 
  ALL(SELECT gdp 
    FROM world 
    WHERE gdp > 0 
    AND continent = 'Europe')

或者

SELECT name 
FROM world 
WHERE gdp > 
   (SELECT MAX(gdp) 
    FROM world 
    WHERE gdp > 0 
    AND continent = 'Europe')

注意：解题思路有两种，一种是用函数ALL，表示所求项大于（或小于）所有某一列的值。另一种是求出某一列的最大值（或最小值）。

题目7：Largest in each continent

Find the largest country (by area) in each continent, show the continent, the name and the area:

(在每个大洲上找到最大的国家(按面积)，显示大洲、名称和面积:)

解题7：

SELECT continent, name, area FROM world x
  WHERE area >= ALL
    (SELECT area FROM world y
        WHERE y.continent=x.continent
          AND area>0) 

注意：嵌套select子句中的where语句 y.continent = x.continent 表示在相同的continent（大洲）中查找，相当于分组函数group by。

题目8：First country of each continent (alphabetically)

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

(按字母顺序列出每个大洲和国家的名字。注意本题目的结果集是每一个大洲对应一个国家名称)

解题8：

SELECT continent, MIN(name) 
FROM world 
GROUP BY continent 

注意：本题巧妙地运用了MIN函数，应用MIN函数对字符串进行排序，其实就是按照字母顺序排列。应用GROUP BY 语句后，把记录按照大洲来分组。

题目9：Difficult Questions

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.

(找出所有国家人口<= 2500万的大洲。然后找出与这些大洲相关的国家的名字。显示姓名，大洲和人口。)

解题9：

SELECT name, continent, population 
FROM world w1
WHERE 25000000 >= ALL(
 SELECT population 
 FROM world w2 
 WHERE w1.continent = w2.continent)

注意：本题的思路是对于满足人口的国家，直接查询其相关信息。巧妙地使用了ALL函数，题目中要求国家人口<=2500万，本题改写成 2500万 >= ALL()。子句中的where语句是用大洲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.

（一些国家的人口是其任何邻国(在同一大洲)的三倍以上。查询符合条件的国家和大洲。）

解题10：

SELECT name, continent 
FROM world w1 
WHERE population >= ALL(
 SELECT population*3 
 FROM world w2 
 WHERE w1.continent = w2.continent 
 AND w1.name <> w2.name
)

注意：本题中查询人口数大于其他邻国（同一个大洲）的三倍以上，用population*3表示。子句中where语句的条件1是利用continent进行表联结，条件2是排除掉人口数多（查询项）本身。