SELECT within SELECT Tutorial

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

1.Bigger than Russia

列出人口大于俄罗斯的国家。

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

Correct answer

name
Bangladesh
Brazil
China
India
Indonesia
Nigeria
Pakistan
United States

2.Richer than UK

列出人均GDP大于美国的欧洲国家。

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

Correct answer

name
Andorra
Austria
Belgium
Denmark
Finland
France
Germany
Iceland
Ireland
Liechtenstein
Luxembourg
Monaco
Netherlands
Norway
San Marino
Sweden
Switzerland

3.Neighbours of Argentina and Australia

列出与阿根廷或澳大利亚所在大洲相同的国家，并以名称字母排序

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

Correct answer

name	continent
Argentina	South America
Australia	Oceania
Bolivia	South America
Brazil	South America
Chile	South America
Colombia	South America
Ecuador	South America
Fiji	Oceania
Guyana	South America
Kiribati	Oceania
Marshall Islands	Oceania
Micronesia, Federated States of	Oceania
Nauru	Oceania
New Zealand	Oceania
Palau	Oceania
Papua New Guinea	Oceania
Paraguay	South America
Peru	South America
Saint Vincent and the Grenadines	South America
Samoa	Oceania
Solomon Islands	Oceania
Suriname	South America
Tonga	Oceania
Tuvalu	Oceania
Uruguay	South America
Vanuatu	Oceania
Venezuela	South America

4.Between Canada and Poland

列出人口比加拿大多，比波兰少的国家。

SELECT name,population FROM world
  WHERE population >
     (SELECT population FROM world
      WHERE name='Canada') AND population  < (SELECT population FROM world
      WHERE name='Poland')

Correct answer

name	population
Iraq	36004552
Sudan	37289406

5.Percentages of Germany

列出欧洲国家名称，以及该国人口占德国人口的百分比。

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

Correct answer

name	percentage
Albania	3%
Andorra	0%
Austria	11%
Belarus	12%
Belgium	14%
Bosnia and Herzegovina	5%
Bulgaria	9%
Croatia	5%
Czech Republic	13%
Denmark	7%
Estonia	2%
Finland	7%
France	82%
Germany	100%
Greece	14%
Hungary	12%
Iceland	0%
Ireland	6%
Italy	75%
Kazakhstan	21%
Latvia	2%
Liechtenstein	0%
Lithuania	4%
Luxembourg	1%
Macedonia	3%
Malta	1%
Moldova	4%
Monaco	0%
Montenegro	1%
Netherlands	21%
Norway	6%
Poland	48%
Portugal	13%
Romania	25%
San Marino	0%
Serbia	9%
Slovakia	7%
Slovenia	3%
Spain	58%
Sweden	12%
Switzerland	10%
Ukraine	53%
United Kingdom	79%
Vatican City	0%

6.Bigger than every country in Europe

列出GDP大于欧洲所有国家的国家名称。

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

Correct answer

name
China
Japan
United States

7.Largest in each continent

列出每个大洲面积最大的国家，按大洲，国家名称，面积。

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

Correct answer

continent	name	area
Africa	Algeria	2381741
Oceania	Australia	7692024
South America	Brazil	8515767
North America	Canada	9984670
Asia	China	9596961
Caribbean	Cuba	109884
Europe	Kazakhstan	2724900
Eurasia	Russia	17125242

8.First country of each continent (alphabetically)

列出大洲，国家名称，只列各大洲的字母排序的第一个国家。

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

Correct answer

continent	name
Africa	Algeria
Asia	Afghanistan
Caribbean	Antigua and Barbuda
Eurasia	Armenia
Europe	Albania
North America	Belize
Oceania	Australia
South America	Argentina

9.Difficult Questions That Utilize Techniques Not Covered In Prior Sections

列出大洲总人口小于25000000的大洲上的国家名称，所在大洲，人口。

SELECT name, continent, population FROM world
WHERE continent IN
(SELECT continent FROM world a
WHERE population >= ALL(SELECT population FROM world b where b.continent = a.continent AND population > 0)
AND population <= 25000000)

Correct answer

name	continent	population
Antigua and Barbuda	Caribbean	86295
Australia	Oceania	23545500
Bahamas	Caribbean	351461
Barbados	Caribbean	285000
Cuba	Caribbean	11167325
Dominica	Caribbean	71293
Dominican Republic	Caribbean	9445281
Fiji	Oceania	858038
Grenada	Caribbean	103328
Haiti	Caribbean	10413211
Jamaica	Caribbean	2717991
Kiribati	Oceania	106461
Marshall Islands	Oceania	56086
Micronesia, Federated States of	Oceania	101351
Nauru	Oceania	9945
New Zealand	Oceania	4538520
Palau	Oceania	20901
Papua New Guinea	Oceania	7398500
Saint Lucia	Caribbean	180000
Samoa	Oceania	187820
Solomon Islands	Oceania	581344
Tonga	Oceania	103036
Trinidad and Tobago	Caribbean	1328019
Tuvalu	Oceania	11323
Vanuatu	Oceania	264652

10.

列出可以3倍于同一大洲任一国家人口的国家名称和大洲名称。

SELECT name, continent 
FROM world a
WHERE a.population/3  
	>=ALL(SELECT population FROM world b where b.continent = a.continent AND b.name<> a.name )

Correct answer

name	continent
Australia	Oceania
Brazil	South America
Russia	Eurasia