Querying Country Codes Range

Introduction

In this project, you will learn how to retrieve countries that speak the same official language as the country with the country code "FRO" using a multi-row subquery operation in MySQL.

👀 Preview

MariaDB [world]> SOURCE /home/labex/project/getCountryCode.sql;
+-------------+----------+
| CountryCode | Language |
+-------------+----------+
| DNK         | Danish   |
| FRO         | Danish   |
| FRO         | Faroese  |
| GRL         | Danish   |
+-------------+----------+
4 rows in set (0.002 sec)

🎯 Tasks

In this project, you will learn:

• How to access MySQL using the sudo command without a password
• How to import data from the world.sql file into the MySQL database
• How to write a SQL query using multi-row subqueries to retrieve the desired information
• How to run the SQL query in the MySQL command-line interface

🏆 Achievements

After completing this project, you will be able to:

• Understand the concept of multi-row subqueries in SQL
• Retrieve specific data from a database using complex SQL queries
• 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 and import the world.sql data into the world database.

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

sudo service mysql start

2. Access the MySQL command-line interface using the sudo command:

sudo mysql

3. Import the world.sql data 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 have successfully accessed MySQL and imported the necessary data. Let's move on to the next step.

Retrieve Countries with the Same Official Language as FRO

In this step, you will write a SQL query to retrieve all the countries that speak the same official language as the country with the country code FRO.

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

SELECT cl1.CountryCode, cl1.Language
FROM countrylanguage AS cl1
WHERE cl1.Language IN (
    -- Subquery to retrieve the language of the country with CountryCode 'FRO' and IsOfficial = 'T'
    SELECT cl2.Language
    FROM countrylanguage AS cl2
    WHERE cl2.CountryCode = 'FRO' AND cl2.IsOfficial = 'T'
)
AND cl1.CountryCode IN (
    -- Subquery to retrieve the CountryCode of the countries that have the same language as the one in FRO and IsOfficial = 'T'
    SELECT cl3.CountryCode
    FROM countrylanguage AS cl3
    WHERE cl3.Language IN (
        -- Subquery to retrieve the language of the country with CountryCode 'FRO' and IsOfficial = 'T'
        SELECT cl4.Language
        FROM countrylanguage AS cl4
        WHERE cl4.CountryCode = 'FRO' AND cl4.IsOfficial = 'T'
    )
    AND cl3.IsOfficial = 'T'
);

This query uses a multi-row subquery to retrieve the countries that speak the same official language as the country with the country code FRO.

2. Save the getCountryCode.sql file.

Now you have created the SQL query to retrieve the desired information. In the next step, you will run the query in MySQL.

Run the SQL Query

In this step, you will run the SQL query stored in the getCountryCode.sql file.

1. Run the SQL query stored in the getCountryCode.sql file:

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

This will execute the SQL query and display the results in the terminal.

The output should look similar to the following:

+-------------+----------+
| CountryCode | Language |
+-------------+----------+
| DNK         | Danish   |
| FRO         | Danish   |
| FRO         | Faroese  |
| GRL         | Danish   |
+-------------+----------+
4 rows in set (0.002 sec)

The query has successfully retrieved the countries that speak the same official language as the country with the country code FRO.

Congratulations! You have completed the project of querying the country codes range using a multi-row subquery operation.

Summary

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