Bank transactions sample database for MySQL
A ready-to-run MySQL script with one fictional account: debits, credits, categories and a running balance. Tested on MySQL 8.4.13. Updated 19 November 2026.
Bank transactions database for MySQL
Each row is one posting on a single fictional checking account, starting 1 January 2024 with a $3,200.00 opening balance: salary twice a month, rent on the 1st, and everyday spending by category. The balance column always equals the opening balance plus every amount so far.
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 bank transactions 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 < bank-transactions-mysql.sqlMySQL Workbench
File > Open SQL Script, choose the database with USE practice; then Execute.
Tables and columns
bank_transactions 1,000 rows
| Column | MySQL type |
|---|---|
| transaction_id | VARCHAR(10) |
| date | DATE |
| description | VARCHAR(30) |
| category | VARCHAR(20) |
| type | VARCHAR(10) |
| amount | DECIMAL(9,2) |
| balance | DECIMAL(9,2) |
Practice questions
answers from MySQL 8.4.13
What are the totals of credits and debits?
SQL
SELECT type, COUNT(*) AS transactions, ROUND(SUM(amount), 2) AS total
FROM bank_transactions
GROUP BY type
ORDER BY type;Result
| type | transactions | total |
|---|---|---|
| Credit | 56 | 80,908.71 |
| Debit | 944 | -76,282.78 |
Where does the money go? Debits by category.
SQL
SELECT category, ROUND(-SUM(amount), 2) AS spent
FROM bank_transactions
WHERE type = 'Debit'
GROUP BY category
ORDER BY spent DESC, category;Result
| category | spent |
|---|---|
| Housing | 23,100.00 |
| Groceries | 13,817.98 |
| Shopping | 10,087.46 |
| Dining | 9,106.02 |
| Cash | 5,292.56 |
| Transfer | 4,746.65 |
| Bills | 4,173.27 |
| Transport | 4,071.89 |
| Health | 1,377.09 |
| Entertainment | 509.86 |
What is the net cash flow (credits minus debits) per month?
SQL
SELECT DATE_FORMAT(date, '%Y-%m') AS month, ROUND(SUM(amount), 2) AS net
FROM bank_transactions
GROUP BY DATE_FORMAT(date, '%Y-%m')
ORDER BY month;DATE_FORMAT(date, '%Y-%m') formats a date as year-month text.
Result
| month | net |
|---|---|
| 2024-01 | -255.98 |
| 2024-02 | 642.15 |
| 2024-03 | 91.32 |
| 2024-04 | -14.97 |
| 2024-05 | 433.16 |
| 2024-06 | 477.43 |
| 2024-07 | 101.14 |
| 2024-08 | 428.53 |
| 2024-09 | 507.36 |
| 2024-10 | -283.32 |
| 2024-11 | -322.82 |
| 2024-12 | -443.36 |
| 2025-01 | 734.51 |
| 2025-02 | 2,530.78 |
What are the 5 largest single debits?
SQL
SELECT transaction_id, date, description, amount
FROM bank_transactions
WHERE type = 'Debit'
ORDER BY amount, transaction_id
LIMIT 5;LIMIT n at the end returns the first n rows.
Result
| transaction_id | date | description | amount |
|---|---|---|---|
| TX700002 | 2024-01-01 | Rent payment | -1,650.00 |
| TX700077 | 2024-02-01 | Rent payment | -1,650.00 |
| TX700146 | 2024-03-01 | Rent payment | -1,650.00 |
| TX700221 | 2024-04-01 | Rent payment | -1,650.00 |
| TX700293 | 2024-05-01 | Rent payment | -1,650.00 |
Check the running balance: in how many rows does the balance differ from a window-function running total (plus the opening balance)?
SQL
WITH x AS (
SELECT transaction_id, balance,
SUM(amount) OVER (ORDER BY transaction_id) AS running
FROM bank_transactions
), o AS (
SELECT balance - amount AS opening FROM bank_transactions WHERE transaction_id = (SELECT MIN(transaction_id) FROM bank_transactions)
)
SELECT COUNT(*) AS mismatches
FROM x, o
WHERE ABS(x.balance - (o.opening + x.running)) > 0.01;Window functions need MySQL 8.0 or later.
Result
| mismatches |
|---|
| 0 |
What was the lowest balance, and on which date did it first occur?
SQL
SELECT date, balance
FROM bank_transactions
ORDER BY balance, transaction_id
LIMIT 1;LIMIT n at the end returns the first n rows.
Result
| date | balance |
|---|---|
| 2024-01-14 | 2,657.89 |
MySQL differences used here
- LIMIT n at the end returns the first n rows.
- Window functions need MySQL 8.0 or later.
- DATE_FORMAT(date, '%Y-%m') formats a date as year-month text.