sqlzoo答案4：SELECT within SELECT Tutorial

sql练习：SELECT within SELECT Tutorial - SQLZoo

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

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

1. select...where...(...select...)

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

SELECT name FROM worldWHERE population >(SELECT population FROM worldWHERE 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

Europe是在continent里面筛选，不是area

select name 
from world 
where continent = 'Europe' 
and gdp/population >
(select gdp/population
from world
where name= 'United Kingdom')

3. in 、order by

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

列出包含阿根廷或澳大利亚的大陆中的国家名称和所属大陆。按国家名称排序。

错误代码：理解错误，Argentina or Australia是国家名

select name, continent
from world
where continent in ('Argentina' , 'Australia')
order by name

正确代码：

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 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')

5. concat...as、round

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:

name	percentage
Albania	3%
Andorra	0%
Austria	11%
...	...

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.

select name, concat(round(population/
(
select population from world
where name = 'Germany'
)*100,0),'%') as percentage
from world 
where continent = 'Europe'

---

To get a well rounded view of the important features of SQL you should move on to the next tutorial concerning aggregates.

To gain an absurdly detailed view of one insignificant feature of the language, read on.

We can use the word ALL to allow >= or > or < or <=to act over a list. For example, you can find the largest country in the world, by population with this query:

我们可以使用单词 ALL 来允许 >= 或 > 或 < 或 <= 在列表上操作。例如，你可以通过这个查询找到世界上人口最多的国家：

SELECT nameFROM worldWHERE population >= ALL(SELECT populationFROM worldWHERE population>0)

You need the condition population>0 in the sub-query as some countries have null for population.

---

6. all

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

哪些国家的GDP高于欧洲所有国家？【仅提供名称】（某些国家可能没有GDP数值）

select name 
from world
where gdp >
all(
select gdp from world
where continent = 'Europe' and gdp > 0 )

name
China
Japan
United States

7.对比同一个洲内 找最大area

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

The above example is known as a correlated or synchronized sub-query.

找出每个洲面积最大的国家，显示洲名、国家名称和面积： 上述示例被称为相关或同步子查询。

Using correlated subqueries？

A correlated subquery works like a nested loop: the subquery only has access to rows related to a single record at a time in the outer query. The technique relies on table aliases to identify two different uses of the same table, one in the outer query and the other in the subquery.

One way to interpret the line in the WHERE clause that references the two table is “… where the correlated values are the same”.

In the example provided, you would say “select the country details from world where the population is greater than or equal to the population of all countries where the continent is the same”.

使用相关子查询？

相关子查询的工作方式类似于嵌套循环：子查询仅能访问外部查询中当前记录相关的行。这种技术依赖表别名来标识同一张表的两种不同用途，一个在外部查询中，另一个在子查询中。 可以将 WHERE 子句中引用两个表的那一行解释为“…其中相关值相同”。 在提供的示例中，你会说“从 world 表中选择国家详情，其中人口大于或等于所有同一大陆的国家的人口”。

SELECT continent, name, area FROM world xWHERE area>= ALL(SELECT area FROM world yWHERE y.continent=x.continentAND population>0)

8.min(name) 按字母顺序排列第一个

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

列出每个大洲及按字母顺序排列第一个国家的名称。

select continent, name from world x
where name=(select min(name) from world ywhere x.continent = y.continent)

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.

查找所有国家人口均不超过25000000的洲。然后找出与这些洲相关的国家名称。显示名称、洲和人口。

select name,continent, population
from world a
where 25000000 > all(select population from world bwhere a.continent = b.continent)

10.

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 a
where (population)/3>all(select population from world bwhere a.continent = b.continentand a.name<> b.name)