Query City Information for Specified IDs

Introduction

In this project, you will learn how to query and retrieve information from a MySQL database. Specifically, you will learn how to access the MySQL database, import a pre-existing database, and execute a SQL query to retrieve information about cities based on their IDs.

👀 Preview

MariaDB [world]> SOURCE /home/labex/project/getTen.sql;
+----+----------------+-------------+---------------+------------+
| ID | Name           | CountryCode | District      | Population |
+----+----------------+-------------+---------------+------------+
|  1 | Kabul          | AFG         | Kabol         |    1780000 |
|  2 | Qandahar       | AFG         | Qandahar      |     237500 |
|  3 | Herat          | AFG         | Herat         |     186800 |
|  4 | Mazar-e-Sharif | AFG         | Balkh         |     127800 |
|  5 | Amsterdam      | NLD         | Noord-Holland |     731200 |
|  6 | Rotterdam      | NLD         | Zuid-Holland  |     593321 |
|  7 | Haag           | NLD         | Zuid-Holland  |     440900 |
|  8 | Utrecht        | NLD         | Utrecht       |     234323 |
|  9 | Eindhoven      | NLD         | Noord-Brabant |     201843 |
| 10 | Tilburg        | NLD         | Noord-Brabant |     193238 |
+----+----------------+-------------+---------------+------------+
10 rows in set (0.000 sec)

🎯 Tasks

In this project, you will learn:

• How to access MySQL using the sudo command without a password
• How to import a pre-existing SQL database into MySQL
• How to write and execute a SQL query to retrieve city information based on ID

🏆 Achievements

After completing this project, you will be able to:

• Understand how to access and work with a MySQL database
• Write SQL queries to retrieve specific data from a database
• Apply your knowledge of SQL to solve real-world data retrieval problems

Skills Graph

Access MySQL and Import the Database

In this step, you will learn how to access MySQL using the sudo command without any password and import the world.sql database.

1. Open a terminal and start the MySQL service:

sudo service mysql start

2. Access MySQL using the sudo command:

sudo mysql

3. Import the world.sql database into MySQL:

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

This will import the world database into your MySQL environment.

Query City Information

In this step, you will learn how to query all the information for cities whose IDs are less than or equal to 10 from the city table.

1. Open the getTen.sql file and add the following SQL query:

SELECT * FROM city
WHERE ID <= 10;

This query will retrieve all the information for cities whose IDs are less than or equal to 10.

2. Save the getTen.sql file.
3. Run the getTen.sql script in MySQL:

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

This will execute the SQL query and display the results.

Verify the Results

In this step, you will verify the results of the SQL query executed in the previous step.

The output of the getTen.sql script should look similar to the following:

+----+----------------+-------------+---------------+------------+
| ID | Name           | CountryCode | District      | Population |
+----+----------------+-------------+---------------+------------+
|  1 | Kabul          | AFG         | Kabol         |    1780000 |
|  2 | Qandahar       | AFG         | Qandahar      |     237500 |
|  3 | Herat          | AFG         | Herat         |     186800 |
|  4 | Mazar-e-Sharif | AFG         | Balkh         |     127800 |
|  5 | Amsterdam      | NLD         | Noord-Holland |     731200 |
|  6 | Rotterdam      | NLD         | Zuid-Holland  |     593321 |
|  7 | Haag           | NLD         | Zuid-Holland  |     440900 |
|  8 | Utrecht        | NLD         | Utrecht       |     234323 |
|  9 | Eindhoven      | NLD         | Noord-Brabant |     201843 |
| 10 | Tilburg        | NLD         | Noord-Brabant |     193238 |
+----+----------------+-------------+---------------+------------+
10 rows in set (0.000 sec)

This output shows the information for the cities with IDs less than or equal to 10, as requested in the challenge.

Summary

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