DataBrighten

    Online store sample database for SQL Server

    3,138rows · 4 tables · 6 questions with answers

    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.

    textnumberdateyes/no

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

    customers 300 rows

    ColumnSQL Server type
    customer_idNVARCHAR(10)
    first_nameNVARCHAR(20)
    last_nameNVARCHAR(20)
    emailNVARCHAR(40)
    cityNVARCHAR(20)
    stateNVARCHAR(10)
    signup_dateDATE
    segmentNVARCHAR(20)

    products 56 rows

    ColumnSQL Server type
    product_idNVARCHAR(10)
    categoryNVARCHAR(20)
    sub_categoryNVARCHAR(20)
    product_nameNVARCHAR(40)
    unit_priceDECIMAL(9,2)
    unit_costDECIMAL(9,2)

    orders 1,000 rows

    ColumnSQL Server type
    order_idINT
    customer_idNVARCHAR(10)
    order_dateDATE
    statusNVARCHAR(20)
    ship_stateNVARCHAR(10)

    order_items 1,782 rows

    ColumnSQL Server type
    item_idINT
    order_idINT
    product_idNVARCHAR(10)
    quantityINT
    unit_priceDECIMAL(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

    tbln
    customers300
    order_items1782
    orders1000
    products56
    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

    categoryrevenue
    Technology464,694.57
    Furniture178,055.14
    Office Supplies58,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_idcustomerrevenue
    CU10118Steven Lewis21,983.66
    CU10237Elizabeth Flores14,375.98
    CU10292Carol Clark14,080.03
    CU10031Susan Carter13,974.42
    CU10039Mei Nelson13,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

    monthordersrevenue
    2024-015949,376.78
    2024-026860,778.52
    2024-036962,303.52
    2024-047243,935.69
    2024-057566,275.68
    2024-066868,386.75
    2024-075849,342.41
    2024-085741,836.65
    2024-096945,975.24
    2024-106551,757.88
    2024-116870,541.49
    2024-127690,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_nameunitsgross_margin
    Workgroup Copier6538,620.50
    Desktop Copier8234,134.62
    Cordless Phone Set5819,166.43
    Label Printer7918,494.60
    Desk Phone6815,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.