SqlZoo.net习题答案：Using nested SELECT.

最新推荐文章于 2022-03-22 20:27:21 发布

转载最新推荐文章于 2022-03-22 20:27:21 发布 · 216 阅读

1 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/Leo-Forest/archive/2012/06/01/2531440.html

本文提供了一系列基于SQL的练习题目及解答，涵盖了从基础查询到复杂条件筛选的各种场景，包括人口统计、区域划分、经济指标等数据的分析。通过实际操作，读者可以提升SQL技能，适用于初学者和进阶学习者。

习题地址：http://sqlzoo.net/1a.htm

表结构：　　bbc(name, region, area, population, gdp)

1a.List each country name where the population is larger than 'Russia'.

select name 
from bbc 
where population > (select population from bbc where name = 'Russia')

1b.List the name and region of countries in the regions containing 'India', 'Iran'.

select name, region 
from bbc 
where region in (select region from bbc where name in ('India', 'Iran'))

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

select name 
from bbc 
where region = 'Europe' 
and GDP/population > (select Gdp/population from bbc where name = 'United Kingdom')

1d.Which country has a population that is more than Canada but less than Algeria?

from bbc 
where population < (select population from bbc where name = 'Algeria') 
and population > (select population from bbc where name = 'Canada')

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

select name
from bbc
where GDP > all (select GDP from bbc where region = 'Europe')

3a.Find the largest country in each region, show the region, the name and the population:

select region, name, population
from bbc x
where population >= all (select population from bbc y where x.region = y.region and y.population is not null)

3b.Find each country that belongs to a region where all populations are less than 25000000. Show name, region and population.

select name, region, population
from bbc x
where 25000000 > all (select population from bbc y where x.region = y.region)

3c.Some countries have populations more than three times that of any of their neighbours (in the same region). Give the countries and regions.

select name, region
from bbc x
where x.population/3 > all (select y.population from bbc y where x.region = y.region and y.name <> x.name)

转载于:https://www.cnblogs.com/Leo-Forest/archive/2012/06/01/2531440.html