SQLZOO习题

https://sqlzoo.net/wiki/SQLZOO:SELECT_from_WORLD_Tutorial/zh

--第一题
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 T1
WHERE (T1.area > 3000000 OR T1.population > 250000000)
AND NOT EXISTS (
    SELECT *
    FROM WORLD T2
    WHERE T1.area > 3000000 
    AND T1.population > 250000000)

--第九题
SELECT name,ROUND(population/1000000,2),ROUND(GDP/1000000000,2)
FROM WORLD
WHERE continent='South America'

--第十题
SELECT name,ROUND((GDP/population)/1000,0)*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 IN ( 'North America', 'South America', 'Caribbean') THEN 'America'
     WHEN continent IN ( 'Europe', 'Asia') THEN 'Eurasia'
ELSE continent END
FROM WORLD
WHERE name LIKE 'A%'
OR name LIKE 'B%'

--第十三题
SELECT name, continent original_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' AND name NOT LIKE 'B%' THEN 'South America'
     ELSE continent
END AS new_continent
FROM WORLD