Online store sample database for SQL Server
A ready-to-run SQL Server script with four related tables (customers, products, orders, order items) for JOIN practice. Tested on SQL Server 2022 16.0.4325.4. Updated 19 November 2026.
Online store database for SQL Server
Four related tables (customers, products, orders, order items) for JOIN practice.
This page has it as a SQL Server script: 4 related tables and 3,138 rows, with column types chosen for SQL Server, 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 SQL Server 2022 16.0.4325.4 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
sqlcmd
sqlcmd -S localhost -Q "CREATE DATABASE practice"
sqlcmd -S localhost -d practice -i store-database-sqlserver.sqlSSMS or Azure Data Studio
Create a database, open the .sql file in a new query window connected to it, and press Execute.
Tables and columns
customers 300 rows
| Column | SQL Server type |
|---|---|
| customer_id | NVARCHAR(10) |
| first_name | NVARCHAR(20) |
| last_name | NVARCHAR(20) |
| NVARCHAR(40) | |
| city | NVARCHAR(20) |
| state | NVARCHAR(10) |
| signup_date | DATE |
| segment | NVARCHAR(20) |
products 56 rows
| Column | SQL Server type |
|---|---|
| product_id | NVARCHAR(10) |
| category | NVARCHAR(20) |
| sub_category | NVARCHAR(20) |
| product_name | NVARCHAR(40) |
| unit_price | DECIMAL(9,2) |
| unit_cost | DECIMAL(9,2) |
orders 1,000 rows
| Column | SQL Server type |
|---|---|
| order_id | INT |
| customer_id | NVARCHAR(10) |
| order_date | DATE |
| status | NVARCHAR(20) |
| ship_state | NVARCHAR(10) |
order_items 1,782 rows
| Column | SQL Server type |
|---|---|
| item_id | INT |
| order_id | INT |
| product_id | NVARCHAR(10) |
| quantity | INT |
| unit_price | DECIMAL(9,2) |
Practice questions
answers from SQL Server 2022 16.0.4325.4
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 TOP 5 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;CONCAT(a, b, ...) joins strings and treats NULL as an empty string.
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 CONVERT(char(7), o.order_date, 126) 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 CONVERT(char(7), o.order_date, 126)
ORDER BY month;CONVERT(char(7), date, 126) keeps the first 7 characters of the ISO date (yyyy-mm). It is faster than FORMAT() and short enough to sort a window frame on.
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 TOP 5 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;SELECT TOP n ... ORDER BY returns the first n rows; SQL Server has no LIMIT.
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 |
SQL Server differences used here
- CONCAT(a, b, ...) joins strings and treats NULL as an empty string.
- SELECT TOP n ... ORDER BY returns the first n rows; SQL Server has no LIMIT.
- CONVERT(char(7), date, 126) keeps the first 7 characters of the ISO date (yyyy-mm). It is faster than FORMAT() and short enough to sort a window frame on.