Banking System

Banking System

(Transactions & Balance Management)

This banking system manages:

Customers
Bank accounts
Transactions

Core operations:

Deposit money
Withdraw money
Automatic balance update
Transaction history

Additional features included:

Account balance inquiry
Customer account summary
Total bank deposits
Daily transaction report

STEP 1: Create Database

				
					CREATE DATABASE bank_db;

-- Verify database
SHOW DATABASES;

STEP 2: Use Database

				
					USE bank_db;

-- Check current database
SELECT DATABASE();

STEP 3: Create Customers Table

Stores customer information.

Fields included:

customer_id
customer_name
phone
city
account_open_date

				
					CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_name VARCHAR(100) NOT NULL,
    phone VARCHAR(15),
    city VARCHAR(50),
    account_open_date DATE
);

-- Check structure
DESC customers;

STEP 4: Insert Customers

				
					INSERT INTO customers (customer_name, phone, city, account_open_date) VALUES
('Aman', '9876543210', 'Delhi', '2026-01-01'),
('Priya', '9876501234', 'Mumbai', '2026-01-02'),
('Rahul', '9876512345', 'Jaipur', '2026-01-03');

-- View customers
SELECT * FROM customers;

STEP 5: Create Accounts Table

Stores bank account details.

Fields included:

account_id
customer_id
account_type
balance
account_status

				
					CREATE TABLE accounts (
    account_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    account_type VARCHAR(20),
    balance DECIMAL(12,2),
    account_status VARCHAR(20),

    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Check structure
DESC accounts;

STEP 6: Insert Accounts

				
					INSERT INTO accounts (customer_id, account_type, balance, account_status) VALUES
(1, 'Savings', 10000, 'Active'),
(2, 'Savings', 15000, 'Active'),
(3, 'Current', 20000, 'Active');

-- View accounts
SELECT * FROM accounts;

STEP 7: Create Transactions Table

Stores all deposit and withdrawal transactions.

Fields included:

transaction_id
account_id
transaction_type
amount
transaction_date

				
					CREATE TABLE transactions (
    transaction_id INT PRIMARY KEY AUTO_INCREMENT,
    account_id INT,
    transaction_type VARCHAR(20),
    amount DECIMAL(12,2),
    transaction_date DATE,

    FOREIGN KEY (account_id) REFERENCES accounts(account_id)
);

-- Check structure
DESC transactions;

STEP 8: Deposit Money

Deposit increases account balance.

Example: Deposit ₹5000 in account 1.

				
					INSERT INTO transactions (account_id, transaction_type, amount, transaction_date)
VALUES (1, 'Deposit', 5000, '2026-03-10');

UPDATE accounts
SET balance = balance + 5000
WHERE account_id = 1;

-- Check updated balance
SELECT * FROM accounts;

STEP 9: Withdraw Money

Withdrawal reduces account balance.

Example: Withdraw ₹2000.

				
					INSERT INTO transactions (account_id, transaction_type, amount, transaction_date)
VALUES (1, 'Withdraw', 2000, '2026-03-11');

UPDATE accounts
SET balance = balance - 2000
WHERE account_id = 1;

-- Check updated balance
SELECT * FROM accounts;

STEP 10: Transaction History

View all transactions.

				
					SELECT * FROM transactions;

-- Displays deposit and withdrawal history

STEP 11: Customer Account Summary

Display customer name and current balance.

				
					SELECT customers.customer_name,
       accounts.account_type,
       accounts.balance
FROM accounts
JOIN customers ON accounts.customer_id = customers.customer_id;

-- Customer account summary displayed

STEP 12: Total Bank Deposits

Calculate total money stored in bank.

				
					SELECT SUM(balance) AS total_bank_balance
FROM accounts;

-- Total bank balance displayed

STEP 13: Daily Transaction Report

Show transactions for a specific day.

				
					SELECT *
FROM transactions
WHERE transaction_date = '2026-03-10';

-- Transactions of that day displayed

🎯 Concepts Covered

Students learn:

Primary key
Foreign key
Financial transactions
Balance updates
Deposit & withdrawal logic
JOIN
SUM
Transaction history
Banking system design
Multi-table relationships