Summary

SQL Population Census - Summary

In this tutorial, we will perform a population census using SQL by joining the city and country tables available on HackerRank. The two tables are linked through the country code, where city.country_code matches country.code. Our goal is to query the sum of populations for all cities located in the continent of Asia.

Steps to Create the Query

Identify Tables: The city table contains population data, but does not have continent information. We will need to join it with the country table, which has the continent data.
Join Tables: We will use an inner join, which selects records that have matching values in both tables. The inner join will allow us to filter the cities based on their corresponding countries.
Filter Records: To focus only on the cities in Asia, we will filter the records from the country table by where the continent is 'Asia'.
Select and Sum: We will use the SUM function to calculate the total population from the city table, ensuring we specify city.population to avoid ambiguity with any similar column names in the country table.
Final Query: The completed SQL query will look something like this:

SELECT SUM(city.population)
FROM city
INNER JOIN country ON country.code = city.country_code
WHERE country.continent = 'Asia';

After executing the query, we will obtain the total population of all cities located in Asia.

Video Transcript

Hey everyone, I will do population census on the section basic join of SQL in hacker rank. We're given the table city and country as you can see here. So these two tables are linked by the country code as you can see city dot country code will have the matching value in country dot code. Okay, so that's what we're going to use to join these tables. So it asks query the sum of all the populations all cities where continent is Asia. So we have the city stable but we do not know where the continent for that city is. So that's why you have to take the matching record from city with the country table. And for the country table we can filter by the continent that's Asia so that we can exclude all the other cities. So let's get started. First I'll do select and star from city. Now we need to get the population and then the sum. So here I'm going to say population. And then I'm going to take the sum of that. But notice if I do it this way, that's just going to take the sum of all the population for all the cities. But I need to filter so I'm going to add a where. Right. So the continent has to be Asia so continent equals Asia. But city table does not have that column. So we have to take city and match with the records in the other table country. Now if you want to do what kind of join we're going to do. We're going to do the very first one in the middle of this Venn diagram here that I like to reference a lot. And that's called the inner join where we take the intersection of the two tables that is the records both appear and table A and table B. So let's go back and I'm going to do inner join country. That's the name of the other table on now country dot code remember has to match so equals the city table dot country code column. So this is the condition to match the lines. Now because of that I know that continent is a column that only appears in country that's not ambiguous name. However, the population is a name that appears for the city dot population column and country dot population column. So to disambiguate that I have to say explicitly city dot population here. So we are referring to the column population from the city table not the country one. Okay. So let's submit that. And there it is.