DataBrighten

    Restaurant orders sample database for SQL Server

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

    A ready-to-run SQL Server script with order lines with menu items, tickets, order type and payment. Tested on SQL Server 2022 16.0.4325.4. Updated 19 November 2026.

    textnumberdateyes/no

    Restaurant orders database for SQL Server

    Each row is one line on a ticket at a fictional restaurant in 2024: ticket number, date and time, item, category, quantity, price and line total, dine-in/takeout/delivery and payment method.

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

    restaurant_orders 1,000 rows

    ColumnSQL Server type
    order_line_idNVARCHAR(10)
    order_idNVARCHAR(10)
    dateDATE
    timeNVARCHAR(10)
    itemNVARCHAR(20)
    categoryNVARCHAR(20)
    quantityINT
    unit_priceDECIMAL(6,2)
    line_totalDECIMAL(6,2)
    order_typeNVARCHAR(10)
    payment_methodNVARCHAR(10)

    Practice questions

    answers from SQL Server 2022 16.0.4325.4

    What are sales by menu category?

    SQL

    SELECT category, SUM(quantity) AS items, ROUND(SUM(line_total), 2) AS sales
    FROM restaurant_orders
    GROUP BY category
    ORDER BY sales DESC, category;

    Result

    categoryitemssales
    Pizza3765,676.00
    Burgers1902,413.00
    Sandwiches1481,841.00
    Salads1201,259.00
    Sides207956.50
    Drinks237702.00
    Desserts74538.50
    Soups45292.50
    What is the average order value? (group lines into orders first)

    SQL

    WITH o AS (
      SELECT order_id, SUM(line_total) AS order_total
      FROM restaurant_orders
      GROUP BY order_id
    )
    SELECT COUNT(*) AS orders, ROUND(AVG(CAST(order_total AS DECIMAL(18,4))), 2) AS avg_order_value
    FROM o;

    AVG of an INT column returns an INT in SQL Server, so the column is cast to DECIMAL first.

    Result

    ordersavg_order_value
    42931.88
    Which 5 menu items sell the most units?

    SQL

    SELECT TOP 5 item, SUM(quantity) AS units
    FROM restaurant_orders
    GROUP BY item
    ORDER BY units DESC, item;

    SELECT TOP n ... ORDER BY returns the first n rows; SQL Server has no LIMIT.

    Result

    itemunits
    Pepperoni Pizza162
    Fries157
    Cheeseburger133
    Margherita Pizza126
    Soda95
    How do order types compare (dine-in, takeout, delivery)?

    SQL

    SELECT order_type, COUNT(DISTINCT order_id) AS orders, ROUND(SUM(line_total), 2) AS sales
    FROM restaurant_orders
    GROUP BY order_type
    ORDER BY sales DESC, order_type;

    Result

    order_typeorderssales
    Dine-in1956,371.50
    Takeout1454,557.50
    Delivery892,749.50
    What share of sales does each payment method take, in percent?

    SQL

    SELECT payment_method, ROUND(CAST(100 * SUM(line_total) AS DECIMAL(18,6)) / NULLIF((SELECT SUM(line_total) FROM restaurant_orders), 0), 2) AS share_pct
    FROM restaurant_orders
    GROUP BY payment_method
    ORDER BY share_pct DESC, payment_method;

    Integer divided by integer truncates in SQL Server, so the numerator is cast to DECIMAL first.

    Result

    payment_methodshare_pct
    Card65.84
    App23.58
    Cash10.57
    Which hour of the day has the most sales?

    SQL

    SELECT TOP 3 SUBSTRING(time, 1, 2) AS hour, ROUND(SUM(line_total), 2) AS sales
    FROM restaurant_orders
    GROUP BY SUBSTRING(time, 1, 2)
    ORDER BY sales DESC, hour;

    SUBSTRING(text, start, length) cuts out part of a string.

    Result

    hoursales
    122,502.50
    192,369.00
    181,928.00

    SQL Server differences used here

    • AVG of an INT column returns an INT in SQL Server, so the column is cast to DECIMAL first.
    • Integer divided by integer truncates in SQL Server, so the numerator is cast to DECIMAL first.
    • SUBSTRING(text, start, length) cuts out part of a string.
    • SELECT TOP n ... ORDER BY returns the first n rows; SQL Server has no LIMIT.