DataBrighten

    Sales orders sample database for SQLite

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

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

    textnumberdateyes/no

    Sales orders database for SQLite

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

    Command line

    sqlite3 practice.db < sales-orders-sqlite.sql
    sqlite3 practice.db ".tables"

    Or skip the script

    Download sales-orders.sqlite and open it in DB Browser for SQLite, DBeaver or: sqlite3 sales-orders.sqlite

    Tables and columns

    sales_orders 1,000 rows

    ColumnSQLite type
    order_idTEXT
    order_dateTEXT
    ship_dateTEXT
    ship_modeTEXT
    customer_idTEXT
    customer_nameTEXT
    segmentTEXT
    regionTEXT
    stateTEXT
    cityTEXT
    categoryTEXT
    sub_categoryTEXT
    product_nameTEXT
    quantityINTEGER
    unit_priceREAL
    discountREAL
    salesREAL
    costREAL
    profitREAL

    Practice questions

    answers from SQLite 3.37.2

    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;

    Multiplying by 1.0 forces decimal division instead of integer division.

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

    strftime('%Y-%m', date) turns a date into 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(julianday(ship_date) - julianday(order_date) AS INTEGER)), 2) AS avg_days
    FROM sales_orders
    GROUP BY ship_mode
    ORDER BY avg_days, ship_mode;

    julianday(b) - julianday(a) gives 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 SQLite 3.25 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

    SQLite differences used here

    • julianday(b) - julianday(a) gives the number of days between two dates.
    • Multiplying by 1.0 forces decimal division instead of integer division.
    • LIMIT n at the end returns the first n rows.
    • Window functions need SQLite 3.25 or later.
    • strftime('%Y-%m', date) turns a date into year-month text.