sqlzoo刷题——SELECT within SELECT Tutorial/zh(子查询)

前言

本文章记录sqlzoo刷题过程以及解题思路,每个小节不仅包含练习，还有选择题（quiz）部分的实现思路
网址：sqlzoo_SELECT within SELECT Tutorial/zh

一、代码练习部分

1. 列出每個國家的名字 name，當中人口 population 是高於俄羅斯’Russia’的人口。

查询结果列——国家名，人口
条件——人口>俄罗斯人口（子查询）
查询逻辑——先用子查询查出俄罗斯的人口，再查询全表人口>俄罗斯人口的行，选择国家名，人口显示

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

2. 列出歐州每國家的人均GDP，當中人均GDP要高於英國’United Kingdom’的數值。

查询结果列——国家名，gdp_人均（gdp/population）
条件——
1、洲际：欧洲
2、gdp_人均>英国gdp_人均（子查询）
3、两个条件同时满足，缺一不可
查询逻辑——先用子查询得到英国人均gdp，再查询欧洲国家且人均gdp大于英国数值的gdp的行，选择国家名和人均gdp显示

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

3. 在阿根廷Argentina 及 澳大利亞 Australia所在的洲份中，列出當中的國家名字 name 及洲分 continent 。按國字名字順序排序

查询结果列——国家名，洲份
条件——
1、洲份：阿根廷所在的洲，澳大利亚所在洲，两个群体（子查询得出洲）
2、排序：按国家名，默认即可
查询逻辑——先用子查询查出满足条件的洲，选择包含这两个洲的行(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的少?列出國家名字name和人口population 。

查询结果列——国家名，人口
条件——人口>加拿大人口 and 人口<波兰人口

两种方法实现：1）between关键字，但要剔除首尾值；2）当成2个主体看。3）不可以使用连等号表示区间，如1<x<100

查询逻辑——
方法1：先用子查询查出加拿大人口和波兰人口，选择人口数在这个区间的行，显示国家名和人口。

select name,population 
from world
where population between (select population from world where name='Canada') and (select population from world where name='Poland')
and name not in ('Canada','Poland')

方法2：分两个部分查询群体，再拼接起来。先查询人口>加拿大人口的行，再查询人口<加拿大人口的行，用and连接，显示国家名和人口

方法2：
select name,population 
from world 
where population>(select population from world where name='Canada') 
	and population<(select population from world where name='Poland') 

5. 顯示歐洲的國家名稱name和每個國家的人口population。以德國的人口的百分比作人口顯示。

查询结果列——国家名，人口（%）
条件——
1、洲际=欧洲
2、人口：转化为占德国的百分之几（子查询得出德国人口再计算）

1）数值转化成百分比：concat(小数，‘%’)，注意小数要比原来大100倍再与%连接，才是正确的百分数
2）利用round（数值，保留的小数位数）四舍五入

查询逻辑——查询包含欧洲的所有行，显示国家名，人口/（查询出的德国人口数）转化成百分数

select name,
	concat(round((population/(select population from world where name='Germany'))*100,0),'%')as population##这里使用concat连接百分号之后小数点后突然有很多 0，不知道为什么
from world
where continent='Europe'

6. 哪些國家的GDP比Europe歐洲的全部國家都要高呢? [只需列出 name 。] (有些國家的記錄中，GDP是NULL，沒有填入資料的。)

查询结果列——国家名
条件——
gdp高于欧洲国家，需要满足2个方面。1）gdp>=全部欧洲国家gdp，2）gdp>0（这是个很重要的条件，与null值比较会报错）
查询逻辑——查询gdp高于欧洲国家的行（子查询），显示国家名列

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

7. 在每一個州中找出最大面積的國家，列出洲份 continent, 國家名字 name 及面積 area。 (有些國家的記錄中，AREA是NULL，沒有填入資料的。)

查询结果列——洲份，国家名，面积
条件——每个洲中，面积最大

旧表area VS all （新表中新洲=旧洲的所有area）
一个面积和这个洲所有的国家面积相比较，自己是不是最大的哪个，是则选择。需要将这个洲的所有国家的面积数据抽取出来（从新表中选取数据）

查询逻辑——先选择新表中两个表中相同continent的area列，旧表area>全部area的行，且面积>0，显示洲份，国家名，面积

select continent,name,area
from world x ##基础表
where area >=all(select area from world y where y.continent=x.continent and area>0)

8. 列出洲份名稱，和每個洲份中國家名字按字母順序是排首位的國家名。(即每洲只有列一國)

查询结果列——洲份，国家
条件——每个洲只选择一行，字母是首位的国家。

字符串也可以比大小，依据是字母顺序，A~Z对应的是从小到大，所以选择名字是这个洲全部国家中最小的

查询逻辑——选择基础表的国家名，与辅助表中同一洲的国家相比较最小的行，显示洲份，国家

select continent,name
from world x
where name<=all(select name from world y where y.continent=x.continent)
order by continent

9. 找出洲份，當中全部國家都有少於或等於 25000000 人口. 在這些洲份中，列出國家名字name，continent 洲份和population人口。

查询结果列——国家名，洲份，人口
条件——全部国家人口<=2500万人口的洲。即任一人口>2500万国家坐在的洲被排除
查询逻辑——查询出人口>2500万的所在的行，选择洲列，不包含这些洲的洲就是要查找的，显示这些洲的国家，洲份和人口

select name,continent,population 
from world 
where continent not in (select continent from world where population>25000000)

11. 有些國家的人口是同洲份的所有其他國的3倍或以上。列出 國家名字name 和 洲份 continent。

查询结果列——国家名，洲份
条件——人口/3仍然是同洲所有国家人口中最大的
查询逻辑——选择基础表人口/3>辅助表相同洲下的所有行人口总数最高的行，同时人口>0，且剔除自己（新表中是原数值，基础表的人口是原来的1/3），显示国家名，洲份

select name,continent
from world x
where population/3 >=all(select population from world y where y.continent=x.continent and population>0 and y.name!=x.name)

二、测试部分（选择题）

1. 選擇代碼以顯示在每個區域人口最小的國家的國家名稱，區域和人口。

查询结果列——国家名,区域，人口
条件——人口<=该区域内全部国家的人口*（辅助表，子查询，all关键字）*
查询逻辑——先查询每个区域内包含的人口行（辅助表），当辅助表region=基础表region时，基础表population VS 辅助表population，选择其中最小且>0的列，显示国家名,区域，人口

select name,region,population
from bbc a
where population<=(select population from bbc b where b.region=a.region and population>0)

2. 選擇代碼以顯示國家名稱，該國所在的地區每國人口都超過50000。

查询结果列——国家名
条件——地区：所有行的人口>5万=剔除人口>5万地区=5万>该地区最大的人口数。
查询逻辑——
思路1：子查询+逆向思维。选择所有洲，剔除查询人口>5万包含的洲，显示国家名
思路2：子查询+all关键字。查询5万>该地区最大的人口数的行，显示名称

思路1：
select name
from bbc
where region not in (select region from bbc where population >50000)
思路2：
select name
from bbc a
where 50000>all(select population from bbc b where b.region=a.region and b.population>0)

3. 選擇代碼以顯示國家名稱，該國家人口少於它周圍的全部國家的人口三分之一。

查询结果列——国家名
条件——人口<all（同区域国家人口/3）=某国人口3<all(同区域国家人口)。选择后者sql执行会更快
查询逻辑——查询出人口3之后<同区域人口最低数的行，显示国家名。

写法1：
select name
from bbc a
where population*3<all(select population from bbc b where b.region=a.region and population>0 and b.name!=a.name)
写法2：
select name
from bbc a
where population<all(select population/3 from bbc b where b.region=a.region and population>0 and b.name!=a.name)

4. 選擇你會從這個代碼獲得的結果。

SELECT name FROM bbc
 WHERE population >
       (SELECT population
          FROM bbc
         WHERE name='United Kingdom')
   AND region IN
       (SELECT region
          FROM bbc
         WHERE name = 'United Kingdom')

查询结果列——国家名
条件——
1、人口：>英国人口
2、地区：与英国所在同地区
查询逻辑——将英国的人口和英国的地区作为两个群体分别查询，再用and连接，同时满足两个条件的行被选中，显示国家名列

5. 選擇代碼以顯示國家名稱，該國有比非洲任何國家更大的國內生產總值GDP。

查询结果列——国家名称
条件——gdp>all(非洲国家gdp）
查询逻辑——子查询+all关键字查询gdp>全部非洲国家gdp的行，选择国家名列显示。注意gdp不一定都是正的，也会有负的gdp，所以写查询的时候条件应该使用 is not null

#我的写法：
select name
from bbc
where gdp>all(select gdp from bbc where region='Africa' and gdp is not null)
#选项写法：使用了子查询+all+max函数
select name
from bbc
where gdp>all(select max(gdp) from bbc where region='Africa' and gdp is not null)
#这样应该是让查询更快。每一行只需要和一个数值比较就可以得出结果。

6. 選擇代碼以顯示國家名稱，該國人口比俄羅斯少，但比丹麥的多。

查询结果列——国家名称
条件——人口：俄人口<人口<丹人口（between…and…）=俄人口<人口&人口<丹人口（and）
查询逻辑——俄罗斯和丹麦的人口先用子查询分别查出来，再使用between或者and连接起来。需要注意如果使用between会包括区间的首尾值，要剔除，所以这种数值区间的问题更推荐and连接的方式。

select population
from bbc
where population<(select population from bbc where name='Russia')
	and population<(select population from bbc where name='Denmark')

7. 選擇你會從這個代碼獲得的結果。

SELECT name FROM bbc
 WHERE population > ALL
       (SELECT MAX(population)
          FROM bbc
         WHERE region = 'Europe')
   AND region = 'South Asia'

查询结果列——国家名
条件——
1、人口大于欧洲最大人口
2、南美洲国家
查询逻辑——查询南美洲国家中人口大于欧洲最大国家人口的国家名称