The problem statement is:
Put the continents right...
- Oceania becomes Australasia
- Countries in Eurasia and Turkey go to Europe/Asia
- Caribbean islands starting with 'B' go to North America, other Caribbean islands go to South America
Show the name, the original continent and the new continent of all countries.
My solution:
SELECT name, continent,
CASE WHEN continent='Oceania' THEN 'Australasia'
WHEN continent IN ('Europe', 'Asia') THEN 'Europe/Asia'
WHEN name='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
The result I get from sqlzoo is "Wrong answer. Some of the data is incorrect.".
This works for me. Don't ask me why I have to use the ORDER BY (didn't work without it).
SELECT name, continent,
CASE WHEN continent='Oceania' THEN 'Australasia'
WHEN continent = 'Eurasia' THEN 'Europe/Asia'
WHEN name='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
Looks to be a bug in their system unless I'm reading the question wrong:
SELECT name, continent,
CASE WHEN continent='Oceania' THEN 'Australasia'
WHEN continent IN ('Eurasia') THEN 'Europe/Asia'
WHEN name='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
If you add in "order by name" it gives a correct answer with the above query. However, if you do not include the order by it marks it as incorrect. As to why I am not sure.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With