11_05_2022_bank.sql

USE BANKING;
CREATE TABLE LOAN   (
LID INT  PRIMARY KEY ,
CUSTID VARCHAR(6),
BID VARCHAR(6) ,
LOAN_AMOUNT BIGINT NOT NULL,
FOREIGN KEY (CUSTID) REFERENCES CUSTOMER(CUSTID),
FOREIGN KEY(BID) REFERENCES BRANCH(BID)
);
 
describe LOAN ;
 

CREATE TABLE BRANCH (
BID VARCHAR(6) PRIMARY KEY,
BNAME VARCHAR(25) NOT NULL,
BCITY VARCHAR(30) NOT NULL
) ;

CREATE TABLE CUSTOMER (
CUSTID VARCHAR(6) PRIMARY KEY,
FNAME VARCHAR(30)NOT NULL ,
MNAME VARCHAR(30),
LNAME VARCHAR(30) NOT NULL,
CITY VARCHAR(15) NOT NULL ,
MOBILENO VARCHAR(10) NOT NULL,
OCCUPATION VARCHAR(10),
DOB DATE
);

CREATE TABLE ACCOUNTS (
ACCNUMBER VARCHAR(6) PRIMARY KEY ,
LID INT,
CUSTID VARCHAR(6),
BID VARCHAR(6),
OPENING_BALANCE BIGINT,
AOD date,
ACCTYPE ENUM("LOAN","SAVINGS","CURRENT" ),
ACCSTATUS ENUM("OPEN","CLOSE"),
FOREIGN KEY (CUSTID) REFERENCES  CUSTOMER(CUSTID),
FOREIGN KEY(BID) REFERENCES BRANCH(BID),
FOREIGN KEY(LID) REFERENCES LOAN(LID)
);

CREATE TABLE TRAN_DETAILS(
TNUMBER BIGINT PRIMARY KEY AUTO_INCREMENT,
ACCNUMBER VARCHAR(10) ,
DOT DATE NOT NULL,
MEDIUM_OF_TRANSACTION ENUM("BT","CD"),
TRANSACTION_TYPE ENUM("CREDIT"),
TRANSACTION_AMOUNT BIGINT NOT NULL,
FOREIGN KEY (ACCNUMBER) REFERENCES ACCOUNTS(ACCNUMBER)
);
SELECT * FROM TRAN_DETAILS;
ALTER TABLE TRAN_dETAILS MODIFY COLUMN TRANSACTION_TYPE ENUM("CREDIT") ;
/* DATA INSERTION  branch , customers ,loans,accounts,tran_details */
SELECT * FROM ACCOUNTS;
SELECT * FROM CUSTOMER ;
SELECT * FROM BRANCH ;
SELECT * FROM TRAN_DETAILS;
SELECT * FROM LOAN;

/*QUERIES*/

/* 1   ALL LOANS  BY CUSTID =1
*/
SELECT LID,LOAN_AMOUNT,CUSTID FROM LOAN WHERE CUSTID=101;

/* 2 FIND LOAN_BALANCE FOR A CUSTID 104 */
SELECT * FROM LOAN;
SELECT * FROM ACCOUNTS;
SELECT * FROM TRAN_DETAILS;
SELECT OPENING_BALANCE FROM ACCOUNTS  WHERE CUSTID=104 AND ACCNUMBER = 123459;

/*3 TOTAL LOAN BAL FOR CUST_ID = 102  */

SELECT SUM(T.TRANSACTION_AMOUNT) INTO @PAID FROM TRAN_DETAILS T JOIN ACCOUNTS C ON  C.ACCNUMBER= T.ACCNUMBER WHERE C.CUSTID=102;
SELECT @PAID;
SELECT  ROUND(SUM(L.LOAN_AMOUNT) - @PAID) AS BALANCE FROM LOAN L WHERE L.CUSTID = 102;

/*4  FIND OUT WHICH BRANCH HAS MOST CUSTOMERS */ 
SELECT X.BID,MAX(X.COUNTS) FROM(
SELECT COUNT(DISTINCT(CUSTID)) AS COUNTS,BID FROM ACCOUNTS GROUP BY BID) X
HAVING COUNTS = MAX(X.COUNTS) ;



/* 5 BRANCH WHICH HAS PAID OUT MOST LOANS */
SELECT *  FROM LOAN 
WHERE BI  IN (SELECT BID,SUM(LOAN_AMOUNT)AS LOANS FROM LOAN GROUP  BY BID HAVING MAX(LOANS))
 ;
