Mastering SQL: Right Outer Join for User Level Management

Introduction

In this project, you will learn how to create a database, tables, and perform a right outer join query to retrieve the level of all users based on their scores.

👀 Preview

🎯 Tasks

In this project, you will learn:

How to access MySQL using the sudo command without a password
How to create a database and tables
How to insert data into the tables
How to perform a right outer join query to retrieve the level of all users

🏆 Achievements

After completing this project, you will be able to:

Understand the basics of MySQL database management
Create and manipulate tables in a database
Perform complex SQL queries, such as right outer joins, to retrieve data
Apply your SQL knowledge to real-world scenarios, such as user management in a video or audio application

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/AdvancedDataOperationsGroup(["`Advanced Data Operations`"]) mysql(("`MySQL`")) -.-> mysql/DatabaseFunctionsandDataTypesGroup(["`Database Functions and Data Types`"]) sql(("`SQL`")) -.-> sql/DataDefinitionandIntegrityGroup(["`Data Definition and Integrity`"]) 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`") 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/DatabaseFunctionsandDataTypesGroup -.-> mysql/int("`Integer Type`") sql/BasicSQLCommandsGroup -.-> sql/insert("`INSERT INTO statements`") sql/BasicSQLCommandsGroup -.-> sql/create_table("`CREATE TABLE statements`") sql/DataManipulationandQueryingGroup -.-> sql/between("`BETWEEN operator`") 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-301404{{"`Right Outer Join Query User Level`"}} sql/select -.-> lab-301404{{"`Right Outer Join Query User Level`"}} sql/where -.-> lab-301404{{"`Right Outer Join Query User Level`"}} sql/in -.-> lab-301404{{"`Right Outer Join Query User Level`"}} sql/join -.-> lab-301404{{"`Right Outer Join Query User Level`"}} mysql/select -.-> lab-301404{{"`Right Outer Join Query User Level`"}} mysql/insert -.-> lab-301404{{"`Right Outer Join Query User Level`"}} mysql/create_table -.-> lab-301404{{"`Right Outer Join Query User Level`"}} mysql/use_database -.-> lab-301404{{"`Right Outer Join Query User Level`"}} mysql/int -.-> lab-301404{{"`Right Outer Join Query User Level`"}} sql/insert -.-> lab-301404{{"`Right Outer Join Query User Level`"}} sql/create_table -.-> lab-301404{{"`Right Outer Join Query User Level`"}} sql/between -.-> lab-301404{{"`Right Outer Join Query User Level`"}} sql/data_types -.-> lab-301404{{"`Right Outer Join Query User Level`"}} sql/constraints -.-> lab-301404{{"`Right Outer Join Query User Level`"}} sql/using_indexes -.-> lab-301404{{"`Right Outer Join Query User Level`"}} end

Access MySQL and Create the Database

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

Start the MySQL service:

sudo service mysql start

Access MySQL:

sudo mysql

Create the vip database:

CREATE SCHEMA vip CHARSET UTF8;
USE vip;

Now you have successfully created the vip database.

Create the Tables and Insert Data

In this step, you will create the level and user tables, and insert data into them.

Create the level table:

CREATE TABLE level (
name VARCHAR(64) NOT NULL,
low INT NOT NULL,
high INT NOT NULL
);

Insert data into the level table:

INSERT INTO level VALUES
('L1', 0, 9),
('L2', 10, 99),
('L3', 100, 499),
('L4', 500, 999);

Create the user table:

CREATE TABLE user (
name VARCHAR(32) NOT NULL,
score INT NOT NULL
);

Insert data into the user table:

INSERT INTO user VALUES
('Jane', 5),
('John', 15),
('Mary', 155),
('David', 9999);

Now you have successfully created the tables and inserted data into them.

Perform the Right Outer Join Query

In this step, you will perform a right outer join query to retrieve the level of all users.

Open the getLevel.sql file and add the following query:

SELECT u.name AS `User`, l.name AS `Level`
FROM `level` l
RIGHT JOIN `user` u ON u.score BETWEEN l.low AND l.high;

This query performs a right outer join between the level and user tables, and retrieves the user name and the corresponding level name.

Save the getLevel.sql file.

Run the Query

In this step, you will run the getLevel.sql script in MySQL.

Run the script:

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

You should see the following output:

MariaDB [vip]> SOURCE /home/labex/project/getLevel.sql;
+-------+-------+
| User  | Level |
+-------+-------+
| Jane  | L1    |
| John  | L2    |
| Mary  | L3    |
| David | NULL  |
+-------+-------+
4 rows in set (0.000 sec)

The output shows the user name and the corresponding level name for each user. If a user's score does not fall within any of the level ranges, the level is shown as NULL.

Congratulations! You have successfully completed the project by creating the database, tables, inserting data, and performing a right outer join query to retrieve the level of all users.