SQLZOO练习题

写在前面

这篇博客主要用于记录SQLZOO的练习题，一些过于简单的题目只是为了目录完整，由于SQL不区分大小写，所以写的时候不会特意去区分它，语句中诸如‘AS’之类的也可能会省略，所以格式强迫症的小伙伴可以去找找其它答案。另外由于SQLZOO中文版中一些题目的检测有bug，所以觉得自己答案是正确的但过不了测的朋友可以去英文原版处检测。
最后附上SQLZOO网站中文习题的连接 https://sqlzoo.net/wiki/SQL_Tutorial/zh

0 SELECT 基础

1

SELECT population FROM world
  WHERE name = 'Germany'

2

SELECT name, gdp/population FROM world
  WHERE area > 5000000

3

SELECT name, population FROM world
  WHERE name IN ('Ireland', 'Iceland', 'Denmark');

4

SELECT name, area FROM world
  WHERE area BETWEEN 200000 AND 250000

1 SELECT name

文字样式匹配查询

1

SELECT name FROM world
  WHERE name LIKE 'Y%'

2

SELECT name FROM world
  WHERE name LIKE '%Y'

3

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

4

SELECT name FROM world
  WHERE name LIKE '%land'

5

SELECT name FROM world
  WHERE name LIKE 'C%ia'

6

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

7

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

8

SELECT name FROM world
 WHERE name LIKE '_t%'
ORDER BY name

9

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

10

SELECT name FROM world
 WHERE name LIKE '____'

11

SELECT name
  FROM world
 WHERE name = capital

12

SELECT name
  FROM world
 WHERE concat(name,' City')=capital

13.这道题翻译不好，应该是列出所有首都及其国家，要求首都中包含其国家名字。

select capital,name
  from world
 where capital like concat('%',name,'%')

14.翻译还是不好，应该是列出国家名字及其首都（否则表列会反）

select name,capital
  from world
 where capital like concat(name,'%_%')

15.这道题翻译也挺离谱的，应该是列出国家名字和其首都的延伸词，其首都的名字是国家名+延伸词的形式。

select name,replace(capital,name,'') as ext
  from world
 where capital like concat(name,'%_%')

测验SELECT

答案如果是第一个就是A，第二个就是B，······第五个就是E，依次顺延

. C
. E
. E
. C
. C
. C
. C

2 SELECT from World

对表格World查询全球各国的资料

1

SELECT name, continent, population FROM world

2

SELECT name
 FROM world
WHERE population>200000000

3

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

4

select name,population/1000000
  from world
 where continent='South America'

5

select name,population
  from world
 where name in ('France','Germany','Italy')

6

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

7

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

8

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

9

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

10

select name,round(GDP/population,-3)
  from world
 where GDP>=1000000000000

11

SELECT name,
       CASE WHEN continent='Oceania' THEN 'Australasia'
            ELSE continent END
  FROM world
 WHERE name LIKE 'N%'

12

select name,
       case when continent in ('Europe','Asia') then 'Eurasia'
            when continent in ('North America','South America','Caribbean') then 'America'
            else continent end
  from world
 where name like 'A%' or name like 'B%'

13 这道题的要求是改变表格中国家所在的洲名，将‘Oceania’变为‘Australasia’，将‘Eurasia’和‘Turkey’变为‘Europe/Asia’；将‘Caribbean’且国家名开头为B的变为‘North America’其余的‘Caribbean’国家变为‘South America’，然后列出他们的国家名字，原始大洲名称和新的大洲名称。

select name,continent,
       case when continent='Oceania' then 'Australasia'
            when continent in ('Eurasia','Turkey') then 'Europe/Asia'
            when continent='Caribbean' and name like 'B%' then 'North America'
            when continent='Caribbean' then 'South America'
       else continent end
  from world

测验World表格

. E
. D
. B
. D
. B
. D
. C

3 SELECT from Nobel

利用诺贝尔奖的表格，再练习基本SQL功能

1

SELECT yr, subject, winner
  FROM nobel
 WHERE yr = 1950

2

SELECT winner
  FROM nobel
 WHERE yr = 1962
   AND subject = 'Literature'

3

select yr,subject
  from nobel
 where winner='Albert Einstein'

4

select winner
  from nobel
 where yr>=2000 and subject='Peace'

5

select * 
  from nobel
 where yr between 1980 and 1989
   and subject='Literature'

6

SELECT * FROM nobel
 WHERE winner IN ('Theodore Roosevelt',
                  'Woodrow Wilson',
                  'Jimmy Carter')

7

select winner