Learning MySQL is much easier when you stop looking at SQL as a collection of isolated commands and start using it to solve an actual problem.

MySQL Database Practical: Building a Real-World Database from Scratch

Introduction

Learning MySQL is much easier when you stop looking at SQL as a collection of isolated commands and start using it to solve an actual problem.

In this practical guide, we will build a small online store database from scratch.

By the end of the exercise, we will have:

• A MySQL database

• Customers

• Products

• Orders

• Order items

• Foreign key relationships

• Sample data

• INSERT, SELECT, UPDATE, and DELETE queries

• JOIN queries

• Aggregation and reporting queries

• A basic database structure that could support a real application

The goal isn't to build a production-ready ecommerce database. Instead, we'll use a realistic example to understand the fundamentals of relational database design.

1. What Is MySQL?

MySQL is a relational database management system that uses SQL, or Structured Query Language, to store and manipulate data.

Applications commonly use MySQL to store information such as:

• Users

• Products

• Orders

• Payments

• Transactions

• Articles

• Employees

• Inventory

A typical application might look like this:

Web Application
       |
       | SQL
       v
    MySQL
       |
       ├── customers
       ├── products
       ├── orders
       └── order_items

The application communicates with the database to create, retrieve, update, and delete information.

2. Our Practical Project

Let's imagine we are building a simple online store.

A customer can purchase multiple products, and an order can contain multiple products.

Our database will therefore contain four main tables:

customers
products
orders
order_items

The relationship looks like this:

customers
    |
    | 1
    |
    | many
orders
    |
    | 1
    |
    | many
order_items
    |
    | many
    |
    | 1
products

This is a common relational database pattern.

3. Create the Database

Let's start by creating the database.

CREATE DATABASE shop_db;

Then select it:

USE shop_db;

You can verify that the database is selected with:

SELECT DATABASE();

You should see:

shop_db

4. Create the Customers Table

Customers need somewhere to store their basic information.

CREATE TABLE customers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    phone VARCHAR(30),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Let's examine the important fields.

id

id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY

This is the unique identifier for each customer.

AUTO_INCREMENT means MySQL automatically generates the next ID.

NOT NULL

For example:

first_name VARCHAR(100) NOT NULL

means a customer cannot be created without a first name.

UNIQUE

The email field is unique:

email VARCHAR(255) NOT NULL UNIQUE

This prevents two customers from using the same email address.

5. Create the Products Table

Next, let's create products.

CREATE TABLE products (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(12, 2) NOT NULL,
    stock_quantity INT UNSIGNED NOT NULL DEFAULT 0,
    status ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

The DECIMAL type is appropriate for monetary values.

For example:

150000.00

is stored accurately without the floating-point issues that can occur with FLOAT or DOUBLE.

6. Create the Orders Table

An order belongs to a customer.

CREATE TABLE orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT UNSIGNED NOT NULL,
    order_number VARCHAR(50) NOT NULL UNIQUE,
    status ENUM(
        'PENDING',
        'PAID',
        'SHIPPED',
        'COMPLETED',
        'CANCELLED'
    ) NOT NULL DEFAULT 'PENDING',
    total_amount DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(id)
);

The important part is:

FOREIGN KEY (customer_id)
REFERENCES customers(id)

This establishes a relationship between orders and customers.

A customer can have many orders.

7. Create the Order Items Table

An order can contain several products.

We therefore need another table to store the individual products in an order.

CREATE TABLE order_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    quantity INT UNSIGNED NOT NULL,
    unit_price DECIMAL(12, 2) NOT NULL,

    CONSTRAINT fk_order_items_order
        FOREIGN KEY (order_id)
        REFERENCES orders(id),

    CONSTRAINT fk_order_items_product
        FOREIGN KEY (product_id)
        REFERENCES products(id)
);

Why do we need unit_price if the product already has a price?

Because product prices can change.

Suppose a customer purchases a laptop for:

₦500,000

Later, the store changes the price to:

₦550,000

The original order should still show that the customer purchased it for ₦500,000.

Therefore, the order item stores the price at the time of purchase.

8. Check Our Tables

Run:

SHOW TABLES;

You should see:

customers
order_items
orders
products

You can inspect a table using:

DESCRIBE customers;

or:

DESCRIBE products;

9. Insert Customers

Let's add some customers.

