Hello, friends! In this article, I’m going to share a solution for a HackerRank SQL problem with you. The problem focuses on querying the names of all cities located in Africa, based on the CITY and COUNTRY tables. / African Cities Solution

If you’re interested in more solutions, be sure to check out the Population Census Solution.

Problem Statement

Task: Given the CITY and COUNTRY tables, write a query to retrieve the names of all cities where the CONTINENT is ‘Africa’.

Note: The columns CITY.CountryCode and COUNTRY.Code are the matching keys.

Input Format

The CITY and COUNTRY tables are structured as follows:

CITY Table:

COUNTRY Table:

Solution – African Cities

Here’s the MySQL query to retrieve the names of all cities in Africa:

SELECT city.name 
FROM city 
JOIN country 
ON city.countrycode = country.code 
WHERE country.continent = 'Africa';

Explanation of the Code

JOIN: We perform an inner join between the CITY and COUNTRY tables, using the CITY.CountryCode and COUNTRY.Code columns as the matching keys.
WHERE clause: The WHERE clause ensures that only cities from countries in the continent of Africa are selected by filtering on country.continent = ‘Africa’.
Result: The query will return the names of all cities located in African countries.

Disclaimer

The problems above are generated by HackerRank, but the solutions are provided by NYANDER.COM. All HackerRank SQL solutions shared here are for educational and learning purposes only.