practical 7.sql

CREATE DATABASE IF NOT EXISTS practical7_db;
USE practical7_db;

DROP TABLE IF EXISTS Grade;

CREATE TABLE Grade (
    name VARCHAR(50),
    salary DECIMAL(10 , 2 )
);

INSERT INTO Grade VALUES 
('Rima', 5000),
('John', 6000),
('Sara', 7000);
SELECT * FROM Grade;


DROP TABLE IF EXISTS Employee_Details;

CREATE TABLE Employee_Details_24012011080 (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    salary DECIMAL(10,2),
    city VARCHAR(50)
);
INSERT INTO Employee_Details_24012011080 VALUES 
(1, 'Riya', 5000, 'Mumbai'),
(2, 'Rima', 6000, 'Delhi'),
(3, 'Amit', 7000, 'Mumbai'),
(4, 'Priya', 8000, 'Bangalore');
SELECT * FROM Employee_Details_24012011080;


DROP TABLE IF EXISTS Empmaster;

CREATE TABLE Empmaster_24012011080 (
    no INT PRIMARY KEY,
    name VARCHAR(50),
    salary DECIMAL(10,2),
    city VARCHAR(50)
);
INSERT INTO Empmaster_24012011080 VALUES 
(1, 'Rajesh', 45000, 'Bombay'),
(2, 'Suresh', 50000, 'Delhi'),
(3, 'Mahesh', 55000, 'Bombay'),
(4, 'Dinesh', 60000, 'Mumbai'),
(5, 'Ramesh', 65000, 'Bombay'),
(6, 'Ganesh', 70000, 'Pune');
SELECT * FROM Empmaster_24012011080;

DROP TABLE IF EXISTS CITYLIST;

CREATE TABLE CITYLIST (
    no INT,
    name VARCHAR(50)
);
DESCRIBE CITYLIST;


DROP TABLE IF EXISTS Temp;


CREATE TABLE Temp_24012011080 (
    NO INT PRIMARY KEY,
    COMPANY VARCHAR(50),
    SALARY DECIMAL(12,2)
);
INSERT INTO Temp_24012011080 VALUES 
(1, 'IBM', 5000000),
(2, 'GOOGLE', 7000000),
(3, 'MICROSOFT', 6000000);
SELECT * FROM Temp_24012011080;


DROP TABLE IF EXISTS EMPLOYEE;

CREATE TABLE EMPLOYEE_24012011080 (
    EmpNo INT PRIMARY KEY,
    Name VARCHAR(50),
    Salary DECIMAL(10,2),
    Designation VARCHAR(50),
    DeptID INT
);
INSERT INTO EMPLOYEE_24012011080 VALUES 
(1, 'Alice', 95000, 'Manager', 10),
(2, 'Bob', 88000, 'Senior Dev', 20),
(3, 'Charlie', 92000, 'Lead', 10),
(4, 'David', 87000, 'Developer', 20),
(5, 'Eve', 90000, 'Architect', 30),
(6, 'Frank', 85000, 'Developer', 20),
(7, 'Grace', 86000, 'Analyst', 30);
SELECT * FROM EMPLOYEE_24012011080;



DELIMITER $$
DROP PROCEDURE IF EXISTS Script_A$$
CREATE PROCEDURE Script_A()
BEGIN
    -- Disable autocommit
    SET autocommit = 0;
    
    START TRANSACTION;
    
    INSERT INTO Grade VALUES ('abc', 6000);
    UPDATE Grade SET salary=4000 WHERE name='Rima';
    
    -- ROLLBACK undoes both INSERT and UPDATE
    ROLLBACK;
    
    -- COMMIT has nothing to save (already rolled back)
    COMMIT;
    
    SET autocommit = 1;
END$$
DELIMITER ;

CALL Script_A();
SELECT * FROM Grade;




DELIMITER $$
DROP PROCEDURE IF EXISTS Script_B$$
CREATE PROCEDURE Script_B()
BEGIN
    SET autocommit = 0;
    START TRANSACTION;
    
    INSERT INTO Grade VALUES ('abc', 6000);
    UPDATE Grade SET salary=4000 WHERE name='Rima';
    
    -- COMMIT saves both INSERT and UPDATE
    COMMIT;
    
    -- ROLLBACK has no effect (already committed)
    ROLLBACK;
    
    SET autocommit = 1;
END$$
DELIMITER ;

