Querying Official Languages of Each Country

Introduction

In this project, you will learn how to retrieve the official languages of each country using an equijoin query in MySQL. This project will help you understand how to work with relational databases, perform complex queries, and extract specific information from a database.

👀 Preview

+--------------------------------------+------------------+------------+
| Name                                 | Language         | IsOfficial |
+--------------------------------------+------------------+------------+
| Aruba                                | Dutch            | T          |
| Afghanistan                          | Dari             | T          |
| Afghanistan                          | Pashto           | T          |
    ...
| South Africa                         | Zulu             | T          |
| Zimbabwe                             | English          | T          |
+--------------------------------------+------------------+------------+
238 rows in set (0.002 sec)

🎯 Tasks

In this project, you will learn:

• How to access MySQL using the sudo command without any password
• How to import data into MySQL from a SQL script
• How to write an equijoin query to retrieve the official languages of each country
• How to execute a SQL script in the MySQL console

🏆 Achievements

After completing this project, you will be able to:

• Understand the basics of relational databases and SQL queries
• Perform complex queries involving multiple tables and conditions
• Extract specific information from a database based on given requirements
• Apply your knowledge of SQL to solve real-world data retrieval problems

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. Open a terminal and run the following command to 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:

SOURCE /home/labex/project/world.sql;
USE world;

Retrieve the Official Languages of Each Country

In this step, you will learn how to retrieve the full name of each country and its official language using an equijoin between the country and countrylanguage tables, and the JOIN ON WHERE syntax.

1. Open the getOfficialLanguage.sql file.
2. Add the following SQL query to the getOfficialLanguage.sql file:

SELECT country.Name, countrylanguage.Language, countrylanguage.IsOfficial
FROM country
JOIN countrylanguage ON country.Code = countrylanguage.CountryCode
WHERE countrylanguage.IsOfficial = 'T';

This query will retrieve the following columns:

• Name: The full name of the country
• Language: The official language of the country
• IsOfficial: Indicates whether the language is an official language of the country ('T' for true, 'F' for false)

The query joins the country and countrylanguage tables on the Code and CountryCode columns, and then filters the results to include only the official languages (where IsOfficial is 'T').

Run the Query

In this step, you will run the SQL script you created in the previous step.

1. Run the getOfficialLanguage.sql script:

SOURCE /home/labex/project/getOfficialLanguage.sql;

This will execute the query and display the results in the MySQL console.

You should see output similar to the following:

+--------------------------------------+------------------+------------+
| Name                                 | Language         | IsOfficial |
+--------------------------------------+------------------+------------+
| Aruba                                | Dutch            | T          |
| Afghanistan                          | Dari             | T          |
| Afghanistan                          | Pashto           | T          |
| Anguilla                             | English          | T          |
| Albania                              | Albaniana        | T          |
| Andorra                              | Catalan          | T          |
| Netherlands Antilles                 | Dutch            | T          |
| Netherlands Antilles                 | Papiamento       | T          |
| United Arab Emirates                 | Arabic           | T          |
| Argentina                            | Spanish          | T          |
| Armenia                              | Armenian         | T          |
    ...
| South Africa                         | Afrikaans        | T          |
| South Africa                         | English          | T          |
| South Africa                         | Xhosa            | T          |
| South Africa                         | Zulu             | T          |
| Zimbabwe                             | English          | T          |
+--------------------------------------+------------------+------------+
238 rows in set (0.002 sec)

This completes the project. You have successfully retrieved the official languages of each country using an equijoin query in MySQL.

Summary

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