Notes

Write a note in this area. It's really easy to share with others. Click here ...



-- 1. Create Database
CREATE DATABASE BankDB;
USE BankDB;

-- 2. Create Tables

-- Branch Table
CREATE TABLE tbl_branch (
branchID INT PRIMARY KEY AUTO_INCREMENT,
br_name VARCHAR(50),
br_city VARCHAR(50),
br_balance DECIMAL(10, 2)
);

-- Customer Table
CREATE TABLE tbl_customer (
customerID INT PRIMARY KEY AUTO_INCREMENT,
cs_name VARCHAR(50),
cs_street VARCHAR(100),
cs_city VARCHAR(50)
);

-- Account Table
CREATE TABLE tbl_account (
accountNo INT PRIMARY KEY AUTO_INCREMENT,
ac_balance DECIMAL(10, 2),
branchID INT,
FOREIGN KEY (branchID) REFERENCES tbl_branch(branchID)
);

-- Loan Table
CREATE TABLE tbl_loan (
loanNo INT PRIMARY KEY AUTO_INCREMENT,
branchID INT,
ln_amount DECIMAL(10, 2),
FOREIGN KEY (branchID) REFERENCES tbl_branch(branchID)
);

-- Depositor Table
CREATE TABLE tbl_depositor (
customerID INT,
accountNo INT,
dp_date DATE,
PRIMARY KEY (customerID, accountNo),
FOREIGN KEY (customerID) REFERENCES tbl_customer(customerID),
FOREIGN KEY (accountNo) REFERENCES tbl_account(accountNo)
);

-- Borrower Table
CREATE TABLE tbl_borrower (
customerID INT,
loanNo INT,
bo_date DATE,
PRIMARY KEY (customerID, loanNo),
FOREIGN KEY (customerID) REFERENCES tbl_customer(customerID),
FOREIGN KEY (loanNo) REFERENCES tbl_loan(loanNo)
);

-- 3. Insert Sample Data

-- Insert into Branch
INSERT INTO tbl_branch (br_name, br_city, br_balance)
VALUES ('Main Branch', 'City A', 100000.00),
('West Branch', 'City B', 150000.00);

-- Insert into Customer
INSERT INTO tbl_customer (cs_name, cs_street, cs_city)
VALUES ('John Doe', '123 Elm St', 'City A'),
('Jane Smith', '456 Oak St', 'City B');

-- Insert into Account
INSERT INTO tbl_account (ac_balance, branchID)
VALUES (5000.00, 1),
(3000.00, 2);

-- Insert into Loan
INSERT INTO tbl_loan (branchID, ln_amount)
VALUES (1, 20000.00),
(2, 15000.00);

-- Insert into Depositor
INSERT INTO tbl_depositor (customerID, accountNo, dp_date)
VALUES (1, 1, '2024-09-23'),
(2, 2, '2024-09-22');

-- Insert into Borrower
INSERT INTO tbl_borrower (customerID, loanNo, bo_date)
VALUES (1, 1, '2024-09-21'),
(2, 2, '2024-09-20');

-- 4. Query Data

-- Fetch all customers
SELECT * FROM tbl_customer;

-- Fetch account details with branch and customer info
SELECT a.accountNo, a.ac_balance, b.br_name, c.cs_name
FROM tbl_account a
JOIN tbl_branch b ON a.branchID = b.branchID
JOIN tbl_depositor d ON a.accountNo = d.accountNo
JOIN tbl_customer c ON d.customerID = c.customerID;

-- Fetch loan details with borrower and branch info
SELECT l.loanNo, l.ln_amount, b.br_name, c.cs_name
FROM tbl_loan l
JOIN tbl_branch b ON l.branchID = b.branchID
JOIN tbl_borrower bo ON l.loanNo = bo.loanNo
JOIN tbl_customer c ON bo.customerID = c.customerID;