CALL Script_B();
SELECT * FROM Grade;




DELIMITER $$
DROP PROCEDURE IF EXISTS Script_C$$
CREATE PROCEDURE Script_C()
BEGIN
    SET autocommit = 0;
    START TRANSACTION;
    
    INSERT INTO Grade VALUES ('xyz', 7000);
    
    -- Create savepoint after INSERT
    SAVEPOINT first;
    
    UPDATE Grade SET salary=50000 WHERE name='xyz';
    
    -- Rollback only the UPDATE, keep the INSERT
    ROLLBACK TO SAVEPOINT first;
    
    -- Commit the INSERT
    COMMIT;
    
    SET autocommit = 1;
END$$
DELIMITER ;


CALL Script_C();
SELECT * FROM Grade;




DELIMITER $$
DROP PROCEDURE IF EXISTS Update_Salary_With_Check$$
CREATE PROCEDURE Update_Salary_With_Check()
BEGIN
    DECLARE total_sal DECIMAL(10,2);
    DECLARE error_msg VARCHAR(100);
    
    -- Disable autocommit
    SET autocommit = 0;
    START TRANSACTION;
    
    -- Step 1: Insert new record
    INSERT INTO Employee_Details_24012011080 (name, salary, city) 
    VALUES ('Neha', 5500, 'Chennai');
    
    UPDATE Employee_Details_24012011080 
    SET salary = salary + 7000 
    WHERE name IN ('Riya', 'Rima');
    
    -- Step 3: Calculate total salary
    SELECT SUM(salary) INTO total_sal FROM Employee_Details_24012011080;

    IF total_sal > 20000 THEN
        -- Exceeds limit: ROLLBACK
        ROLLBACK;
        SET error_msg = 'TOTAL SALARY EXCEEDED 20000';
        SELECT error_msg AS Error_Message, total_sal AS Total_Salary;
    ELSE
        -- Within limit: COMMIT
        COMMIT;
        SELECT 'Changes committed successfully' AS Status, 
               total_sal AS Total_Salary;
    END IF;
    SET autocommit = 1;
END$$
DELIMITER ;


CALL Update_Salary_With_Check();
SELECT * FROM Employee_Details_24012011080;



DELIMITER $$
DROP PROCEDURE IF EXISTS Give_5_Percent_Raise$$
CREATE PROCEDURE Give_5_Percent_Raise()
BEGIN
    -- Update all employee salaries by 5%
    UPDATE Employee_Details_24012011080 
    SET salary = salary * 1.05;
    
    SELECT 'All employees received 5% raise' AS Message;
    SELECT * FROM Employee_Details_24012011080;
END$$
DELIMITER ;


CALL Give_5_Percent_Raise()




DELIMITER $$
DROP PROCEDURE IF EXISTS Copy_Bombay_Data_Method_A$$
CREATE PROCEDURE Copy_Bombay_Data_Method_A()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE emp_no INT;
    DECLARE emp_name VARCHAR(50);
    
    DECLARE emp_cursor CURSOR FOR 
        SELECT no, name FROM Empmaster_24012011080 WHERE city='Bombay';
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- Clear previous data
    TRUNCATE TABLE CITYLIST;
    
    OPEN emp_cursor;
    
    read_loop: LOOP
        FETCH emp_cursor INTO emp_no, emp_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        INSERT INTO CITYLIST VALUES (emp_no, emp_name);
    END LOOP;
    
    CLOSE emp_cursor;
    
    SELECT 'Data copied using regular variables' AS Message;
    SELECT * FROM CITYLIST;
END$$
DELIMITER ;


CALL Copy_Bombay_Data_Method_A();





DELIMITER $$
DROP PROCEDURE IF EXISTS Copy_Bombay_Data_Method_B$$
CREATE PROCEDURE Copy_Bombay_Data_Method_B()
BEGIN
    -- Anchored to table columns (manual typing in MySQL)
    DECLARE v_no INT;              -- Anchored to Empmaster.no
    DECLARE v_name VARCHAR(50);    -- Anchored to Empmaster.name
    DECLARE done INT DEFAULT FALSE;
    
    DECLARE emp_cursor CURSOR FOR 
        SELECT no, name FROM Empmaster_24012011080 WHERE city='Bombay';
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    TRUNCATE TABLE CITYLIST;
    
    OPEN emp_cursor;
    
    read_loop: LOOP
        FETCH emp_cursor INTO v_no, v_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        INSERT INTO CITYLIST VALUES (v_no, v_name);
    END LOOP;
    
    CLOSE emp_cursor;
    
    SELECT 'Data copied using TYPE-anchored variables' AS Message;
    SELECT * FROM CITYLIST;
