154、MySQL入门（四）：子查询

Sqlzoo习题练习：SELECT in SELECT

习题链接：<u>http://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial</u>

下面为SELECT in SELECT习题内容：

--#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'.
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.
*/
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');

知识点：
1）ROUND() 函数
ROUND 函数用于把数值字段舍入为指定的小数位数。
SQL ROUND() 语法
SELECT ROUND(column_name,decimals) FROM table_name
2）CONCAT（）函数
CONCAT（）函数用于将多个字符串连接成一个字符串
CONCAT（）语法：CONCAT(str1,str2,…)
返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。

--#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.
Decimal places
You can use the function ROUND to remove the decimal places.
Percent symbol %
*/
SELECT name,CONCAT(ROUND((population/(SELECT population FROM world WHERE name = 'Germany')*100),0),'%')
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) 
*/
SELECT name FROM world
WHERE gdp > (
  SELECT MAX(gdp)
  FROM world
  WHERE continent = 'Europe');

知识点：
下面例子中使用相关子查询方法。相关的子查询像嵌套循环一样工作：子查询只能访问外部查询中与单个记录相关的行。
WHERE子句中的x y是引用两个表的行的一种方法：“相关值相同的地方”。

--#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);

知识点：
LIMIT 1：查询前面1行的数据，在下面的例子中表示每一个洲的国家按字母排好序后，只查询最前面第一个数据。

--#8
/*
List each continent and the name of the country that comes first alphabetically.
*/
SELECT continent,name FROM world x
where name = (select name from world y where x.continent = y.continent order by name LIMIT 1);

--#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 25000000 > ALL(SELECT population FROM world y WHERE x.continent = y.continent AND y.population > 0);

--#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 population*3 FROM world y WHERE x.continent = y.continent AND y.population > 0 AND x.name != y.name);