Task1.sql

USE collgePr1;

CREATE TABLE ACCOUNT_24012011080 (
    acc_no VARCHAR(5),
    Name VARCHAR(30),
    City VARCHAR(20),
    Balance DECIMAL(10,2),
    loan_taken VARCHAR(5),
);


INSERT INTO ACCOUNT_24012011080 (acc_no, Name, City, Balance, loan_taken)
VALUES ('A001', 'Patel Jigar', 'Mehsana', 50000.00, 'YES');

INSERT INTO ACCOUNT_24012011080 (acc_no, Name, City, Balance, loan_taken)
VALUES ('A002', 'Patel Ramesh', 'Mehsana', 50000.00, 'YES');

INSERT INTO ACCOUNT_24012011080 (acc_no, Name, City, Balance, loan_taken)
VALUES ('A003', 'Dave Hardik', 'Ahmedabad', 75000.00, 'NO');

INSERT INTO ACCOUNT_24012011080 (acc_no, Name, City, Balance, loan_taken)
VALUES ('A004', 'Soni Hetal', 'Ahmedabad', 100000.00, 'NO');

INSERT INTO ACCOUNT_24012011080 (acc_no, Name, City, Balance, loan_taken)
VALUES ('A005', 'Sony Atul', 'Vadodara', 100000.00, 'YES');


SELECT * FROM ACCOUNT_24012011080;


SELECT acc_no, Name, Balance
FROM ACCOUNT_24012011080
WHERE City = 'Mehsana';

DESCRIBE ACCOUNT_24012011080;

SET SQL_SAFE_UPDATES = 0;

UPDATE ACCOUNT_24012011080
SET Name = 'Patel Hiren'
WHERE Name = 'Patel Jigar';
SELECT * FROM ACCOUNT_24012011080;


UPDATE ACCOUNT_24012011080
SET Name = 'Kothari Nehal',
    City = 'Kherva'
WHERE acc_no = 'A005';
SELECT * FROM ACCOUNT_24012011080;



SELECT *
FROM ACCOUNT_24012011080
WHERE loan_taken = 'YES';


ALTER TABLE ACCOUNT_24012011080
ADD address VARCHAR(20);
DESCRIBE ACCOUNT_24012011080;



CREATE TABLE ACCOUNT_TEMP AS
SELECT acc_no, Name, Balance
FROM ACCOUNT_24012011080;
SELECT * FROM ACCOUNT_TEMP;

SET SQL_SAFE_UPDATES = 0;
    
DELETE FROM ACCOUNT_24012011080
WHERE acc_no = 'A005'; 
SELECT * FROM ACCOUNT_24012011080;


SELECT * 
FROM ACCOUNT_24012011080
ORDER BY acc_no DESC;


ALTER TABLE ACCOUNT_24012011080
ADD address VARCHAR(100);
SELECT * FROM ACCOUNT_24012011080;

ALTER TABLE ACCOUNT_24012011080
DROP COLUMN address;
SELECT * FROM ACCOUNT_24012011080;


ALTER TABLE ACCOUNT_24012011080
CHANGE Name New_name VARCHAR(50);
SELECT * FROM ACCOUNT_24012011080;


SELECT *
FROM ACCOUNT_24012011080
WHERE City NOT IN ('Ahmedabad', 'Vadodara');
SELECT * FROM ACCOUNT_24012011080;


SELECT *
FROM ACCOUNT_24012011080
WHERE acc_no = 'A001'
  AND City = 'Mehsana';
  
  
SELECT *
FROM ACCOUNT_24012011080
WHERE acc_no = 'A001'
   OR City = 'Mehsana';
   
   
SELECT *
FROM ACCOUNT_24012011080
WHERE Balance BETWEEN 75000 AND 100000;


SELECT *
FROM ACCOUNT_24012011080
WHERE Balance NOT BETWEEN 50000 AND 75000;


SELECT *
FROM ACCOUNT_24012011080
WHERE New_name LIKE 'D%';

SELECT CURDATE();

SELECT DATE_ADD(CURDATE(), INTERVAL 15 DAY) AS After_15_Days;


SELECT DATE_SUB(CURDATE(), INTERVAL 20 DAY) AS Before_20_Days;


SELECT 
    5*5 AS Multiply,
    34+34 AS Addition,
    1000/300 AS Division,
    LENGTH('uvpce') AS String_Length,
    MONTHNAME(CURDATE()) AS Month_Name
FROM DUAL;



-- Practical - 4 Queries start from here


SELECT city, SUM(balance) AS total_balance
FROM ACCOUNT_24012011080
GROUP BY city;

SELECT city, COUNT(*) AS total_holders
FROM ACCOUNT_24012011080
GROUP BY city
ORDER BY total_holders ASC;


SELECT loan_taken, SUM(balance) AS total_balance
FROM ACCOUNT_24012011080
GROUP BY loan_taken
HAVING loan_taken = 'NO';

SELECT *
FROM account
WHERE balance = loan_amount;


SELECT a.*
FROM ACCOUNT_24012011080 a
JOIN LOAN_24012011080 l ON a.acc_no = l.acc_no
WHERE a.Balance = l.loan_amt;


SELECT a.acc_no, a.city, l.remaining_loan, l.loan_date, l.loan_no
FROM ACCOUNT_24012011080 a
LEFT JOIN LOAN_24012011080 l ON a.acc_no = l.acc_no;


SELECT * FROM ACCOUNT_24012011080