END$$
DELIMITER ;


CALL Copy_Bombay_Data_Method_B();





DELIMITER $$
DROP PROCEDURE IF EXISTS Copy_Bombay_Data_Method_C$$
CREATE PROCEDURE Copy_Bombay_Data_Method_C()
BEGIN
    -- Simulating ROWTYPE with individual variables
    DECLARE emp_record_no INT;
    DECLARE emp_record_name VARCHAR(50);
    DECLARE done INT DEFAULT FALSE;
    
    DECLARE emp_cursor CURSOR FOR 
        SELECT no, name FROM Empmaster_24012011080 WHERE city='Bombay';
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    TRUNCATE TABLE CITYLIST;
    
    OPEN emp_cursor;
    
    read_loop: LOOP
        FETCH emp_cursor INTO emp_record_no, emp_record_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        INSERT INTO CITYLIST VALUES (emp_record_no, emp_record_name);
    END LOOP;
    
    CLOSE emp_cursor;
    
    SELECT 'Data copied using ROWTYPE-style variables' AS Message;
    SELECT * FROM CITYLIST;
END$$
DELIMITER ;

CALL Copy_Bombay_Data_Method_C();





DELIMITER $$
DROP PROCEDURE IF EXISTS Update_Company_Salary$$
CREATE PROCEDURE Update_Company_Salary(IN p_company VARCHAR(50), IN p_increment DECIMAL(12,2))
BEGIN
    DECLARE v_no INT;
    DECLARE v_current_salary DECIMAL(12,2);
    DECLARE done INT DEFAULT FALSE;
    
    -- Cursor with parameter condition
    DECLARE company_cursor CURSOR FOR 
        SELECT NO, SALARY FROM Temp_24012011080 WHERE COMPANY = p_company;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN company_cursor;
    
    read_loop: LOOP
        FETCH company_cursor INTO v_no, v_current_salary;
        IF done THEN
            LEAVE read_loop;
        END IF;

        UPDATE Temp_24012011080 
        SET SALARY = v_current_salary + p_increment 
        WHERE NO = v_no;
    END LOOP;
    
    CLOSE company_cursor;
    
    SELECT CONCAT('Salary updated for company: ', p_company) AS Message;
    SELECT * FROM Temp_24012011080 WHERE COMPANY = p_company;
END$$
DELIMITER ;


CALL Update_Company_Salary('IBM', 500000);

CALL Update_Company_Salary('GOOGLE', 900000);





DELIMITER $$
DROP PROCEDURE IF EXISTS Top_Five_Highest_Paid$$
CREATE PROCEDURE Top_Five_Highest_Paid()
BEGIN
    DECLARE v_empno INT;
    DECLARE v_name VARCHAR(50);
    DECLARE v_salary DECIMAL(10,2);
    DECLARE v_designation VARCHAR(50);
    DECLARE v_deptid INT;
    DECLARE done INT DEFAULT FALSE;
    
    -- Cursor to select top 5 by salary
    DECLARE top_emp_cursor CURSOR FOR 
        SELECT EmpNo, Name, Salary, Designation, DeptID 
        FROM EMPLOYEE_24012011080 
        ORDER BY Salary DESC 
        LIMIT 5;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- Create temp table for results
    DROP TEMPORARY TABLE IF EXISTS Top_Five_Employees;
    CREATE TEMPORARY TABLE Top_Five_Employees (
        EmpNo INT,
        Name VARCHAR(50),
        Salary DECIMAL(10,2),
        Designation VARCHAR(50),
        DeptID INT
    );
    
    OPEN top_emp_cursor;
    
    read_loop: LOOP
        FETCH top_emp_cursor INTO v_empno, v_name, v_salary, v_designation, v_deptid;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        INSERT INTO Top_Five_Employees 
        VALUES (v_empno, v_name, v_salary, v_designation, v_deptid);
    END LOOP;
    
    CLOSE top_emp_cursor;
    
    SELECT 'Top 5 Highest Paid Employees:' AS Message;
    SELECT * FROM Top_Five_Employees ORDER BY Salary DESC;
END$$
DELIMITER ;


CALL Top_Five_Highest_Paid();