Practical 2.sql

USE collgePr1;

CREATE TABLE TRANSACTION_24012011080 (
    Acc_no VARCHAR(10),
    Tr_date DATE,
    Amt DECIMAL(10,2),
    Type_of_tr CHAR(1),
    Mode_of_pay VARCHAR(10)
);
DESCRIBE TRANSACTION_24012011080;


INSERT INTO TRANSACTION_24012011080 (Acc_no, Tr_date, Amt, Type_of_tr, Mode_of_pay) VALUES
('A001', '2021-05-03', 10000, 'D', 'Cash'),
('A002', '2021-07-05', 5000, 'W', 'Cheque'),
('A003', '2021-08-12', 25000, 'D', 'Cheque'),
('A004', '2021-05-15', 30000, 'D', 'Cheque'),
('A005', '2021-10-22', 15000, 'W', 'Cash');
SELECT * FROM TRANSACTION_24012011080;



SELECT SUM(Amt) AS Total_Transaction_Amount
FROM TRANSACTION_24012011080;

SELECT MIN(Amt) AS Minimum_Amount
FROM TRANSACTION_24012011080;

SELECT MAX(Amt) AS Maximum_Amount
FROM TRANSACTION_24012011080;

SELECT COUNT(*) AS Total_Account_Holders
FROM TRANSACTION_24012011080;



SELECT COUNT(*) AS Cheque_Transactions
FROM TRANSACTION_24012011080
WHERE Mode_of_pay = 'Cheque';


SELECT COUNT(*) AS Non_Deposit_Transactions
FROM TRANSACTION_24012011080
WHERE Type_of_tr != 'D';


SELECT COUNT(*) AS May_Transactions
FROM TRANSACTION_24012011080
WHERE MONTH(Tr_date) = 5;

SELECT AVG(Amt) AS Average_Transaction
FROM TRANSACTION_24012011080;

SELECT POWER(4, 4) AS Result;

SELECT SQRT(25) AS Square_Root;


SELECT LOWER('Dev Patel') AS Lowercase_Text;  -- 'dev patel'
SELECT UPPER('Dev Patel') AS Uppercase_Text; -- 'DEV PATEL'
SELECT INITCAP('dev patel') AS Initcap_Text; -- 'Dev Patel'
SELECT SUBSTR('Dev Patel', 5, 5) AS Substring_Text; -- 'Patel'
SELECT LENGTH('Dev Patel') AS String_Length; -- 9 (includes space)
SELECT LTRIM('    Dev Patel') AS Ltrim_Text; -- 'Dev Patel'
SELECT RTRIM('Dev Patel    ') AS Rtrim_Text; -- 'Dev Patel'
SELECT LPAD('Dev', 10, '*') AS Lpad_Text; -- '*******Dev' (total length 10, * added left)
SELECT RPAD('Dev', 10, '*') AS Rpad_Text; -- 'Dev*******' (total length 10, * added right)


-- Practical - 4 starts from here


SELECT t.amt, a.New_name, a.acc_no, t.mode_of_pay
FROM TRANSACTION_24012011080 t
JOIN ACCOUNT_24012011080 a ON t.acc_no = a.acc_no
WHERE t.mode_of_pay = 'Cheque';


SELECT l.loan_amt, t.amt, t.mode_of_pay
FROM LOAN_24012011080 l
JOIN TRANSACTION_24012011080 t ON l.acc_no = t.acc_no
WHERE MONTH(l.loan_date) = 5 OR MONTH(t.tr_date) = 5;


SELECT a1.New_name, a1.balance
FROM ACCOUNT_24012011080 a1
JOIN ACCOUNT_24012011080 a2 ON a2.New_name = 'Dave Hardik'
WHERE a1.balance > a2.balance;