SQLZOO练习1

练习内容：

1. SELECT关键字
2. ROUND函数
3. LEFT函数
   

ROUND函数

ROUND(x,n):四舍五入，x为四舍五入的数，n为位数
（1）位数 = 0，则四舍五入到整数
（2）位数 > 0，则四舍五入到指定的小数位
（3）位数 < 0，则在小数点左侧进行四舍五入

题目：
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.
Millions and billions
Divide by 1000000 (6 zeros) for millions. Divide by 1000000000 (9 zeros) for billions.

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

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,1) = LEFT(capital,1) 
 AND name <> capital

（不等也可用‘!=’）

通配符

题目：
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 '% %'