1.sql

CREATE DATABASE CHITTYCOMPANY;
USE CHITTYCOMPANY;

CREATE TABLE CUSTOMER(
    name VARCHAR(20),
    aadharno BIGINT CONSTRAINT twelvedigits CHECK(aadharno BETWEEN 100000000000 AND 999999999999),
    panno VARCHAR(20),
    mobno VARCHAR(20),
    address VARCHAR(50),
    emailid VARCHAR(50),
    PRIMARY KEY(aadharno)
);

-- Insert values into CUSTOMER table
INSERT INTO CUSTOMER VALUES('HARI', 613842823588, 'PAN101', '9496123456', 'SREEHARI, MANACADU, TRIVANDRUM', 'rharikrishnan2020@gmail.com');
INSERT INTO CUSTOMER VALUES('RAM', 613842641211, 'PAN102', '9296123456', 'FLAT NO-60, NEW DELHI', 'ram@gmail.com');
INSERT INTO CUSTOMER VALUES('RAVI', 613842641300, 'PAN103', '9298123456', 'FLAT NO-444, ERNAKULAM', 'ravi@gmail.com');
INSERT INTO CUSTOMER VALUES('RAJU', 613842641000, 'PAN104', '9895123456', 'FLAT 232, KOCHI', 'raju@gmail.com');
-- Adding a customer with no loan
INSERT INTO CUSTOMER VALUES('SITA', 613842800000, 'PAN105', '9876543210', 'HOUSE NO-8, MUMBAI', 'sita@gmail.com');

CREATE TABLE CHITTY (
    chittyno INT PRIMARY KEY,
    branchname VARCHAR(50),
    chittyamt DECIMAL(10, 2),
    startdate DATE,
    installmentmemo INT,
    status VARCHAR(50) CHECK (status IN ('closed', 'notclosed'))
);

-- Insert values into CHITTY table
INSERT INTO CHITTY VALUES(201, 'Main Branch', 10000.00, '2024-01-01', 12, 'notclosed');
INSERT INTO CHITTY VALUES(202, 'Sub Branch', 1000.00, '2024-02-02', 10, 'closed');
INSERT INTO CHITTY VALUES(203, 'Sub Branch', 2000.00, '2024-03-03', 8, 'closed');
INSERT INTO CHITTY VALUES(204, 'Main Branch', 3000.00, '2024-04-04', 6, 'notclosed');
INSERT INTO CHITTY VALUES(205, 'Main Branch', 4000.00, '2024-06-06', 12, 'notclosed');

CREATE TABLE LOAN(
    loanno INT,
    loanbranch VARCHAR(50), 
    loantype VARCHAR(50) CHECK(loantype IN('Homeloan','Personalloan','Carloan','Businessloan')),
    aadharno BIGINT,
    chittyno INT,
    amount DECIMAL(12, 2),
    period VARCHAR(50),
    emi DECIMAL(10, 2),
    FOREIGN KEY(aadharno) REFERENCES CUSTOMER(aadharno) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(chittyno) REFERENCES CHITTY(chittyno) ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(loanno, loanbranch)
);

-- Insert values into LOAN table
INSERT INTO LOAN VALUES (301, 'Main Branch', 'Homeloan', 613842823588, 201, 500000.00, '1 year', 2500.00);
INSERT INTO LOAN VALUES (302, 'Sub Branch', 'Homeloan', 613842641211, 202, 500000.00, '1 year', 2500.00);
INSERT INTO LOAN VALUES (303, 'Sub Branch', 'Personalloan', 613842641300, 203, 1000000.00, '2 years', 3500.00);
INSERT INTO LOAN VALUES (304, 'Main Branch', 'Carloan', 613842641000, 204, 2000000.00, '3 years', 4500.00);

