Query Population of All Countries

Introduction

In this project, you will learn how to access a MySQL database, import data, and retrieve population data for all countries. This project will help you develop skills in working with relational databases and SQL queries.

👀 Preview

MariaDB [world]> SOURCE /home/labex/project/getPopulation.sql;
+--------------------+
| Population / 10000 |
+--------------------+
| 10.3000            |
| 2272.0000          |
| 1287.8000          |
| 0.8000             |
| 340.1200           |
| 7.8000             |
| 21.7000            |
    ...
| 1064.0000          |
| 4037.7000          |
| 916.9000           |
| 1166.9000          |
+--------------------+
239 rows in set (0.001 sec)

🎯 Tasks

In this project, you will learn:

• How to access MySQL using the sudo command without a password
• How to import a database into MySQL
• How to write and execute SQL queries to retrieve data from a database

🏆 Achievements

After completing this project, you will be able to:

• Manage a MySQL database, including importing and querying data
• Write SQL queries to retrieve specific data from a database
• Understand how to work with population data and express it in units of ten thousand people

Access MySQL and Import the Database

In this step, you will learn how to access MySQL using the sudo command without a password, and import the world.sql database into MySQL.

1. Open a terminal and start the MySQL service:

sudo service mysql start

2. Access MySQL using the sudo command without a password:

sudo mysql

3. Import the world.sql database into MySQL:

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

This will import the world database into your MySQL environment.

Retrieve the Population Data

In this step, you will learn how to retrieve the population data for all countries from the country table and express it in units of ten thousand people.

1. Open the getPopulation.sql file.
2. Add the following SQL query to the getPopulation.sql file:

SELECT CONCAT(ROUND(Population/10000, 4)) AS `Population / 10000`
FROM country;

This query will retrieve the population data for all countries from the country table and express it in units of ten thousand people.

3. Save the getPopulation.sql file.

Run the Script

In this step, you will learn how to run the getPopulation.sql script in MySQL.

1. In the MySQL prompt, run the getPopulation.sql script:

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

This will execute the SQL query in the getPopulation.sql file and display the population data for all countries in units of ten thousand people.

The output should look similar to the following:

+--------------------+
| Population / 10000 |
+--------------------+
| 10.3000            |
| 2272.0000          |
| 1287.8000          |
| 0.8000             |
| 340.1200           |
| 7.8000             |
| 21.7000            |
    ...
| 1064.0000          |
| 4037.7000          |
| 916.9000           |
| 1166.9000          |
+--------------------+
239 rows in set (0.001 sec)

Congratulations! You have successfully completed the project of querying the population of all countries using the world database.

Summary

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