Learn MySQL: Accessing Database and Querying Million-Population Cities

Introduction

In this project, you will learn how to access MySQL, import data, and query cities with a population over one million from the city table.

👀 Preview

MariaDB [world]> SOURCE /home/labex/project/getMillion.sql;
+------+--------------+-------------+----------+------------+
| ID   | Name         | CountryCode | District | Population |
+------+--------------+-------------+----------+------------+
| 1907 | Shijiazhuang | CHN         | Hebei    |    2041500 |
| 1924 | Tangshan     | CHN         | Hebei    |    1040000 |
+------+--------------+-------------+----------+------------+
2 rows in set (0.001 sec)

🎯 Tasks

In this project, you will learn:

How to start the MySQL service and access MySQL using the sudo command
How to import data from a SQL file into the MySQL database
How to query the city table to retrieve information for cities in the Hebei region with a population over one million

🏆 Achievements

After completing this project, you will be able to:

Manage a MySQL database, including starting the service and importing data
Write SQL queries to retrieve specific information from a database table
Demonstrate your understanding of working with MySQL and SQL queries

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-301296{{"`Cities with Over One Million Population`"}} sql/select -.-> lab-301296{{"`Cities with Over One Million Population`"}} sql/where -.-> lab-301296{{"`Cities with Over One Million Population`"}} sql/in -.-> lab-301296{{"`Cities with Over One Million Population`"}} mysql/select -.-> lab-301296{{"`Cities with Over One Million Population`"}} sql/using_indexes -.-> lab-301296{{"`Cities with Over One Million Population`"}} end

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 /home/labex/project/world.sql into MySQL.

Open a terminal on the server.
Start the MySQL service:

sudo service mysql start

Access MySQL using the sudo command:

sudo mysql

Import the data from /home/labex/project/world.sql into MySQL and switch to the world database:

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

Query Cities With Over One Million Population

In this step, you will learn how to query all information regarding Hebei region cities with a population over one million from the city table.

Open the getMillion.sql file.
Add the following code to the getMillion.sql file:

-- Query to retrieve city information for cities in Hebei district with a population over 1 million
SELECT *
FROM city
WHERE District = 'Hebei' AND Population > 1000000;

Save the file.
Run the getMillion.sql script in MySQL:

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

This will display the information for the cities in the Hebei region with a population over one million.

MariaDB [world]> SOURCE /home/labex/project/getMillion.sql;
+------+--------------+-------------+----------+------------+
| ID   | Name         | CountryCode | District | Population |
+------+--------------+-------------+----------+------------+
| 1907 | Shijiazhuang | CHN         | Hebei    |    2041500 |
| 1924 | Tangshan     | CHN         | Hebei    |    1040000 |
+------+--------------+-------------+----------+------------+
2 rows in set (0.001 sec)

✨ Check Solution and Practice

Summary

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

Cities with Over One Million Population