关于sqlzoo中的select_from_world表的练习题~

00. 前言

练习地址：https://sqlzoo.net/wiki/SELECT_from_WORLD_Tutorial

使用的为sqlzoo的MySQL库。

表名：world

字段名：name、continent、population、gdp

01.Introduction

Read the notes about this table. Observe the result of running this SQL command to show the name, continent and population of all countries.

阅读有关此表的注释。观察运行此SQL命令的结果，以显示所有国家的名称、大陆和人口。

1
2
select name,continent,population
from world

02.Large Countries

How to use WHERE to filter records. Show the name for the countries that have a population of at least 200 million. 200 million is 200000000, there are eight zeros.

如何使用WHERE来筛选记录。显示人口至少为2亿的国家的名字。2亿是200000000，有八个零。

1
2
3
select name 名字
from world
where population>=200000000

03.Per capita GDP

Give the name and the per capita GDP for those countries with a population of at least 200 million.
HELP:How to calculate per capita GDP（per capita GDP is the GDP divided by the population GDP/population）

请给出人口至少2亿的国家的名字和人均国内生产总值。

帮助：如何计算人均国内生产总值（人均GDP=GDP/人口）

1
2
3
4
5
6
7
select name,gdp/population 人均gdp
from world
where population>=200000000

别名：可以用as，也可以直接空格。
select 字段名 as 别名
注意：as可以省略。

04.South America In millions

Show the name and population in millions for the countries of the continent ‘South America’. Divide the population by 1000000 to get population in millions.

显示南美洲国家的名字和人口(百万)。将人口除以1000000得到百万人口。

1
2
3
select name,population/1000000 人口（百万）
from world
where continent='South America'

05.France, Germany, Italy

Show the name and population for France, Germany, ltaly

显示法国，德国，意大利的名称和人口

1
2
3
4
5
6
7
select name,population
from world
where name in ('france','germany','italy')

关于in的用法:
in会筛选出字段值中所有与括号内数据相等的行。
表示在这个范围内的数据都算。

06.United

Show the countries which have a name that includes the word ‘United’

显示名称中包含“United”字样的国家

1
2
3
4
5
6
7
8
select name
from world
where name like '%United%'

模糊查询like的用法：
select 字段名 from 表名 where 字段名 like '通配符+字符'
- 占位符
% 零个或单个或多个字符

07.Two ways to be big

Two ways to be big: A country is big if it has an area of more than 3 milion sq km or it has apopulation of more than 250 million.
Show the countries that are big by area or big by population. Show name, population and area.

大国的两种方式：一个国家的面积超过300万平方公里，或者人口超过2.5亿，这个国家就是大国。
列出面积大或人口多的国家。列出名称、人口和面积。

1
2
3
select name,population,area
from world
where area>3000000 or population>250000000

08.One or the other (but not both)

Exclusive OR (XOR). Show the countries that are big by area (more than 3 million) or big by population (more than 250 million) but not both. Show name, population and area.

  • Australia has a big area but a small population, it should be included.

  • Indonesia has a big population but a small area, it should be included.

  • China has a big population and big area, it should be excluded.

  • United Kingdom has a small population and a small area, it should be excluded.

显示按面积大小(超过300万)或人口大小(超过2.5亿)的国家，但不能同时显示这两个国家。显示名称、人口和面积。

  • 澳大利亚面积大，人口少，应该算进去。

  • 印度尼西亚人口多，面积小，应该包括在内。

  • 中国人口多，面积大，应该被排除在外。

  • 英国人口少，面积小，应该被排除在外。

1
2
3
select name,population,area
from world
where (area>3000000 and population<=250000000) or (area<=3000000 and population>250000000)

09.Rounding

Show the name and population in millions and the GDP in billions for the countries of the continent ‘South America’. Use the ROUND function to show the values to two decimal places.For South America show population in millions and GDP in billions both to 2 decimal places.

显示南美洲国家的名字和人口(以百万计)以及国内生产总值(以十亿计)。使用ROUND函数将值显示到小数点后两位。南美洲的人口以百万计，国内生产总值以十亿计，二个小数位。

1
2
3
4
5
6
7
8
select name,round(population/1000000,2) 人口（百万）,round(gdp/1000000000,2) gdp（十亿）
from world
where continent='South America'

round函数用法：
round(f,p)返回四舍五入到小数点后p位的f。
例1：round(7878.89,0) -> 7878
例2：round(7878.89,1) -> 7878.9

10.Trillion dollar economies

Show the name and per-capita GDP for those countries with a GDP of at least one trillion (1000000000000; that is 12 zeros). Round this value to the nearest 1000.
Show per-capita GDP for the trillion dollar countries to the nearest $1000.

请列出国内生产总值至少1万亿(1000000000000;即12个零)的国家名称和人均国内生产总值，将这个数值绕到最近的1000。
显示万亿美元国家人均国内生产总值至最接近1000美元。

1
2
3
4
5
6
7
8
select name,round(gdp/population,-3)
from world
where gdp>1000000000000

round函数用法：
round(f,p)返回四舍五入到小数点后p位的f。
小数位数可以是负数，这将四舍五入到最接近的10（当p为-1时）或100（当p为-2时）或1000（当p为-3时）等。
例：round(7878.89,-3) -> 7000

11.Name and capital have the same length

Greece has capital Athens.
Each of the strings ‘Greece’, and ‘Athens’ has 6 characters.Show the name and capital where the name and the capital have the same number of characters.
You can use the LENGTH function to find the number of characters in a string.

Greece有首都Athens。Greece和Athens都有6个字符。显示名称和首都有相同字符数的地方。可以使用LENGTH函数来查找字符串中的字符数。

1
2
3
4
5
6
7
select name,capital
from world
where length(name)=length(capital)

length函数用法:
length(s) 返回字符串s中的字符数。
例：length('Hello') -> 5

12.Matching name and capital

The capital of Sweden is Stockholm. Both words start with the letter ‘s’.

Show the name and the capital where the first letters of each match. Don’t include countries where the name and the capital are the same word.

  • You can use the function LEFT to isolate the first character.

  • You can use <> as the NOT EQUALS operator.

Sweden的首都是Stockholm。这两个词都以字母S开头。
在每个词的首字母相匹配的地方显示名称和首都，不要包括名称和首都是同一个单词的国家。

  • 可以使用LEFT函数隔离第一个字符。

  • 可以使用<>用作不等式运算符。

1
2
3
4
5
6
7
8
select name,capital
from world
where left(name,1)=left(capital,1) and name<>capital

1、left函数用法:
left(s,n)允许从字符串s的开头提取n个字符。
例：left('hello world',4) -> 'hell'
2、不等式可以用<>或者!=表示

13.All the vowels

Equatorial Guinea and Dominican Republic have all of the vowels (a e i o u) in the name. They don’t count because they have more than one word in the name.

Find the country that has all the vowels and no spaces in its name.

  • You can use the phrase name NOT LIKE ’%a%’ to exclude characters from your results.

  • The query shown misses countries like Bahamas and Belarus because they contain at least one ‘a’.

Equatorial Guinea和Dominican Republic的名称中包含所有元音(aeiou)。它们不计算在内，因为它们的名称中包含多个单词。

找出名称中所有元音都没有空格的国家。

  • 可以使用not like ‘%a%’的短语名称来从结果中排除字符。

  • 显示的查询遗漏了Bahamas和Belarus等国家，因为它们至少包含一个’a’。

1
2
3
4
5
select name
from world
where name like '%a%' and name like '%e%' and name like '%i%' and name like '%o%' and name like '%u%' and name not like '% %'

使用not like '% %'来排除空格