INSERT INTO customers
(first_name, last_name, email, phone)
VALUES
('Julius', 'Fasema', 'julius@example.com', '08030000001'),
('John', 'Okoro', 'john@example.com', '08030000002'),
('Mary', 'James', 'mary@example.com', '08030000003');

Now retrieve them:

SELECT * FROM customers;

10. Insert Products

Let's add some products.

INSERT INTO products
(name, description, price, stock_quantity)
VALUES
(
    'Laptop',
    '15-inch developer laptop',
    750000.00,
    10
),
(
    'Mechanical Keyboard',
    'Mechanical keyboard for developers',
    85000.00,
    25
),
(
    'Wireless Mouse',
    'Wireless ergonomic mouse',
    35000.00,
    40
),
(
    'USB-C Hub',
    'Multi-port USB-C adapter',
    45000.00,
    15
);

Check the products:

SELECT * FROM products;

11. Basic SELECT Queries

To retrieve everything:

SELECT * FROM products;

To retrieve specific columns:

SELECT name, price
FROM products;

To find products above ₦100,000:

SELECT name, price
FROM products
WHERE price > 100000;

To find products below ₦100,000:

SELECT name, price
FROM products
WHERE price < 100000;

12. Sorting Results

You can sort products by price:

SELECT name, price
FROM products
ORDER BY price ASC;

For highest price first:

SELECT name, price
FROM products
ORDER BY price DESC;

You can also sort by multiple columns:

SELECT name, price, stock_quantity
FROM products
ORDER BY price DESC, stock_quantity ASC;

13. Searching with LIKE

Suppose we want to find products containing the word Wireless.

SELECT *
FROM products
WHERE name LIKE '%Wireless%';

The % wildcard means any characters can appear before or after the search term.

14. Creating an Order

Let's say Julius purchases a laptop and a wireless mouse.

First, create the order:

INSERT INTO orders
(customer_id, order_number, status, total_amount)
VALUES
(1, 'ORD-1001', 'PAID', 820000.00);

The customer ID is 1.

The order total is:

Laptop:         ₦750,000
Wireless Mouse:  ₦35,000
-------------------------
Total:          ₦785,000

Let's correct the total:

UPDATE orders
SET total_amount = 785000.00
WHERE order_number = 'ORD-1001';

15. Add Products to the Order

Now we add the laptop:

INSERT INTO order_items
(order_id, product_id, quantity, unit_price)
VALUES
(1, 1, 1, 750000.00);

Then the mouse:

INSERT INTO order_items
(order_id, product_id, quantity, unit_price)
VALUES
(1, 3, 1, 35000.00);

The order now contains two products.

16. Understanding JOIN

One of the most important SQL concepts is the JOIN.

Suppose we want to see the order together with the customer who placed it.

SELECT
    orders.order_number,
    customers.first_name,
    customers.last_name,
    orders.status,
    orders.total_amount
FROM orders
JOIN customers
    ON orders.customer_id = customers.id;

The result could look like:

ORD-1001 | Julius | Fasema | PAID | 785000.00

We joined two tables using their relationship.

17. Joining Multiple Tables

Now let's retrieve the actual products purchased in an order.

SELECT
    orders.order_number,
    customers.first_name,
    products.name,
    order_items.quantity,
    order_items.unit_price
FROM orders
JOIN customers
    ON orders.customer_id = customers.id
JOIN order_items
    ON orders.id = order_items.order_id
JOIN products
    ON order_items.product_id = products.id;

This gives us a much more useful result:

ORD-1001 | Julius | Laptop         | 1 | 750000.00
ORD-1001 | Julius | Wireless Mouse | 1 |  35000.00

This is where relational databases become powerful.

Instead of duplicating customer and product information inside the order table, we connect related records through IDs.

18. Calculating Order Totals

Instead of relying on the stored order total, we can calculate it from the order items.

SELECT
    order_id,
    SUM(quantity * unit_price) AS calculated_total
FROM order_items
GROUP BY order_id;

For our order, MySQL calculates:

1 | 785000.00

This approach is useful when generating reports or verifying stored totals.

19. Counting Products

How many products are currently in the database?

SELECT COUNT(*) AS total_products
FROM products;

How many products are active?

SELECT COUNT(*) AS active_products
FROM products
WHERE status = 'ACTIVE';

20. Calculating the Average Product Price

We can use AVG():

SELECT AVG(price) AS average_price
FROM products;

