DataBrighten

    Invoices sample database for MySQL

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

    A ready-to-run MySQL script with invoices with due dates, payment dates, tax, status and days late. Tested on MySQL 8.4.13. Updated 19 November 2026.

    textnumberdateyes/no

    Invoices database for MySQL

    Each row is one invoice issued in 2024 to a fictional client: issue and due dates, paid date, subtotal, tax rate and amount, total, status as of 31 January 2025 and days late.

    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 invoices 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 < invoices-mysql.sql

    MySQL Workbench

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

    Tables and columns

    invoices 1,000 rows

    ColumnMySQL type
    invoice_idVARCHAR(20)
    clientVARCHAR(30)
    issue_dateDATE
    due_dateDATE
    paid_dateDATE
    subtotalDECIMAL(9,2)
    tax_rateDECIMAL(8,4)
    taxDECIMAL(8,2)
    totalDECIMAL(10,2)
    statusVARCHAR(20)
    days_lateINT

    Practice questions

    answers from MySQL 8.4.13

    How many invoices are in each status, and what is their total value?

    SQL

    SELECT status, COUNT(*) AS invoices, ROUND(SUM(total), 2) AS total
    FROM invoices
    GROUP BY status
    ORDER BY invoices DESC, status;

    Result

    statusinvoicestotal
    Paid6392,429,763.23
    Paid late215799,346.67
    Overdue146506,974.73
    How many invoices have no paid date yet, and how much is outstanding?

    SQL

    SELECT COUNT(*) AS unpaid, ROUND(SUM(total), 2) AS outstanding
    FROM invoices
    WHERE paid_date IS NULL;

    Result

    unpaidoutstanding
    146506,974.73
    On average, how many days late are invoices that were paid after the due date? (computed from the dates)

    SQL

    SELECT COUNT(*) AS paid_late, ROUND(AVG(CAST(DATEDIFF(paid_date, due_date) AS DECIMAL(18,4))), 2) AS avg_days_late
    FROM invoices
    WHERE paid_date > due_date;

    DATEDIFF(later, earlier) returns the number of days between two dates.

    Result

    paid_lateavg_days_late
    21522.50
    Which 5 clients were invoiced the most?

    SQL

    SELECT client, COUNT(*) AS invoices, ROUND(SUM(total), 2) AS invoiced
    FROM invoices
    GROUP BY client
    ORDER BY invoiced DESC, client
    LIMIT 5;

    LIMIT n at the end returns the first n rows.

    Result

    clientinvoicesinvoiced
    Granite Dental Group71289,367.35
    Maple Design Studio63286,534.26
    Cedar Design Studio66246,002.47
    Delta Logistics LLC59243,071.27
    Redwood Print Shop58237,442.06
    What was the total invoiced per month of issue?

    SQL

    SELECT DATE_FORMAT(issue_date, '%Y-%m') AS month, ROUND(SUM(total), 2) AS invoiced
    FROM invoices
    GROUP BY DATE_FORMAT(issue_date, '%Y-%m')
    ORDER BY month;

    DATE_FORMAT(date, '%Y-%m') formats a date as year-month text.

    Result

    monthinvoiced
    2024-01297,105.81
    2024-02298,363.89
    2024-03292,532.10
    2024-04288,950.21
    2024-05309,437.91
    2024-06366,142.27
    2024-07313,354.37
    2024-08414,329.34
    2024-09304,277.59
    2024-10291,716.19
    2024-11309,157.08
    2024-12250,717.87
    Check the data: how many rows have a total that differs from subtotal + tax by more than one cent?

    SQL

    SELECT COUNT(*) AS mismatches
    FROM invoices
    WHERE ABS(total - (subtotal + tax)) > 0.01;

    Result

    mismatches
    0

    MySQL differences used here

    • DATEDIFF(later, earlier) returns the number of days between two dates.
    • LIMIT n at the end returns the first n rows.
    • DATE_FORMAT(date, '%Y-%m') formats a date as year-month text.