Untitled

OVERVIEW: -
A bank management system is a software platform that automates
and manages banking processes, including account management,
transactions, loan processing, customer service, and reporting. It
enhances operational efficiency, ensures data security, and provides
real-time access to financial information for both
customers and bank staff.
KEY CONCEPTS: -
1. Bank: Stores the bank's name, bank's ID, and contact details.
2. Branch: Details each branch with a unique branch ID, location,
contact information, and manager details
3. Loan: Oversees loan information with a unique loan ID, loan
type (e.g., personal, mortgage, auto), principal amount, interest
rate, and repayment schedule.
4. Customer: Manages personal information, account details,
transaction history, and loan information for each bank
customer.
5. Account: Tracks different account types such as savings and
checking, including balance, interest rates, and overdraft limits.
DDL COMMANDS: -
mysql> CREATE TABLE Bank (BankID INT PRIMARY KEY, BankName
VARCHAR(50) );
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TABLE Branch (BranchID INT PRIMARY KEY,BankID
INT,BranchName VARCHAR(50),FOREIGN KEY (BankID) REFERENCES
Bank(BankID) );
Query OK, 0 rows affected (0.07 sec)
mysql> CREATE TABLE Customer (CustomerID INT PRIMARY
KEY,CustomerName VARCHAR(50),BranchID INT,FOREIGN KEY (BranchID)
REFERENCES Branch(BranchID));
Query OK, 0 rows affected (0.16 sec)

