本系列刷题笔记主要用以记录刷SQLZOO的过程中的思路、个人答案以及陌生的或者新的知识点。
题目来源 - SQLZOO
SQLZOO中题目中文版本与英文版本略有差异，题目以英文版为准
目录
4 SELECT within SELECT Tutorial
5 SUM and COUNT
4 SELECT within SELECT Tutorial
查询表格
查询表格_world
4_1 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')
4_2 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')
4_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_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')
4_5 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'
4_6 Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values)
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:
example
SELECT name
FROM world
WHERE population >= ALL(SELECT population
FROM world
WHERE population>0)
You need the condition population>0 in the sub-query as some countries have null for population.
SELECT name
FROM world
WHERE gdp > ALL (SELECT gdp FROM world WHERE gdp > 0 AND continent = 'Europe')
4_7 Find the largest country (by area) in each continent, show the continent, the name and the area.
使用窗口函数rank，首先计算出各国家在各自洲的rank，然后再取rank为1的国家亦可实现。
SELECT continent,
name,
area
FROM world x
WHERE area >= ALL (SELECT area
FROM world y
WHERE y.continent = x.continent
AND area > 0)
4_8 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)
4_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 population > 0)
4_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 x.population / 3 >= ALL (SELECT population
FROM world y
WHERE x.continent = y.continent
AND x.name <> y.name
AND y.population > 0)
5 SUM and COUNT
查询表格（同world）
5_1 Show the total population of the world.
SELECT SUM(population)
FROM world
5_2 List all the continents - just once each.
SELECT DISTINCT continent
FROM world
5_3 Give the total GDP of Africa
SELECT SUM(gdp)
FROM world
WHERE continent = 'Africa'
5_4 How many countries have an area of at least 1000000
SELECT COUNT(DISTINCT name)
FROM world
WHERE area >= 1000000
5_5 What is the total population of ('Estonia', 'Latvia', 'Lithuania')
SELECT SUM(population)
FROM world
WHERE name IN ('Estonia','Latvia','Lithuania')
5_6 For each continent show the continent and number of countries.
SELECT continent,
COUNT(DISTINCT name)
FROM world
GROUP BY continent
5_7 For each continent show the continent and number of countries with populations of at least 10 million.
SELECT continent,
COUNT(name)
FROM world
WHERE population >= 10000000
GROUP BY continent
5_8 List the continents that have a total population of at least 100 million.
SELECT continent
FROM world
GROUP BY continent
HAVING SUM(population) >= 100000000
