SQL Database Creation and Non-Equijoin Query

Introduction

In this project, you will learn how to create a database, tables, and insert data. You will then execute a non-equijoin query to retrieve the level of all users based on the information in the database.

👀 Preview

🎯 Tasks

In this project, you will learn:

• How to access MySQL using the sudo command without any password
• How to create the vip database
• How to create the level and user tables and insert data into them
• How to write a SQL query to retrieve the level of all users

🏆 Achievements

After completing this project, you will be able to:

• Understand the process of creating a database and tables
• Gain experience in inserting data into tables
• Learn how to execute a non-equijoin query to retrieve data from multiple tables
• Apply your SQL knowledge to solve a real-world problem

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 using the sudo command:

sudo mysql

3. Create the vip database with the provided charset:

CREATE SCHEMA vip CHARSET UTF8;

4. Use the vip database:

USE vip;

Create the Tables and Insert Data

In this step, you will learn how to 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);

Retrieve the User Levels

In this step, you will learn how to retrieve the level of all users based on the information in the vip database.

1. Add the following code to the getLevel.sql file:

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

2. Save and close the file.

3. Run the getLevel.sql script in MySQL:

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

The output should be:

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

Congratulations! You have completed the project.

Summary

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