-- =========================
-- LABSHEET 1
-- DDL & DML COMMANDS
-- =========================

CREATE DATABASE STUDENTDB;

SHOW DATABASES;

USE STUDENTDB;

SELECT DATABASE();

CREATE TABLE STUDENT(
    SNUM INT PRIMARY KEY,
    SNAME VARCHAR(20) NOT NULL,
    MAJOR VARCHAR(5) NOT NULL,
    LEVEL CHAR(5),
    DOB DATE
);

SHOW TABLES;

DESC STUDENT;

ALTER TABLE STUDENT ADD SEM INT DEFAULT 4;

DESC STUDENT;

ALTER TABLE STUDENT MODIFY MAJOR VARCHAR(20) NULL;

DESC STUDENT;

ALTER TABLE STUDENT MODIFY SNAME VARCHAR(20) UNIQUE;

DESC STUDENT;

ALTER TABLE STUDENT DROP COLUMN LEVEL;

DESC STUDENT;

TRUNCATE TABLE ENROLL1;

DROP TABLE ENROLL1;

INSERT INTO STUDENT VALUES
(1001,'CHETHAN','CSE','2000-11-03',4),
(1002,'CHETHAN CHAVAN','CSE','2000-11-03',4);

SELECT * FROM STUDENT;

UPDATE STUDENT SET MAJOR='ISE';

SELECT * FROM STUDENT;

UPDATE STUDENT SET MAJOR='ECE'
WHERE SNUM=1001;

CREATE DATABASE BankingDB;

USE BankingDB;

CREATE TABLE Customer (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(30),
    city VARCHAR(20)
);

CREATE TABLE Account (
    account_no INT PRIMARY KEY,
    customer_id INT,
    balance INT
);

DESC Customer;

ALTER TABLE Customer ADD phone_no VARCHAR(10);

ALTER TABLE Customer MODIFY city VARCHAR(30);

ALTER TABLE Customer DROP COLUMN phone_no;

RENAME TABLE Customer TO Customer_Details;

INSERT INTO Customer VALUES
(101, 'Ramesh', 'Chennai'),
(102, 'Suresh', 'Bangalore'),
(103, 'Mahesh', 'Hyderabad');

INSERT INTO Account VALUES
(5001, 101, 45000),
(5002, 102, 30000),
(5003, 103, 55000);

SELECT * FROM Customer;

SELECT * FROM Account;

UPDATE Customer
SET city = 'Mumbai'
WHERE customer_id = 101;

UPDATE Customer
SET city = 'Chennai'
WHERE city = 'Madras';

UPDATE Account
SET balance = 60000
WHERE account_no = 5001;

DELETE FROM Customer
WHERE customer_id = 103;

DELETE FROM Account
WHERE balance < 30000;

DELETE FROM Customer;





-- =========================
-- LABSHEET 2
-- CONSTRAINTS & OPERATORS
-- =========================

CREATE DATABASE BANKINGDB;

USE BANKINGDB;

CREATE TABLE CUSTOMER1 (
 CustomerID INT PRIMARY KEY,
 CustomerName VARCHAR(50) NOT NULL,
 Email VARCHAR(50) UNIQUE,
 Phone VARCHAR(15) UNIQUE
);

CREATE TABLE ACCOUNT1 (
 AccountNo INT PRIMARY KEY,
 CustomerID INT,
 AccountType VARCHAR(20) NOT NULL,
 Balance DECIMAL(10,2) NOT NULL,
 FOREIGN KEY (CustomerID) REFERENCES CUSTOMER1(CustomerID)
);

CREATE TABLE TRANSACTION_DETAILS (
 TransactionID INT PRIMARY KEY,
 AccountNo INT,
 Amount DECIMAL(10,2) NOT NULL,
 TransactionType VARCHAR(20),
 FOREIGN KEY (AccountNo) REFERENCES ACCOUNT1(AccountNo)
);

