DataBrighten

    Bank transactions sample database for SQL Server

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

    A ready-to-run SQL Server script with one fictional account: debits, credits, categories and a running balance. Tested on SQL Server 2022 16.0.4325.4. Updated 19 November 2026.

    textnumberdateyes/no

    Bank transactions database for SQL Server

    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 SQL Server script: one table and 1,000 rows, with column types chosen for SQL Server, 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 SQL Server 2022 16.0.4325.4 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

    sqlcmd

    sqlcmd -S localhost -Q "CREATE DATABASE practice"
    sqlcmd -S localhost -d practice -i bank-transactions-sqlserver.sql

    SSMS or Azure Data Studio

    Create a database, open the .sql file in a new query window connected to it, and press Execute.

    Tables and columns

    bank_transactions 1,000 rows

    ColumnSQL Server type
    transaction_idNVARCHAR(10)
    dateDATE
    descriptionNVARCHAR(30)
    categoryNVARCHAR(20)
    typeNVARCHAR(10)
    amountDECIMAL(9,2)
    balanceDECIMAL(9,2)

    Practice questions

    answers from SQL Server 2022 16.0.4325.4

    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

    typetransactionstotal
    Credit5680,908.71
    Debit944-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

    categoryspent
    Housing23,100.00
    Groceries13,817.98
    Shopping10,087.46
    Dining9,106.02
    Cash5,292.56
    Transfer4,746.65
    Bills4,173.27
    Transport4,071.89
    Health1,377.09
    Entertainment509.86
    What is the net cash flow (credits minus debits) per month?

    SQL

    SELECT CONVERT(char(7), date, 126) AS month, ROUND(SUM(amount), 2) AS net
    FROM bank_transactions
    GROUP BY CONVERT(char(7), date, 126)
    ORDER BY month;

    CONVERT(char(7), date, 126) keeps the first 7 characters of the ISO date (yyyy-mm). It is faster than FORMAT() and short enough to sort a window frame on.

    Result

    monthnet
    2024-01-255.98
    2024-02642.15
    2024-0391.32
    2024-04-14.97
    2024-05433.16
    2024-06477.43
    2024-07101.14
    2024-08428.53
    2024-09507.36
    2024-10-283.32
    2024-11-322.82
    2024-12-443.36
    2025-01734.51
    2025-022,530.78
    What are the 5 largest single debits?

    SQL

    SELECT TOP 5 transaction_id, date, description, amount
    FROM bank_transactions
    WHERE type = 'Debit'
    ORDER BY amount, transaction_id;

    SELECT TOP n ... ORDER BY returns the first n rows; SQL Server has no LIMIT.

    Result

    transaction_iddatedescriptionamount
    TX7000022024-01-01Rent payment-1,650.00
    TX7000772024-02-01Rent payment-1,650.00
    TX7001462024-03-01Rent payment-1,650.00
    TX7002212024-04-01Rent payment-1,650.00
    TX7002932024-05-01Rent 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;

    RANK() and ROW_NUMBER() OVER (PARTITION BY ...) work in SQL Server 2012 and later.

    Result

    mismatches
    0
    What was the lowest balance, and on which date did it first occur?

    SQL

    SELECT TOP 1 date, balance
    FROM bank_transactions
    ORDER BY balance, transaction_id;

    SELECT TOP n ... ORDER BY returns the first n rows; SQL Server has no LIMIT.

    Result

    datebalance
    2024-01-142,657.89

    SQL Server differences used here

    • SELECT TOP n ... ORDER BY returns the first n rows; SQL Server has no LIMIT.
    • RANK() and ROW_NUMBER() OVER (PARTITION BY ...) work in SQL Server 2012 and later.
    • CONVERT(char(7), date, 126) keeps the first 7 characters of the ISO date (yyyy-mm). It is faster than FORMAT() and short enough to sort a window frame on.