SELECT SUM(LOAN_AMOUNT) AS LOANS,BID FROM LOAN GROUP BY BID ORDER BY LOANS DESC LIMIT 1; # USING LIMIT
SELECT * FROM LOAN;

/* 6 in how many transactions customer closed his loan */
SELECT COUNT(TNUMBER),ACCNUMBER FROM TRAN_DETAILS GROUP BY ACCNUMBER ; 
SELECT COUNT(T.TNUMBER),T.ACCNUMBER FROM TRAN_DETAILS T JOIN ACCOUNTS A ON A.ACCNUMBER = T.ACCNUMBER WHERE ACCSTATUS="CLOSE" GROUP BY ACCNUMBER ; 

# 7 if opening_bal = 0 and astatus = close then sum of tran_amt = loan_amt
SELECT *FROM ACCOUNTS;
SELECT * FROM LOAN;
SELECT * FROM TRAN_DETAILS;

SELECT L.LID,L.CUSTID,A.ACCNUMBER,L.LOAN_AMOUNT,SUM(T.TRANSACTION_AMOUNT) FROM LOAN L INNER JOIN ACCOUNTS A ON A.LID=L.LID
JOIN TRAN_DETAILS T ON T.ACCNUMBER =A.ACCNUMBER
WHERE A.ACCSTATUS = "CLOSE" 
GROUP BY T.ACCNUMBER;

#8.. FIND OUT WHICH CUSTOMER HAS HOW MANY OPEN LOAN ACC_STATUS
SELECT * FROM ACCOUNTS;
SELECT CUSTID,COUNT(ACCSTATUS) FROM ACCOUNTS WHERE ACCSTATUS="OPEN" GROUP BY CUSTID;

# 9 SHOW ME ALL CONSTRAINT VALIDATIONS ARE GOING THROUGH
SELECT * FROM TRAN_DETAILS;
INSERT INTO TRAN_DETAILS VALUES (569320230,123456,"2022-05-11","DE","CREDIT",1000);
#Error Code: 1265. Data truncated for column 'MEDIUM_OF_TRANSACTION' at row 1

INSERT INTO TRAN_DETAILS VALUES (569320230,123456,"2022-05-11","BT","DEBIT",1000);
INSERT INTO ACCOUNTS VALUES(123490,1010,107,1,10000,"2022-05-11","SALARY","ACTIVE");
INSERT INTO ACCOUNTS VALUES(123490,1010,107,1,10000,"2022-05-11","LOAN","ACTIVE");
INSERT INTO ACCOUNTS VALUES(123490,1010,107,1,10000,"2022-05-11","SALARY","CLOSE");
 
 
 #10.INSERT INTO TRANSACTION TABLE SHOULD NOT HAPPEN IF THE OPENING BALANCE FOR THAT SPECIFIC ACCOUNT IS 0 AND IF ASTATUS = 'CLOSE'
SET @TX=1000;
SET @ACC=123464 ;
INSERT INTO TRAN_DETAILS (TNUMBER,ACCNUMBER,DOT,MEDIUM_OF_TRANSACTION,TRANSACTION_TYPE,TRANSACTION_AMOUNT)
SELECT 569320234,@ACC,'2022-05-11','CD','CREDIT',@TX
FROM ACCOUNTS A WHERE  A.ACCSTATUS = 'CLOSE' AND A.OPENING_BALANCE =0 AND A.ACCNUMBER = @ACC AND A.OPENING_BALANCE >= @TX
LIMIT 1;
SELECT * FROM ACCOUNTS;


#11.INSERT INTO TRANSACTION TABLE SHOULD HAPPEN WHEN ASTATUS='OPEN'
#AND THE OPENING BALANCE IS GRREATER THAN OR EQUAL TO THE AMOUNT BEING INSERTED INTO THE TRANSACTION TABLE
SET @TX=1000000;
SET @ACC=123461 ;
INSERT INTO TRAN_DETAILS (TNUMBER,ACCNUMBER,DOT,MEDIUM_OF_TRANSACTION,TRANSACTION_TYPE,TRANSACTION_AMOUNT)
SELECT 569320234,@ACC,'2022-05-11','CD','CREDIT',@TX
FROM ACCOUNTS A WHERE A.ACCSTATUS = 'OPEN' AND A.OPENING_BALANCE >= @TX AND A.ACCNUMBER = @ACC
LIMIT 1;
SELECT * FROM TRAN_DETAILS;
SELECT * FROM TRAN_DETails;