Right Outer Join Query User Level

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

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.

1. Start the MySQL service:

sudo service mysql start

2. Access MySQL:

sudo mysql

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

1. Create the level table:

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

2. Insert data into the level table:

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

3. Create the user table:

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

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

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

2. Save the getLevel.sql file.

Run the Query

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

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

Summary

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