INSERT INTO CUSTOMER1 VALUES
(1,'Ravi','ravi@gmail.com','9876543210'),
(2,'Anita','anita@gmail.com','9123456780');

INSERT INTO ACCOUNT1 VALUES
(101,1,'Savings',50000),
(102,2,'Current',75000);

INSERT INTO TRANSACTION_DETAILS VALUES
(1001,101,5000,'Deposit'),
(1002,102,3000,'Withdrawal');

SELECT * FROM ACCOUNT1
WHERE AccountType = 'Savings';

SELECT * FROM ACCOUNT1
WHERE Balance > 60000;

SELECT * FROM TRANSACTION_DETAILS
WHERE Amount < 4000;

SELECT * FROM ACCOUNT1
WHERE AccountType='Savings'
AND Balance > 40000;

SELECT * FROM TRANSACTION_DETAILS
WHERE TransactionType='Deposit'
OR Amount > 4000;

SELECT * FROM ACCOUNT1
WHERE NOT AccountType='Savings';

SELECT * FROM CUSTOMER1
WHERE CustomerName LIKE 'R%';

SELECT * FROM ACCOUNT1
WHERE Balance BETWEEN 40000 AND 80000;

SELECT * FROM TRANSACTION_DETAILS
WHERE TransactionType IS NULL;

SELECT * FROM ACCOUNT1
WHERE AccountType IN ('Savings','Current');

SELECT * FROM TRANSACTION_DETAILS
WHERE TransactionType NOT IN ('Deposit');

INSERT INTO CUSTOMER1 VALUES
(1,'Kumar','kumar@gmail.com','9000000000');

INSERT INTO ACCOUNT1 VALUES
(103,1,NULL,30000);

INSERT INTO CUSTOMER1 VALUES
(3,'Meena','ravi@gmail.com','9888888888');

INSERT INTO ACCOUNT1 VALUES
(104,5,'Savings',20000);

INSERT INTO TRANSACTION_DETAILS VALUES
(1003,999,1000,'Deposit');

INSERT INTO CUSTOMER1 VALUES
(3,'Kiran','kiran@gmail.com','9555555555');

INSERT INTO ACCOUNT1 VALUES
(103,3,'Savings',40000);

INSERT INTO TRANSACTION_DETAILS VALUES
(1003,103,7000,'Deposit');




-- =========================
-- LABSHEET 3
-- GROUP BY & AGGREGATE FUNCTIONS
-- =========================

SELECT AccountType, COUNT(AccountNo) AS TotalAccounts
FROM ACCOUNT
GROUP BY AccountType;

SELECT AccountType, SUM(Balance) AS TotalBalance
FROM ACCOUNT
GROUP BY AccountType;

SELECT AccountType, AVG(Balance) AS AverageBalance
FROM ACCOUNT
GROUP BY AccountType;

SELECT AccountType, SUM(Balance) AS TotalBalance
FROM ACCOUNT
GROUP BY AccountType
ORDER BY TotalBalance DESC;

SELECT AccountNo, COUNT(TransactionID) AS TotalTransactions
FROM TRANSACTION_DETAILS
GROUP BY AccountNo;

SELECT AccountNo, SUM(Amount) AS TotalAmount
FROM TRANSACTION_DETAILS
GROUP BY AccountNo
ORDER BY TotalAmount DESC;

SELECT AccountNo, SUM(Amount) AS TotalAmount
FROM TRANSACTION_DETAILS
GROUP BY AccountNo
HAVING SUM(Amount) > 4000;

SELECT TransactionType, COUNT(*) AS Count
FROM TRANSACTION_DETAILS
GROUP BY TransactionType
ORDER BY Count DESC;

SELECT AccountType,
MAX(Balance) AS MaxBalance,
MIN(Balance) AS MinBalance
FROM ACCOUNT
GROUP BY AccountType;

CREATE DATABASE LIBRARYDB;

USE LIBRARYDB;