CREATE TABLE CUSTOMERCHITTY (
    aadharno BIGINT,
    branch VARCHAR(50),
    chittyno INT,
    chittalno INT,
    PRIMARY KEY (chittyno, chittalno),
    FOREIGN KEY (aadharno) REFERENCES CUSTOMER(aadharno) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (chittyno) REFERENCES CHITTY(chittyno) ON DELETE CASCADE ON UPDATE CASCADE
);

-- Insert values into CUSTOMERCHITTY table, including an entry for customer without loan
INSERT INTO CUSTOMERCHITTY VALUES(613842823588, 'Trivandrum', 201, 2016138);
INSERT INTO CUSTOMERCHITTY VALUES(613842641211, 'Kottayam', 202, 2026138);
INSERT INTO CUSTOMERCHITTY VALUES(613842641300, 'Thrissur', 203, 2036138);
INSERT INTO CUSTOMERCHITTY VALUES(613842641000, 'Palakkad', 204, 2046138);
INSERT INTO CUSTOMERCHITTY VALUES(613842823588, 'Trivandrum', 205, 2056138);

-- Entry for customer SITA who does not have a loan
INSERT INTO CUSTOMERCHITTY VALUES(613842800000, 'Mumbai', 204, 2046139);

CREATE TABLE CHITTYPAY (
    chittyno INT,
    chittalno INT,
    branchname VARCHAR(50),
    paymentstatus VARCHAR(50) CHECK (paymentstatus IN ('closed', 'notclosed')),
    paid_amount DECIMAL(10, 2),
    paymentdate DATE,
    paymentmode VARCHAR(20) CHECK (paymentmode IN ('cash', 'card', 'netbanking', 'UPI', 'cheque')),
    paymentbranch VARCHAR(20),
    PRIMARY KEY (chittyno, chittalno, branchname),
    FOREIGN KEY (chittyno, chittalno) REFERENCES CUSTOMERCHITTY (chittyno, chittalno) ON DELETE CASCADE ON UPDATE CASCADE
);

-- Insert values into CHITTYPAY table
INSERT INTO CHITTYPAY VALUES(201, 2016138, 'Trivandrum', 'notclosed', 1500.00, '2024-11-15', 'cash', 'Main Branch');
INSERT INTO CHITTYPAY VALUES(202, 2026138, 'Kottayam', 'closed', 2000.00, '2024-10-15', 'card', 'Sub Branch');
INSERT INTO CHITTYPAY VALUES(203, 2036138, 'Thrissur', 'closed', 3000.00, '2024-05-15', 'card', 'Sub Branch');
INSERT INTO CHITTYPAY VALUES(204, 2046138, 'Palakkad', 'notclosed', 5000.00, '2024-05-15', 'card', 'Main Branch');
INSERT INTO CHITTYPAY VALUES(205, 2056138, 'Trivandrum', 'notclosed', 2000.00, '2024-11-20', 'card', 'Sub Branch');

CREATE TABLE CHITTYAUCTION (
    aadharno BIGINT,
    chittyno INT,
    chittalno INT,
    branchname VARCHAR(50),
    auctionamt DECIMAL(10, 2),
    PRIMARY KEY (aadharno, chittyno, chittalno, branchname),
    FOREIGN KEY (aadharno) REFERENCES CUSTOMER (aadharno) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (chittyno) REFERENCES CHITTY (chittyno) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (chittyno, chittalno) REFERENCES CUSTOMERCHITTY (chittyno, chittalno) ON DELETE CASCADE ON UPDATE CASCADE
);

-- Insert values into CHITTYAUCTION table
INSERT INTO CHITTYAUCTION VALUES(613842823588, 201, 2016138, 'Main Branch', 2000.00);
INSERT INTO CHITTYAUCTION VALUES(613842641211, 202, 2026138, 'Sub Branch', 4000.00);
INSERT INTO CHITTYAUCTION VALUES(613842641300, 203, 2036138, 'Sub Branch', 5000.00);
INSERT INTO CHITTYAUCTION VALUES(613842641000, 204, 2046138, 'Main Branch', 6000.00);

