DataBrighten

    Online store sample database for MySQL

    3,138rows · 4 tables · 6 questions with answers

    A ready-to-run MySQL script with four related tables (customers, products, orders, order items) for JOIN practice. Tested on MySQL 8.4.13. Updated 19 November 2026.

    textnumberdateyes/no

    Online store database for MySQL

    Four related tables (customers, products, orders, order items) for JOIN practice.

    This page has it as a MySQL script: 4 related tables and 3,138 rows, with column types chosen for MySQL, primary keys and foreign keys. The script drops the tables if they exist, creates them and inserts the rows in one transaction, so you can run it again to reset the data. It is the same fictional data, seed 42, as on the online store dataset page, where it is also available as CSV and Excel.

    We loaded this exact file into MySQL 8.4.13 and ran every question below on it; the answers shown are that output. The same questions, written for SQLite, PostgreSQL, MySQL, SQL Server and DuckDB, return the same results on all five.

    Load it

    mysql client

    mysql -u root -p -e "CREATE DATABASE practice"
    mysql -u root -p practice < store-database-mysql.sql

    MySQL Workbench

    File > Open SQL Script, choose the database with USE practice; then Execute.

    Tables and columns

    customers 300 rows

    ColumnMySQL type
    customer_idVARCHAR(10)
    first_nameVARCHAR(20)
    last_nameVARCHAR(20)
    emailVARCHAR(40)
    cityVARCHAR(20)
    stateVARCHAR(10)
    signup_dateDATE
    segmentVARCHAR(20)

    products 56 rows

    ColumnMySQL type
    product_idVARCHAR(10)
    categoryVARCHAR(20)
    sub_categoryVARCHAR(20)
    product_nameVARCHAR(40)
    unit_priceDECIMAL(9,2)
    unit_costDECIMAL(9,2)

    orders 1,000 rows

    ColumnMySQL type
    order_idINT
    customer_idVARCHAR(10)
    order_dateDATE
    statusVARCHAR(20)
    ship_stateVARCHAR(10)

    order_items 1,782 rows

    ColumnMySQL type
    item_idINT
    order_idINT
    product_idVARCHAR(10)
    quantityINT
    unit_priceDECIMAL(9,2)

    Practice questions

    answers from MySQL 8.4.13

    How many rows does each table have?

    SQL

    SELECT 'customers' AS tbl, COUNT(*) AS n FROM customers
    UNION ALL SELECT 'products', COUNT(*) FROM products
    UNION ALL SELECT 'orders', COUNT(*) FROM orders
    UNION ALL SELECT 'order_items', COUNT(*) FROM order_items
    ORDER BY tbl;

    Result

    tbln
    customers300
    order_items1782
    orders1000
    products56
    What is revenue by product category, counting only delivered orders? (join 3 tables)

    SQL

    SELECT p.category, ROUND(SUM(i.quantity * i.unit_price), 2) AS revenue
    FROM order_items i
    JOIN orders o   ON o.order_id = i.order_id
    JOIN products p ON p.product_id = i.product_id
    WHERE o.status = 'Delivered'
    GROUP BY p.category
    ORDER BY revenue DESC, p.category;

    Result

    categoryrevenue
    Technology464,694.57
    Furniture178,055.14
    Office Supplies58,016.22
    Who are the 5 customers with the highest delivered revenue?

    SQL

    SELECT c.customer_id, CONCAT(c.first_name, ' ', c.last_name) AS customer,
           ROUND(SUM(i.quantity * i.unit_price), 2) AS revenue
    FROM customers c
    JOIN orders o      ON o.customer_id = c.customer_id
    JOIN order_items i ON i.order_id = o.order_id
    WHERE o.status = 'Delivered'
    GROUP BY c.customer_id, c.first_name, c.last_name
    ORDER BY revenue DESC, c.customer_id
    LIMIT 5;

    CONCAT(a, b, ...) joins strings; || is not string concatenation by default.

    Result

    customer_idcustomerrevenue
    CU10118Steven Lewis21,983.66
    CU10237Elizabeth Flores14,375.98
    CU10292Carol Clark14,080.03
    CU10031Susan Carter13,974.42
    CU10039Mei Nelson13,634.07
    How many customers have never placed an order? (LEFT JOIN)

    SQL

    SELECT COUNT(*) AS customers_without_orders
    FROM customers c
    LEFT JOIN orders o ON o.customer_id = c.customer_id
    WHERE o.order_id IS NULL;

    Result

    customers_without_orders
    8
    What was delivered revenue in each month?

    SQL

    SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS month, COUNT(DISTINCT o.order_id) AS orders,
           ROUND(SUM(i.quantity * i.unit_price), 2) AS revenue
    FROM orders o
    JOIN order_items i ON i.order_id = o.order_id
    WHERE o.status = 'Delivered'
    GROUP BY DATE_FORMAT(o.order_date, '%Y-%m')
    ORDER BY month;

    DATE_FORMAT(date, '%Y-%m') formats a date as year-month text.

    Result

    monthordersrevenue
    2024-015949,376.78
    2024-026860,778.52
    2024-036962,303.52
    2024-047243,935.69
    2024-057566,275.68
    2024-066868,386.75
    2024-075849,342.41
    2024-085741,836.65
    2024-096945,975.24
    2024-106551,757.88
    2024-116870,541.49
    2024-127690,255.32
    Which 5 products earn the highest gross margin (price minus cost) across all orders?

    SQL

    SELECT p.product_name, SUM(i.quantity) AS units,
           ROUND(SUM(i.quantity * (i.unit_price - p.unit_cost)), 2) AS gross_margin
    FROM order_items i
    JOIN products p ON p.product_id = i.product_id
    GROUP BY p.product_name
    ORDER BY gross_margin DESC, p.product_name
    LIMIT 5;

    LIMIT n at the end returns the first n rows.

    Result

    product_nameunitsgross_margin
    Workgroup Copier65.0038,620.50
    Desktop Copier82.0034,134.62
    Cordless Phone Set58.0019,166.43
    Label Printer79.0018,494.60
    Desk Phone68.0015,666.66

    MySQL differences used here

    • CONCAT(a, b, ...) joins strings; || is not string concatenation by default.
    • LIMIT n at the end returns the first n rows.
    • DATE_FORMAT(date, '%Y-%m') formats a date as year-month text.