SQLZOO练习（一）SELECT BASICS，SELECT form world

name	continent	area	population	gdp
Afghanistan	Asia	652230	25500100	20343000000
Albania	Europe	28748	2831741	12960000000
Algeria	Africa	2381741	37100000	188681000000
Andorra	Europe	468	78115	3712000000
Angola	Africa	1246700	20609294	100990000000
……

name:国家名称 continent:大洲 area:面积 population:人口 gdp:国内生产总值

一、 SELECT basics

1、显示德国人口

The example uses a WHERE clause to show the population of 'France'. Note that strings (pieces of text that are data) should be in 'single quotes';

Modify it to show the population of Germany

SELECT population FROM world
WHERE name='Germany';

2、显示Sweden瑞典，Norway挪威，Denmark丹麦的国家名称和人口

Checking a list The word IN allows us to check if an item is in a list. The example shows the name and population for the countries 'Brazil', 'Russia', 'India' and 'China'.

Show the name and the population for 'Sweden', 'Norway' and 'Denmark'.

SELECT name,population FROM world
WHERE name IN ('Sweden','Norway','Denmark');

3、显示面积为200,000及250,000之间的国家名称和该国面积

Which countries are not too small and not too big? BETWEEN allows range checking (range specified is inclusive of boundary values). The example below shows countries with an area of 250,000-300,000 sq. km. Modify it to show the country and the area for countries with an area between 200,000 and 250,000.

SELECT name,area FROM world
WHERE area BETWEEN 200000 AND 250000;

二、SELECT from world

1、显示所有国家的名称，所在大洲，人口

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

SELECT name,continent,population
FROM world;

2、显示人口超过2亿的国家名称

Show the name for the countries that have a population of at least 200 million. 200 million is 200000000, there are eight zeros.

SELECT name FROM world
WHERE population >= 200000000;

3、显示人口超过2亿的国家名称和人均GDP

Show the name for the countries that have a population of at least 200 million. 200 million is 200000000, there are eight zeros.

SELECT name,GDP/population FROM world
WHERE population >=200000000;

下面的表达也是正确的。

 

 

4、显示continent ='South America'的国家的名称和人口。

将人口除以100万，以获得数百万人口,也就是population的单位为百万。

Show the name for the countries that have a population of at least 200 million. 200 million is 200000000, there are eight zeros.

SELECT name,population/1000000 FROM world
WHERE continent='South America';

5、显示法国、德国、意大利的国家名称和人口。

Show the name and population for France, Germany, Italy

SELECT name,population FROM world
WHERE name IN ('France','Germany','Italy');

6、显示名字中有‘United’的国家名称。

Show the countries which have a name that includes the word 'United'

SELECT name FROM world
WHERE name LIKE '%United%';

7、Two ways to be big

如果一个国家面积超过300万平方公里，或者人口超过2.5亿，那么这个国家就很大。按人口显示面积大或面积大的国家。 显示国家名称，人口和面积。

SELECT name,population,area FROM world
WHERE area >=3000000 OR population >=250000000;

8、One or the other (but not both)

XOR 国家面积超过300万平方公里，或者人口超过2.5亿的国家，但是不能同时满足。显示国家名称，人口，面积。

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.

SELECT name,population,area FROM world
WHERE area >=3000000 XOR population >=250000000;

9、ROUND函数

除以1000000（6个0）是以百万计，除以1000000000（9个0）是以十亿计。

显示南美洲的国家名称，人口，GDP（人口以百万为单位，GDP以十亿为单位）；并且用ROUND函数保留两位小数。

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.

SELECT name,ROUND(population/1000000,2),ROUND(GDP/1000000000,2) FROM world
WHERE continent='South America';

10、显示GDP超过1万亿的国家名称和人均GDP，人均GDP用ROUND函数四舍五入到$1000。

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.

SELECT name,ROUND(GDP/population,-3) FROM world
WHERE GDP >=1000000000000;

11、Name and capital have the same length

显示字符长度是一样的国家名称和首都名称，你可以使用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

SELECT name,capital FROM world
WHERE LENGTH(name)=LENGTH(capital);

12、Matching name and capital

显示开头字母相同的国家名称、首都名称，但是国家名称和首都名称不能相同。

你可以使用LEFT函数来锁定首字母，可以用不等于号（<>）来进行不等于判定。

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.

SELECT name,capital FROM world
WHERE LEFT(name)=LEFT(capital) AND name<>capital;

13、All the vowels（有难度，☆☆☆☆）

显示包含所有元音字母（aeiou），而且不能有空格的国家名称。

赤道几内亚和多米尼加共和国名称中都包含了元音字母（aeiou），但是因为超过一个单词不能被计算在内。

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'

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 '% %';