We can also find the most expensive product:

SELECT MAX(price) AS highest_price
FROM products;

And the cheapest:

SELECT MIN(price) AS lowest_price
FROM products;

21. Updating Data

Suppose the price of the mechanical keyboard changes.

UPDATE products
SET price = 90000.00
WHERE id = 2;

Always be careful with UPDATE.

This is dangerous:

UPDATE products
SET price = 90000;

Without a WHERE clause, every product could be updated.

22. Deleting Data

To delete a product:

DELETE FROM products
WHERE id = 4;

Again, be careful.

This:

DELETE FROM products;

deletes all rows in the table.

In a production environment, destructive operations should be handled carefully and tested before execution.

23. Transactions

Imagine a customer places an order.

Several database operations might be required:

Create order
     ↓
Add order items
     ↓
Reduce product stock
     ↓
Record payment

What happens if the first three operations succeed but the payment record fails?

You could end up with inconsistent data.

This is where database transactions become important.

Conceptually:

START TRANSACTION;

-- Create order

-- Add order items

-- Update stock

-- Record payment

COMMIT;

If something goes wrong:

ROLLBACK;

A transaction allows related operations to succeed or fail together.

24. Indexes

Indexes help MySQL find records more efficiently.

For example:

CREATE INDEX idx_products_name
ON products(name);

You might also index frequently queried foreign keys:

CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

However, indexes aren't free.

They consume storage and can make INSERT, UPDATE, and DELETE operations more expensive.

Therefore, indexes should be added based on actual query patterns rather than simply indexing every column.

25. Database Design Matters

One of the biggest mistakes beginners make is putting everything into one table.

For example:

customer_name
customer_email
product_name
product_price
order_date

This becomes problematic when a customer has multiple orders or an order contains multiple products.

A relational design separates the information:

customers
products
orders
order_items

Each table has a clear responsibility.

Relationships connect the tables.

This makes the database easier to maintain and reduces unnecessary duplication.

26. A Simple Database Query for an Order Report

Let's create a more realistic report.

We want:

• Order number

• Customer name

• Product

• Quantity

• Unit price

• Total for that product

SELECT
    o.order_number,
    CONCAT(c.first_name, ' ', c.last_name) AS customer,
    p.name AS product,
    oi.quantity,
    oi.unit_price,
    (oi.quantity * oi.unit_price) AS item_total
FROM orders o
JOIN customers c
    ON o.customer_id = c.id
JOIN order_items oi
    ON o.id = oi.order_id
JOIN products p
    ON oi.product_id = p.id
ORDER BY o.created_at DESC;

This is the kind of query that a backend application might execute when displaying an order history or generating an administrative report.

27. MySQL in a Real Application

In a real application, your backend would sit between the user interface and MySQL.

For example:

Vue / React / Web Browser
            |
            | HTTP
            v
       Spring Boot
            |
            | JDBC / JPA
            v
          MySQL

The frontend should generally not connect directly to MySQL.

Instead, the backend handles:

• Authentication

• Authorization

• Validation

• Business logic

• Database queries

• Transactions

• Error handling

• API responses

MySQL focuses on storing and retrieving the application's data.

28. What You Should Practice

After completing this example, try extending the database.

Add:

Categories

categories

and allow each product to belong to a category.

Payments

Create:

payments

to record payment transactions.

Addresses

Create:

customer_addresses

to allow customers to have multiple delivery addresses.

Reviews

Create:

product_reviews

so customers can review products.

Each addition gives you an opportunity to practice relationships, foreign keys, queries, and database design.

Conclusion

Learning MySQL isn't about memorizing SELECT, INSERT, UPDATE, and DELETE.

The real skill is understanding how data should be structured and how different pieces of information relate to each other.

In this practical exercise, we created a small ecommerce database containing customers, products, orders, and order items.

We then used SQL to:

• Create tables

• Define primary keys

• Define foreign keys

• Insert data

• Retrieve data

• Update records

• Delete records

• Join tables

• Aggregate data

• Calculate totals

• Use transactions

• Create indexes

These are foundational skills that transfer directly to real-world applications.

Whether you're building a Spring Boot backend, Laravel application, Node.js API, or another software system, understanding relational database design and SQL will remain one of the most valuable skills in backend development.

Tags: MySQL, SQL, Database, Relational Database, Database Design, SQL Queries, Backend Development