Learn SQL with a Real-World Retail Dataset

12 min read

Ready to dive into SQL and learn how to work with data like a pro? This blog post provides a real-world retail dataset to help you practice SQL queries, perfect for beginners starting their SQL journey. Designed to align with resources like the Mode SQL Tutorial, SQL for Data Analytics by Luke Barousse, and the SQL for Dummies book, this dataset will help you master data extraction and manipulation in a practical context.

Why Learn SQL with This Dataset?

SQL (Structured Query Language) is a key skill for anyone working with data, whether you're aiming to become a data analyst, business intelligence developer, or database administrator. Our retail dataset simulates a real-world retail store, including customers, products, orders, and order details. It's designed to be beginner-friendly yet robust, allowing you to practice essential SQL concepts like SELECT, JOIN, and WHERE in scenarios like inventory management and customer analytics.

The Retail Dataset

The dataset represents a retail business with four tables: customers, products, orders, and order_details. It includes 10 customers, 10 products, 20 orders, and 37 order details, providing plenty of data to explore. Below is the SQL code to create and populate the database. You can download it or copy it directly to set up your own database.

Download the Dataset

Copy the SQL code below to set up your retail database

Complete Retail Database Setup

Full SQL script to create and populate the retail database with sample data

-- Customers data --
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    join_date DATE
);

-- Products data --
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10, 2),
    stock_quantity INT
);

