Largest Population in Five Countries

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

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.

1. Start the MySQL service:

sudo service mysql start

2. Access MySQL using the sudo command:

sudo mysql

3. 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.

1. Open the getBigPopulation.sql file.
2. 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.

1. 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.

Summary

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