Learn SQL: Discover Top 5 Countries by Population

Introduction

In this project, you will learn how to query the population data in the city table and retrieve the top 5 countries or regions with the greatest population.

👀 Preview

MariaDB [world]> SOURCE /home/labex/project/getBigPopulation.sql;
+-------------+---------------+
| CountryCode | SumPopulation |
+-------------+---------------+
| CHN         |     175953614 |
| IND         |     123298526 |
| BRA         |      85876862 |
| USA         |      78625774 |
| JPN         |      77965107 |
+-------------+---------------+
5 rows in set (0.007 sec)

🎯 Tasks

In this project, you will learn:

How to access MySQL using the sudo command without any password
How to import data from a SQL file into MySQL
How to write an SQL query to fetch the top 5 countries or regions with the greatest population
How to run the SQL query and display the results

🏆 Achievements

After completing this project, you will be able to:

Understand how to work with MySQL databases
Write SQL queries to retrieve data from a database
Analyze and interpret population data from the city table

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL mysql(("MySQL")) -.-> mysql/BasicKeywordsandStatementsGroup(["Basic Keywords and Statements"]) sql(("SQL")) -.-> sql/BasicSQLCommandsGroup(["Basic SQL Commands"]) sql(("SQL")) -.-> sql/DataManipulationandQueryingGroup(["Data Manipulation and Querying"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/use_database("Database Selection") sql/BasicSQLCommandsGroup -.-> sql/select("SELECT statements") sql/BasicSQLCommandsGroup -.-> sql/insert("INSERT INTO statements") mysql/BasicKeywordsandStatementsGroup -.-> mysql/select("Data Retrieval") mysql/BasicKeywordsandStatementsGroup -.-> mysql/source("External Code Execution") sql/BasicSQLCommandsGroup -.-> sql/create_table("CREATE TABLE statements") sql/DataManipulationandQueryingGroup -.-> sql/order_by("ORDER BY clause") sql/DataManipulationandQueryingGroup -.-> sql/group_by("GROUP BY clause") subgraph Lab Skills mysql/use_database -.-> lab-301352{{"Largest Population in Five Countries"}} sql/select -.-> lab-301352{{"Largest Population in Five Countries"}} sql/insert -.-> lab-301352{{"Largest Population in Five Countries"}} mysql/select -.-> lab-301352{{"Largest Population in Five Countries"}} mysql/source -.-> lab-301352{{"Largest Population in Five Countries"}} sql/create_table -.-> lab-301352{{"Largest Population in Five Countries"}} sql/order_by -.-> lab-301352{{"Largest Population in Five Countries"}} sql/group_by -.-> lab-301352{{"Largest Population in Five Countries"}} end

Access MySQL and Import the Data

In this step, you will learn how to access MySQL using the sudo command without any password, and import the data from /home/labex/project/world.sql into MySQL.

Start the MySQL service:

sudo service mysql start

Access MySQL using the sudo command:

sudo mysql

Import the data from /home/labex/project/world.sql into MySQL:

MariaDB [(none)]> SOURCE /home/labex/project/world.sql;

Write the SQL Query

In this step, you will write the SQL query to fetch the top 5 countries or regions with the greatest population from the city table, and retrieve the CountryCode and SumPopulation columns.

Open the getBigPopulation.sql file.
Add the following SQL query to the file:

SELECT CountryCode, SUM(Population) AS SumPopulation
FROM city
GROUP BY CountryCode
ORDER BY SumPopulation DESC
LIMIT 5;

This query will:

Select the CountryCode and the sum of the Population column (as SumPopulation) from the city table.
Group the results by CountryCode.
Order the results by SumPopulation in descending order.
Limit the results to the top 5 rows.

Run the SQL Query

In this step, you will run the SQL query saved in the getBigPopulation.sql file.

In the MySQL prompt, run the following command to execute the SQL script:

MariaDB [world]> SOURCE /home/labex/project/getBigPopulation.sql;

This will execute the SQL query in the getBigPopulation.sql file and display the results:

+-------------+---------------+
| CountryCode | SumPopulation |
+-------------+---------------+
| CHN         |     175953614 |
| IND         |     123298526 |
| BRA         |      85876862 |
| USA         |      78625774 |
| JPN         |      77965107 |
+-------------+---------------+
5 rows in set (0.007 sec)

The output shows the top 5 countries or regions with the greatest population, along with their CountryCode and the SumPopulation.

✨ Check Solution and Practice

Summary

Congratulations! You have completed this project. You can practice more labs in LabEx to improve your skills.