[SQL] [Codewars] 7Kyu - Countries Capitals for Trivia Night (SQL for Beginners #6)

 

Codewars: Achieve mastery through coding challenge

문제

Your friends told you that if you keep coding on your computer, you are going to hurt your eyes. They suggested that you go with them to trivia night at the local club.

Once you arrive at the club, you realize the true motive behind your friends' invitation. They know that you are a computer nerd, and they want you to query the countries table and get the answers to the trivia questions.

Schema of the countries table:

 

• country (String)
• capital (String)
• continent (String)

The first question: from the African countries that start with the character E, get the names of their capitals ordered alphabetically.

 

• You should only give the names of the capitals. Any additional information is just noise
• If you get more than 3, you will be kicked out, for being too smart
• Also, this database is crowd-sourced, so sometimes Africa is written Africa and in other times Afrika.

코드

SELECT capital
FROM countries
	WHERE country LIKE 'E%' AND continent IN ('Africa', 'Afrika') 
ORDER BY capital
LIMIT 3;

 

설명

조건은 세 가지다. 먼저 E로 시작하는 아프리카 나라일 것, 수도를 알파벳 순서로 정렬할 것, 세 개만 표시할 것.

첫 번째 조건은 WHERE 절로 쉽게 풀이가 가능하다. 다만, 아프리카를 Africa, Afrika로 이중으로 표기하고 있다는 세부 조건도 지켜줘야 한다. 두 번째 정렬은 ORDER BY 절로, 세 번째 조건은 LIMIT로 쉽게 풀 수 있다.