Cities with Over One Million Population

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

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.

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

sudo service mysql start

3. Access MySQL using the sudo command:

sudo mysql

4. 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.

1. Open the getMillion.sql file.
2. 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;

3. Save the file.
4. 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)

Summary

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