BANKING SYSYTEM DATABASE WITH RECORDS

-

Creation of the Database              RED COLOUR : DEFALUT COMMAND

 mysql> create database 05D8;                                    GREEN COLOUR :OUTPUT
Query OK, 1 row affected (0.12 sec)

useing if the database

mysql> use 05D8;
Database changed

CREATING THE TABLE


mysql> CREATE TABLE Bank(Bank_ID varchar(20) primary key,Bank_Name VARCHAR(255),Bank_Address VARCHAR(255),Bank_Phone VARCHAR(15));
Query OK, 0 rows affected (0.58 sec)

mysql> CREATE TABLE Branch(Branch_ID varchar(25) PRIMARY KEY,Branch_Name VARCHAR(255),Branch_Address VARCHAR(255),Branch_Phone VARCHAR(15),Bank_ID varchar(25),FOREIGN KEY(Bank_ID)REFERENCES Bank(Bank_id));
Query OK, 0 rows affected (0.80 sec)

mysql> CREATE TABLE Customer(Customer_ID varchar(25) PRIMARY KEY,Customer_Name VARCHAR(255),Customer_Address VARCHAR(255),Customer_Phone VARCHAR(15),Customer_Email VARCHAR(225),Customer_DOB DATE);
Query OK, 0 rows affected (0.78 sec)

mysql> CREATE TABLE Account(Account_ID varchar(25) PRIMARY KEY,Account_Type VARCHAR(50),Account_Balance DECIMAL(15,2),Branch_ID varchar(25),Customer_ID varchar(25),FOREIGN KEY(Branch_ID)REFERENCES Branch(Branch_ID),FOREIGN KEY(Customer_ID)REFERENCES Customer(Customer_ID));
Query OK, 0 rows affected (1.56 sec)

mysql> CREATE TABLE Loan(Loan_ID varchar(25) PRIMARY KEY,Loan_Type VARCHAR(100),Loan_Amount DECIMAL(15,2),Interest_Rate DECIMAL(5,2),Loan_Start_Date DATE,Loan_End_Date DATE,Loan_Balance DECIMAL(15,2),Customer_ID varchar(25),FOREIGN KEY(Customer_ID)REFERENCES Customer(Customer_ID));
Query OK, 0 rows affected (0.69 sec)

mysql> CREATE TABLE Transaction(Transaction_ID varchar(25) PRIMARY KEY,Transaction_Type VARCHAR(50),Amount DECIMAL(15,2),Transaction_Date DATE,Account_ID varchar(25),FOREIGN KEY(Account_ID)REFERENCES Account(Account_ID));
Query OK, 0 rows affected (0.89 sec)

mysql> CREATE TABLE Employee(Employee_ID varchar(25) PRIMARY KEY,Employee_Name VARCHAR(255),Employee_Phone VaRCHAR(15),Employee_Role VARCHAR(100),Branch_ID varchar(25),FOREIGN KEY(Branch_ID)REFERENCES Branch(Branch_ID));
Query OK, 0 rows affected (0.62 sec)

show tables
mysql> show tables;
+----------------+
| Tables_in_05D8 |
+----------------+
| Account        |
| Bank           |
| Branch         |
| Customer       |
| Employee       |
| Loan           |
| Transaction    |
+----------------+
7 rows in set (0.00 sec)

structure of a table in BANK

mysql> desc Bank;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| Bank_ID      | varchar(20)  | NO   | PRI | NULL    |       |
| Bank_Name    | varchar(255) | YES  |     | NULL    |       |
| Bank_Address | varchar(255) | YES  |     | NULL    |       |
| Bank_Phone   | varchar(15)  | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

structure of a table in BRANCH

mysql>  Desc Branch;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| Branch_ID      | varchar(25)  | NO   | PRI | NULL    |       |
| Branch_Name    | varchar(255) | YES  |     | NULL    |       |
| Branch_Address | varchar(255) | YES  |     | NULL    |       |
| Branch_Phone   | varchar(15)  | YES  |     | NULL    |       |
| Bank_ID        | varchar(25)  | YES  | MUL | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

 structure of a table in EMPLOYEE 

mysql> Desc Employee;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| Employee_ID    | varchar(25)  | NO   | PRI | NULL    |       |
| Employee_Name  | varchar(255) | YES  |     | NULL    |       |
| Employee_Phone | varchar(15)  | YES  |     | NULL    |       |
| Employee_Role  | varchar(100) | YES  |     | NULL    |       |
| Branch_ID      | varchar(25)  | YES  | MUL | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

