SQLZOO练习-- SELECT within SELECT Tutorial（含题目翻译）

知识点

子查询

数据表

world

题目内容

1.List each country name where the population is larger than that of 'Russia'.（查找人口数大于'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'.（查找人均GDP大于'United Kingdom'的国家，人均GDP=GDP/人口数）

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

3.List the name and continent of countries in the continents containing either Argentina or Australia. Order by name of the country.（查找和‘Argentina’或者‘Australia’在同一个洲的国家名称和洲名称，按照国家名称排序）

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.（查找人口数多于‘Canada’，但少于‘Poland’的国家名称和人口数）

ps：这道题不可以用between and，因为题目要求不包含上下限，而between and是包含上下限的

select name,population from world 
where population > (select population from world where name='Canada') 
and population <(select population from world where name='Poland') 
order by name

5.Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.（查找‘Europe’洲中的国家名称和人口百分比，人口百分比=人口数/‘Germany’国家人口数）

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).（查找GDP大于‘Europe’中每一个国家GDP的国家，需要注意有的国家GDP为0）

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

进阶练习

7.Find the largest country (by area) in each continent, show the continent, the name and the area.（查找每个大洲中面积最大的国家的国家名称、隶属洲名、面积）

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

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)

难度挑战

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 x
where 25000000 >= all(select population from world ywhere x.continent = y.continentand 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.（查找隶属同一个洲，且人口数是其他国家人口数3倍的国家名称、洲名称）

select name,continent from world x 
where x.population/3 >= all (select y.population from world ywhere x.continent = y.continentand x.name <> y.nameand population >0)

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！