DataBrighten

    Online store sample database for PostgreSQL

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

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

    textnumberdateyes/no

    Online store database for PostgreSQL

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

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

    psql

    createdb practice
    psql -d practice -f store-database-postgresql.sql

    pgAdmin or DBeaver

    Create a database, open the Query Tool, open the .sql file and run it.

    Tables and columns

    customers 300 rows

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

    products 56 rows

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

    orders 1,000 rows

    ColumnPostgreSQL type
    order_idINTEGER
    customer_idVARCHAR(10)
    order_dateDATE
    statusVARCHAR(20)
    ship_stateVARCHAR(10)

    order_items 1,782 rows

    ColumnPostgreSQL type
    item_idINTEGER
    order_idINTEGER
    product_idVARCHAR(10)
    quantityINTEGER
    unit_priceDECIMAL(9,2)

    Practice questions

    answers from PostgreSQL 15.18

    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 to_char(o.order_date, 'YYYY-MM') 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 to_char(o.order_date, 'YYYY-MM')
    ORDER BY month;

    to_char(date, 'YYYY-MM') 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 Copier6538,620.50
    Desktop Copier8234,134.62
    Cordless Phone Set5819,166.43
    Label Printer7918,494.60
    Desk Phone6815,666.66

    PostgreSQL differences used here

    • || joins strings.
    • LIMIT n at the end returns the first n rows.
    • to_char(date, 'YYYY-MM') formats a date as year-month text.