Query Population Data for 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

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`"]) sql(("`SQL`")) -.-> sql/AdvancedDataOperationsGroup(["`Advanced Data Operations`"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/source("`External Code Execution`") sql/BasicSQLCommandsGroup -.-> sql/select("`SELECT statements`") sql/DataManipulationandQueryingGroup -.-> sql/in("`IN clause`") mysql/BasicKeywordsandStatementsGroup -.-> mysql/select("`Data Retrieval`") sql/AdvancedDataOperationsGroup -.-> sql/string_functions("`String functions`") sql/AdvancedDataOperationsGroup -.-> sql/numeric_functions("`Numeric functions`") subgraph Lab Skills mysql/source -.-> lab-301388{{"`Query Population of All Countries`"}} sql/select -.-> lab-301388{{"`Query Population of All Countries`"}} sql/in -.-> lab-301388{{"`Query Population of All Countries`"}} mysql/select -.-> lab-301388{{"`Query Population of All Countries`"}} sql/string_functions -.-> lab-301388{{"`Query Population of All Countries`"}} sql/numeric_functions -.-> lab-301388{{"`Query Population of All Countries`"}} end

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.

Open a terminal and start the MySQL service:

sudo service mysql start

Access MySQL using the sudo command without a password:

sudo mysql

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.

Open the getPopulation.sql file.
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.

Save the getPopulation.sql file.

Run the Script

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

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.

✨ Check Solution and Practice