DataBrighten

    Online store sample database for SQLite

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

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

    textnumberdateyes/no

    Online store database for SQLite

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

    This page has it as a SQLite script: 4 related tables and 3,138 rows, with column types chosen for SQLite, 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 SQLite 3.37.2 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

    Command line

    sqlite3 practice.db < store-database-sqlite.sql
    sqlite3 practice.db ".tables"

    Or skip the script

    Download store-database.sqlite and open it in DB Browser for SQLite, DBeaver or: sqlite3 store-database.sqlite

    Tables and columns

    customers 300 rows

    ColumnSQLite type
    customer_idTEXT
    first_nameTEXT
    last_nameTEXT
    emailTEXT
    cityTEXT
    stateTEXT
    signup_dateTEXT
    segmentTEXT

    products 56 rows

    ColumnSQLite type
    product_idTEXT
    categoryTEXT
    sub_categoryTEXT
    product_nameTEXT
    unit_priceREAL
    unit_costREAL

    orders 1,000 rows

    ColumnSQLite type
    order_idINTEGER
    customer_idTEXT
    order_dateTEXT
    statusTEXT
    ship_stateTEXT

    order_items 1,782 rows

    ColumnSQLite type
    item_idINTEGER
    order_idINTEGER
    product_idTEXT
    quantityINTEGER
    unit_priceREAL

    Practice questions

    answers from SQLite 3.37.2

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

    || joins strings.

    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 strftime('%Y-%m', o.order_date) 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 strftime('%Y-%m', o.order_date)
    ORDER BY month;

    strftime('%Y-%m', date) turns a date into 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 Copier6538,620.50
    Desktop Copier8234,134.62
    Cordless Phone Set5819,166.43
    Label Printer7918,494.60
    Desk Phone6815,666.66

    SQLite differences used here

    • || joins strings.
    • LIMIT n at the end returns the first n rows.
    • strftime('%Y-%m', date) turns a date into year-month text.