structure of a table in CUSTOMER

mysql> Desc Customer;
+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| Customer_ID      | varchar(25)  | NO   | PRI | NULL    |       |
| Customer_Name    | varchar(255) | YES  |     | NULL    |       |
| Customer_Address | varchar(255) | YES  |     | NULL    |       |
| Customer_Phone   | varchar(15)  | YES  |     | NULL    |       |
| Customer_Email   | varchar(225) | YES  |     | NULL    |       |
| Customer_DOB     | date         | YES  |     | NULL    |       |
+------------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

 structure of a table in ACCOUNT 

mysql> Desc Account;
+-----------------+---------------+------+-----+---------+-------+
| Field           | Type          | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+-------+
| Account_ID      | varchar(25)   | NO   | PRI | NULL    |       |
| Account_Type    | varchar(50)   | YES  |     | NULL    |       |
| Account_Balance | decimal(15,2) | YES  |     | NULL    |       |
| Branch_ID       | varchar(25)   | YES  | MUL | NULL    |       |
| Customer_ID     | varchar(25)   | YES  | MUL | NULL    |       |
+-----------------+---------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

structure of a table in TRANSACTION

mysql> Desc Transaction;
+------------------+---------------+------+-----+---------+-------+
| Field            | Type          | Null | Key | Default | Extra |
+------------------+---------------+------+-----+---------+-------+
| Transaction_ID   | varchar(25)   | NO   | PRI | NULL    |       |
| Transaction_Type | varchar(50)   | YES  |     | NULL    |       |
| Amount           | decimal(15,2) | YES  |     | NULL    |       |
| Transaction_Date | date          | YES  |     | NULL    |       |
| Account_ID       | varchar(25)   | YES  | MUL | NULL    |       |
+------------------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

 

 

INSERTING DATA INTO THE TABLES 

Bank_ID

mysql> INSERT INTO Bank (Bank_ID, Bank_Name, Bank_Address, Bank_Phone) VALUES

 ('B001', 'SBI', 'Hyderabad, Telangana', '040-12345678'),

 ('B002', 'HDFC', 'Warangal, Telangana', '040-23456789'),

 ('B003', 'Union Bank', 'Karimnagar, Telangana', '040-34567890'),

 ('B004', 'ICICI', 'Nizamabad, Telangana', '040-45678901'),

 ('B005', 'Axis Bank', 'Khammam, Telangana', '040-56789012');

Query OK, 5 rows affected (0.12 sec)

Records: 5  Duplicates: 0  Warnings: 0

Branch

mysql> INSERT INTO Branch (Branch_ID, Branch_Name, Branch_Address, Branch_Phone, Bank_ID) VALUES

 ('BR001', 'SBI-HYD', 'Hyderabad, Telangana', '040-12345601', 'B001'),

 ('BR002', 'SBI-WGL', 'Warangal, Telangana', '040-12345602', 'B001'),

 ('BR003', 'SBI-KNR', 'Karimnagar, Telangana', '040-12345603', 'B001'),

 ('BR004', 'HDFC-HYD', 'Hyderabad, Telangana', '040-23456701', 'B002'),

 ('BR005', 'HDFC-WGL', 'Warangal, Telangana', '040-23456702', 'B002'),

 ('BR006', 'HDFC-KNR', 'Karimnagar, Telangana', '040-23456703', 'B002'),

 ('BR007', 'Union-HYD', 'Hyderabad, Telangana', '040-34567801', 'B003'),

 ('BR008', 'Union-WGL', 'Warangal, Telangana', '040-34567802', 'B003'),

 ('BR009', 'Union-KNR', 'Karimnagar, Telangana', '040-34567803', 'B003'),

 ('BR010', 'ICICI-HYD', 'Hyderabad, Telangana', '040-45678901', 'B004'),

 ('BR011', 'ICICI-WGL', 'Warangal, Telangana', '040-45678902', 'B004'),

 ('BR012', 'ICICI-KNR', 'Karimnagar, Telangana', '040-45678903', 'B004'),

 ('BR013', 'Axis-HYD', 'Hyderabad, Telangana', '040-56789001', 'B005'),

 ('BR014', 'Axis-WGL', 'Warangal, Telangana', '040-56789002', 'B005'),

 ('BR015', 'Axis-KNR', 'Karimnagar, Telangana', '040-56789003', 'B005');