CREATE TABLE BOOK (
    BookID INT PRIMARY KEY,
    Title VARCHAR(50) NOT NULL,
    Author VARCHAR(50),
    Category VARCHAR(30),
    Price INT,
    Quantity INT
);

INSERT INTO BOOK VALUES
(1,'DBMS','Navathe','Computer',450,10),
(2,'OS','Silberschatz','Computer',500,8),
(3,'Python','Guido','Programming',600,5),
(4,'Java','James','Programming',550,6),
(5,'Maths','Sharma','Science',300,12),
(6,'Physics','HC Verma','Science',400,7);

SELECT * FROM BOOK;

SELECT Category, COUNT(BookID) AS TotalBooks
FROM BOOK
GROUP BY Category;

SELECT Category, SUM(Quantity) AS TotalQuantity
FROM BOOK
GROUP BY Category;

SELECT Category, AVG(Price) AS AveragePrice
FROM BOOK
GROUP BY Category;

SELECT MAX(Price) AS HighestPrice,
MIN(Price) AS LowestPrice
FROM BOOK;

SELECT Category, SUM(Quantity) AS TotalQuantity
FROM BOOK
GROUP BY Category
HAVING SUM(Quantity) > 15;

SELECT * FROM BOOK
ORDER BY Price ASC;

SELECT * FROM BOOK
ORDER BY Price DESC;

SELECT Category, AVG(Price) AS AvgPrice
FROM BOOK
GROUP BY Category
ORDER BY AvgPrice DESC;




-- =========================
-- LABSHEET 4
-- SET OPERATIONS & JOINS
-- =========================

CREATE DATABASE AIRLINEDB;

USE AIRLINEDB;

CREATE TABLE FLIGHT (
    FlightID INT PRIMARY KEY,
    FlightName VARCHAR(30),
    Source VARCHAR(30),
    Destination VARCHAR(30)
);

CREATE TABLE PASSENGER (
    PassengerID INT PRIMARY KEY,
    PassengerName VARCHAR(30),
    FlightID INT,
    FOREIGN KEY(FlightID) REFERENCES FLIGHT(FlightID)
);

CREATE TABLE BOOKING (
    BookingID INT PRIMARY KEY,
    PassengerID INT,
    FlightID INT,
    FOREIGN KEY(PassengerID) REFERENCES PASSENGER(PassengerID),
    FOREIGN KEY(FlightID) REFERENCES FLIGHT(FlightID)
);

INSERT INTO FLIGHT VALUES
(101,'Indigo','Bangalore','Delhi'),
(102,'AirIndia','Chennai','Mumbai'),
(103,'Vistara','Delhi','Bangalore'),
(104,'SpiceJet','Mumbai','Chennai');

INSERT INTO PASSENGER VALUES
(1,'Ravi',101),
(2,'Anita',102),
(3,'Kiran',103),
(4,'Meena',NULL);

INSERT INTO BOOKING VALUES
(1001,1,101),
(1002,2,102),
(1003,3,103);

SELECT PassengerName AS Name FROM PASSENGER
UNION
SELECT FlightName FROM FLIGHT;

SELECT Source FROM FLIGHT
UNION ALL
SELECT Destination FROM FLIGHT;

SELECT FlightID FROM PASSENGER
WHERE FlightID IN
(SELECT FlightID FROM BOOKING);

SELECT PassengerID FROM PASSENGER
WHERE PassengerID NOT IN
(SELECT PassengerID FROM BOOKING);

SELECT PassengerName, FlightName, Source, Destination
FROM PASSENGER
INNER JOIN FLIGHT
ON PASSENGER.FlightID = FLIGHT.FlightID;

SELECT PassengerName, FlightName
FROM PASSENGER
LEFT JOIN FLIGHT
ON PASSENGER.FlightID = FLIGHT.FlightID;

SELECT PassengerName, FlightName
FROM PASSENGER
RIGHT JOIN FLIGHT
ON PASSENGER.FlightID = FLIGHT.FlightID;

