SQL Database Creation and Non-Equijoin Query Tutorial

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

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`"]) 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`") 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-301392{{"`SQL Database Creation and Non-Equijoin Query`"}} sql/select -.-> lab-301392{{"`SQL Database Creation and Non-Equijoin Query`"}} sql/where -.-> lab-301392{{"`SQL Database Creation and Non-Equijoin Query`"}} sql/in -.-> lab-301392{{"`SQL Database Creation and Non-Equijoin Query`"}} mysql/select -.-> lab-301392{{"`SQL Database Creation and Non-Equijoin Query`"}} mysql/insert -.-> lab-301392{{"`SQL Database Creation and Non-Equijoin Query`"}} mysql/create_table -.-> lab-301392{{"`SQL Database Creation and Non-Equijoin Query`"}} mysql/use_database -.-> lab-301392{{"`SQL Database Creation and Non-Equijoin Query`"}} mysql/int -.-> lab-301392{{"`SQL Database Creation and Non-Equijoin Query`"}} sql/insert -.-> lab-301392{{"`SQL Database Creation and Non-Equijoin Query`"}} sql/create_table -.-> lab-301392{{"`SQL Database Creation and Non-Equijoin Query`"}} sql/between -.-> lab-301392{{"`SQL Database Creation and Non-Equijoin Query`"}} sql/data_types -.-> lab-301392{{"`SQL Database Creation and Non-Equijoin Query`"}} sql/constraints -.-> lab-301392{{"`SQL Database Creation and Non-Equijoin Query`"}} sql/using_indexes -.-> lab-301392{{"`SQL Database Creation and Non-Equijoin Query`"}} 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 using the sudo command:

sudo mysql

Create the vip database with the provided charset:

CREATE SCHEMA vip CHARSET UTF8;

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.

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);

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.

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;

Save and close the file.

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.