sql在线练习网站sqlzoo.cn全部答案（1）

一：SELECT 教程

 

1a. 点击查看关于bbc表的详细说明． 执行命令: SELECT name, region, population FROM bbc． 查看结果.
SELECT name, region, population FROM bbc

 

2a. 给出人口不小于２亿的国家的名称
select name from bbc where population>= 200000000

 

2b. 给出人口不小于２亿的国家的名称和人均 GDP.
select name,gdp/population pergdp from bbc where population >=200000000

 

2c. 给出地区属于'South Asia'的国家的名称和人口数（以百万为单位，人口数除以1000000，然后用round函数取整）
select name,round(population/1000000,2) from bbc where region = 'south asia'

 

2d. 给出'France', 'Germany', 'Italy'三个国家的名称和人口．
select name,population from bbc where name in ('France', 'Germany', 'Italy')

 

2e. 给出包含'United'字符国家名称
select name from bbc where name like '%united%'

 

二：如何使用WHERE 来筛选记录.

 

1. 'France'的人口数.
select name,population from bbc where name = 'france'

 

2. 国土大国的国名和人口密度．
select name,population/area p_area from bbc where area > 3000000

 

3. 给出一些小而富有的国家
select name from bbc where area < 5000 and gdp > 500000000

 

4. Ceylon, Iran, Persia and Sri Lanka 中哪些是国家名？
select name from bbc where name in ('Ceylon', 'Iran', 'Persia','Sri Lanka')

 

5. 哪些国家的名称以字符D开始？
select name from bbc where name like 'D%'

 

6. 哪些国家的面积不大不小？
select name from bbc where area between 500000 and 1000000

 

三：使用嵌套语句

1：获得和'Brazil'（巴西）同属一个地区的所有国家．
select name from bbc where region = (select region from bbc where name = 'brazil')

 

2. 一个嵌套语句使用IN操作符的例子：
select name from bbc where region in (select region from bbc where name = 'brazil')

 

四：SELECT 语句中嵌套使用SELECT来进行更复杂的查询．

 

1a. 给出人口多于Russia（俄国）的国家名称．
select name from bbc where population > all(select population from bbc where name = 'russia')

 

1b. 给出'India'（印度）, 'Iran'（伊朗）所在地区的所有国家的所有信息． 
select * from bbc where region in (select region from bbc where name in ('india','iran'))

 

1c. 给出人均GDP超过'United Kingdom'（英国）的欧洲国家
select name from bbc where gdp/population > (select gdp/population from bbc where name = 'united kingdom')

 

1d. 哪个国家的人口比Canada（加拿大）多但少于Algeria（阿尔及利亚）？ 
select name from bbc where population > (select population from bbc where name = 'Canada') and population < (select population from bbc where name = 'Algeria')

 

2a. 给出GDP比任何欧洲国家都多的国家（只要显示国家名称）．
select name from bbc where gdp > all(select gdp from bbc where region = 'Europe')

 

3a. 给出每个地区人口最大的国家： 
select name from bbc x where population = (select max(population) from bbc y where x.region = y.region)
或
select name from bbc where population in (select max(population) from bbc group by region)

 

3b. 给出地区中所有国家的人口总数为０的地区
select region from bbc x where 0 = (select sum(population) from bbc y where x.region = y.region)
或
select region from bbc group by region having sum(population) = 0

 

3c. 有些国家的人口数比她的周边国家（周边国家指在同一地区的国家）要多三倍，列出这些国家和地区．
select name,region from bbc x where population > all(select population*3 from bbc y where x.region=y.region and x.name <> y.name)

 

五：Aggregate functions（集合函数）

1a. 显示世界人口总和.
SELECT SUM(population) FROM bbc

 

1b. 列出所有的地区，但每个地区只能显示一次．
select distinct region from bbc

 

1c. 显示GDP超过所有Africa（非洲）国家总和的国家． 
select name from bbc where gdp > (select sum(gdp) from bbc where region = 'africa')

 

2a. 显示每个地区以及的该地区的国家总数．
select count(name),region from bbc group by region

 

2b. 显示每个地区以及的该地区国家总人口数不少于１０００万的国家总数． 
select region,count(name) from (select region,name from bbc where population > 10000000) temp group by region
或
select region,count(name) from bbc where population > 10000000 group by region

或

select region from bbc group by region having sum(population) >= 100000000

 

六：如何使用 SUM, COUNT, MAX, AVG, DISTINCT and ORDER BY.

 

1. 欧洲的人口总数和总GDP
select sum(population),sum(gdp) from bbc where region = 'Europe'

 

2. 有哪些地区？ 
select distinct region from bbc

 

3. 显示每个人口超过100000000国家的name（名称）和 population（人口） 这些国家以人口数作降序排列．（即人口越多排在越前）
select name,population from bbc where population > 100000000 order by population desc

 

七：如何使用 GROUP BY and HAVING.

 

1. 显示每个地区的总人口数和总面积．以总人口来排序．
select sum(population),sum(area),region from bbc group by region order by sum(population) desc

 

2. 显示每个地区的总人口数和总面积．以总人口来排序．仅显示那些面积超过1000000的地区．
select sum(population),sum(area),region from bbc group by region having sum(area)>1000000 order by sum(population) desc