SQLZOO习题详解(05)——SELECT within SELECT

女爷i 2023-01-19 14:49 80阅读 0赞

--------------------

*  本文原创作者：谷哥的小弟
 *  作者博客地址：http://blog.csdn.net/lfdfhl

--------------------

# world表 #

world表如下：  
![在这里插入图片描述][20210402153522731.png]

# 链接地址 #

[https://napier.sqlzoo.net/wiki/SELECT\_within\_SELECT\_Tutorial][https_napier.sqlzoo.net_wiki_SELECT_within_SELECT_Tutorial]

# SQL练习 #

01、查询人口数量比Russia人口数量多的国家的名字

SELECT name FROM world WHERE population > (SELECT population FROM world WHERE name='Russia')

02、查询人均GDP比United Kingdom高且属于欧洲Europe的国家的名字

SELECT name FROM world
    WHERE gdp / population > (SELECT gdp / population FROM world WHERE name = 'United Kingdom') AND continent = 'Europe'

03、查询阿根廷Argentina及澳大利亚Australia所在的洲份中的国家的名称及其所属洲；并按照国家名称排序

SELECT name, continent
    FROM world
    WHERE continent IN (
    	SELECT continent
    	FROM world
    	WHERE name IN ('Argentina', 'Australia')
    )
    ORDER BY name

04、查询人口数量比加拿大Canada多，但比波兰Poland少的国家的名称及其人口数量

SELECT name, population
    FROM world
    WHERE population > (
    		SELECT population
    		FROM world
    		WHERE name = 'Canada'
    	)
    	AND population < (
    		SELECT population
    		FROM world
    		WHERE name = 'Poland'
    	)

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')

05、查询所属洲为Europe的国家的名称及其人口数量。其中，人口数量以德国的人口数量的百分比作显示。

SELECT name
    	, concat(round(100 * population / (
    		SELECT population
    		FROM world
    		WHERE name = 'Germany'
    	), 0), '%') AS percentage
    FROM world
    WHERE continent = 'Europe'

![在这里插入图片描述][20210407220436833.png]  
06、查询非欧洲Europe国家中GDP比所有欧洲Europe国家都要高的国家的名称

SELECT name
    FROM world
    WHERE gdp >= ALL (
    		SELECT gdp
    		FROM world
    		WHERE gdp >= 0 AND continent = 'Europe'
    	)
    	AND continent != 'Europe'

07、在每一個州中找出该洲国土面积最大的国家。请列出这些国家的continent，name，area

SELECT continent, name, area
    FROM world AS a
    WHERE area >= ALL (
    	SELECT area
    	FROM world AS b
    	WHERE b.continent = a.continent AND area > 0
    )

![在这里插入图片描述][20210407222016556.png]  
08、查询每个洲的洲名，以及在该洲continent中按照字母排序的首位国家。

SELECT continent, name
    FROM world AS a
    WHERE name <= ALL (
    	SELECT name
    	FROM world AS b
    	WHERE b.continent = a.continent
    )

![在这里插入图片描述][202104072225169.png]  
09、查询洲continent中所有国家人口均小于或等于25000000的洲；并列出该洲中的国家名称，人口数量，洲名

SELECT name, continent, population
    FROM world AS a
    WHERE 25000000 >= ALL (
    	SELECT population
    	FROM world AS b
    	WHERE a.continent = b.continent AND b.population > 0
    )

![在这里插入图片描述][20210407223957991.png]  
10、某些国家的人口数量是同洲份的其它任意国家的3倍或以上。请查询这些国家的名称以及所属洲continent

SELECT name, continent
    FROM world AS a
    WHERE population > ALL (
    	SELECT population * 3
    	FROM world AS b
    	WHERE a.continent = b.continent AND a.name != b.name
    )

![在这里插入图片描述][20210407224833334.png]

[20210402153522731.png]: /images/20221021/fd38672a734f4b249348548b181302d0.png
[https_napier.sqlzoo.net_wiki_SELECT_within_SELECT_Tutorial]: https://napier.sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial
[20210407220436833.png]: /images/20221021/61cbb9af7dcc4f2eac6c5d2c837a69a3.png
[20210407222016556.png]: /images/20221021/f07c99cbecf144c988435f431adb4e10.png
[202104072225169.png]: /images/20221021/d83a679eb3454e0ea946c5f845412f18.png
[20210407223957991.png]: /images/20221021/fe2a68e7124a4d219d631875039f17dc.png
[20210407224833334.png]: /images/20221021/a1c17355380540eeb719880bd7027be2.png