Querying Official Languages and Sorting

Introduction

In this project, you will learn how to query and sort the official languages spoken in different countries using SQL. You will access the MySQL database, import the necessary data, and write a SQL script to retrieve and order the official languages.

👀 Preview

MariaDB [world]> SOURCE /home/labex/project/getOfficialLanguageOrder.sql;
+-------------+------------+
| CountryCode | Language   |
+-------------+------------+
| ABW         | Dutch      |
| AFG         | Dari       |
| AFG         | Pashto     |
| AIA         | English    |
| ALB         | Albaniana  |
| AND         | Catalan    |
| ANT         | Dutch      |
| ANT         | Papiamento |
| ARE         | Arabic     |
| ARG         | Spanish    |
+-------------+------------+
10 rows in set (0.000 sec)

🎯 Tasks

In this project, you will learn:

• How to access the MySQL database using the sudo command without a password
• How to import data from a SQL script into the MySQL database
• How to write a SQL query to retrieve the official languages spoken in each country, sorted by their country codes
• How to run a SQL script in the MySQL database

🏆 Achievements

After completing this project, you will be able to:

• Understand how to work with the MySQL database using the command line
• Write SQL queries to extract and sort data from a database
• Apply your SQL knowledge to solve real-world data manipulation problems

Skills Graph

Access MySQL and Import Data

In this step, you will learn how to access the MySQL database using the sudo command without a password, and import the data from the /home/labex/project/world.sql script into MySQL.

1. Start the MySQL service:

sudo service mysql start

2. Access the MySQL database using the sudo command without a password:

sudo mysql

3. Import the data from the /home/labex/project/world.sql script into MySQL:

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

Query and Sort Official Languages

In this step, you will learn how to query the official languages spoken in each country listed in the countrylanguage table, sort them by their respective country codes, and retrieve the top 10 records.

1. Create the getOfficialLanguageOrder.sql file.
2. In the getOfficialLanguageOrder.sql file, add the following SQL query:

SELECT `CountryCode`, `Language`
FROM countrylanguage
WHERE IsOfficial = 'T'
ORDER BY CountryCode
LIMIT 10;

This query will select the CountryCode and Language attributes from the countrylanguage table, where the IsOfficial column is 'T' (true), order the results by the CountryCode column, and limit the output to the top 10 records.

3. Save the getOfficialLanguageOrder.sql file.

Run the Script

In this step, you will learn how to run the getOfficialLanguageOrder.sql script in the MySQL database.

1. In the MySQL prompt, run the following command to execute the script:

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

This will execute the SQL query in the getOfficialLanguageOrder.sql script and display the results in the MySQL prompt.

The output should look similar to the following:

+-------------+------------+
| CountryCode | Language   |
+-------------+------------+
| ABW         | Dutch      |
| AFG         | Dari       |
| AFG         | Pashto     |
| AIA         | English    |
| ALB         | Albaniana  |
| AND         | Catalan    |
| ANT         | Dutch      |
| ANT         | Papiamento |
| ARE         | Arabic     |
| ARG         | Spanish    |
+-------------+------------+
10 rows in set (0.000 sec)

Congratulations! You have successfully completed the project of querying and sorting the official languages spoken in different countries.

Summary

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