SELECT within SELECT Tutorial

数据表：world

name	continent	area	population	gdp
Afghanistan	Asia	652230	25500100	20343000000
Albania	Europe	28748	2831741	12960000000
Algeria	Africa	2381741	37100000	188681000000
Andorra	Europe	468	78115	3712000000
Angola	Africa	1246700	20609294	100990000000
...

Q1 Bigger than Russia

List each country name where the population is larger than that of 'Russia'.

SELECT name FROM world 
WHERE population > 
    (SELECT population FROM world 
    WHERE name='Russia')

Q2 Richer than UK

Show the countries in Europe with a per capita GDP greater than 'United Kingdom'.

SELECT name FROM world 
WHERE continent='Europe' 
AND gdp/population >
    (SELECT gdp/population FROM world 
    WHERE name='United Kingdom')

Q3 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.

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

Q4 Between Canada and Poland

Which country has a population that is more than United Kingdom but less than Germany? Show the name and the population.

SELECT name,population FROM world WHERE 
population>(SELECT population FROM world WHERE name='United Kingdom')
AND
population<(SELECT population FROM world WHERE name='Germany')

Q5 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.

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

CONCAT( )

CONCAT(str1,str2,…) 连接多个字符串

mysql > SELECT CONCAT('张三','李四','王五');

result > 张三李四王五

如有任何一个参数为NULL ，则返回值为 NULL

mysql> SELECT CONCAT('张三','李四',NULL);

result> NUll
 

CONCAT_WS( )

CONCAT_WS(separator,str1,str2,...) 
将多个字符串连接成一个字符串，可以一次性指定分隔符，默认分隔符为逗号

mysql> SELECT CONCAT_WS(',','张三','李四','王五');
result> 张三,李四,王五

CONCAT_WS不会因为NULL值而返回NULL 

mysql> SELECT CONCAT_WS(',','张三',NULL,'王五');
result> 张三,王五

Q6 Bigger than every country in Europe

Which countries have a GDP greater than every country in Europe? [Give the name only]

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

Q7 Largest in each continent

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)

我们可以在内部 SELECT 中引用外部 SELECT 中的值，可以命名这些表以区分内部和外部

同步子查询

相关子查询：子查询一次只能访问外部查询中与单个记录相关的行。该技术依赖于表别名来识别同一表的两种不同用法，一种在外部查询中，另一种在子查询中。

解释引用两个表的 WHERE 子句中的行的一种方法是“......其中相关值相同“。

Q8 First country of each continent (alphabetically)

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

SELECT continent,name FROM world x 
WHERE x.name =
    (SELECT y.name FROM world y 
    WHERE y.continent=x.continent 
    ORDER BY name 
    LIMIT 1)

Q9 Difficult Questions That Utilize Techniques Not Covered In Prior Sections

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)

Q10 Three time bigger

Some countries have populations more than three times that of all of their neighbours (in the same continent). Give the countries and continents.

SELECT name,continent FROM world x 
WHERE x.population/3 > 
    ALL(SELECT population FROM world y WHERE x.continent=y.continent AND x.name<>y.name)