-- Orders data --
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Order details data --
CREATE TABLE order_details (
    order_detail_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    unit_price DECIMAL(10, 2),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- Customers data --
INSERT INTO customers (customer_id, first_name, last_name, email, join_date) VALUES
(1, 'Alice', 'Smith', 'alice.smith@email.com', '2024-01-15'),
(2, 'Bob', 'Johnson', 'bob.johnson@email.com', '2024-03-22'),
(3, 'Carol', 'Williams', 'carol.w@email.com', '2024-06-10'),
(4, 'David', 'Brown', 'david.brown@email.com', '2024-09-05'),
(5, 'Emma', 'Davis', 'emma.davis@email.com', '2024-11-20'),
(6, 'Frank', 'Miller', 'frank.miller@email.com', '2025-01-10'),
(7, 'Grace', 'Wilson', 'grace.wilson@email.com', '2025-02-15'),
(8, 'Henry', 'Moore', 'henry.moore@email.com', '2025-03-01'),
(9, 'Isabella', 'Taylor', 'isabella.t@email.com', '2025-04-12'),
(10, 'James', 'Anderson', 'james.a@email.com', '2025-05-20');

-- Products data --
INSERT INTO products (product_id, product_name, category, price, stock_quantity) VALUES
(1, 'Laptop', 'Electronics', 999.99, 15),
(2, 'Smartphone', 'Electronics', 599.99, 25),
(3, 'Headphones', 'Electronics', 79.99, 50),
(4, 'T-Shirt', 'Clothing', 19.99, 100),
(5, 'Jeans', 'Clothing', 49.99, 80),
(6, 'Smartwatch', 'Electronics', 199.99, 30),
(7, 'Sneakers', 'Clothing', 89.99, 60),
(8, 'Tablet', 'Electronics', 349.99, 20),
(9, 'Jacket', 'Clothing', 129.99, 40),
(10, 'Speaker', 'Electronics', 149.99, 35);

-- Orders data --
INSERT INTO orders (order_id, customer_id, order_date, total_amount) VALUES
(1, 1, '2025-03-01', 1079.98),
(2, 2, '2025-03-05', 619.98),
(3, 3, '2025-04-10', 149.97),
(4, 1, '2025-05-15', 1999.98),
(5, 4, '2025-06-01', 69.98),
(6, 5, '2025-06-05', 249.97),
(7, 6, '2025-06-10', 1149.98),
(8, 7, '2025-06-15', 39.98),
(9, 2, '2025-07-01', 799.97),
(10, 8, '2025-07-05', 279.98),
(11, 9, '2025-07-10', 1299.96),
(12, 10, '2025-07-15', 99.98),
(13, 3, '2025-08-01', 349.99),
(14, 4, '2025-08-05', 179.98),
(15, 5, '2025-08-10', 1049.97),
(16, 6, '2025-08-15', 219.98),
(17, 7, '2025-09-01', 599.99),
(18, 1, '2025-09-05', 149.99),
(19, 8, '2025-09-10', 259.98),
(20, 9, '2025-09-15', 449.97);

-- Order details data --
INSERT INTO order_details (order_detail_id, order_id, product_id, quantity, unit_price) VALUES
(1, 1, 1, 1, 999.99),
(2, 1, 3, 1, 79.99),
(3, 2, 2, 1, 599.99),
(4, 2, 4, 1, 19.99),
(5, 3, 3, 1, 79.99),
(6, 3, 4, 1, 19.99),
(7, 3, 5, 1, 49.99),
(8, 4, 1, 2, 999.99),
(9, 5, 4, 2, 19.99),
(10, 5, 3, 1, 29.99),
(11, 6, 6, 1, 199.99),
(12, 6, 4, 1, 19.99),
(13, 6, 5, 1, 29.99),
(14, 7, 1, 1, 999.99),
(15, 7, 9, 1, 149.99),
(16, 8, 4, 2, 19.99),
(17, 9, 2, 1, 599.99),
(18, 9, 6, 1, 199.99),
(19, 10, 7, 1, 89.99),
(20, 10, 3, 1, 79.99),
(21, 10, 4, 1, 19.99),
(22, 11, 1, 1, 999.99),
(23, 11, 8, 1, 299.99),
(24, 12, 5, 2, 49.99),
(25, 13, 8, 1, 349.99),
(26, 14, 3, 1, 79.99),
(27, 14, 7, 1, 99.99),
(28, 15, 1, 1, 999.99),
(29, 15, 4, 1, 49.99),
(30, 16, 6, 1, 199.99),
(31, 16, 4, 1, 19.99),
(32, 17, 2, 1, 599.99),
(33, 18, 10, 1, 149.99),
(34, 19, 9, 1, 129.99),
(35, 19, 4, 1, 29.99),
(36, 20, 8, 1, 349.99),
(37, 20, 3, 1, 99.99);

How to Use the Dataset

Download or Copy: Save the Retail_Dataset.sql file from the code block above or copy the SQL code.
Set Up the Database:
- Option 1: SQLite (Recommended for Beginners):
  Download and install SQLite from sqlite.org. Open a terminal, run sqlite3 retail.db to create a database. Paste the SQL code into the SQLite prompt or use a tool like DB Browser for SQLite to import and execute it.
- Option 2: Online Tools:
  Use DB Fiddle (select SQLite or MySQL), paste the SQL code into the schema section, and execute it. Alternatively, try W3Schools SQL Tryit Editor if it supports custom datasets.
- Option 3: MySQL/PostgreSQL:
  Install MySQL or PostgreSQL, create a database, and run the SQL code using a client like MySQL Workbench or pgAdmin.
Verify Setup: Run SELECT * FROM customers; to confirm the tables are populated (you should see 10 customers). Check other tables (products, orders, order_details) similarly.
Troubleshooting: If errors occur, ensure CREATE TABLE statements are executed before INSERT statements. Verify foreign key references are correct.

Sample Query for Beginners

To help you get started, here is a simple query to explore the dataset. This query identifies products with low stock levels, a common task for inventory managers in a retail business:

Low Stock Inventory Query

Find products with low stock levels for inventory management

SELECT product_name, category, stock_quantity
FROM products
WHERE stock_quantity < 20
ORDER BY stock_quantity ASC;

Expected Output:

Query Results

Expected output from the low stock inventory query

product_name | category     | stock_quantity
-------------+-------------+---------------
Laptop       | Electronics | 15
Tablet       | Electronics | 20

This query lists products with stock below 20 units, helping the inventory team prioritize restocking. Try running it in your database to see the results, and experiment with modifying it (e.g., change the threshold to 30).

Practice SQL Queries

Here are additional SQL examples to practice with the retail dataset:

Customer Analysis Queries

Essential queries for customer data analysis and business insights

-- 1. Find top 5 customers by total spending
SELECT 
    c.first_name,
    c.last_name,
    c.email,
    SUM(o.total_amount) as total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name, c.email
ORDER BY total_spent DESC
LIMIT 5;

-- 2. Get customer order history with product details
SELECT 
    c.first_name + ' ' + c.last_name as customer_name,
    o.order_date,
    p.product_name,
    od.quantity,
    od.unit_price,
    (od.quantity * od.unit_price) as line_total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id
WHERE c.customer_id = 1
ORDER BY o.order_date DESC;

-- 3. Find customers who haven't placed orders recently
SELECT 
    c.first_name,
    c.last_name,
    c.email,
    MAX(o.order_date) as last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name, c.email
HAVING MAX(o.order_date) < '2025-08-01' OR MAX(o.order_date) IS NULL;

-- 4. Calculate average order value by customer
SELECT 
    c.first_name,
    c.last_name,
    COUNT(o.order_id) as total_orders,
    AVG(o.total_amount) as avg_order_value,
    MIN(o.total_amount) as min_order,
    MAX(o.total_amount) as max_order
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY avg_order_value DESC;

Product and Sales Analysis

Advanced queries for product performance and sales analytics

-- 1. Best selling products by quantity
SELECT 
    p.product_name,
    p.category,
    SUM(od.quantity) as total_sold,
    SUM(od.quantity * od.unit_price) as total_revenue
FROM products p
JOIN order_details od ON p.product_id = od.product_id
GROUP BY p.product_id, p.product_name, p.category
ORDER BY total_sold DESC;

-- 2. Monthly sales report
SELECT 
    strftime('%Y-%m', o.order_date) as month,
    COUNT(o.order_id) as total_orders,
    SUM(o.total_amount) as monthly_revenue,
    AVG(o.total_amount) as avg_order_value
FROM orders o
GROUP BY strftime('%Y-%m', o.order_date)
ORDER BY month;

-- 3. Category performance analysis
SELECT 
    p.category,
    COUNT(DISTINCT od.order_id) as orders_with_category,
    SUM(od.quantity) as total_items_sold,
    SUM(od.quantity * od.unit_price) as category_revenue,
    AVG(od.unit_price) as avg_item_price
FROM products p
JOIN order_details od ON p.product_id = od.product_id
GROUP BY p.category
ORDER BY category_revenue DESC;

-- 4. Products never ordered
SELECT 
    p.product_name,
    p.category,
    p.price,
    p.stock_quantity
FROM products p
LEFT JOIN order_details od ON p.product_id = od.product_id
WHERE od.product_id IS NULL;

-- 5. Customer lifetime value calculation
SELECT 
    c.first_name,
    c.last_name,
    c.join_date,
    COUNT(o.order_id) as total_orders,
    SUM(o.total_amount) as lifetime_value,
    DATEDIFF(CURRENT_DATE, c.join_date) as days_as_customer,
    ROUND(SUM(o.total_amount) / DATEDIFF(CURRENT_DATE, c.join_date), 2) as value_per_day
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name, c.join_date
ORDER BY lifetime_value DESC;

Follow these steps to start querying and build your SQL skills:

Database Setup Commands

Step-by-step commands to set up your SQL learning environment

# Option 1: SQLite Setup (Recommended for Beginners)
# Download SQLite from sqlite.org
# Create database and run SQL script
sqlite3 retail.db < retail_dataset.sql

# Verify setup
sqlite3 retail.db
.tables
SELECT COUNT(*) FROM customers;
SELECT COUNT(*) FROM products;
SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM order_details;

# Option 2: MySQL Setup
mysql -u root -p
CREATE DATABASE retail_db;
USE retail_db;
SOURCE retail_dataset.sql;

# Verify MySQL setup
SHOW TABLES;
SELECT COUNT(*) FROM customers;

# Option 3: PostgreSQL Setup
createdb retail_db
psql retail_db < retail_dataset.sql

# Verify PostgreSQL setup
psql retail_db
\dt
SELECT COUNT(*) FROM customers;

# Basic validation queries
SELECT * FROM customers LIMIT 5;
SELECT * FROM products WHERE category = 'Electronics';
SELECT * FROM orders WHERE total_amount > 1000;

Learning Path

Start with basic SELECT queries to explore the data
Practice filtering with WHERE clauses
Learn JOINs to combine data from multiple tables
Master GROUP BY for aggregations and summaries
Advanced: Window functions and subqueries

Take Your Learning Further

To deepen your SQL skills:

Explore More Data: Download retail datasets from Kaggle to practice similar queries
Build a Project: Export query results to CSV and visualize them with Python (e.g., using Pandas) to create a sales dashboard
Join Communities: Engage with SQL communities on Stack Overflow or forums to ask questions and share your queries

Why This Matters

Working with this retail dataset prepares you for real-world tasks like generating business reports, managing inventory, or analyzing customer behavior. SQL is a critical skill for roles in data analysis, business intelligence, and database management, and this dataset gives you a practical starting point.

Happy querying!