Mastering MySQL: Retrieve Caribbean Country Codes

Introduction

In this project, you will learn how to retrieve the countries and their corresponding codes for the Caribbean region from the world database. This project will help you understand how to access MySQL, import data, and execute SQL queries to extract specific information from a database.

👀 Preview

+------+----------------------------------+
| Code | Name                             |
+------+----------------------------------+
| ABW  | Aruba                            |
| AIA  | Anguilla                         |
| ANT  | Netherlands Antilles             |
    ...
| VGB  | Virgin Islands, British          |
| VIR  | Virgin Islands, U.S.             |
+------+----------------------------------+
24 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 the MySQL database
How to switch to a specific database and execute SQL queries
How to retrieve data from a table based on a specific condition

🏆 Achievements

After completing this project, you will be able to:

Understand the basic operations of accessing and managing a MySQL database
Write SQL queries to extract data from a database based on specific criteria
Apply your knowledge of SQL to solve real-world data retrieval problems

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/DatabaseManagementandOptimizationGroup(["`Database Management and Optimization`"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/source("`External Code Execution`") sql/BasicSQLCommandsGroup -.-> sql/select("`SELECT statements`") sql/DataManipulationandQueryingGroup -.-> sql/where("`WHERE clause`") sql/DataManipulationandQueryingGroup -.-> sql/in("`IN clause`") mysql/BasicKeywordsandStatementsGroup -.-> mysql/select("`Data Retrieval`") sql/DatabaseManagementandOptimizationGroup -.-> sql/using_indexes("`Using Indexes`") subgraph Lab Skills mysql/source -.-> lab-301311{{"`Countries and Codes in Region`"}} sql/select -.-> lab-301311{{"`Countries and Codes in Region`"}} sql/where -.-> lab-301311{{"`Countries and Codes in Region`"}} sql/in -.-> lab-301311{{"`Countries and Codes in Region`"}} mysql/select -.-> lab-301311{{"`Countries and Codes in Region`"}} sql/using_indexes -.-> lab-301311{{"`Countries and Codes in Region`"}} end

Access MySQL and Import the World Database

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.

Open a terminal and run the following command to start the MySQL service:

sudo service mysql start

Now, access the MySQL prompt using the sudo command without any password:

sudo mysql

Once you are in the MySQL prompt, import the world.sql file into the MySQL database:

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

This will import the data from the world.sql file into the MySQL database.

Now, you are ready to move on to the next step.

Retrieve the Countries and Codes for the Caribbean Region

In this step, you will learn how to retrieve the codes and countries for the Caribbean region from the country table.

Open the getCaribbean.sql file in a text editor and add the following SQL query:

SELECT Code, Name
FROM country
WHERE Region = 'Caribbean';

This query will retrieve the Code and Name attributes for the countries in the Caribbean region from the country table.

Save the getCaribbean.sql file.
Now, run the getCaribbean.sql script in the MySQL prompt:

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

This will execute the SQL query in the getCaribbean.sql file and display the results.

The output should look similar to the following:

+------+----------------------------------+
| Code | Name                             |
+------+----------------------------------+
| ABW  | Aruba                            |
| AIA  | Anguilla                         |
| ANT  | Netherlands Antilles             |
| ATG  | Antigua and Barbuda              |
| BHS  | Bahamas                          |
| BRB  | Barbados                         |
| CUB  | Cuba                             |
| CYM  | Cayman Islands                   |
| DMA  | Dominica                         |
| DOM  | Dominican Republic               |
| GLP  | Guadeloupe                       |
| GRD  | Grenada                          |
| HTI  | Haiti                            |
| JAM  | Jamaica                          |
| KNA  | Saint Kitts and Nevis            |
| LCA  | Saint Lucia                      |
| MSR  | Montserrat                       |
| MTQ  | Martinique                       |
| PRI  | Puerto Rico                      |
| TCA  | Turks and Caicos Islands         |
| TTO  | Trinidad and Tobago              |
| VCT  | Saint Vincent and the Grenadines |
| VGB  | Virgin Islands, British          |
| VIR  | Virgin Islands, U.S.             |
+------+----------------------------------+
24 rows in set (0.001 sec)

This completes the project. You have successfully retrieved the codes and countries for the Caribbean region from the world database.

Summary

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

Countries and Codes in Region