City Names and Respective Countries

Introduction

In this project, you will learn how to retrieve city names and their corresponding country names from a MySQL database using an equi-join query. This project will help you understand the basics of SQL queries and how to work with relational databases.

👀 Preview

MariaDB [world]> SOURCE /home/labex/project/getCountryName.sql;
+----------------+-------------+
| CityName       | CountryName |
+----------------+-------------+
| Oranjestad     | Aruba       |
| Kabul          | Afghanistan |
| Qandahar       | Afghanistan |
| Herat          | Afghanistan |
| Mazar-e-Sharif | Afghanistan |
| Luanda         | Angola      |
| Huambo         | Angola      |
| Lobito         | Angola      |
| Benguela       | Angola      |
| Namibe         | Angola      |
+----------------+-------------+
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 a SQL file into a MySQL database
• How to write an equi-join query to retrieve data from multiple tables
• How to simplify table names and use aliases in SQL queries
• How to run a SQL script in MySQL

🏆 Achievements

After completing this project, you will be able to:

• Understand the basic structure and syntax of SQL queries
• Perform equi-join operations to retrieve data from multiple tables
• Simplify table names and use aliases to make your SQL queries more readable
• Import and manage data in a MySQL database
• Run SQL scripts to execute complex queries

Skills Graph

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. Start the MySQL service:

sudo service mysql start

2. Access MySQL:

sudo mysql

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

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

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

Retrieve City Names and Country Names

In this step, you will learn how to retrieve the city names and their full country names using an equi-join query.

1. Create the getCountryName.sql file in the /home/labex/project directory.
2. Add the following code to the getCountryName.sql file:

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

This query will retrieve the city names and their corresponding country names from the city and country tables, and limit the output to the first 10 records.

Run the Script

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

1. Run the script in MySQL:

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

This will execute the script and display the results:

+----------------+-------------+
| CityName       | CountryName |
+----------------+-------------+
| Oranjestad     | Aruba       |
| Kabul          | Afghanistan |
| Qandahar       | Afghanistan |
| Herat          | Afghanistan |
| Mazar-e-Sharif | Afghanistan |
| Luanda         | Angola      |
| Huambo         | Angola      |
| Lobito         | Angola      |
| Benguela       | Angola      |
| Namibe         | Angola      |
+----------------+-------------+
10 rows in set (0.001 sec)

The resulting table has two columns: CityName and CountryName, which display the city names and their corresponding country names.

Summary

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