7A
-- Create the table
CREATE TABLE customers (
ID INT PRIMARY KEY,
NAME VARCHAR(50),
AGE INT,
ADDRESS VARCHAR(100),
SALARY DECIMAL(10,2)
);
-- Insert sample data
INSERT INTO customers (ID, NAME, AGE, ADDRESS, SALARY) VALUES
(1, 'Ramesh', 23, 'Allahabad', 20000),
(2, 'Suresh', 22, 'Kanpur', 22000),
(3, 'Mahesh', 24, 'Ghaziabad', 24000),
(4, 'Chandan', 25, 'Noida', 26000),
(5, 'Alex', 21, 'Paris', 28000),
(6, 'Sunita', 20, 'Delhi', 30000);
-- Create the stored procedure
DELIMITER //
CREATE PROCEDURE UpdateCustomerSalaries()
BEGIN
-- Update the salary of all customers by adding 5000
UPDATE customers SET SALARY = SALARY + 5000;
-- Output the number of rows updated
SELECT CONCAT(ROW_COUNT(), ' customers updated') AS Message;
END //
DELIMITER ;
-- Call the stored procedure
CALL UpdateCustomerSalaries();
-- Verify the update
SELECT * FROM customers;
7B
-- Create the table
CREATE TABLE customers (
ID INT PRIMARY KEY,
NAME VARCHAR(50),
AGE INT,
ADDRESS VARCHAR(100),
SALARY DECIMAL(10,2)
);
-- Insert sample data
INSERT INTO customers (ID, NAME, AGE, ADDRESS, SALARY) VALUES
(1, 'Ramesh', 23, 'Allahabad', 20000),
(2, 'Suresh', 22, 'Kanpur', 22000),
(3, 'Mahesh', 24, 'Ghaziabad', 24000),
(4, 'Chandan', 25, 'Noida', 26000),
(5, 'Alex', 21, 'Paris', 28000),
(6, 'Sunita', 20, 'Delhi', 30000);
-- Create the stored procedure with an explicit cursor
DELIMITER //
CREATE PROCEDURE ListCustomerDetails()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE v_id INT;
DECLARE v_name VARCHAR(50);
DECLARE v_address VARCHAR(100);
-- Declare the cursor
DECLARE cur CURSOR FOR
SELECT ID, NAME, ADDRESS
FROM customers;
-- Declare the handler for when no more rows are found
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- Open the cursor
OPEN cur;
-- Loop through all rows
read_loop: LOOP
FETCH cur INTO v_id, v_name, v_address;
IF done THEN
LEAVE read_loop;
END IF;
-- Output the row details
SELECT v_id AS 'ID', v_name AS 'Name', v_address AS 'Address';
END LOOP;
-- Close the cursor
CLOSE cur;
END //
DELIMITER ;
-- Call the stored procedure
CALL ListCustomerDetails();
8A
Steps to Achieve the Example
1. Create the customers Table
2. Insert Sample Data
3. Create the Trigger
4. Update a Record to Test the Trigger
1. Create the customers Table
CREATE TABLE customers (
ID INT PRIMARY KEY,
NAME VARCHAR(50),
AGE INT,
ADDRESS VARCHAR(100),
SALARY DECIMAL(10,2)
);
2. Insert Sample Data
INSERT INTO customers (ID, NAME, AGE, ADDRESS, SALARY) VALUES
(1, 'Ramesh', 23, 'Allahabad', 20000),
(2, 'Suresh', 22, 'Kanpur', 22000),
(3, 'Mahesh', 24, 'Ghaziabad', 24000),
(4, 'Chandan', 25, 'Noida', 26000),
(5, 'Alex', 21, 'Paris', 28000),
(6, 'Sunita', 20, 'Delhi', 30000);
3. Create the Trigger
This trigger will fire before an UPDATE operation on the customers table and will log the old
salary, new salary, and the difference.
DELIMITER //
CREATE TRIGGER before_salary_update
BEFORE UPDATE ON customers
FOR EACH ROW
BEGIN
-- Declare variables to hold old and new salary values
DECLARE old_salary DECIMAL(10,2);
DECLARE new_salary DECIMAL(10,2);
DECLARE salary_difference DECIMAL(10,2);
-- Set old and new salary values
SET old_salary = OLD.SALARY;
SET new_salary = NEW.SALARY;
SET salary_difference = new_salary - old_salary;
-- Output the result (In practice, you would insert this into a log
table)
SELECT CONCAT('Old salary: ', old_salary) AS Old_Salary,
CONCAT('New salary: ', new_salary) AS New_Salary,
CONCAT('Salary difference: ', salary_difference) AS
Salary_Difference;
END //
DELIMITER ;
4. Update a Record to Test the Trigger
You can now update a record to see the trigger in action:
-- Update the salary of a customer (e.g., ID=1)
UPDATE customers
SET SALARY = SALARY + 5000
WHERE ID = 1;
Sample Output
After running the UPDATE statement, the trigger will output:
+--------------------+--------------------+-----------------------+
| Old_Salary | New_Salary | Salary_Difference |
+--------------------+--------------------+-----------------------+
| Old salary: 20000 | New salary: 25000 | Salary difference: 5000 |
+--------------------+--------------------+-----------------------+
Explanation
Trigger Definition: before_salary_update is a BEFORE UPDATE trigger that fires
before an update operation is applied to the customers table.
OLD and NEW Values:
o OLD.SALARY refers to the salary before the update.
o NEW.SALARY refers to the salary after the update.
Calculations:
o salary_difference is computed as NEW.SALARY - OLD.SALARY.
Output: The SELECT statement in the trigger outputs the old salary, new salary, and
salary difference. In real scenarios, you would typically log this information to another
table instead of directly selecting it.
8B
In MySQL, you can create a similar functionality using stored procedures and triggers, but
MySQL does not support PL/SQL packages directly. However, you can achieve similar behavior
by creating separate stored procedures and calling them as needed.
Here’s how you can implement the functionalities described using MySQL.
1. Create the customers Table
First, create a table to store customer information.
CREATE TABLE customers (
ID INT PRIMARY KEY,
NAME VARCHAR(50),
AGE INT,
ADDRESS VARCHAR(100),
SALARY DECIMAL(10,2)
);
2. Create the Stored Procedures
2.1 Add Customer
DELIMITER //
CREATE PROCEDURE addCustomer(IN c_id INT, IN c_name VARCHAR(50), IN c_age
INT, IN c_addr VARCHAR(100), IN c_sal DECIMAL(10,2))
BEGIN
INSERT INTO customers (ID, NAME, AGE, ADDRESS, SALARY)
VALUES (c_id, c_name, c_age, c_addr, c_sal);
END //
DELIMITER ;
2.2 Delete Customer
DELIMITER //
CREATE PROCEDURE delCustomer(IN c_id INT)
BEGIN
DELETE FROM customers WHERE ID = c_id;
END //
DELIMITER ;
2.3 List Customers
DELIMITER //
CREATE PROCEDURE listCustomer()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE v_id INT;
DECLARE v_name VARCHAR(50);
DECLARE cur CURSOR FOR SELECT ID, NAME FROM customers;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO v_id, v_name;
IF done THEN
LEAVE read_loop;
END IF;
-- Print the results (In practice, you would select this to a log
table or use another method to display)
SELECT CONCAT('Customer(', v_id, '): ', v_name) AS Customer_Details;
END LOOP;
CLOSE cur;
END //
DELIMITER ;
3. Using the Procedures
To use the procedures, you can run the following commands:
Add Customers
CALL addCustomer(1, 'Ramesh', 32, 'Ahmedabad', 3000.00);
CALL addCustomer(2, 'Khilan', 25, 'Delhi', 3000.00);
CALL addCustomer(3, 'Kaushik', 23, 'Kota', 3000.00);
CALL addCustomer(4, 'Chaitali', 25, 'Mumbai', 7500.00);
CALL addCustomer(5, 'Hardik', 27, 'Bhopal', 9500.00);
CALL addCustomer(6, 'Komal', 22, 'MP', 5500.00);
List Customers
CALL listCustomer();
Delete a Customer
CALL delCustomer(4); -- Example to delete customer with ID 4
List Customers Again
CALL listCustomer();
Sample Output
Here’s the expected output from listing customers before and after deletion:
Before Deletion:
+---------------------------+
| Customer_Details |
+---------------------------+
| Customer(1): Ramesh |
| Customer(2): Khilan |
| Customer(3): Kaushik |
| Customer(4): Chaitali |
| Customer(5): Hardik |
| Customer(6): Komal |
+---------------------------+
After Deletion (Customer ID 4 removed):
+---------------------------+
| Customer_Details |
+---------------------------+
| Customer(1): Ramesh |
| Customer(2): Khilan |
| Customer(3): Kaushik |
| Customer(5): Hardik |
| Customer(6): Komal |
+---------------------------+
Explanation
Procedures:
o addCustomer: Adds a new customer to the customers table.
o delCustomer: Deletes a customer from the customers table based on the ID.
o listCustomer: Lists all customers from the customers table using a cursor.
Cursor:
o Declare Cursor: DECLARE cur CURSOR FOR SELECT ID, NAME FROM
customers; specifies the cursor to iterate through customer records.
o Handler: DECLARE CONTINUE HANDLER FOR NOT FOUND handles the end of the
cursor’s result set.
o Loop: Loops through the cursor to fetch and display each customer.
