Practical 8.sql

USE collgePr1;


DELIMITER $$
CREATE FUNCTION ADDITION(num1 INT, num2 INT)
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN num1 + num2;
END$$
DELIMITER ;

SELECT ADDITION(10, 10) AS Addition_Result;











DELIMITER $$
CREATE FUNCTION SUBTRACTION(num1 INT, num2 INT)
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN num1 - num2;
END$$
DELIMITER ;


SELECT SUBTRACTION(20, 5) AS Subtraction_Result;



DELIMITER $$
CREATE FUNCTION MULTIPLICATION(num1 INT, num2 INT)
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN num1 * num2;
END$$
DELIMITER ;

SELECT MULTIPLICATION(5, 6) AS Multiplication_Result;




DELIMITER $$
CREATE FUNCTION DIVISION(num1 DECIMAL(10,2), num2 DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    IF num2 = 0 THEN
        RETURN NULL; -- Avoid division by zero
    ELSE
        RETURN num1 / num2;
    END IF;
END$$
DELIMITER ;


SELECT DIVISION(100, 4) AS Division_Result;


SELECT DIVISION(10, 0) AS Division_By_Zero;




DELIMITER $$
CREATE FUNCTION GET_MAXIMUM_24012011080(val1 INT, val2 INT)
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE max_value INT;
    
    IF val1 > val2 THEN
        SET max_value = val1;
    ELSE
        SET max_value = val2;
    END IF;
    
    RETURN max_value;
END$$
DELIMITER ;


SELECT GET_MAXIMUM_24012011080(50, 30) AS Maximum_Value;




SELECT GET_MAXIMUM_24012011080(15, 75) AS Maximum_Value;


SELECT GET_MAXIMUM_24012011080(100, 100) AS Maximum_Value;




DELIMITER $$
CREATE FUNCTION CALCULATOR_24012011080(num1 DECIMAL(10,2), operator CHAR(1), num2 DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE result DECIMAL(10,2);
    
    CASE operator
        WHEN '+' THEN
            SET result = num1 + num2;
        WHEN '-' THEN
            SET result = num1 - num2;
        WHEN '*' THEN
            SET result = num1 * num2;
        WHEN '/' THEN
            IF num2 = 0 THEN
                SET result = NULL; -- Division by zero
            ELSE
                SET result = num1 / num2;
            END IF;
        ELSE
            SET result = NULL; -- Invalid operator
    END CASE;
    
    RETURN result;
END$$
DELIMITER ;


SELECT CALCULATOR_24012011080(10, '+', 5) AS Addition;


SELECT CALCULATOR_24012011080(10, '-', 5) AS Subtraction;


SELECT CALCULATOR_24012011080(10, '*', 5) AS Multiplication;

SELECT CALCULATOR_24012011080(10, '/', 5) AS Division;

SELECT CALCULATOR_24012011080(10, '/', 0) AS Division_Error;

SELECT CALCULATOR_24012011080(10, '%', 5) AS Invalid_Operator;




CREATE TABLE IF NOT EXISTS Employee_Details_24012011080(
    Emp_No INT PRIMARY KEY,
    Name VARCHAR(50),
    Salary DECIMAL(10,2)
);
DESCRIBE Employee_Details_24012011080;


INSERT INTO Employee_Details_24012011080 (Emp_No, Name, Salary) VALUES
(111, 'Rakesh', 12000),
(112, 'Ramesh', 14000),
(113, 'Dilip', 50000);
SELECT * FROM Employee_Details_24012011080;


DROP PROCEDURE IF EXISTS UPDATE_SALARY_24012011080;



DELIMITER $$
CREATE PROCEDURE UPDATE_SALARY_24012011080(
    IN emp_name VARCHAR(50),
    IN percentage DECIMAL(5,2)
)
BEGIN
    UPDATE Employee_Details_24012011080
    SET Salary = Salary + (Salary * percentage / 100)
    WHERE Name = emp_name;
    
    SELECT CONCAT('Salary updated for ', emp_name, ' by ', percentage, '%') AS Message;
END$$
DELIMITER ;

SET SQL_SAFE_UPDATES = 0;

CALL UPDATE_SALARY_24012011080('Rakesh', 10);

CALL UPDATE_SALARY_24012011080('Ramesh', 15);

SELECT * FROM Employee_Details_24012011080;




CREATE TABLE IF NOT EXISTS Company1_24012011080 (
    no INT PRIMARY KEY,
    name VARCHAR(50)
);
DESCRIBE Company1_24012011080;



CREATE TABLE IF NOT EXISTS Company2_24012011080 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    no INT,
    name VARCHAR(50),
    operation VARCHAR(10),
    operation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
DESCRIBE Company2_24012011080;


INSERT INTO Company1_24012011080 (no, name) VALUES
(1, 'TCS'),
(2, 'Infosys'),
(3, 'Wipro'),
(4, 'HCL');
SELECT * FROM Company1_24012011080;


-- For update operation

DELIMITER $$
CREATE TRIGGER after_company1_update
AFTER UPDATE ON Company1_24012011080
FOR EACH ROW
BEGIN
    INSERT INTO Company2_24012011080 (no, name, operation)
    VALUES (OLD.no, OLD.name, 'UPDATE');
END$$
DELIMITER ;

-- For delete operation 
DELIMITER $$
CREATE TRIGGER after_company1_delete
AFTER DELETE ON Company1_24012011080
FOR EACH ROW
BEGIN
    INSERT INTO Company2_24012011080 (no, name, operation)
    VALUES (OLD.no, OLD.name, 'DELETE');
END$$
DELIMITER ;

UPDATE Company1_24012011080 SET name = 'Tata Consultancy Services' WHERE no = 1;

DELETE FROM Company1_24012011080 WHERE no = 4;

SELECT * FROM Company2_24012011080;



CREATE TABLE IF NOT EXISTS Test1_24012011080 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    No1 INT,
    No2 INT
);


CREATE TABLE IF NOT EXISTS Test2_24012011080 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    Total INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


DELIMITER $$
CREATE TRIGGER after_test1_insert
AFTER INSERT ON Test1_24012011080
FOR EACH ROW
BEGIN
    INSERT INTO Test2_24012011080 (Total)
    VALUES (NEW.No1 + NEW.No2);
END$$
DELIMITER ;


INSERT INTO Test1_24012011080 (No1, No2) VALUES (10, 20);
INSERT INTO Test1_24012011080 (No1, No2) VALUES (15, 25);
INSERT INTO Test1_24012011080 (No1, No2) VALUES (100, 200);


SELECT * FROM Test1_24012011080;

SELECT * FROM Test2_24012011080;