Online store sample database for PostgreSQL
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.
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.sqlpgAdmin or DBeaver
Create a database, open the Query Tool, open the .sql file and run it.
Tables and columns
customers 300 rows
| Column | PostgreSQL type |
|---|---|
| customer_id | VARCHAR(10) |
| first_name | VARCHAR(20) |
| last_name | VARCHAR(20) |
| VARCHAR(40) | |
| city | VARCHAR(20) |
| state | VARCHAR(10) |
| signup_date | DATE |
| segment | VARCHAR(20) |
products 56 rows
| Column | PostgreSQL type |
|---|---|
| product_id | VARCHAR(10) |
| category | VARCHAR(20) |
| sub_category | VARCHAR(20) |
| product_name | VARCHAR(40) |
| unit_price | DECIMAL(9,2) |
| unit_cost | DECIMAL(9,2) |
orders 1,000 rows
| Column | PostgreSQL type |
|---|---|
| order_id | INTEGER |
| customer_id | VARCHAR(10) |
| order_date | DATE |
| status | VARCHAR(20) |
| ship_state | VARCHAR(10) |
order_items 1,782 rows
| Column | PostgreSQL type |
|---|---|
| item_id | INTEGER |
| order_id | INTEGER |
| product_id | VARCHAR(10) |
| quantity | INTEGER |
| unit_price | DECIMAL(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
| tbl | n |
|---|---|
| customers | 300 |
| order_items | 1782 |
| orders | 1000 |
| products | 56 |
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
| category | revenue |
|---|---|
| Technology | 464,694.57 |
| Furniture | 178,055.14 |
| Office Supplies | 58,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_id | customer | revenue |
|---|---|---|
| CU10118 | Steven Lewis | 21,983.66 |
| CU10237 | Elizabeth Flores | 14,375.98 |
| CU10292 | Carol Clark | 14,080.03 |
| CU10031 | Susan Carter | 13,974.42 |
| CU10039 | Mei Nelson | 13,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
| month | orders | revenue |
|---|---|---|
| 2024-01 | 59 | 49,376.78 |
| 2024-02 | 68 | 60,778.52 |
| 2024-03 | 69 | 62,303.52 |
| 2024-04 | 72 | 43,935.69 |
| 2024-05 | 75 | 66,275.68 |
| 2024-06 | 68 | 68,386.75 |
| 2024-07 | 58 | 49,342.41 |
| 2024-08 | 57 | 41,836.65 |
| 2024-09 | 69 | 45,975.24 |
| 2024-10 | 65 | 51,757.88 |
| 2024-11 | 68 | 70,541.49 |
| 2024-12 | 76 | 90,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_name | units | gross_margin |
|---|---|---|
| Workgroup Copier | 65 | 38,620.50 |
| Desktop Copier | 82 | 34,134.62 |
| Cordless Phone Set | 58 | 19,166.43 |
| Label Printer | 79 | 18,494.60 |
| Desk Phone | 68 | 15,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.