Query City Names with Country

Introduction

In this project, you will learn how to execute an equal join query on the city, country, and countrylanguage tables in MySQL. The goal is to retrieve the city name, corresponding country name, and language from these tables.

👀 Preview

MariaDB [world]> SOURCE /home/labex/project/getCountryNameAndLanguage.sql;
+----------------+-------------+------------+
| CityName       | CountryName | Language   |
+----------------+-------------+------------+
| Oranjestad     | Aruba       | Dutch      |
| Oranjestad     | Aruba       | English    |
| Oranjestad     | Aruba       | Papiamento |
| Oranjestad     | Aruba       | Spanish    |
| Kabul          | Afghanistan | Balochi    |
| Qandahar       | Afghanistan | Balochi    |
| Herat          | Afghanistan | Balochi    |
| Mazar-e-Sharif | Afghanistan | Balochi    |
| Kabul          | Afghanistan | Dari       |
| Qandahar       | Afghanistan | Dari       |
+----------------+-------------+------------+
10 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 the world.sql script into MySQL
• How to write an equal join query to retrieve the desired information from the tables
• How to limit the query results to the first 10 rows

🏆 Achievements

After completing this project, you will be able to:

• Understand the concept of SQL joins and how to use them effectively
• Retrieve data from multiple tables in a relational database
• Apply SQL queries to filter and limit the results as needed
• Gain practical experience in working with MySQL databases

Skills Graph

Access MySQL and Import Data

In this step, you will learn how to access MySQL using the sudo command without any password, and import the data from the provided world.sql script into MySQL.

1. Start the MySQL service:

sudo service mysql start

2. Access MySQL:

sudo mysql

3. Import the data from the world.sql script:

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

Execute the Join Query

In this step, you will learn how to execute an equal join query to retrieve the city name, corresponding country name, and language from the city, country, and countrylanguage tables.

1. Open the getCountryNameAndLanguage.sql file.
2. Add the following code to the getCountryNameAndLanguage.sql file:

SELECT ci.Name AS CityName, co.Name AS CountryName, col.Language
FROM city AS ci
JOIN country AS co ON ci.CountryCode = co.Code
JOIN countrylanguage AS col ON co.Code = col.CountryCode
LIMIT 10;

This query performs an equal join between the city, country, and countrylanguage tables, and limits the results to the first 10 rows. The query output includes the CityName, CountryName, and Language headers.

Run the Query

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

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

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

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

The output should look similar to the following:

+----------------+-------------+------------+
| CityName       | CountryName | Language   |
+----------------+-------------+------------+
| Oranjestad     | Aruba       | Dutch      |
| Oranjestad     | Aruba       | English    |
| Oranjestad     | Aruba       | Papiamento |
| Oranjestad     | Aruba       | Spanish    |
| Kabul          | Afghanistan | Balochi    |
| Qandahar       | Afghanistan | Balochi    |
| Herat          | Afghanistan | Balochi    |
| Mazar-e-Sharif | Afghanistan | Balochi    |
| Kabul          | Afghanistan | Dari       |
| Qandahar       | Afghanistan | Dari       |
+----------------+-------------+------------+
10 rows in set (0.001 sec)

Congratulations! You have successfully completed the project.

Summary

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