Retrieve City Names and Country Names from MySQL Database

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

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL sql(("SQL")) -.-> sql/DataManipulationandQueryingGroup(["Data Manipulation and Querying"]) mysql(("MySQL")) -.-> mysql/DatabaseFunctionsandDataTypesGroup(["Database Functions and Data Types"]) mysql(("MySQL")) -.-> mysql/BasicKeywordsandStatementsGroup(["Basic Keywords and Statements"]) sql(("SQL")) -.-> sql/BasicSQLCommandsGroup(["Basic SQL Commands"]) sql(("SQL")) -.-> sql/AdvancedDataOperationsGroup(["Advanced Data Operations"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/use_database("Database Selection") sql/BasicSQLCommandsGroup -.-> sql/select("SELECT statements") sql/BasicSQLCommandsGroup -.-> sql/insert("INSERT INTO statements") mysql/BasicKeywordsandStatementsGroup -.-> mysql/select("Data Retrieval") mysql/BasicKeywordsandStatementsGroup -.-> mysql/source("External Code Execution") sql/BasicSQLCommandsGroup -.-> sql/create_table("CREATE TABLE statements") sql/DataManipulationandQueryingGroup -.-> sql/where("WHERE clause") mysql/DatabaseFunctionsandDataTypesGroup -.-> mysql/database("DB Function - Info Retrieval") sql/AdvancedDataOperationsGroup -.-> sql/join("JOIN operations") subgraph Lab Skills mysql/use_database -.-> lab-301298{{"City Names and Respective Countries"}} sql/select -.-> lab-301298{{"City Names and Respective Countries"}} sql/insert -.-> lab-301298{{"City Names and Respective Countries"}} mysql/select -.-> lab-301298{{"City Names and Respective Countries"}} mysql/source -.-> lab-301298{{"City Names and Respective Countries"}} sql/create_table -.-> lab-301298{{"City Names and Respective Countries"}} sql/where -.-> lab-301298{{"City Names and Respective Countries"}} mysql/database -.-> lab-301298{{"City Names and Respective Countries"}} sql/join -.-> lab-301298{{"City Names and Respective Countries"}} end

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.

Start the MySQL service:

sudo service mysql start

Access MySQL:

sudo mysql

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.

Create the getCountryName.sql file in the /home/labex/project directory.
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.

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.

✨ Check Solution and Practice

Summary

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