Summary

Summary of SQL Querying in HackerRank: African Cities

In this session, the speaker discusses how to query African cities using SQL within HackerRank. Here’s a concise breakdown of the process:

Objective:
- Query the names of all cities located in Africa.
Tables Involved:
- Two tables: city and country.
- They are linked via city.countrycode and country.code.
Potential Ambiguity:
- Both the city and country tables contain a column named name, so it's important to specify city.name to avoid ambiguity.
Filtering for Africa:
- Since there's no continent column in the city table, the query must reference the country table to filter by the continent.
Joining Tables:
- An INNER JOIN is used to combine the two tables based on matching country.code with city.countrycode.
SQL Query Structure:
- Start with the SELECT statement to get the city names.
- Use an INNER JOIN followed by the ON clause to specify the condition for joining.
- The SQL query can be summarized as:
```
SELECT city.name 
FROM city 
INNER JOIN country 
ON country.code = city.countrycode 
WHERE country.continent = 'Africa';
```
Reference Tool:
- The speaker recommends using a Venn diagram to visualize different types of SQL joins when necessary.
Execution:
- After constructing the query, the speaker plans to submit the code for evaluation.

This session provides valuable insights into SQL operations, particularly focusing on joins and handling ambiguous table columns.

Video Transcript

Hey everyone, I will do African cities from the basic join section of SQL in HackerRank. So we're given these two tables city and country and they match by the city.country code with the country.code columns. And we're asked to query the name of all cities where the continent is Africa. Now I can do here first if we start select and we're going to do name from city, but we got to be careful. Does name appear in the other column? It does. So country.name appears. So that's ambiguous. Two columns with the same name, right? Two different tables. So we have to say city.name. That's the name of the table to differentiate with the country.name. Now with that out of the way, we have to do a filter here because we want only the ones whose continent is Africa. Now we don't have any column about continent in the city table. So we have to refer to the country table. So we have to combine these two tables and match the records based on the country code. So here the where would be the continent, the Africa. Now continent is a column that only appears in the country table. Therefore we don't have to explicitly disambiguate with a country.continent there. Now select city.name from city. That's fine, but we need to get continent from the other tables. So we have to do a join here. What kind of join are we going to do? Let me show you the Venn diagram that I like to reference a lot for SQL joins. Whenever you need to do it and you don't know which one, refer to this diagram. Now we're going to do the one in the center here called an inner join where we have two tables A and B and we're going to have records that both appear in A and B that is the intersection between these two tables. So we're going to match these two according to city.countrycode and country.code. So here we're going to say inner join, the other table is country, okay? On, you have to say on, what's the condition to match the records from one table with the other? We're going to say country.code, that's the column, has to be equal to the city.countrycode. So these two values have to match so that we have the two combined together into a single row. Now let's submit the code and see what we get.