第4关《从零学会SQL复杂查询》练习题答案

这是《从零学会sql》系列课程第4节课《复杂查询》的练习题，也是常考常考的面试题。

一、下面这个题目的答案是错误的：

正确的应该是：sql面试题：topN问题

二、题目来自sqlzoo的子查询题目

网址：

https://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial/zh

这部分题目使用的是world表：世界国家信息表

表列名含义：

name:国家名称

continent:该国家属于哪个洲

area:面积

population:人口

gdp:国内

1.列出符合条件的国家名称，条件：国家认可大于俄罗斯(Russia)的人口

【知识点】标量子查询

select name 
from world 
where population > 
(select population 
from world 
where name = 'Russia');

2.列出欧洲每个国家的人均GDP，其中人均GDP要高于英国(United Kingdom)

【知识点】比较运算符（人均GDP=人口/gdp），逻辑运算符（and），标量子查询

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

3.在阿根廷(Argentina)和澳大利亞(Australia)所在的洲份中的国家有哪些？查找出国家名称和洲名称，并按国家名称排序

【知识点】在运算符in里使用子查询

select name, continent from world
where continent in
(select continent 
from world
where name='Argentina' or name='Australia')
order by name;

4.查找符合下面条件的国家名称和人口：国家的人口比加拿大(Canada)的多，但比波兰(Poland)的少

【知识点】在运算符between里使用标量子查询，这里用between查找出的范围边界值包括了边界值，所以要+1，和-1去掉边界值

比如范围是 1=<x<=10，（其中1是加拿大人口，10是波兰人口），为了不包括边界值，需要去掉两个边界值，变成 1+1=<x<= (10-1)

select name, population 
from world
where population between
(select population 
from world
where name='Canada')+1 and
(select population 
from world
where name='Poland')-1;

5.德国(Germany)在欧洲(Europe)國家的人口最多。奧地利(Austria)拥有德国总人口的11％。

查找欧洲的国家名称和每个国家的人口，其中人口以德国人口的百分比来显示人口数

【知识点】标量子查询，字符串连接函数concat,浮点数保留多少位round函数

select name, concat(round(population*100/(select population from world where name='Germany')), '%') AS population 
from world
where continent = 'Europe';

6.哪些国家的GDP比欧洲(Europe)的全部国家都要高呢? (有些国家的记录中，GDP是空值NULL，没有填入资料）

【知识点】all的用法，子查询,条件中gdp>0用来去掉空值的情况

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

7.在每一个州中找出最大面积的国家，查找出洲, 国家名字，面积。 (有些国家的记录中，面试是空值NULL，没有填入资料）

【知识点】all的用法，关联子查询

select continent, name, area 
from world as x
where area >= all
(select area 
from world as y
where y.continent=x.continentand area>0);

8.列出洲份名称和国家名称，其中每个洲只取出一个国家（条件：该国家排序在这个洲的首位）

【知识点】all的用法，关联子查询

select continent, name 
from world as x
where name <= all
(select name 
from world as y
where y.continent=x.continent);

9.找出符合条件的洲和国家名称，条件：该洲中的全部国家人口都有少于或等于 25000000 人口）

select name, continent, population 
from world as x
where 25000000 >= all
(select population 
from world as y
where y.continent=x.continent);

10.有些国家的人口是同洲份的所有其他国的3倍或以上。列出这些国家的名称和洲

select name, continent 
from world as x
where population > all
(select 3*population 
from world as y
where y.continent=x.continent and x.name <> y.name);

如果上面的sql 写成下面是错误的：

all可以与=、>、>=、<、<=、<>结合起来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于all里面的所有数据。

如果是两个数字比较，a > 3b 等价于 a/3 > b 。但是，在mysql里all得到的不是一个数字，是一个集合，也就是得到的是n行数据，所以不能写a > 3all(b)，语法只能是 a/3 > all(b)。

all与子查询的语法如下：

select 列名1 from 表名1 where 列名1 > all (子查询);

推荐：常见面试题：我们为什么要选择你？