DataBrighten

    Sales orders sample database for SQL Server

    1,000rows · 1 table · 6 questions with answers

    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.

    textnumberdateyes/no

    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.sql

    SSMS 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

    ColumnSQL Server type
    order_idNVARCHAR(20)
    order_dateDATE
    ship_dateDATE
    ship_modeNVARCHAR(20)
    customer_idNVARCHAR(10)
    customer_nameNVARCHAR(30)
    segmentNVARCHAR(20)
    regionNVARCHAR(20)
    stateNVARCHAR(10)
    cityNVARCHAR(20)
    categoryNVARCHAR(20)
    sub_categoryNVARCHAR(20)
    product_nameNVARCHAR(40)
    quantityINT
    unit_priceDECIMAL(9,2)
    discountDECIMAL(6,2)
    salesDECIMAL(10,2)
    costDECIMAL(10,2)
    profitDECIMAL(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

    orderstotal_salestotal_profit
    1000955,613.76214,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

    regionorderssalesmargin_pct
    South281299,380.4122.50
    Midwest229234,801.8923.14
    West258217,262.6820.60
    Northeast232204,168.7823.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

    monthsales
    2024-0126,366.14
    2024-0229,322.17
    2024-0366,306.68
    2024-0437,334.21
    2024-0529,706.26
    2024-0617,953.87
    2024-0740,330.93
    2024-0837,906.14
    2024-0951,728.41
    2024-1026,125.41
    2024-1127,640.26
    2024-1238,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_nameprofit
    Desktop Copier34,340.80
    Workgroup Copier24,284.55
    Document Scanner18,015.23
    Cordless Phone Set14,305.57
    Desk Phone12,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_modeordersavg_days
    Same Day530.00
    First Class1441.52
    Second Class2132.95
    Standard5905.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

    regioncategorysalesrnk
    MidwestTechnology162,922.131
    MidwestFurniture53,217.782
    MidwestOffice Supplies18,661.983
    NortheastTechnology113,871.901
    NortheastFurniture66,661.112
    NortheastOffice Supplies23,635.773
    SouthTechnology215,518.371
    SouthFurniture63,992.982
    SouthOffice Supplies19,869.063
    WestTechnology147,200.481
    WestFurniture51,849.612
    WestOffice Supplies18,212.593

    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.