Discover Official Languages Across the Globe

Introduction

In this project, you will learn how to access the MySQL database, import data, and query the official languages spoken in different countries.

👀 Preview

🎯 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 file into the MySQL database
How to write a query to retrieve the first 10 records of official languages from the countrylanguage table

🏆 Achievements

After completing this project, you will be able to:

Understand how to access and manage a MySQL database using the command line
Demonstrate the ability to import data into a MySQL database
Write SQL queries to retrieve specific data from a database table

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`") mysql/BasicKeywordsandStatementsGroup -.-> mysql/use_database("`Database Selection`") sql/DatabaseManagementandOptimizationGroup -.-> sql/using_indexes("`Using Indexes`") subgraph Lab Skills mysql/source -.-> lab-301370{{"`Official Languages in Various Countries`"}} sql/select -.-> lab-301370{{"`Official Languages in Various Countries`"}} sql/where -.-> lab-301370{{"`Official Languages in Various Countries`"}} sql/in -.-> lab-301370{{"`Official Languages in Various Countries`"}} mysql/select -.-> lab-301370{{"`Official Languages in Various Countries`"}} mysql/use_database -.-> lab-301370{{"`Official Languages in Various Countries`"}} sql/using_indexes -.-> lab-301370{{"`Official Languages in Various Countries`"}} end

Access MySQL and Import the 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 file into the MySQL database.

Open a terminal and start the MySQL service:

sudo service mysql start

Access the MySQL database using the sudo command:

sudo mysql

Import the data from the /home/labex/project/world.sql file into the MySQL database:

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

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

Query the Official Languages

In this step, you will learn how to query the official languages for all countries listed in the countrylanguage table and retrieve the first 10 records.

Switch to the world database:

USE world;

Write the query to retrieve the first 10 records of official languages:

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

This query will select the CountryCode and Language columns from the countrylanguage table, where the IsOfficial column is set to 'T' (true), and limit the results to the first 10 records.

Save the Query as a Script

In this step, you will learn how to save the query as a script and place it in the /home/labex/project directory.

Copy the query from the previous step and paste it into the file:
```
SELECT `CountryCode`, `Language`
FROM countrylanguage
WHERE IsOfficial = 'T'
LIMIT 10;
```
Now, the getOfficialLanguage.sql script is ready to be executed in the MySQL database.
In the MySQL client, run the following command to execute the getOfficialLanguage.sql script:
```
SOURCE /home/labex/project/getOfficialLanguage.sql
```

You should see the following output:

MariaDB [world]> SOURCE /home/labex/project/getOfficialLanguage.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)

Summary

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

Official Languages in Various Countries