SQLZOO：SELECT from WORLD

数据表：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
...

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

SELECT name, continent, population FROM world

Q2 Large Countries

How to use WHERE to filter records. Show the name for the countries that have a population of at least 200 million. 

SELECT name FROM world
WHERE population >=200000000

语法结构

SELECT + FROM + WHERE + GROUP BY + HAVING + ORDER BY + LIMIT

Q3 Per capita GDP

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

SELECT name,gdp/population FROM world 
WHERE population >=200000000

Q4 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

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

Q5 France, Germany, Italy

Show the name and population for France, Germany, Italy

SELECT name,population FROM world 
WHERE name='France' OR name='Germany' OR name='Italy'

AND OR

AND：两条都符合才显示

OR：任意符合一条就显示

AND 优先级大于 OR ，可以使用括号来结合使用 AND OR 

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

IN

允许在 WHERE 子句中规定多个值

SQL IN 语法：

SELECT column1, column2, ...
FROM table_name
WHERE column IN (value1, value2, ...);

Q6 United

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

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

模糊查询 LIKE

% ：表示任意 0 或多个字符。可匹配任意类型和长度的字符，若是中文使用%%表示

_ ： 表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句

Q7 Two ways to be big

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.

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

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

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

相当于： 

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

XOR 异或

获得仅满足一条的数据

如果A为真，则查出不满足B条件数据；如果A为假，则查出满足B条件数据

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

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

ROUND(x,n)

x保留n位小数，n可为负数

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

SELECT name,ROUND(gdp/population/1000)*1000 FROM world 
WHERE gdp>=1000000000000

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

Q11 Name and capital have the same length

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

You can use the LENGTH function to find the number of characters in a string

Q12 Matching name and capital

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(x,n)

x保留前n位字符

< >

You can use <> as the NOT EQUALS operator

Q13 All the vowels

Find the country that has all the vowels (a e i o u) and no spaces in its name.

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