What is the solution to 13th part of 'select from world' tutorial on sqlzoo?

Geoffrey picture Geoffrey · May 12, 2015 · Viewed 7.6k times · Source

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.".

Answer

markus picture markus · May 12, 2015

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