Assignment - 4.sql

-- Creating CUSTOMER Table
CREATE TABLE CUSTOMER (
    CUST_NO CHAR(5) PRIMARY KEY CHECK (CUST_NO LIKE 'C____'),
    NAME VARCHAR(100) NOT NULL,
    PHONE_NO VARCHAR(15),
    CITY VARCHAR(50) NOT NULL
);


-- Creating BRANCH Table
CREATE TABLE BRANCH (
    BRANCH_CODE CHAR(5) PRIMARY KEY,
    BRANCH_NAME VARCHAR(100) NOT NULL,
    BRANCH_CITY VARCHAR(50) CHECK (BRANCH_CITY IN ('DELHI', 'MUMBAI', 'KOLKATA', 'CHENNAI'))
);


-- Creating ACCOUNT Table
CREATE TABLE ACCOUNT (
    ACCOUNT_NO CHAR(5) PRIMARY KEY CHECK (ACCOUNT_NO LIKE 'A____'),
    TYPE CHAR(2) CHECK (TYPE IN ('SB', 'FD', 'CA')),
    BALANCE NUMERIC CHECK (BALANCE < 10000000),
    BRANCH_CODE CHAR(5),
    FOREIGN KEY (BRANCH_CODE) REFERENCES BRANCH(BRANCH_CODE)
);


-- Creating DEPOSITOR Table
CREATE TABLE DEPOSITOR (
    CUST_NO CHAR(5),
    ACCOUNT_NO CHAR(5),
    PRIMARY KEY (CUST_NO, ACCOUNT_NO),
    FOREIGN KEY (CUST_NO) REFERENCES CUSTOMER(CUST_NO),
    FOREIGN KEY (ACCOUNT_NO) REFERENCES ACCOUNT(ACCOUNT_NO)
);


-- Creating LOAN Table
CREATE TABLE LOAN (
    LOAN_NO CHAR(5) PRIMARY KEY CHECK (LOAN_NO LIKE 'L____'),
    CUST_NO CHAR(5),
    AMOUNT NUMERIC CHECK (AMOUNT > 1000),
    BRANCH_CODE CHAR(5),
    FOREIGN KEY (CUST_NO) REFERENCES CUSTOMER(CUST_NO),
    FOREIGN KEY (BRANCH_CODE) REFERENCES BRANCH(BRANCH_CODE)
);


-- Creating INSTALLMENT Table
CREATE TABLE INSTALLMENT (
    INST_NO INT CHECK (INST_NO <= 10),
    LOAN_NO CHAR(5),
    INST_AMOUNT NUMERIC NOT NULL,
    PRIMARY KEY (INST_NO, LOAN_NO),
    FOREIGN KEY (LOAN_NO) REFERENCES LOAN(LOAN_NO)
);


-- Inserting records into CUSTOMER table
INSERT INTO CUSTOMER (CUST_NO, NAME, PHONE_NO, CITY) VALUES
('C0001', 'RAJ ANAND SINGH', '9861258466', 'DELHI'),
('C0002', 'ANKITA SINGH', '9879958651', 'BANGALORE'),
('C0003', 'SOUMYA JHA', '9885623344', 'MUMBAI'),
('C0004', 'ABHIJIT MISHRA', '9455845425', 'MUMBAI'),
('C0005', 'YASH SARAF', '9665854585', 'KOLKATA'),
('C0006', 'SWAROOP RAY', '9437855466', 'CHENNAI'),
('C0007', 'SURYA NARAYAN PRADHAN', '9937955212', 'GURGAON'),
('C0008', 'PRANAV PRAVEEN', '9336652441', 'PUNE'),
('C0009', 'STUTI MISRA', '7870266534', 'DELHI'),
('C0010', 'ASLESHA TIWARI', NULL, 'MUMBAI');


-- Inserting records into BRANCH table
INSERT INTO BRANCH (BRANCH_CODE, BRANCH_NAME, BRANCH_CITY) VALUES
('B001', 'JANAKPURI BRANCH', 'DELHI'),
('B002', 'CHANDNICHOWK BRANCH', 'DELHI'),
('B003', 'JUHU BRANCH', 'MUMBAI'),
('B004', 'ANDHERI BRANCH', 'MUMBAI'),
('B005', 'SALTLAKE BRANCH', 'KOLKATA'),
('B006', 'SRIRAMPURAM BRANCH', 'CHENNAI');


-- Inserting records into ACCOUNT table
INSERT INTO ACCOUNT (ACCOUNT_NO, TYPE, BALANCE, BRANCH_CODE) VALUES
('A0001', 'SB', 200000, 'B003'),
('A0002', 'SB', 15000, 'B002'),
('A0003', 'CA', 850000, 'B004'),
('A0004', 'CA', 35000, 'B004'),
('A0005', 'FD', 28500, 'B005'),
('A0006', 'FD', 550000, 'B005'),
('A0007', 'SB', 48000, 'B001'),
('A0008', 'SB', 7200, 'B002'),
('A0009', 'SB', 18750, 'B003'),
('A0010', 'FD', 99000, 'B004');


-- Inserting records into DEPOSITOR table
INSERT INTO DEPOSITOR (CUST_NO, ACCOUNT_NO) VALUES
('C0003', 'A0001'),
('C0004', 'A0001'),
('C0004', 'A0002'),
('C0006', 'A0003'),
('C0006', 'A0004'),
('C0001', 'A0005'),
('C0002', 'A0005'),
('C0010', 'A0006'),
('C0009', 'A0007'),
('C0008', 'A0008'),
('C0007', 'A0009'),
('C0006', 'A0010');


-- Inserting records into LOAN table
INSERT INTO LOAN (LOAN_NO, CUST_NO, AMOUNT, BRANCH_CODE) VALUES
('L0001', 'C0005', 3000000, 'B006'),
('L0002', 'C0001', 50000, 'B005'),
('L0003', 'C0002', 8000000, 'B004'),
('L0004', 'C0010', 100000, 'B004'),
('L0005', 'C0009', 9500000, 'B005'),
('L0006', 'C0008', 25000, 'B006');


-- Inserting records into INSTALLMENT table
INSERT INTO INSTALLMENT (INST_NO, LOAN_NO, INST_AMOUNT) VALUES
(1, 'L0005', 500000),
(1, 'L0002', 10000),
(1, 'L0003', 50000),
(1, 'L0004', 20000),
(2, 'L0005', 500000),
(1, 'L0006', 3000),
(2, 'L0002', 10000),
(3, 'L0002', 10000),
(2, 'L0003', 50000),
(2, 'L0004', 20000);