Accessing MySQL Database and Querying GNP

Introduction

In this project, you will learn how to access a MySQL database, import data, and query the Gross National Product (GNP) for all countries.

👀 Preview

MariaDB [world]> SOURCE /home/labex/project/getGNP.sql;
+----------------------------------------------+------------+
| Name                                         | GNP        |
+----------------------------------------------+------------+
| Aruba                                        |     828.00 |
| Afghanistan                                  |    5976.00 |
| Angola                                       |    6648.00 |
    ...
| Zambia                                       |    3377.00 |
| Zimbabwe                                     |    5951.00 |
+----------------------------------------------+------------+
239 rows in set (0.000 sec)

🎯 Tasks

In this project, you will learn:

• How to start the MySQL service and access the database using the sudo command without a password
• How to import data from a SQL file into the world database
• How to query the GNP attribute from the country table and display the Name and GNP for all countries

🏆 Achievements

After completing this project, you will be able to:

• Manage a MySQL database, including starting the service, accessing the database, and importing data
• Write SQL queries to retrieve specific data from a database table
• Understand how to work with the world database and the country table

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 the world database.

1. Start the MySQL service:

sudo service mysql start

2. Access MySQL without a password:

sudo mysql

3. Import the data from the world.sql file into the world database:

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

Query the Gross National Product (GNP)

In this step, you will learn how to query the Gross National Product (GNP) for all countries using data from the country table.

1. Open the getGNP.sql file and add the following query:

SELECT `Name`, `GNP` FROM country;

2. Run the getGNP.sql script in MySQL:

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

This will display the Name and GNP attributes for all countries in the country table.

MariaDB [world]> SOURCE /home/labex/project/getGNP.sql;
+----------------------------------------------+------------+
| Name                                         | GNP        |
+----------------------------------------------+------------+
| Aruba                                        |     828.00 |
| Afghanistan                                  |    5976.00 |
| Angola                                       |    6648.00 |
| Anguilla                                     |      63.20 |
| Albania                                      |    3205.00 |
| Andorra                                      |    1630.00 |
    ...
| Vietnam                                      |   21929.00 |
| Vanuatu                                      |     261.00 |
| Wallis and Futuna                            |       0.00 |
| Samoa                                        |     141.00 |
| Yemen                                        |    6041.00 |
| Yugoslavia                                   |   17000.00 |
| South Africa                                 |  116729.00 |
| Zambia                                       |    3377.00 |
| Zimbabwe                                     |    5951.00 |
+----------------------------------------------+------------+
239 rows in set (0.000 sec)

Summary

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