Country GNP and Population Query

Introduction

In this project, you will learn how to query the GNP (Gross National Product) and population of countries with a higher GNP than France using a single row subquery operation on the country table in a MySQL database.

👀 Preview

MariaDB [world]> SOURCE /home/labex/project/getGNP.sql;
+---------------+------------+------------+
| Name          | GNP        | Population |
+---------------+------------+------------+
| Germany       | 2133367.00 |   82164700 |
| Japan         | 3787042.00 |  126714000 |
| United States | 8510700.00 |  278357000 |
+---------------+------------+------------+
3 rows in set (0.001 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 a MySQL database
• How to write a SQL query to retrieve the name, GNP, and population of countries with a higher GNP than France

🏆 Achievements

After completing this project, you will be able to:

• Understand how to use a single row subquery in SQL to filter data based on a condition
• Demonstrate the ability to import data into a MySQL database and perform complex queries
• Apply your SQL knowledge to solve real-world data analysis problems

Skills Graph

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 database 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 and switch to the world database:

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

Query the GNP and Population of Countries With a Higher GNP Than France

In this step, you will learn how to write a SQL query to retrieve the name, GNP, and population of countries with a higher GNP than France.

1. Open the getGNP.sql file
2. In the getGNP.sql file, add the following SQL query:

SELECT Name, GNP, Population
FROM country
WHERE GNP > (SELECT GNP FROM country WHERE Name = 'France');

This query first retrieves the GNP of France using a subquery, and then selects the name, GNP, and population of countries where the GNP is higher than France's GNP.

Run the SQL Script

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

1. In the MySQL prompt, run the SQL script:

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

This will execute the SQL query in the getGNP.sql file and display the results in the MySQL prompt.

The output should look similar to the following:

+---------------+------------+------------+
| Name          | GNP        | Population |
+---------------+------------+------------+
| Germany       | 2133367.00 |   82164700 |
| Japan         | 3787042.00 |  126714000 |
| United States | 8510700.00 |  278357000 |
+---------------+------------+------------+
3 rows in set (0.001 sec)

Congratulations! You have successfully completed the project.

Summary

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