SQL Query | GNP and Population of Countries with Higher GNP than France

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

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL mysql(("MySQL")) -.-> mysql/BasicKeywordsandStatementsGroup(["Basic Keywords and Statements"]) sql(("SQL")) -.-> sql/DataManipulationandQueryingGroup(["Data Manipulation and Querying"]) sql(("SQL")) -.-> sql/BasicSQLCommandsGroup(["Basic SQL Commands"]) 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/where("WHERE clause") sql/DataManipulationandQueryingGroup -.-> sql/group_by("GROUP BY clause") sql/DataManipulationandQueryingGroup -.-> sql/subqueries("Subqueries") subgraph Lab Skills mysql/use_database -.-> lab-301317{{"Country GNP and Population Query"}} sql/select -.-> lab-301317{{"Country GNP and Population Query"}} sql/insert -.-> lab-301317{{"Country GNP and Population Query"}} mysql/select -.-> lab-301317{{"Country GNP and Population Query"}} mysql/source -.-> lab-301317{{"Country GNP and Population Query"}} sql/create_table -.-> lab-301317{{"Country GNP and Population Query"}} sql/where -.-> lab-301317{{"Country GNP and Population Query"}} sql/group_by -.-> lab-301317{{"Country GNP and Population Query"}} sql/subqueries -.-> lab-301317{{"Country GNP and Population Query"}} 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 database service:

sudo service mysql start

Access MySQL using the sudo command:

sudo mysql

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.

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

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.

✨ Check Solution and Practice

Summary

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