SQLZOO 2 select from world

2 select from world

1.show the name, continent and population of all countries.

显示所有国家的名字，大陆，以及人口

select name,continent,population from world

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

找出至少2亿人的国家

select name from world where population>=200000000

3.Give the name and the per capita GDP for those countries with a population of at least 200 million.

显示至少2亿人口的国家的名字和人均gdp

select name,gdp/population from world where population>=200000000

4.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.

显示南美大陆国家的名字和人口，以百万计。人口除以100万，得到百万人口。

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.Show the countries which have a name that includes the word ‘United’

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

select name from world where name like '%United%'

7.Two ways to be big: A country is big if it has an area of more than 3 million sq km or it has a population 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亿人以上，就是大国。
显示国家的面积大或人口多。显示姓名、人口和地区。

select name,population,area from 
world where area>=3000000 or population>=250000000

8.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.

异或(XOR)。显示面积大的国家(超过300万)或人口大的国家(超过2.5亿)，但不是两者都大的国家。显示姓名、人口和地区。

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

9.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.
展示南美大陆国家的名字和人口，以百万计，GDP以十亿计。使用ROUND函数将值显示到小数点后两位。
在南美洲，以百万计的人口和以十亿计的GDP都显示到小数点后两位。

select name,round(population/1000000,2),round(gdp/1000000000,2) 
from world where
continent='South America'

round函数的用法：round(处理的参数，参数保留的小数位数),比如round(3.4444,2)结果为3.44，舍入模式是四舍五入

10.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

round函数保留小数是负数时表示从小数点之前的位数置为0，比如：round(1444,2)结果为1400，结果也是四舍五入

11.Show the name and capital where the name and the capital have the same number of characters.

显示名称和大写字母，其中名称和大写字母有相同数量的字符。

select name,capital from world where length(name) = length(capital)

length()函数，返回指定参数的长度

12.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.

显示名称和首字母匹配的大写字母。不包括名字和首都用同一个词的国家。

select name,capital from world
where left(name,1) = left(capital,1) and name<>capital

left(a,b)函数，表示将a截取b长度的字符,返回的是截出的字符，比如left(‘123’,1)的结果是1。
<>符号表示不等于

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

找出一个国家，它的名字中有所有的元音而且没有空格。

SELECT name
   FROM world
WHERE 
(name LIKE '%a%' or name like '%A%')
and (name like '%e%' or name like '%E%')
and (name like '%i%' or name like '%I%')
and (name like '%o%' or name like '%O%')
and (name like '%u%' or name like '%U%')
  AND name NOT LIKE '% %'