SQLZOO 中文第二套 （困难版）

11.Show the name - but substitute Australasia for Oceania - for countries beginning with N.

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

12.Show the name and the continent - but substitute Eurasia for Europe and Asia.

SELECT name, 
CASE WHEN continent = 'Europe' OR continent = 'Asia' THEN 'Eurasia'
WHEN continent = 'North America' OR continent = 'South America' OR continent = 'Caribbean' THEN 'America'
ELSE continent
END
FROM world WHERE name LIKE 'A%' OR name LIKE 'B%'

3.Show the name, the original continent and the new continent of all countries.

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' AND name NOT LIKE 'B%' THEN 'South America'
ELSE continent
END
FROM world ORDER BY name