这是《从零学会sql》系列课程第4节课《复杂查询》的练习题,也是常考常考的面试题。
题目来自sqlzoo的子查询题目。网址:
https://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial/zh
这部分题目使用的是world表:世界国家信息表
表列名含义:
name:国家名称
continent:该国家属于哪个洲
area:面积
population:人口
gdp:国内
1.列出符合条件的国家名称,条件:国家认可大于俄罗斯(Russia)的人口
【知识点】标量子查询
selectnamefromworldwherepopulation >(selectpopulationfromworldwherename='Russia');
2.列出欧洲每个国家的人均GDP,其中人均GDP要高于英国(United Kingdom)
【知识点】比较运算符(人均GDP=人口/gdp),逻辑运算符(and),标量子查询
selectnamefromworldwherecontinent ='Europe'andgdp/population >(selectgdp/populationfromworldwherename='United Kingdom');
3.在阿根廷(Argentina)和澳大利亞(Australia)所在的洲份中的国家有哪些?查找出国家名称和洲名称,并按国家名称排序
【知识点】在运算符in里使用子查询
selectname, continentfromworldwherecontinentin(selectcontinentfromworldwherename='Argentina'orname='Australia')orderbyname;
4.查找符合下面条件的国家名称和人口:国家的人口比加拿大(Canada)的多,但比波兰(Poland)的少
【知识点】在运算符between里使用标量子查询,这里用between查找出的范围边界值包括了边界值,所以要+1,和-1去掉边界值
比如范围是 1=<x<=10,(其中1是加拿大人口,10是波兰人口),为了不包括边界值,需要去掉两个边界值,变成 1+1=<x<= (10-1)
selectname, populationfromworldwherepopulationbetween(selectpopulationfromworldwherename='Canada')+1and(selectpopulationfromworldwherename='Poland')-1;
5.德国(Germany)在欧洲(Europe)國家的人口最多。奧地利(Austria)拥有德国总人口的11%。
查找欧洲的国家名称和每个国家的人口,其中人口以德国人口的百分比来显示人口数
【知识点】标量子查询,字符串连接函数concat,浮点数保留多少位round函数
selectname,concat(round(population*100/(selectpopulationfromworldwherename='Germany')),'%')ASpopulationfromworldwherecontinent ='Europe';
6.哪些国家的GDP比欧洲(Europe)的全部国家都要高呢? (有些国家的记录中,GDP是空值NULL,没有填入资料)
【知识点】all的用法,子查询,条件中gdp>0用来去掉空值的情况
selectnamefromworldwheregdp >all(selectgdpfromworldwherecontinent ='Europe'andgdp >0);
7.在每一个州中找出最大面积的国家,查找出洲, 国家名字,面积。 (有些国家的记录中,面试是空值NULL,没有填入资料)
【知识点】all的用法,关联子查询
selectcontinent,name, areafromworldasxwherearea >=all(selectareafromworldasywherey.continent=x.continentand area>0);
8.列出洲份名称和国家名称,其中每个洲只取出一个国家(条件:该国家排序在这个洲的首位)
【知识点】all的用法,关联子查询
selectcontinent,namefromworldasxwherename<=all(selectnamefromworldasywherey.continent=x.continent);
9.找出符合条件的洲和国家名称,条件:该洲中的全部国家人口都有少于或等于 25000000 人口)
selectname, continent, populationfromworldasxwhere25000000>=all(selectpopulationfromworldasywherey.continent=x.continent);
10.有些国家的人口是同洲份的所有其他国的3倍或以上。列出这些国家的名称和洲
selectname, continentfromworldasxwherepopulation >all(select3*populationfromworldasywherey.continent=x.continentandx.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 (子查询);
网友评论