Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

like image 649
Geoffrey Avatar asked May 12 '15 15:05

Geoffrey


2 Answers

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
like image 151
markus Avatar answered Sep 23 '22 13:09

markus


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.

like image 42
clesiemo3 Avatar answered Sep 26 '22 13:09

clesiemo3