CREATE TABLE Branches (
Branch_ID INT PRIMARY KEY,
BranchName VARCHAR(50),
ContactNumber VARCHAR(15),
BranchAddress VARCHAR(100),
BankCode INT,
);
INSERT INTO Branches(Branch_ID,BankCode,BranchName,ContactNumber,BranchAddress)
VALUES
(0201,100,'Main Branch', '123-456-7890', '123 Main Street, City'),
(0202,200,'City Branch','011-366-1234','233 Colombo'),
(0203,300,'Malabe Branch','011-276-0744','787 Colombo Road,Malabe'),
(0204,400,'Kadawatha Branch','032-244-7777','715 High Level Road,Kadawatha'),
(0205,500,'Battaramulla Branch','011-447-2200','418,Pannipitiya Road,Battaramulla'),
(0206,600,'Matara Branch','041-222-4444','No.18,Station Road,Matara'),
(0207,700,'Bopitiya Branch','011-224-3172','No.02,Bopitiya Junction,Pamunugama'),
(008,800,'Negombo Branch','031-222-7555','No.454,Main Street,Negombo'),
(0209,900,'Galle Branch','091-223-2242','No.1st Cross Street,Galle'),
(0210,1000,'Maharagama Branch','011-274-6146','No.88,High Level Road,Maharagama'),
(0211,1100,'Kaduwela Branch','011-212-7670','No.155,Avissawela Road, Kaduwela'),
(0212,1200,'Rajagiriya Branch','011-288-4025','No.478,Kotte Road,Rajagiriya'),
(0213,1300,'Kaluthara Branch','034-222-2283','No.314,Galle Road, Kaluthara'),
(0214,1400,'Borrela Branch','011-461-2617','No.71,Silva Mawatha,Borrela'),
(0215,1500,'Kollupitiya Branch','011-245-3233','No.90,Main Street,Kollupitiya');
SELECT*FROM Branches;
CREATE TABLE RegionalManager (
Employee_ID INT PRIMARY KEY,
Name VARCHAR(50),
Address VARCHAR(100),
Email VARCHAR(50),
PhoneNumber VARCHAR(15)
);
INSERT INTO RegionalManager(Employee_ID,Name,Address,Email,PhoneNumber)
VALUES
(101,'Saluni Piris','No.23,Battaramulla Road,Colombo08','[email protected]','076-167-8910'),
(102,'Prasadi Karunara','214,Castle Road,Borrela','[email protected]','077-356-7890'),
(103,'Dilshani Perera','45,Kandy Road,Colombo05','[email protected]','074-123-4567'),
(104,'Kulani Fernando','No9,Unawatuna Road,Galle','[email protected]','078-234-5678'),
(105,'Madushika Weragama','No789,Biyagama Road,Kaduwela','[email protected]','077-123-5434'),
(106,'Nimalsha Sliva','No.09,Pothuarawa Road,Malabe','[email protected]','076-432-5673'),
(107,'Akarsha Bandara','No.32,Negomabo Road,Gampaha','[email protected]','070-345-6780'),
(108,'Nuwan Perera','N0.65/C, Kandy Road,Malabe','[email protected]','072-666-9090'),
(109,'Malsha Heshani','No.19,Mathara Road, Galle','[email protected]','075-787-7654'),
(110,'Dilani Anuradha','No.18 Anuradhapura Road,Kurugala','[email protected]','076-132-5656'),
(111,'Buddikaa Silva','49/1,Kurunagala Road,Kandy','[email protected]','075-890-9999'),
(112,'Janaka Jayawardena','No.40,Battaramulla Road, Malabe','[email protected]','072-901-2345'),
(113,'Sadamalie Ranathunga','No.22, Kadawatha Road,Gampaha','[email protected]','074-222-5555'),
(114,'Kavindu Jayasuriya','No.30,Kotte Road,Rajagiriya','[email protected]','076-555-6666'),
(115,'Nimedani Perera','No.50,Kaduwela Road,Malabe','[email protected]','071-900-7878');
SELECT*FROM RegionalManager;
CREATE TABLE Customers (
Customer_ID INT PRIMARY KEY,
NIC VARCHAR(12),
Name VARCHAR(50),
PhoneNumber VARCHAR(15),
Cu_Address VARCHAR(100),
Branch_ID INT,
);
INSERT INTO Customers(Customer_ID,NIC,Name,PhoneNumber,Cu_Address,Branch_ID)
VALUES
(01,'199012345V','Nuwan Perera','077-345-2210','25A Kandy Road,Colombo 05',0201),
(02,'198723412V','Kasun Sampath','077-123-4567','10,Galle Road,Mount Lavina',0202),
(03,'196312345V','Nanda Kulasingha','076-234-5678','15,Negombo Road,Wattala',0203),
(04,'199434781V','Dilni Silva','074-349-2210','5B, Dehiwala Road,Maharagama',0204),
(05,'199500678V','Chamodi Fernando','071-345-6789','No.30,Panadura Road,Moratuwa',0205),
(06,'199690056V','Nadeesha Rajapaksha','070-456-7890','8/1 Kaduwela Road,Malabe',0206),
(07,'197750207V','Tharidu Bandara','071-567-8901','12,Gampaha Road,Kadawatha',0207),
(08,'199822156V','Saduni Rathnayaka','076-890-1234','40,Kotte Road,Rajagiriya',008),
(09,'199978122V','Ishara Dissanayaka','071-901-5008','3/2,Nawala Road,Nugegoda',0209),
(10,'198090876V','Malitha Gunathilaka','076-788-3000','20,Battaramulla Road, Malabe',0210),
(11,'199134456V','Thilna Perera','070-978-8888','18,Anuradhapura Road,Kurunagala',0211),
(12,'198245677V','Buddika Sliva','077-345-6789','678/B, Athurugiriya Road,Malabe',0212),
(13,'198331233V','Chathuri Munasinghe','070-333-1000','234/A,Walivita Road,Kaduwela',0213),
(14,'196489944V','Baghya Silva','071-999-2910','456/C,Galle Road,Hikkaduwa',0214),
(15,'199856448V','Fathima Mohomad','070-770-6767','56/G Hokandara Road, Athurugiriya',0215);
SELECT*FROM Customers;
CREATE TABLE Accounts (
AccountNo INT PRIMARY KEY,
NIC VARCHAR(12),
AccountType VARCHAR(20),
Balance DECIMAL(15, 2),
Customer_ID INT,
);
INSERT INTO Accounts(AccountNo,Customer_ID,NIC,AccountType,Balance)
VALUES
(1,01,'199012345V','Savings',500000),
(2,02,'198723412V','Current',120000),
(3,03,'196312345V','Savings',150000),
(4,04,'199434781V','Savings',750000),
(5,05,'199500678V','Current',200000),
(6,06,'199690056V','Savings',23400),
(7,07,'197750207V','Savings',100000),
(8,08,'199822156V','Current',350000),
(9,09,'199978122V','Savings',60000),
(10,10,'198090876V','Current',80000),
(11,11,'199134456V','Savings',10000),
(12,12,'198245677V','savings',1500000),
(13,13,'198331233V','Savings',400000),
(14,14,'196489944V','Current',250000),
(15,15,'199856448V','Savings',900000);
SELECT*FROM Accounts;
SELECT*FROM Accounts;
CREATE TABLE Loans (
LoanID INT PRIMARY KEY,
NIC VARCHAR(12),
LoanType VARCHAR(20),
LoanAmount DECIMAL(15, 2),
InterestRate DECIMAL(5, 2),
Customer_ID INT,
);
INSERT INTO Loans(LoanID,Customer_ID,NIC,LoanType,LoanAmount,InterestRate)
VALUES
(1,001,'199012345V','Car Loan',15000000,7.5),
(2,002,'198723412V','Business Loan',120000000,8),
(3,003,'196312345V','Personal Loan',105000000,6),
(4,004,'199434781V','Personal Loan',7500000,6),
(5,005,'199500678V','Car Loan',20000000,7.5),
(6,006,'199690056V','Education Laon',23400000,5.5),
(7,007,'197750207V','Home Loan',10000000,9),
(8,008,'199822156V','Education Loan',3500000,5.5),
(9,009,'199978122V','Education Loan',6000000,5.5),
(10,010,'198090876V','Home Loan',8000000,9),
(11,011,'199134456V','Business Loan',10000000,8),
(12,012,'198245677V','Car Loan',150000000,7.5),
(13,013,'198331233V','Home Loan',40000000,9),
(14,014,'196489944V','Home Loan',250000000,9),
(15,015,'199856448V','Business Loan',90000000,8);
SELECT*FROM Loans;