DataBrighten

    Sales orders sample database for MySQL

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

    A ready-to-run MySQL script with superstore-style retail orders with sales, cost, discount and profit. Tested on MySQL 8.4.13. Updated 19 November 2026.

    textnumberdateyes/no

    Sales orders database for MySQL

    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 MySQL script: one table and 1,000 rows, with column types chosen for MySQL, 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 MySQL 8.4.13 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

    mysql client

    mysql -u root -p -e "CREATE DATABASE practice"
    mysql -u root -p practice < sales-orders-mysql.sql

    MySQL Workbench

    File > Open SQL Script, choose the database with USE practice; then Execute.

    Tables and columns

    sales_orders 1,000 rows

    ColumnMySQL type
    order_idVARCHAR(20)
    order_dateDATE
    ship_dateDATE
    ship_modeVARCHAR(20)
    customer_idVARCHAR(10)
    customer_nameVARCHAR(30)
    segmentVARCHAR(20)
    regionVARCHAR(20)
    stateVARCHAR(10)
    cityVARCHAR(20)
    categoryVARCHAR(20)
    sub_categoryVARCHAR(20)
    product_nameVARCHAR(40)
    quantityINT
    unit_priceDECIMAL(9,2)
    discountDECIMAL(6,2)
    salesDECIMAL(10,2)
    costDECIMAL(10,2)
    profitDECIMAL(9,2)

    Practice questions

    answers from MySQL 8.4.13

    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(1.0 * 100 * SUM(profit) / NULLIF(SUM(sales), 0), 2) AS margin_pct
    FROM sales_orders
    GROUP BY region
    ORDER BY sales DESC, region;

    1.0 * a / b gives a decimal result.

    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 DATE_FORMAT(order_date, '%Y-%m') AS month, ROUND(SUM(sales), 2) AS sales
    FROM sales_orders
    WHERE YEAR(order_date) = 2024
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
    ORDER BY month;

    DATE_FORMAT(date, '%Y-%m') formats a date as year-month text.

    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 product_name, ROUND(SUM(profit), 2) AS profit
    FROM sales_orders
    GROUP BY product_name
    ORDER BY profit DESC, product_name
    LIMIT 5;

    LIMIT n at the end returns the first n rows.

    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(ship_date, order_date) AS DECIMAL(18,4))), 2) AS avg_days
    FROM sales_orders
    GROUP BY ship_mode
    ORDER BY avg_days, ship_mode;

    DATEDIFF(later, earlier) 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;

    Window functions need MySQL 8.0 or 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

    MySQL differences used here

    • DATEDIFF(later, earlier) returns the number of days between two dates.
    • 1.0 * a / b gives a decimal result.
    • LIMIT n at the end returns the first n rows.
    • Window functions need MySQL 8.0 or later.
    • DATE_FORMAT(date, '%Y-%m') formats a date as year-month text.