DataBrighten

    Restaurant orders sample database for PostgreSQL

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

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

    textnumberdateyes/no

    Restaurant orders database for PostgreSQL

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

    psql

    createdb practice
    psql -d practice -f restaurant-orders-postgresql.sql

    pgAdmin or DBeaver

    Create a database, open the Query Tool, open the .sql file and run it.

    Tables and columns

    restaurant_orders 1,000 rows

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

    Practice questions

    answers from PostgreSQL 15.18

    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(order_total), 2) AS avg_order_value
    FROM o;

    AVG of an integer column returns a numeric, so no cast is needed.

    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
    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(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;

    Multiplying by 1.0 avoids integer division.

    Result

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

    SQL

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

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

    Result

    hoursales
    122,502.50
    192,369.00
    181,928.00

    PostgreSQL differences used here

    • AVG of an integer column returns a numeric, so no cast is needed.
    • Multiplying by 1.0 avoids integer division.
    • substr(text, start, length) cuts out part of a string.
    • LIMIT n at the end returns the first n rows.