MySQL Database Querying | SQL Tutorial | Data Retrieval

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

%%%%{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`") sql/DatabaseManagementandOptimizationGroup -.-> sql/using_indexes("`Using Indexes`") subgraph Lab Skills mysql/source -.-> lab-301380{{"`Query City Information for Specified IDs`"}} sql/select -.-> lab-301380{{"`Query City Information for Specified IDs`"}} sql/where -.-> lab-301380{{"`Query City Information for Specified IDs`"}} sql/in -.-> lab-301380{{"`Query City Information for Specified IDs`"}} mysql/select -.-> lab-301380{{"`Query City Information for Specified IDs`"}} sql/using_indexes -.-> lab-301380{{"`Query City Information for Specified IDs`"}} end

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.

Open a terminal and start the MySQL service:

sudo service mysql start

Access MySQL using the sudo command:

sudo mysql

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.

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.

Save the getTen.sql file.
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.

✨ Check Solution and Practice