sqlzoo练习系列(三)——SELECT from World
【Tips】
- sqlzoo官网链接:
- 每个系列开头介绍所用表及其信息
- 每道题均测试通过,含有题目描述、代码和结果
- 题目为自己简写,最好在官网查看具体题目
- 部分测试结果不完整,仅为一部分截图
SELECT from World练习链接:
- name:国家名称
- continent:洲
- area:面积
- population:人口
- gdp:国内生产总值
1.简单命令
SELECT name, continent, population FROM world
SELECT name FROM world WHERE population>200000000
SELECT name,gdp/population FROM world WHERE population > 200000000
SELECT name,population/1000000 FROM world WHERE continent='South America'
SELECT name,population FROM world WHERE name IN ('France', 'Germany', 'Italy')
SELECT name FROM world WHERE name LIKE '%United%'
SELECT name,population,area FROM world WHERE area>3000000 OR population > 250000000
SELECT name,population,area FROM world WHERE (area>3000000 OR population > 250000000) AND NOT (area>3000000 AND population > 250000000)
SELECT name,ROUND(population/1000000,2),ROUND(gdp/1000000000,2) FROM world WHERE continent='South America'
SELECT name, ROUND(gdp/population/1000)*1000 FROM world WHERE gdp>1000000000000
SELECT name, CASE WHEN continent = 'Oceania' THEN 'Australasia' ELSE continent END FROM world WHERE name LIKE 'N%'
SELECT name, CASE WHEN continent='Europe' OR continent='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%'
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 ORDER BY name