Top 5 Countries by Land Area

Introduction

In this project, you will learn how to retrieve the top 5 countries by land area from the MySQL country table. You will learn how to access MySQL, import data, and write SQL queries to retrieve the desired information.

👀 Preview

MariaDB [world]> SOURCE /home/labex/project/getBigArea.sql;
+--------------------+-------------+
| Name               | SurfaceArea |
+--------------------+-------------+
| Russian Federation | 17075400.00 |
| Antarctica         | 13120000.00 |
| Canada             |  9970610.00 |
| China              |  9572900.00 |
| United States      |  9363520.00 |
+--------------------+-------------+
5 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 data from a SQL file into MySQL
• How to write a SQL query to retrieve the top 5 countries by land area

🏆 Achievements

After completing this project, you will be able to:

• Understand how to access and interact with a MySQL database
• Write SQL queries to retrieve specific data from a database
• Apply SQL techniques like ordering and limiting results
• Gain practical experience in working with a real-world database

Access MySQL and Import 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. Open a terminal and start the MySQL service:

sudo service mysql start

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

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;

Retrieve the Top 5 Countries by Land Area

In this step, you will learn how to retrieve the Name and SurfaceArea columns for the five countries with the largest land area, as listed in the country table.

1. Open the getBigArea.sql file and add the following SQL query:

SELECT `Name`, `SurfaceArea`
FROM country
ORDER BY SurfaceArea DESC
LIMIT 5;

This query will select the Name and SurfaceArea columns from the country table, order the results by SurfaceArea in descending order, and limit the output to the top 5 rows.

2. Save the getBigArea.sql file.
3. Run the SQL script in MySQL:

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

This will execute the SQL script and display the top 5 countries by land area.

+--------------------+-------------+
| Name               | SurfaceArea |
+--------------------+-------------+
| Russian Federation | 17075400.00 |
| Antarctica         | 13120000.00 |
| Canada             |  9970610.00 |
| China              |  9572900.00 |
| United States      |  9363520.00 |
+--------------------+-------------+
5 rows in set (0.001 sec)

Summary

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