本系列刷题笔记主要用以记录刷SQLZOO的过程中的思路、个人答案以及陌生的或者新的知识点。
题目来源 - SQLZOO
SQLZOO中题目中文版本与英文版本略有差异，题目以英文版为准
目录
0 SELECT basics
1 SELECT name
2 SELECT from World
0 SELECT basics(SELECT 基础)
查询表格
查询表格_world
0_1 Show the population of Germany
SELECT population
FROM world
WHERE name = 'Germany'
0_2 Show the name and the population for 'Sweden', 'Norway' and 'Denmark'.
SELECT name,
population
FROM world
WHERE name IN ('Sweden','Norway','Denmark')
0_3 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
1 SELECT name
查询表格(同Select Basics - World)
1_1 Find the country that start with Y
SELECT name
FROM world
WHERE name LIKE 'Y%'
1_2 Find the countries that end with y
SELECT name
FROM world
WHERE name LIKE '%y'
1_3 Find the countries that contain the letter x
SELECT name
FROM world
WHERE name LIKE '%x%'
1_4 Find the countries that end with land
SELECT name
FROM world
WHERE name LIKE '%land'
1_5 Find the countries that start with C and end with ia
SELECT name
FROM world
WHERE name LIKE 'C%ia'
1_6 Find the country that has oo in the name
SELECT name
FROM world
WHERE name LIKE '%oo%'
1_7 Find the countries that have three or more a in the name
SELECT name
FROM world
WHERE name LIKE '%a%a%a%''
1_8 Find the countries that have "t" as the second character.
SELECT name
FROM world
WHERE name LIKE '_t%''
ORDER BY name
1_9 Find the countries that have two "o" characters separated by two others.
SELECT name
FROM world
WHERE name LIKE '%o__o%''
1_10 Find the countries that have exactly four characters.
SELECT name
FROM world
WHERE name LIKE '%____%''
1_11 Find the country where the name is the capital city.
SELECT name
FROM world
WHERE name = capital
1_12 Find the country where the capital is the country plus "City".
SELECT name
FROM world
WHERE capital = concat(name,' City')
1_13 Find the capital and the name where the capital includes the name of the country.
SELECT capital,
name
FROM world
WHERE capital LIKE concat(name,'%')
OR capital LIKE concat('%',name)
1_14 Find the capital and the name where the capital is an extension of name of the country.
SELECT capital,
name
FROM world
WHERE capital LIKE concat(name,'_%')
1_15 Show the name and the extension where the capital is an extension of name of the country.
SELECT name,
replace(capital,name,'')
FROM world
WHERE capital LIKE concat(name,'_%')
2 SELECT from World
查询表格(同Select Basics - World)
2_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
2_3 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
2_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.
SELECT name,
population / 1000000 AS population
FROM world
WHERE continent = 'South America'
2_5 Show the name and population for France, Germany, Italy
SELECT name,
population
FROM world
WHERE name IN ('France','Germany','Italy')
2_6 Show the countries which have a name that includes the word 'United'
SELECT name
FROM world
WHERE name LIKE '%United%'
2_7 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
2_8 Show the countries that are big by area or big by population but not both. Show name, population and area.
SELECT name,
population,
area
FROM world
WHERE area > 3000000
XOR population > 250000000
2_9 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'
2_10 Show per-capita GDP for the trillion dollar countries to the nearest $1000.
SELECT name,
ROUND((gdp /(population*1000)))*1000
FROM world
WHERE gdp >= 1000000000000
2_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)
2_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
2_13 Find the country that has all the vowels 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 '% %'
