Invoices sample database for MySQL
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.
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.sqlMySQL Workbench
File > Open SQL Script, choose the database with USE practice; then Execute.
Tables and columns
invoices 1,000 rows
| Column | MySQL type |
|---|---|
| invoice_id | VARCHAR(20) |
| client | VARCHAR(30) |
| issue_date | DATE |
| due_date | DATE |
| paid_date | DATE |
| subtotal | DECIMAL(9,2) |
| tax_rate | DECIMAL(8,4) |
| tax | DECIMAL(8,2) |
| total | DECIMAL(10,2) |
| status | VARCHAR(20) |
| days_late | INT |
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
| status | invoices | total |
|---|---|---|
| Paid | 639 | 2,429,763.23 |
| Paid late | 215 | 799,346.67 |
| Overdue | 146 | 506,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
| unpaid | outstanding |
|---|---|
| 146 | 506,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_late | avg_days_late |
|---|---|
| 215 | 22.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
| client | invoices | invoiced |
|---|---|---|
| Granite Dental Group | 71 | 289,367.35 |
| Maple Design Studio | 63 | 286,534.26 |
| Cedar Design Studio | 66 | 246,002.47 |
| Delta Logistics LLC | 59 | 243,071.27 |
| Redwood Print Shop | 58 | 237,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
| month | invoiced |
|---|---|
| 2024-01 | 297,105.81 |
| 2024-02 | 298,363.89 |
| 2024-03 | 292,532.10 |
| 2024-04 | 288,950.21 |
| 2024-05 | 309,437.91 |
| 2024-06 | 366,142.27 |
| 2024-07 | 313,354.37 |
| 2024-08 | 414,329.34 |
| 2024-09 | 304,277.59 |
| 2024-10 | 291,716.19 |
| 2024-11 | 309,157.08 |
| 2024-12 | 250,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.