SQLZOO练习题答案参考（全）

自学SQL将近一年，比较有名的SQL题库都尝试刷过，目前SQLZOO、XUESQL、牛客、Hackerrank初阶中阶、炼码基本刷完，Hackerrank高阶、Leetcode、SQL_intern仍然在刷，题目越来越难，不过水平也在逐渐提高啦！

一边刷高阶题，一边还会回头复盘基础，尝试多种解法，因此会不定时分享刷的题库的参考解法。注：SQL没有严格的格式要求，是我个人比较习惯缩进和大小写。

首先从SQLZOO开始。

SQLZOO有增删改查的讲解，也有练习题，涉及内容很基础，是个很考虑新手的题库了。

适用人群：刚入门小白、巩固基础准备进阶

优点：1.既可以学习，也可以刷题，相对全面

           2.练习题中也包含讲解，情境化设置，循序渐进，难度递增，适合新手培养逻辑

           3.某些部分会出现难题，对一个内容可以锻炼地比较深入

缺点：1.练习题的情境化设置导致有些题目要求查询的字段表述不清，会因为缺少答案里的字段而报错

           2.部分题目有问题，莫名其妙报错

           3.练习题涉及内容不全面，增删改没有对应的练习题

解法参考如下：

SELECT basics部分

-- Modify it to show the population of Germany
SELECT population
FROM world
WHERE name = 'Germany';

-- Show the name and the population for 'Sweden', 'Norway' and 'Denmark'
SELECT 
	name,
 	population
FROM world
WHERE name in ('Sweden','Norway','Denmark');

-- 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部分

-- Show the name, continent and population of all countries
SELECT
	name,
	continent,
	population
FROM world;

-- Show the name for the countries that have a population of at least 200 million
SELECT name
FROM world
WHERE population >= 200000000;

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

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

-- Show the name and population for France, Germany, Italy
SELECT 
	name,
    population
FROM world
WHERE name in ('France','Germany','Italy');

-- Show the countries which have a name that includes the word 'United'
SELECT name
FROM world
WHERE name like '%United%';

-- Show the countries that are big by area or big by population. Show name, population and area
-- 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
SELECT
	name,
	population,
	area
FROM world
WHERE area >= 3000000 or population >= 250000000;

-- 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 and population < 250000000) or
      (area < 3000000 and population >= 250000000);

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

-- Show per-capita GDP for the trillion dollar countries to the nearest $1000
SELECT
	name,
	round(gdp/population,-3)
FROM world
WHERE gdp >= 1000000000000;

-- Show the name and capital where the name and the capital have the same number of characters
SELECT 
	name,
	capital
FROM world
WHERE len(name)=len(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;

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

SELECT from nobel部分

-- Change the query shown so that it displays Nobel prizes for 1950
SELECT *
FROM nobel
WHERE yr = '1950';

-- Show who won the 1962 prize for literature
SELECT winner
FROM nobel
WHERE yr = '1962' 
  and subject = 'Literature';

-- Show the