Query OK, 15 rows affected (0.12 sec)

Records: 15  Duplicates: 0  Warnings: 0

 Employee

mysql> INSERT INTO Employee (Employee_ID, Employee_Name, Employee_Phone, Employee_Role, Branch_ID) VALUES

 ('E001', 'Sathish', '9876543210', 'Manager', 'BR001'),

 ('E002', 'Yashwika', '8765432109', 'Clerk', 'BR002'),

 ('E003', 'Swathi', '7654321098', 'Cashier', 'BR003'),

 ('E004', 'Anil', '6543210987', 'Officer', 'BR004'),

 ('E005', 'Vikas', '5432109876', 'Manager', 'BR005'),

 ('E006', 'Sandeep', '9321654789', 'Assistant Manager', 'BR006'),

 ('E007', 'Lokesh', '8112233445', 'Clerk', 'BR007'),

 ('E008', 'Naresh', '7223344556', 'Cashier', 'BR008'),

 ('E009', 'Harsha', '9998887776', 'Officer', 'BR009'),

 ('E010', 'Divya', '8887776665', 'Manager', 'BR010'),

 ('E011', 'Rajesh', '7776665554', 'Clerk', 'BR011'),

 ('E012', 'Bhavana', '6665554443', 'Cashier', 'BR012'),

 ('E013', 'Karthik', '5554443332', 'Officer', 'BR013'),

 ('E014', 'Pranavi', '4443332221', 'Manager', 'BR014'),

 ('E015', 'Varun', '3332221110', 'Assistant Manager', 'BR015');

Query OK, 15 rows affected (0.13 sec)

Records: 15  Duplicates: 0  Warnings: 0

 Customer

mysql> INSERT INTO Customer (Customer_ID, Customer_Name, Customer_Phone, Customer_Address, Customer_Email, Customer_DOB) VALUES

 ('C001', 'Sathish', '9876543210', 'Hyderabad, Telangana', 'sathish@example.com', '1990-01-01'),

 ('C002', 'Yashwika', '8765432109', 'Warangal, Telangana', 'yashwika@example.com', '1992-02-02'),

 ('C003', 'Swathi', '7654321098', 'Karimnagar, Telangana', 'swathi@example.com', '1993-03-03'),

 ('C004', 'Anil', '6543210987', 'Nizamabad, Telangana', 'anil@example.com', '1994-04-04'),

 ('C005', 'Vikas', '5432109876', 'Khammam, Telangana', 'vikas@example.com', '1995-05-05'),

 ('C006', 'Sandeep', '9321654789', 'Hyderabad, Telangana', 'sandeep@example.com', '1996-06-06'),

 ('C007', 'Lokesh', '8112233445', 'Warangal, Telangana', 'lokesh@example.com', '1997-07-07'),

 ('C008', 'Naresh', '7223344556', 'Karimnagar, Telangana', 'naresh@example.com', '1998-08-08'),

 ('C009', 'Harsha', '9998887776', 'Nizamabad, Telangana', 'harsha@example.com', '1999-09-09'),

 ('C010', 'Divya', '8887776665', 'Khammam, Telangana', 'divya@example.com', '2000-10-10'),

 ('C011', 'Rajesh', '7776665554', 'Hyderabad, Telangana', 'rajesh@example.com', '2001-11-11'),

 ('C012', 'Bhavana', '6665554443', 'Warangal, Telangana', 'bhavana@example.com', '2002-12-12'),

 ('C013', 'Karthik', '5554443332', 'Karimnagar, Telangana', 'karthik@example.com', '2003-01-01'),

 ('C014', 'Pranavi', '4443332221', 'Nizamabad, Telangana', 'pranavi@example.com', '2004-02-02'),

 ('C015', 'Varun', '3332221110', 'Khammam, Telangana', 'varun@example.com', '2005-03-03');

Query OK, 15 rows affected (0.10 sec)

Records: 15  Duplicates: 0  Warnings: 0

Account