CREATE TABLE LOANPAY (
    aadharno BIGINT,
    loanno INT,
    loanbranch VARCHAR(20),
    loanamt DECIMAL(12, 2),
    loanpayday DATE,
    loanpaybranch VARCHAR(20),
    PRIMARY KEY (aadharno, loanno, loanbranch),
    FOREIGN KEY (loanno, loanbranch) REFERENCES LOAN (loanno, loanbranch) ON DELETE CASCADE ON UPDATE CASCADE
);

-- Insert values into LOANPAY table
INSERT INTO LOANPAY VALUES(613842823588, 301, 'Main Branch', 500000.00, '2024-05-15', 'Main Branch');
INSERT INTO LOANPAY VALUES(613842641211, 302, 'Sub Branch', 8000.00, '2024-03-10', 'Sub Branch');
INSERT INTO LOANPAY VALUES(613842641300, 303, 'Sub Branch', 8000.00, '2024-03-10', 'Sub Branch');
INSERT INTO LOANPAY VALUES(613842641000, 304, 'Main Branch', 8000.00, '2024-04-20', 'Main Branch');

-- Queries
SELECT DISTINCT * FROM CUSTOMER JOIN LOAN ON CUSTOMER.aadharno = LOAN.aadharno JOIN CUSTOMERCHITTY ON CUSTOMER.aadharno = CUSTOMERCHITTY.aadharno;

SELECT * FROM CUSTOMER 
JOIN CUSTOMERCHITTY ON CUSTOMER.aadharno = CUSTOMERCHITTY.aadharno LEFT JOIN LOAN ON CUSTOMER.aadharno = LOAN.aadharno WHERE LOAN.aadharno IS NULL;

SELECT * FROM CUSTOMER JOIN LOAN ON CUSTOMER.aadharno = LOAN.aadharno ORDER BY LOAN.amount DESC;

SELECT loanbranch, loantype, COUNT(*) AS number_of_loans FROM LOAN GROUP BY loanbranch, loantype ORDER BY loanbranch, loantype;

SELECT * FROM CUSTOMERCHITTY JOIN CHITTY ON CUSTOMERCHITTY.chittyno = CHITTY.chittyno JOIN CHITTYPAY ON CUSTOMERCHITTY.chittyno = CHITTYPAY.chittyno AND CUSTOMERCHITTY.chittalno = CHITTYPAY.chittalno WHERE CHITTY.branchname <> CHITTYPAY.paymentbranch;

SELECT c.name, ca.chittyno, ca.chittalno, ca.branchname, ca.auctionamt
FROM CUSTOMER AS c 
JOIN CHITTYAUCTION AS ca ON c.aadharno = ca.aadharno
JOIN CHITTY AS ch ON ch.chittyno = ca.chittyno 
WHERE ch.status = 'closed' AND ca.auctionamt = 
    (SELECT MAX(ca1.auctionamt) FROM CHITTYAUCTION AS ca1 JOIN CHITTY AS ch1 ON ch1.chittyno = ca1.chittyno WHERE ch1.status = 'closed' AND ch1.branchname = ca.branchname);

SELECT c.name, ca.chittyno, ca.chittalno, ca.branchname, ca.auctionamt
FROM CUSTOMER AS c 
JOIN CHITTYAUCTION AS ca ON c.aadharno = ca.aadharno
JOIN CHITTY AS ch ON ch.chittyno = ca.chittyno 
WHERE ch.status = 'closed' AND ca.auctionamt = 
    (SELECT MIN(ca1.auctionamt) FROM CHITTYAUCTION AS ca1 JOIN CHITTY AS ch1 ON ch1.chittyno = ca1.chittyno WHERE ch1.status = 'closed' AND ch1.branchname = ca.branchname);

-- Drop the database
DROP DATABASE CHITTYCOMPANY;