sqlzoo练习6-select in select
本文中主要介绍的是子查询select in select
This tutorial looks at how we can use SELECT statements within SELECT statements to perform more complex queries.
- List each country name where the population is larger than that of ‘Russia’.
找出人口比俄罗斯多的国家
1 | select name from world |
- Show the countries in Europe with a per capita GDP greater than ‘United Kingdom’.
找出人均gdp比UK大的国家
1 | select name |
- List the name and continent of countries in the continents containing either Argentina or Australia .Order by name of the country.
- 找出和Argentina or Australia 相同洲的国家
1 | select name, continent |
- Which country has a population that is more than Canada but less than Poland? Show the name and the population.
1 | select name,population |
- Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.
Germany (population 80 million) has the largest population of the countries in Europe. Austria (population 8.5 million) has 11% of the population of Germany.
- 找出欧洲所有国家name
- 人口
population
以和德国的百分比表示
1 | select name, concat(round(100*population / (select population from world where name='Germany')), '%') |
笔记:换成百分比必须加上100*
- round函数是去掉小数位
- concat函数是指定连接符号,必须使用单引号将
%
抱起来
关于all关键字的使用:通过符号
\>= or > or < or <=
来表示大小关系。通过一个demo来表示
1 | SELECT name |
- Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values)
哪些国家的GDP比欧洲每个国家的GDP都要大
1 | select name |
- Find the largest country (by area) in each continent, show the continent,the name and the area
- 找出每个洲中面积最大的国家
1 | select continent, name, area |
- List each continent and the name of the country that comes first alphabetically.
找出所有的洲,按照国家的字母顺序排序
1 | select continent,name |
- Find the continents where all countries have a population <= 25000000. Then find the names of the countries associated with these continents. Show nameontinent and population
找出洲中全部国家的人口都小于25000000的那些洲
1 | select name, continent, population |
- Some countries have populations more than three times that of any of their neighbours (in the same continent). Give the countries and continents.
找出在同一个洲中超出其他任意一个国家人口3倍的那些国家
1 | select x.name, x.continent -- 指定从哪个表中查找的数据 |