mysql> INSERT INTO Account (Account_ID, Account_Type, Account_Balance, Branch_ID, Customer_ID) VALUES

 ('A001', 'Savings', 50000.00, 'BR001', 'C001'),

 ('A002', 'Checking', 30000.00, 'BR002', 'C002'),

 ('A003', 'Savings', 20000.00, 'BR003', 'C003'),

 ('A004', 'Checking', 45000.00, 'BR004', 'C004'),

 ('A005', 'Savings', 100000.00, 'BR005', 'C005'),

 ('A006', 'Checking', 25000.00, 'BR006', 'C006'),

 ('A007', 'Savings', 75000.00, 'BR007', 'C007'),

 ('A008', 'Checking', 60000.00, 'BR008', 'C008'),

 ('A009', 'Savings', 90000.00, 'BR009', 'C009'),

 ('A010', 'Savings', 65000.00, 'BR010', 'C010'),

 ('A011', 'Checking', 55000.00, 'BR011', 'C011'),

 ('A012', 'Savings', 80000.00, 'BR012', 'C012'),

 ('A013', 'Checking', 70000.00, 'BR013', 'C013'),

 ('A014', 'Savings', 95000.00, 'BR014', 'C014'),

 ('A015', 'Checking', 40000.00, 'BR015', 'C015');

Query OK, 15 rows affected (0.18 sec)

Records: 15  Duplicates: 0  Warnings: 0

 Loan

mysql> INSERT INTO Loan (Loan_ID, Loan_Type, Loan_Amount, Interest_Rate, Loan_Start_Date, Loan_End_Date, Loan_Balance, Customer_ID) VALUES

 ('L001', 'Home', 1000000.00, 7.5, '2023-01-01', '2033-01-01', 800000.00, 'C001'),

 ('L002', 'Car', 500000.00, 8.0, '2022-06-15', '2027-06-15', 300000.00, 'C002'),

 ('L003', 'Personal', 200000.00, 10.0, '2023-02-20', '2026-02-20', 150000.00, 'C003'),

 ('L004', 'Education', 750000.00, 6.5, '2021-09-10', '2031-09-10', 600000.00, 'C004'),

 ('L005', 'Home', 1200000.00, 7.0, '2020-07-05', '2030-07-05', 950000.00, 'C005'),

 ('L006', 'Car', 400000.00, 8.5, '2022-11-30', '2027-11-30', 250000.00, 'C006'),

 ('L007', 'Personal', 300000.00, 9.5, '2023-06-01', '2028-06-01', 200000.00, 'C007'),

 ('L008', 'Home', 950000.00, 7.2, '2021-04-15', '2031-04-15', 700000.00, 'C008'),

 ('L009', 'Education', 650000.00, 6.8, '2020-12-20', '2030-12-20', 500000.00, 'C009'),

 ('L010', 'Car', 550000.00, 8.2, '2023-03-10', '2028-03-10', 400000.00, 'C010'),

 ('L011', 'Personal', 250000.00, 10.5, '2022-08-25', '2027-08-25', 100000.00, 'C011'),

 ('L012', 'Home', 1100000.00, 7.3, '2019-06-15', '2029-06-15', 900000.00, 'C012'),

 ('L013', 'Car', 480000.00, 8.0, '2022-05-05', '2027-05-05', 350000.00, 'C013'),

 ('L014', 'Education', 720000.00, 6.7, '2021-07-20', '2031-07-20', 500000.00, 'C014'),

 ('L015', 'Personal', 180000.00, 9.0, '2023-01-10', '2026-01-10', 120000.00, 'C015');

Query OK, 15 rows affected (0.09 sec)

Records: 15  Duplicates: 0  Warnings: 0

 Transaction

mysql> INSERT INTO Transaction (Transaction_ID, Transaction_Type, Amount, Transaction_Date, Account_ID) VALUES

 ('T001', 'Deposit', 20000.00, '2024-03-01', 'A001'),

 ('T002', 'Withdrawal', 10000.00, '2024-03-02', 'A002'),

 ('T003', 'Deposit', 5000.00, '2024-03-03', 'A003'),

 ('T004', 'Withdrawal', 7000.00, '2024-03-04', 'A004'),

 ('T005', 'Deposit', 15000.00, '2024-03-05', 'A005'),

 ('T006', 'Deposit', 25000.00, '2024-03-06', 'A006'),

 ('T007', 'Withdrawal', 12000.00, '2024-03-07', 'A007'),

 ('T008', 'Deposit', 8000.00, '2024-03-08', 'A008'),

 ('T009', 'Withdrawal', 6000.00, '2024-03-09', 'A009'),

 ('T010', 'Deposit', 30000.00, '2024-03-10', 'A010'),

 ('T011', 'Withdrawal', 20000.00, '2024-03-11', 'A011'),

 ('T012', 'Deposit', 17000.00, '2024-03-12', 'A012'),

 ('T013', 'Withdrawal', 9000.00, '2024-03-13', 'A013'),

 ('T014', 'Deposit', 22000.00, '2024-03-14', 'A014'),

 ('T015', 'Withdrawal', 11000.00, '2024-03-15', 'A015');