SELECT PassengerName, FlightName
FROM PASSENGER
LEFT JOIN FLIGHT
ON PASSENGER.FlightID = FLIGHT.FlightID

UNION

SELECT PassengerName, FlightName
FROM PASSENGER
RIGHT JOIN FLIGHT
ON PASSENGER.FlightID = FLIGHT.FlightID;

SELECT PassengerName, FlightName
FROM PASSENGER
CROSS JOIN FLIGHT;

SELECT PassengerName, FlightName
FROM PASSENGER
NATURAL JOIN FLIGHT;





-- =========================
-- LABSHEET 5
-- VIEWS & PROCEDURES
-- =========================

USE studentdb;

CREATE TABLE STUDENT(
    SNUM INT PRIMARY KEY,
    SNAME VARCHAR(20) NOT NULL,
    MAJOR VARCHAR(5) NOT NULL,
    DOB DATE,
    SEM INT DEFAULT 4
);

CREATE VIEW studentdetails AS
SELECT * FROM student;

SELECT * FROM studentdetails;

SELECT snum, sname
FROM studentdetails
WHERE major='cse';

CREATE VIEW studentcount AS
SELECT major, COUNT(*) AS total_students
FROM student
GROUP BY major;

SELECT * FROM studentcount;

DROP VIEW studentdetails;

DROP VIEW studentcount;

DELIMITER //

CREATE PROCEDURE updateSalary(IN dept_no INT)

BEGIN

UPDATE deptsal
SET totalsalary =
(
    SELECT SUM(salary)
    FROM employee
    WHERE dno = dept_no
)
WHERE dnumber = dept_no;

END //

DELIMITER ;

CALL updateSalary(1);

CALL updateSalary(2);

CALL updateSalary(3);

SELECT * FROM deptsal;

SHOW PROCEDURE STATUS;

UPDATE deptsal
SET totalsalary = 0;

DELIMITER //

CREATE PROCEDURE raise_salary()

BEGIN

DECLARE done INT DEFAULT FALSE;
DECLARE emp_id INT;
DECLARE emp_salary INT;

DECLARE emp_cursor CURSOR FOR
SELECT id, salary FROM employee;

OPEN emp_cursor;

read_loop: LOOP

FETCH emp_cursor INTO emp_id, emp_salary;

IF done THEN
    LEAVE read_loop;
END IF;

UPDATE employee
SET salary = salary + 1000
WHERE id = emp_id;

END LOOP;

CLOSE emp_cursor;

END //

DELIMITER ;

-- =========================
-- LABSHEET 6
-- FUNCTIONS & TRIGGERS
-- =========================

DELIMITER //

CREATE FUNCTION square_num(x INT)
RETURNS INT
DETERMINISTIC

BEGIN

RETURN x*x;

END //

DELIMITER ;

SELECT square_num(5);

DELIMITER //

CREATE FUNCTION total_salary(basic INT, bonus INT)
RETURNS INT
DETERMINISTIC

BEGIN

RETURN basic + bonus;

END //

DELIMITER ;

SELECT total_salary(50000,5000);

CREATE TRIGGER update_salary_insert

AFTER INSERT
ON employee

FOR EACH ROW

UPDATE department
SET totalsalary = totalsalary + NEW.salary
WHERE dnumber = NEW.dno;

CREATE TRIGGER update_salary_update

AFTER UPDATE
ON employee

FOR EACH ROW

UPDATE department
SET totalsalary =
totalsalary - OLD.salary + NEW.salary
WHERE dnumber = NEW.dno;

CREATE TRIGGER update_salary_delete

AFTER DELETE
ON employee

FOR EACH ROW

UPDATE department
SET totalsalary = totalsalary - OLD.salary
WHERE dnumber = OLD.dno;

SHOW TRIGGERS;

DROP TRIGGER update_salary_insert;

DROP TRIGGER update_salary_update;

DROP TRIGGER update_salary_delete;