SQLZOO练习题（5）

冷不防 2023-10-09 15:24 59阅读 0赞

### Sqlzoo练习题（5） ###

![在这里插入图片描述][17c6e84885f34a9faaae1dd14472712b.png_pic_center]  
**1.列出每个人口比“俄罗斯”多的国家的名字。**

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

![在这里插入图片描述][4bc36100ebdc464a93dd679bbb4d9415.png_pic_center]

**2.显示人均GDP超过“英国”的欧洲国家**

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

![在这里插入图片描述][e66192740e514ce08e6a406fff3a590f.png_pic_center]

**3.列出包括阿根廷或澳大利亚在内的各大洲国家的名称和大洲。按国家名称排序。**

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

![在这里插入图片描述][c69d79be5a544c26b21f2473147cbf5b.png_pic_center]

**4.哪个国家的人口比英国多但比德国少?显示名字和人口。**

select name,population from world 
      where population>(select population from world where name = 'United Kingdom' )
        and population<(select population from world where name ='Germany')；

![在这里插入图片描述][fc341bb2e1ec4b05bd13d0fe2299b792.png_pic_center]

**5.展示欧洲每个国家人口占德国人口的百分比。**

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

![在这里插入图片描述][407834d53dec4beaaa7da10b496d78f7.png_pic_center]

**6.哪些国家的GDP比欧洲所有国家都高?**

select name from world 
       where gdp > (select max(gdp) from world where continent='Europe')

![在这里插入图片描述][c5b8bdebb4ac4adcb4dd4e1a7a3d25bd.png_pic_center]

**7.找出各大洲最大的国家，显示大洲，名称和面积:**

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
    )

![在这里插入图片描述][2b12cafdff8c4f1f865e598717ea4ff4.png_pic_center]

**8.列出每一个大洲，以及按字母顺序排在第一位的国家名称。**

select continent,name from world as a where 
     name <= all (select name from world as b where a.continent = b.continent  )

![在这里插入图片描述][e80880c238914c4c8232cd482cf29af8.png_pic_center]

**9.找出所有国家人口都<= 25000000的大陆。然后找到与这些大陆相关的国家名称。显示名称，大洲和人口。**

select name,continent,population from world as a 
     where 25000000>= all(select population from world as b where a.continent = b.continent )

![在这里插入图片描述][93e090b5a38e41058d5fe7fdbd28669f.png_pic_center]

**10.一些国家的人口是其所有邻国人口的三倍还多。给出国家和大洲。**

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

![在这里插入图片描述][8be724d5ecd24462a4890a09076854c0.png_pic_center]

[17c6e84885f34a9faaae1dd14472712b.png_pic_center]: https://img-blog.csdnimg.cn/17c6e84885f34a9faaae1dd14472712b.png#pic_center
[4bc36100ebdc464a93dd679bbb4d9415.png_pic_center]: https://img-blog.csdnimg.cn/4bc36100ebdc464a93dd679bbb4d9415.png#pic_center
[e66192740e514ce08e6a406fff3a590f.png_pic_center]: https://img-blog.csdnimg.cn/e66192740e514ce08e6a406fff3a590f.png#pic_center
[c69d79be5a544c26b21f2473147cbf5b.png_pic_center]: https://img-blog.csdnimg.cn/c69d79be5a544c26b21f2473147cbf5b.png#pic_center
[fc341bb2e1ec4b05bd13d0fe2299b792.png_pic_center]: https://img-blog.csdnimg.cn/fc341bb2e1ec4b05bd13d0fe2299b792.png#pic_center
[407834d53dec4beaaa7da10b496d78f7.png_pic_center]: https://img-blog.csdnimg.cn/407834d53dec4beaaa7da10b496d78f7.png#pic_center
[c5b8bdebb4ac4adcb4dd4e1a7a3d25bd.png_pic_center]: https://img-blog.csdnimg.cn/c5b8bdebb4ac4adcb4dd4e1a7a3d25bd.png#pic_center
[2b12cafdff8c4f1f865e598717ea4ff4.png_pic_center]: https://img-blog.csdnimg.cn/2b12cafdff8c4f1f865e598717ea4ff4.png#pic_center
[e80880c238914c4c8232cd482cf29af8.png_pic_center]: https://img-blog.csdnimg.cn/e80880c238914c4c8232cd482cf29af8.png#pic_center
[93e090b5a38e41058d5fe7fdbd28669f.png_pic_center]: https://img-blog.csdnimg.cn/93e090b5a38e41058d5fe7fdbd28669f.png#pic_center
[8be724d5ecd24462a4890a09076854c0.png_pic_center]: https://img-blog.csdnimg.cn/8be724d5ecd24462a4890a09076854c0.png#pic_center