Sales orders sample database for SQL Server
A ready-to-run SQL Server script with superstore-style retail orders with sales, cost, discount and profit. Tested on SQL Server 2022 16.0.4325.4. Updated 19 November 2026.
Sales orders database for SQL Server
Each row is one order line from a fictional U.S. office-supply retailer: date, ship mode, customer, region, product, quantity, discount, sales and profit. Profit can be negative on heavily discounted lines, as in real retail data.
This page has it as a SQL Server script: one table and 1,000 rows, with column types chosen for SQL Server, primary keys. The script drops the table if it exists, creates it 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 sales orders 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 sales-orders-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
sales_orders 1,000 rows
| Column | SQL Server type |
|---|---|
| order_id | NVARCHAR(20) |
| order_date | DATE |
| ship_date | DATE |
| ship_mode | NVARCHAR(20) |
| customer_id | NVARCHAR(10) |
| customer_name | NVARCHAR(30) |
| segment | NVARCHAR(20) |
| region | NVARCHAR(20) |
| state | NVARCHAR(10) |
| city | NVARCHAR(20) |
| category | NVARCHAR(20) |
| sub_category | NVARCHAR(20) |
| product_name | NVARCHAR(40) |
| quantity | INT |
| unit_price | DECIMAL(9,2) |
| discount | DECIMAL(6,2) |
| sales | DECIMAL(10,2) |
| cost | DECIMAL(10,2) |
| profit | DECIMAL(9,2) |
Practice questions
answers from SQL Server 2022 16.0.4325.4
How many orders are there, and what are total sales and total profit?
SQL
SELECT COUNT(*) AS orders, ROUND(SUM(sales), 2) AS total_sales, ROUND(SUM(profit), 2) AS total_profit
FROM sales_orders;Result
| orders | total_sales | total_profit |
|---|---|---|
| 1000 | 955,613.76 | 214,946.97 |
Which region has the highest sales? Show orders, sales and profit margin per region.
SQL
SELECT region, COUNT(*) AS orders, ROUND(SUM(sales), 2) AS sales,
ROUND(CAST(100 * SUM(profit) AS DECIMAL(18,6)) / NULLIF(SUM(sales), 0), 2) AS margin_pct
FROM sales_orders
GROUP BY region
ORDER BY sales DESC, region;Integer divided by integer truncates in SQL Server, so the numerator is cast to DECIMAL first.
Result
| region | orders | sales | margin_pct |
|---|---|---|---|
| South | 281 | 299,380.41 | 22.50 |
| Midwest | 229 | 234,801.89 | 23.14 |
| West | 258 | 217,262.68 | 20.60 |
| Northeast | 232 | 204,168.78 | 23.75 |
What were total sales in each month of 2024?
SQL
SELECT CONVERT(char(7), order_date, 126) AS month, ROUND(SUM(sales), 2) AS sales
FROM sales_orders
WHERE YEAR(order_date) = 2024
GROUP BY CONVERT(char(7), 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 | sales |
|---|---|
| 2024-01 | 26,366.14 |
| 2024-02 | 29,322.17 |
| 2024-03 | 66,306.68 |
| 2024-04 | 37,334.21 |
| 2024-05 | 29,706.26 |
| 2024-06 | 17,953.87 |
| 2024-07 | 40,330.93 |
| 2024-08 | 37,906.14 |
| 2024-09 | 51,728.41 |
| 2024-10 | 26,125.41 |
| 2024-11 | 27,640.26 |
| 2024-12 | 38,613.94 |
Which 5 products bring in the most profit?
SQL
SELECT TOP 5 product_name, ROUND(SUM(profit), 2) AS profit
FROM sales_orders
GROUP BY product_name
ORDER BY profit DESC, product_name;SELECT TOP n ... ORDER BY returns the first n rows; SQL Server has no LIMIT.
Result
| product_name | profit |
|---|---|
| Desktop Copier | 34,340.80 |
| Workgroup Copier | 24,284.55 |
| Document Scanner | 18,015.23 |
| Cordless Phone Set | 14,305.57 |
| Desk Phone | 12,295.10 |
How many days does each ship mode take on average, from order to shipping?
SQL
SELECT ship_mode, COUNT(*) AS orders, ROUND(AVG(CAST(DATEDIFF(day, order_date, ship_date) AS DECIMAL(18,4))), 2) AS avg_days
FROM sales_orders
GROUP BY ship_mode
ORDER BY avg_days, ship_mode;DATEDIFF(day, earlier, later) returns the number of days between two dates.
Result
| ship_mode | orders | avg_days |
|---|---|---|
| Same Day | 53 | 0.00 |
| First Class | 144 | 1.52 |
| Second Class | 213 | 2.95 |
| Standard | 590 | 5.02 |
Rank the categories by sales within each region (1 = highest).
SQL
SELECT region, category, ROUND(SUM(sales), 2) AS sales,
RANK() OVER (PARTITION BY region ORDER BY SUM(sales) DESC) AS rnk
FROM sales_orders
GROUP BY region, category
ORDER BY region, rnk, category;RANK() and ROW_NUMBER() OVER (PARTITION BY ...) work in SQL Server 2012 and later.
Result
| region | category | sales | rnk |
|---|---|---|---|
| Midwest | Technology | 162,922.13 | 1 |
| Midwest | Furniture | 53,217.78 | 2 |
| Midwest | Office Supplies | 18,661.98 | 3 |
| Northeast | Technology | 113,871.90 | 1 |
| Northeast | Furniture | 66,661.11 | 2 |
| Northeast | Office Supplies | 23,635.77 | 3 |
| South | Technology | 215,518.37 | 1 |
| South | Furniture | 63,992.98 | 2 |
| South | Office Supplies | 19,869.06 | 3 |
| West | Technology | 147,200.48 | 1 |
| West | Furniture | 51,849.61 | 2 |
| West | Office Supplies | 18,212.59 | 3 |
SQL Server differences used here
- DATEDIFF(day, earlier, later) returns the number of days between two dates.
- Integer divided by integer truncates in SQL Server, so the numerator is cast to DECIMAL first.
- SELECT TOP n ... ORDER BY returns the first n rows; SQL Server has no LIMIT.
- RANK() and ROW_NUMBER() OVER (PARTITION BY ...) work in SQL Server 2012 and later.
- 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.