sqlzoo练习题之select_from_world
关于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 | select name,continent,population |

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 | select name 名字 |

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 | select name,gdp/population 人均gdp |

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 | select name,population/1000000 人口(百万) |

05.France, Germany, Italy
Show the name and population for France, Germany, ltaly
显示法国,德国,意大利的名称和人口
1 | select name,population |

06.United
Show the countries which have a name that includes the word ‘United’
显示名称中包含“United”字样的国家
1 | select name |

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 | select name,population,area |

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 | select name,population,area |

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 | select name,round(population/1000000,2) 人口(百万),round(gdp/1000000000,2) gdp(十亿) |

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 | select name,round(gdp/population,-3) |

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 | select name,capital |

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 | select name,capital |

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 | select name |