Query OK, 15 rows affected (0.10 sec)

Records: 15  Duplicates: 0  Warnings: 0

 

 

TO GET WHOLE DATA IN THE TABLE 

COMMANAD :mysql> select * from Bank;   

                        mysql> select * from Branch;                            

                         mysql> select * from Employee; 

                         ETC..........!

TO GET DATA FROM PARTICULAR TABLE 

COMMANAD :mysql> select * from Bank;   

                        mysql> select * from Branch;                            

                         mysql> select * from Employee; 

 

 

RELATIONAL OPERATORS:

COMMANAD

1.mysql> select * from Account where Account_balance >50000;

2.mysql> select * from Loan where Interest_Rate <=8;

3.mysql> select * from Transaction  where Amount !=10000;

 

 

distinct :

COMMANAD

mysql> select distinct Account_Type from Account;

 

LIKE:

COMMANAD

mysql> select * from Customer where Customer_Name like 's%';

 

limit:

1.mysql> select * from Customer limit 5;

2.mysql> select * from Loan order by Loan_Amount Desc  limit 3; 


Order by : 

COMMANAD

1.mysql> select * from Customer order by Customer_Name  asc;

2.mysql> select * from Customer order by Customer_Name  Desc; 



 

UPDATING THE RECORDS IN THE DATABASE 

COMMANADS

mysql> update Customer set   

example :

Customer_name="santhosh",Customer_phone="9848022338",Customer_Email="santhosh@gmail.com" where Customer_ID="C001";






Comments

Post a Comment

Popular posts from this blog

SINGLE LINKED LIST by smd

-
#include <stdio.h> #include <stdlib.h> int count = 0; struct node {       int data;       struct node *next; }  *head = NULL, *newnode, *trav; void create_list() {       int value;       struct node *temp;       newnode = (struct node *)malloc(sizeof(struct node));       printf("Enter the value to be inserted: ");       scanf("%d", &value);       newnode->data = value;       newnode->next = NULL;       if (head == NULL) {           head = newnode;       } else {           temp = head;           while (temp->next != NULL)               temp = temp->next;           temp->next = newnode;       }       count++; } void insert_at_beg...
Read more

CLL by smd

-
PROGRAM #include <stdio.h> #include <stdlib.h> int count = 0; struct node {  int data; struct node *next; }; struct node *head = NULL, *newnode, *temp; void create_circle_linked_list(); void insert_at_begin(); void insert_at_end(); void insert_at_specific_point(); void delete_at_begin(); void delete_at_end(); void delete_at_specific_point(); void display(); void create_circle_linked_list()  {  int value; printf("Enter the value to be inserted: "); scanf("%d", &value); newnode = (struct node*)malloc(sizeof(struct node)); newnode->data = value; if (head == NULL)  {  head = newnode;  newnode->next = head;  } else  {  temp = head;  while (temp->next != head) temp = temp->next; temp->next = newnode; newnode->next = head;     }     count++; } void insert_at_begin()  { int value;  printf("Enter the value to be inserted: "); scanf("%d", &value); newnode = (struct node*)malloc(si...
Read more

QUEUE USING ARRAYS by smd

-
 #include<stdio.h> #define size 100 int queue[size]; int front = -1, rear = -1; void enqueue(int x)  {     if (rear == size - 1)      {         printf("Queue is overflow\n");     } else      {         if (front == -1)          {             front = 0; // Initialize front if queue was empty         }         rear++;         queue[rear] = x;         printf("%d inserted sucessfully\n", x);     } } void dequeue()  {     if (front == -1 || front > rear)      {         printf("Queue is underflow\n");     } else      {         printf("%d deleted successfully\n", queue[front]);         front++;         if (front ...
Read more