DataBrighten

    Restaurant orders sample database for MySQL

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

    A ready-to-run MySQL script with order lines with menu items, tickets, order type and payment. Tested on MySQL 8.4.13. Updated 19 November 2026.

    textnumberdateyes/no

    Restaurant orders database for MySQL

    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 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 restaurant 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 < restaurant-orders-mysql.sql

    MySQL Workbench

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

    Tables and columns

    restaurant_orders 1,000 rows

    ColumnMySQL type
    order_line_idVARCHAR(10)
    order_idVARCHAR(10)
    dateDATE
    timeVARCHAR(10)
    itemVARCHAR(20)
    categoryVARCHAR(20)
    quantityINT
    unit_priceDECIMAL(6,2)
    line_totalDECIMAL(6,2)
    order_typeVARCHAR(10)
    payment_methodVARCHAR(10)

    Practice questions

    answers from MySQL 8.4.13

    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
    Pizza376.005,676.00
    Burgers190.002,413.00
    Sandwiches148.001,841.00
    Salads120.001,259.00
    Sides207.00956.50
    Drinks237.00702.00
    Desserts74.00538.50
    Soups45.00292.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;

    The cast to DECIMAL keeps the average exact before rounding.

    Result

    ordersavg_order_value
    42931.88
    Which 5 menu items sell the most units?

    SQL

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

    LIMIT n at the end returns the first n rows.

    Result

    itemunits
    Pepperoni Pizza162.00
    Fries157.00
    Cheeseburger133.00
    Margherita Pizza126.00
    Soda95.00
    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(1.0 * 100 * SUM(line_total) / 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;

    1.0 * a / b gives a decimal result.

    Result

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

    SQL

    SELECT 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
    LIMIT 3;

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

    Result

    hoursales
    122,502.50
    192,369.00
    181,928.00

    MySQL differences used here

    • The cast to DECIMAL keeps the average exact before rounding.
    • 1.0 * a / b gives a decimal result.
    • SUBSTRING(text, start, length) cuts out part of a string.
    • LIMIT n at the end returns the first n rows.