Database Management and SQL Self-Join

Introduction

In this project, you will learn how to create a database, a table, and insert data into the table. You will also learn how to perform a self-join query on the data to find the province to which a city belongs.

👀 Preview

🎯 Tasks

In this project, you will learn:

• How to start the MySQL service and access MySQL using the sudo command without any password.
• How to create a database, a table, and insert data into the table.
• How to write a self-join query on the division table to find the province for each city.
• How to run the self-join query script in MySQL.

🏆 Achievements

After completing this project, you will be able to:

• Understand the basic operations of creating a database, a table, and inserting data in MySQL.
• Perform self-join queries to retrieve related information from a table.
• Apply your knowledge of SQL queries to solve real-world problems.

Start MySQL Service and Access MySQL

In this step, you will learn how to start the MySQL service and access MySQL using the sudo command without any password.

1. Start the MySQL service:

sudo service mysql start

2. Access MySQL:

sudo mysql

You should now be able to access the MySQL prompt without any password.

Create Database, Table, and Insert Data

In this step, you will learn how to create a database, a table, and insert data into the table.

1. Create the division database:

CREATE DATABASE division;

2. Use the division database:

USE division;

3. Create the division table:

CREATE TABLE division (
  id INT PRIMARY KEY,
  name VARCHAR(22),
  province_id INT
);

4. Insert data into the division table:

INSERT INTO division VALUES
(1, 'Ontario', NULL),
(2, 'Toronto', 1),
(3, 'Quebec', NULL),
(4, 'Montreal', 3);

Write the Self-Join Query

In this step, you will learn how to write a self-join query on the division table to find the province to which a city belongs.

1. Create a new file named getProvince.sql in the /home/labex/project directory.

2. In the getProvince.sql file, add the following query:

SELECT city.name AS City, province.name AS Province
FROM division AS city
JOIN division AS province ON city.province_id = province.id
WHERE city.province_id IS NOT NULL;

This query performs a self-join on the division table, where the city table is joined with the province table based on the province_id column. The WHERE clause ensures that only the cities with a non-null province_id are included in the result.

The resulting table will have the columns labeled as City and Province.

Run the Self-Join Query

In this step, you will learn how to run the self-join query script in MySQL.

1. Go back to the MySQL prompt:

sudo mysql

2. Run the getProvince.sql script:

SOURCE /home/labex/project/getProvince.sql;

You should see the following output:

MariaDB [division]> SOURCE /home/labex/project/getProvince.sql;
+----------+----------+
| City     | Province |
+----------+----------+
| Toronto  | Ontario  |
| Montreal | Quebec   |
+----------+----------+
2 rows in set (0.000 sec)

This output shows the cities and their corresponding provinces, as retrieved by the self-join query.

Congratulations! You have successfully completed the project by creating a database, a table, inserting data, and executing a self-join query to find the province for each city.

Summary

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