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

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL mysql(("`MySQL`")) -.-> mysql/BasicKeywordsandStatementsGroup(["`Basic Keywords and Statements`"]) mysql(("`MySQL`")) -.-> mysql/DatabaseFunctionsandDataTypesGroup(["`Database Functions and Data Types`"]) sql(("`SQL`")) -.-> sql/BasicSQLCommandsGroup(["`Basic SQL Commands`"]) sql(("`SQL`")) -.-> sql/DataManipulationandQueryingGroup(["`Data Manipulation and Querying`"]) sql(("`SQL`")) -.-> sql/AdvancedDataOperationsGroup(["`Advanced Data Operations`"]) sql(("`SQL`")) -.-> sql/DataDefinitionandIntegrityGroup(["`Data Definition and Integrity`"]) sql(("`SQL`")) -.-> sql/DatabaseManagementandOptimizationGroup(["`Database Management and Optimization`"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/source("`External Code Execution`") mysql/DatabaseFunctionsandDataTypesGroup -.-> mysql/database("`DB Function - Info Retrieval`") sql/BasicSQLCommandsGroup -.-> sql/select("`SELECT statements`") sql/DataManipulationandQueryingGroup -.-> sql/where("`WHERE clause`") sql/DataManipulationandQueryingGroup -.-> sql/in("`IN clause`") sql/AdvancedDataOperationsGroup -.-> sql/join("`JOIN operations`") mysql/BasicKeywordsandStatementsGroup -.-> mysql/select("`Data Retrieval`") mysql/BasicKeywordsandStatementsGroup -.-> mysql/insert("`Data Insertion`") mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_table("`Table Creation`") mysql/BasicKeywordsandStatementsGroup -.-> mysql/use_database("`Database Selection`") mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_database("`Database Creation`") mysql/DatabaseFunctionsandDataTypesGroup -.-> mysql/int("`Integer Type`") sql/BasicSQLCommandsGroup -.-> sql/insert("`INSERT INTO statements`") sql/BasicSQLCommandsGroup -.-> sql/create_table("`CREATE TABLE statements`") sql/DataDefinitionandIntegrityGroup -.-> sql/data_types("`Data Types`") sql/DataDefinitionandIntegrityGroup -.-> sql/constraints("`Constraints`") sql/DatabaseManagementandOptimizationGroup -.-> sql/using_indexes("`Using Indexes`") subgraph Lab Skills mysql/source -.-> lab-301300{{"`Database Management and SQL Self-Join`"}} mysql/database -.-> lab-301300{{"`Database Management and SQL Self-Join`"}} sql/select -.-> lab-301300{{"`Database Management and SQL Self-Join`"}} sql/where -.-> lab-301300{{"`Database Management and SQL Self-Join`"}} sql/in -.-> lab-301300{{"`Database Management and SQL Self-Join`"}} sql/join -.-> lab-301300{{"`Database Management and SQL Self-Join`"}} mysql/select -.-> lab-301300{{"`Database Management and SQL Self-Join`"}} mysql/insert -.-> lab-301300{{"`Database Management and SQL Self-Join`"}} mysql/create_table -.-> lab-301300{{"`Database Management and SQL Self-Join`"}} mysql/use_database -.-> lab-301300{{"`Database Management and SQL Self-Join`"}} mysql/create_database -.-> lab-301300{{"`Database Management and SQL Self-Join`"}} mysql/int -.-> lab-301300{{"`Database Management and SQL Self-Join`"}} sql/insert -.-> lab-301300{{"`Database Management and SQL Self-Join`"}} sql/create_table -.-> lab-301300{{"`Database Management and SQL Self-Join`"}} sql/data_types -.-> lab-301300{{"`Database Management and SQL Self-Join`"}} sql/constraints -.-> lab-301300{{"`Database Management and SQL Self-Join`"}} sql/using_indexes -.-> lab-301300{{"`Database Management and SQL Self-Join`"}} end

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.

Start the MySQL service:

sudo service mysql start

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.

Create the division database:

CREATE DATABASE division;

Use the division database:

USE division;

Create the division table:

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

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.

Create a new file named getProvince.sql in the /home/labex/project directory.
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.

Go back to the MySQL prompt:

sudo mysql

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.