Offline Notepad View raw

Shared snapshot

LAb exam

-- ========================= -- 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;