Task1(2).sql

USE collgePr1;


CREATE TABLE LOAN_24012011080 (
loan_no VARCHAR(5),
acc_no VARCHAR(5),
loan_amt DECIMAL(10, 2),
interest_rate DECIMAL(5, 2),
loan_date DATE,
remaining_loan DECIMAL(10, 2)
);


INSERT INTO LOAN_24012011080 (loan_no, acc_no, loan_amt, interest_rate, loan_date, remaining_loan)
VALUES ('L001', 'A001', 100000.00, 7.00, '2004-01-01', 75000.00);

INSERT INTO LOAN_24012011080 (loan_no, acc_no, loan_amt, interest_rate, loan_date, remaining_loan)
VALUES ('L002', 'A002', 300000.00, 9.00, '2004-05-18', 150000.00);

INSERT INTO LOAN_24012011080 (loan_no, acc_no, loan_amt, interest_rate, loan_date, remaining_loan)
VALUES ('L003', 'A005', 500000.00, 11.00, '2004-06-15', 300000.00);

SELECT * FROM LOAN_24012011080;



SELECT *
FROM LOAN_24012011080
WHERE loan_amt > 300000;


DESCRIBE LOAN_24012011080;


ALTER TABLE LOAN_24012011080
ADD credit_no VARCHAR(4);
DESCRIBE LOAN_24012011080;


CREATE TABLE LOAN_TEMP AS
SELECT loan_no, acc_no, loan_amt, loan_date
FROM LOAN_24012011080;
SELECT * FROM LOAN_TEMP;



CREATE TABLE TRANS_TEMP AS
SELECT
	acc_no AS account_no,
    loan_no,
    loan_amt,
    interest_rate, 
    loan_date,
    remaining_loan
FROM LOAN_24012011080;
SELECT * FROM TRANS_TEMP;


ALTER TABLE LOAN_24012011080
MODIFY acc_no VARCHAR(7);
DESCRIBE LOAN_24012011080;


UPDATE LOAN_24012011080
SET interest_rate = interest_rate + 2;
SELECT * FROM LOAN_24012011080;


SELECT * 
FROM LOAN_24012011080
WHERE MONTH(loan_date) = 1;


SELECT loan_amt * 2 AS double_amount
FROM LOAN_24012011080;



UPDATE LOAN_24012011080
SET loan_amt = 150000
WHERE loan_no = 'L001' AND loan_amt = 100000;
SELECT * FROM LOAN_24012011080;


DROP TABLE LOAN_TEMP;
DESCRIBE LOAN_TEMP;



-- Practical - 4 starts from here

SELECT l.loan_no, l.interest_rate, t.tr_date, t.type_of_tr
FROM LOAN_24012011080 l
RIGHT JOIN TRANSACTION_24012011080 t
ON l.acc_no = t.acc_no;