SELECT within SELECT Tutorial -- SQLZOO
2024-10-21 03:44:49
SELECT within SELECT Tutorial
注意:where语句中对表示条件的需要用单引号, 下面的译文使用的是有道翻译如有不正确,请直接投诉有道
01.List each country name where the population is larger than that of 'Russia'.
译文:列出每个国家的人口超过“俄罗斯”的名字。
select name from world where population>(select population from world where name='Russia' )
02.Show the countries in Europe with a per capita GDP greater than 'United Kingdom'.
译文:显示欧洲国家的人均GDP大于“英国”。
select name from world where continent='Europe' and (GDP/POPULATION)>(select gdp/population from world where name='United Kingdom')
03.List the name and continent of countries in the continents containing either Argentina or Australia. Order by name of the country.
译文:列出包括阿根廷或澳大利亚在内的各大洲的国家名称和大洲。按国家名称排序。
select name,continent from world where continent in (select continent from world where name in('Argentina', 'Australia')) order by name
04.Which country has a population that is more than Canada but less than Poland? Show the name and the population.
译文:哪个国家的人口比加拿大多但比波兰少?显示名字和人口。
select name,population from world where population>(select population from world where name='Canada ') and population<(select population from world where name='Poland')
05.Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.
译文:显示欧洲每个国家的名称和人口。显示人口占德国人口的百分比。
# 这题写的有点问题 concat拼接的时候自己在%加上了好多零
SELECT name,concat(round(population/(select population from world where name='Germany'),2)* 100, '%')
FROM world
WHERE continent='Europe'
06.Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values)
译文:哪些国家的GDP比欧洲的每个国家都高?只告诉我名字。(有些国家可能有零gdp值)
select name from world where GDP > (select max(GDP) from world where continent='Europe')
07.Find the largest country (by area) in each continent, show the continent, the name and the area:
译文:在每个洲找出最大的国家(按面积),显示洲,名称和地区:
select continent,name,area from world where area in(select max(area) from world group by continent)
08.List each continent and the name of the country that comes first alphabetically.
译文:按字母顺序列出每个洲和国家名称。
SELECT continent,name FROM world a WHERE name <= ALL(SELECT name from world b WHERE a.continent = b.continent )ORDER by name
09.Find the continents where all countries have a population <= 25000000. Then find the names of the countries associated with these continents. Show name, continent and population.
译文:找出所有国家的人口都小于2500亿的大陆。然后找出与这些大陆相关的国家的名字。显示姓名,大陆和人口。
SELECT name,continent,population FROM world x WHERE 25000000 >= ALL(SELECT population FROM world y WHERE y.continent = x.continent)
10.Some countries have populations more than three times that of any of their neighbours (in the same continent). Give the countries and continents.
译文:有些国家的人口是其邻国(在同一大陆)的三倍还多。给出国家和大陆。
SELECT name,continent FROM world x WHERE x.population/3 >= ALL(SELECT population FROM world y WHERE y.continent = x.continent AND y.name != x.name)
练习网址:https://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial
------------------------------------------------------------------------------------------------------------------------------------------------------------------
最新文章
- js中bind、call、apply函数的用法
- 关于NODE NPM 输入命令后没反应的问题
- selenium隔离环境安装、以及示例
- JS 根据特定URL获取ID数组
- [转]Android中Application类的用法
- 中国电信大亚DP607光猫破解,设置路由,wifi!关闭远程管理,改连接限制,SN码查询!
- APP测试点总结
- final关键字+const关键字
- extern 关键字
- ASP.NET操作WMI
- HTTP长轮询和短轮询
- scala知识点(一)
- [转贴]使用CryptoAPI解析X509证书和P12证书
- Redis集群战法整理
- Unity的Profiler性能分析
- TEA encryption with 128bit key
- 将Python代码嵌入C++程序进行编写
- shell相关命令
- Shell常见用法小记
- PTA——近似求PI
热门文章
- Javascript 中 数组遍历 forin和forof 的区别
- pl/sql案例
- SpringBoot2.x入门:引入web模块
- 【Python3爬虫】破解时光网登录加密参数并实现模拟登录
- css如何将图片横向平铺?
- C++栈(stack)、队列(queue)、链表(list)的常用函数
- 调整数组顺序使奇数位于偶数前面(剑指offer-13)
- day05 程序与用户交互和基本运算符
- Mysql基础(一):Mysql初识、基本指令、数据库密码相关、创建用户及授权
- 渐进式Web应用(PWA)