mysql> CREATE TABLE Loan (LoanID INT PRIMARY KEY,CustomerID INT,Amount
DECIMAL(10, 2),FOREIGN KEY (CustomerID) REFERENCES
Customer(CustomerID) );
Query OK, 0 rows affected (0.07 sec)
mysql> CREATE TABLE Account (AccountID INT PRIMARY KEY,CustomerID
INT,BranchID INT,Balance DECIMAL
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> DESC Bank;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| BankID | int | NO | PRI | NULL | |
| BankName | varchar(50) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> DESC Branch;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| BranchID | int | NO | PRI | NULL | |
| BankID | int | YES | MUL | NULL | |
| BranchName | varchar(50) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> DESC Customer;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| CustomerID | int | NO | PRI | NULL | |
| CustomerName | varchar(50) | YES | | NULL | |
| BranchID | int | YES | MUL | NULL | |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> DESC Account;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| AccountID | int | NO | PRI | NULL | |
| CustomerID | int | YES | | NULL | |
| BranchID | int | YES | | NULL | |
| Balance | decimal(10,0) | YES | | NULL | |
+------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> DESC Loan;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| LoanID | int | NO | PRI | NULL | |
| CustomerID | int | YES | MUL | NULL | |
| Amount | decimal(10,2) | YES | | NULL | |
+------------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
DML COMMANDS: -
mysql> INSERT INTO Bank VALUES (1, 'SBI'), (2, 'Kotak'), (3,
'ICICI'),(4,'ICICI'),(5,'SBI');
Query OK, 5 rows affected (0.85 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> INSERT INTO Branch VALUES(1, 1, 'SBI Main Branch'),(2, 2, 'Kotak Main
Branch'),(3, 3, 'ICICI Main Branch'),(4, 4, 'Kotak Main Branch'),(5, 5, 'SBI Main
Branch');
Query OK, 5 rows affected (0.03 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> INSERT INTO Customer VALUES(1, 'Ravi', 1),(2, 'Saketh', 2),(3, 'Sai',
3),(4, 'Ram', 1),(5, 'Krishna', 2);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> INSERT INTO Account VALUES(1, 1, 1, 5000.00),(2, 2, 2, 15000.00),(3, 3,
3, 20000.00),(4, 4, 1, 3000.00),(5, 5, 2, 7000.00);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> INSERT INTO Loan VALUES(1, 1, 10000.00),(2, 2, 15000.00),(3, 3,
5000.00),(4, 4, 20000.00),(5, 5, 25000.00);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from Account;
+-----------+------------+----------+---------+
| AccountID | CustomerID | BranchID | Balance |
+-----------+------------+----------+---------+
| 1 | 1 | 1 | 5000 |
| 2 | 2 | 2 | 15000 |
| 3 | 3 | 3 | 20000 |
| 4 | 4 | 1 | 3000 |
| 5 | 5 | 2 | 7000 |
+-----------+------------+----------+---------+
5 rows in set (0.00 sec)

mysql> select * from Bank;
+--------+----------+
| BankID | BankName |
+--------+----------+
| 1 | SBI |
| 2 | Kotak |
| 3 | ICICI |
| 4 | ICICI |
| 5 | SBI |
+--------+----------+
5 rows in set (0.00 sec)
mysql> select * from Branch;
+----------+--------+-------------------+
| BranchID | BankID | BranchName |
+----------+--------+-------------------+
| 1 | 1 | SBI Main Branch |
| 2 | 2 | Kotak Main Branch |
| 3 | 3 | ICICI Main Branch |
| 4 | 4 | Kotak Main Branch |
| 5 | 5 | SBI Main Branch |
+----------+--------+-------------------+
5 rows in set (0.00 sec)

mysql> select * from Customer;
+------------+--------------+----------+
| CustomerID | CustomerName | BranchID |
+------------+--------------+----------+
| 1 | Ravi | 1 |
| 2 | Saketh | 2 |
| 3 | Sai | 3 |
| 4 | Ram | 1 |
| 5 | Krishna | 2 |
+------------+--------------+----------+
5 rows in set (0.00 sec)
mysql> select * from Loan;
+--------+------------+----------+
| LoanID | CustomerID | Amount |
+--------+------------+----------+
| 1 | 1 | 10000.00 |
| 2 | 2 | 15000.00 |
| 3 | 3 | 5000.00 |
| 4 | 4 | 20000.00 |
| 5 | 5 | 25000.00 |
+--------+------------+----------+
VIEWS: -
mysql> CREATE VIEW CustomerAccounts AS SELECT c.CustomerName,
a.Balance FROM Customer c JOIN Account a ON c.CustomerID = a.CustomerID;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from CustomerAccounts;
+--------------+---------+
| CustomerName | Balance |
+--------------+---------+
| Ravi | 5000 |
| Saketh | 15000 |
| Sai | 20000 |
| Ram | 3000 |
| Krishna | 7000 |
+--------------+---------+
5 rows in set (0.00 sec)
TRIGGERS: -
mysql> DELIMITER $$
mysql> CREATE TRIGGER PreventNegativeBalance BEFORE UPDATE ON Account
-> FOR EACH ROW
-> BEGIN
-> IF NEW.Balance < 0 THEN
-> SIGNAL SQLSTATE '45000'
-> SET MESSAGE_TEXT = 'Balance cannot be negative';
-> END IF;
-> END $$
Query OK, 0 rows affected (0.04 sec)
mysql> select * from Acount;
-> $$
ERROR 1146 (42S02): Table 'bankingmanagementsystem.acount' doesn't exist

mysql> select * from Account;
-> $$
+-----------+------------+----------+---------+
| AccountID | CustomerID | BranchID | Balance |
+-----------+------------+----------+---------+
| 1 | 1 | 1 | 5000 |
| 2 | 2 | 2 | 15000 |
| 3 | 3 | 3 | 20000 |
| 4 | 4 | 1 | 3000 |
| 5 | 5 | 2 | 7000 |
+-----------+------------+----------+---------+
5 rows in set (0.00 sec)
GROUP BY AND HAVEING: -
mysql> SELECT BranchID, SUM(Balance) AS TotalBalance FROM AccountGROUP
BY BranchID HAVING TotalBalance > 10000;
+----------+--------------+
| BranchID | TotalBalance |
+----------+--------------+
| 2 | 22000.00 |
| 3 | 20000.00 |
+----------+--------------+
2 rows in set (0.00 sec)
UNION AND INTERSECT: -
mysql> SELECT c1.CustomerName
FROM Customer c1 JOIN Customer c2 ON c1.CustomerID = c2.CustomerID
WHERE c1.BranchID = 1 AND c2.BranchID = 2;
Empty set (0.00 sec)

PROCEDURE: -
mysql> DELIMITER $$
mysql> CREATE PROCEDURE GetCustomerBalance(IN customer_id INT)
-> BEGIN
-> SELECT Balance
-> FROM Account
-> WHERE CustomerID = customer_id;
-> END $$
Query OK, 0 rows affected (0.04 sec)
mysql> SELECT Balance FROM Customer ;
-> $$
ERROR 1054 (42S22): Unknown column 'Balance' in 'field list'
mysql> SELECT Balance FROM Account ;
-> $$
+---------+
| Balance |
+---------+
| 5000 |
| 15000 |
| 20000 |
| 3000 |
| 7000 |
+---------+
5 rows in set (0.00 sec)

ORDEER BY: -
SELECT * FROM Customer ORDER BY CustomerName;
+------------+--------------+----------+
| CustomerID | CustomerName | BranchID |
+------------+--------------+----------+
| 5 | Krishna | 2 |
| 4 | Ram | 1 |
| 1 | Ravi | 1 |
| 3 | Sai | 3 |
| 2 | Saketh | 2 |
+------------+--------------+----------+
5 rows in set (0.00 sec)
EXCEPT: -
mysql> SELECT CustomerName FROM Customer WHERE BranchID = 1AND
CustomerID NOT IN (SELECT CustomerID FROM Customer WHERE BranchID =
2);
+--------------+
| CustomerName |
+--------------+
| Ravi |
| Ram |
+--------------+
2 rows in set (0.00 sec)

NESTED: -
mysql> SELECT CustomerName FROM Customer SELECT CustomerID WHERE
CustomerID IN ( FROM Account WHERE Balance > 10000);
+--------------+
| CustomerName |
+--------------+
| Saketh |
| Sai |
+--------------+
2 rows in set (0.00 sec)