Experiments - 1

Create a table called Employee & execute the following.

Employee(EMPNO,ENAME,JOB, MANAGER_NO, SAL, COMMISSION)

1. Create a user and grant all permissions to the user.

2. Insert the any three records in the employee table contains attributes
EMPNO,ENAME JOB, MANAGER_NO, SAL, COMMISSION and use rollback.
Check the result.
3. Add primary key constraint and not null constraint to the employee table.
4. Insert null values to the employee table and verify the result.

Rohith H P 1
 MySQL - Create Users
• We can create a new user account using the CREATE USER statement in
MySQL. To execute this statement, the current account must have the CREATE
USER privilege or the INSERT privilege for the MySQL system schema.
Open terminal , type sudo mysql –u root then type password : vcetputtur
Following is the syntax of the MySQL CREATE USER statement −
CREATE USER 'user_name'@'host_name’ IDENTIFIED BY 'password’;
Where,
• user_name is the name of the user you need to create.
• hostname specifies the host from which the user can connect.
• password is the user's password.
Rohith H P 2
In the following query, we are creating a user named ‘rohith’ who can only connect
from the 'localhost' host and sets their password as ‘vcetputtur'. Make sure that you
have logged in with a user with admin privileges (root)
CREATE USER ‘rohith’@’localhost’ IDENTIFIED BY ‘vcetputtur’ ;
The output will be displayed as –
Query OK, 0 rows affected(0.12 sec)
VERIFICATION – using following query we can verify the list of users
SELECT USER FROM [Link];
The table will be displayed like this
USER
[Link]
[Link]
-----------------
root
rohith

Rohith H P 3
 Granting Privileges(permissions) in MySQL
• We can grant all privileges to the created user using the GRANT ALL statement. This
allows you to give specific permissions to users for actions like accessing databases,
tables, and performing operations, such as SELECT, INSERT, or DELETE, on them.
• Following is the syntax to grant all privileges in MySQL −
GRANT ALL PRIVILEGES ON database_name.* TO ‘username’@’localhost’ ;
EX:

GRANT ALL PRIVILEGES ON *.* TO ‘rohith’ @ ‘localhost’ ;

Query OK, 0 rows affected

To remove the existing user :

DROP USER rohith@localhost ;
Rohith H P 4
CREATE DATABASE
o CREATE DATABASE database_name;
EX: CREATE DATABASE company;
o SHOW DATABASES;

o DROP DATABASE database_name;

o USE database_name;
database changed

Rohith H P 5
CREATE TABLE statement is used to create
tables in MYSQL database
CREATE TABLE table_name(
column-1 datatype,
column-2 datatype,
column-3 datatype,
.....
...
column-N datatype );

Rohith H P 6
Create a table called Employee
Employee(EMPNO,ENAME,JOB, MANAGER_NO, SAL, COMMISSION)

CREATE TABLE Employee (

EmpNo INT,
Ename VARCHAR(25),
Job VARCHAR(15),
Manager_No INT,
Salary INT,
Commission DECIMAL(5,2) );

Rohith H P 7
To check structure of Table
• The MySQL’s DESCRIBE or DESC both are equivalent. The DESC is the short
form of DESCRIBE command and used to dipslay the information about a table
like column names and constraints on column name.
Mysql> desc table_name;
• Above query generates FIELD, TYPE, NULL, DEFAULT and EXTRA

Mysql> desc Employee;

mysql> show columns from Employee; // equivalent code

Rohith H P 8
INSERT THREE RECORDS

We can add new rows to an existing table of MySQL using the INSERT statement.
INSERT INTO table_name (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);

INSERT INTO table_name VALUES(‘value1’,’value2’,…..,’valueN’);

INSERT INTO table_name VALUES((record1), (record2),(record3));

Rohith H P 9
• Verify the inserted records (Retrive all information from the table)
SELECT * FROM table_name;
mysql> COMMIT;
Verify………….
Insert One(new) record…
Verify….
mysql> ROLLBACK;
Verify…

Rohith H P 10
3. Add primary key constraint and not null constraint to
the employee table
Step 1: Add NOT NULL Constraint

We have a column names ‘EmpNo’ which we want to make NOT NULL,

ALTER TABLE Employee MODIFY EmpNo INT NOT NULL;

Step 2: Add Primary Key Constraint

ALTER TABLE Employee ADD PRIMARY KEY(EmpNo);

Check the structure….. Using desc command

Rohith H P 11
4) Insert null values to the employee table and verify the result.

• insert into employee values(104, 'Ganesh’ , 'Trainee’ , 40, Null, 50)

• Verify the table by using SELECT statements…

Rohith H P 12
DELETE and UPDATE
DELETE FROM table_name UPDATE table_name
WHERE condition; SET column1=value1,
column2=value2,
delete from employee where …………
ename=‘XXXX’; WHERE condition;

delete from employee where update employee set empno=105

empno=105; where ename=‘xxxx’;

Rohith H P 13
Experiments -2
Create a table called Employee that contain attributes EMPNO, ENAME,
JOB, MGR, SAL and execute the following.

1. Add a column COMMISION with domain to the Employee table.

2. Insert any five records into the table.
3. Update the column details of JOB
4. Rename the column of Employ table using alter command.
5. Delete the employee whose Empno is 105.

Rohith H P 14
Creating the Employee Table
mysql> CREATE DATABASE COMPANY02;
Query OK, 1 row affected (0.16 sec)

mysql> USE COMPANY02;

Database changed

mysql> CREATE TABLE Employee (

-> EMPNO INT,
-> ENAME VARCHAR(255),
-> JOB VARCHAR(255),
-> MGR INT,
-> SAL DECIMAL(10, 2)
-> );
Query OK, 0 rows affected (0.48 sec)

mysql> show tables;

mysql> desc Employee;
Rohith H P 15
Adding column COMMISSION to the Employee table
(1). Using ALTER command we can add new column to existing table

mysql> ALTER TABLE Employee

-> ADD COLUMN COMMISSION DECIMAL(10, 2); ALTER TABLE table_name
Query OK, 0 rows affected (0.37 sec) ADD COLUMN new_column_name DATATYPE;
mysql> DESC Employee;
+-------------------+-------------------+-------+------+-----------+--------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-------------------+-------+------+-----------+--------+
| EMPNO | int | YES | | NULL | |
| ENAME | varchar(255) | YES | | NULL | |
| JOB | varchar(255) | YES | | NULL | |
| MGR | int | YES | | NULL | |
| SAL | decimal(10,2) | YES | | NULL | |
| COMMISSION | decimal(10,2) | YES | | NULL | |
+-------------------+--------------------+-------+------+----------+--------+
6 rows in set (0.00 sec)

Rohith H P 16
 (2). Insert any FIVE records into the table
mysql> INSERT INTO Employee (EMPNO, ENAME, JOB, MGR, SAL, COMMISSION) VALUES
( (101, 'Radha Bai', 'Manager', NULL, 5000.00, 1000.00),
(102, 'Krishna Kumar', 'Developer', 101, 4000.00, NULL),
(103, 'Abdul Sattar', 'Salesperson', 102, 3000.00, 500.00),
(104, 'Bob Johnson', 'Accountant', 101, 4500.00, NULL),
(105, 'Amartya Sen', 'HR Manager', 101, 4800.00, 800.00) );
Query OK, 5 rows affected (0.12 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM Employee;

+------------+--------------------+------------------+----------+------------+-------------------+
| EMPNO | ENAME | JOB | MGR | SAL | COMMISSION |
+------------+--------------------+------------------+----------+------------+-------------------+
| 101 | Radha Bai | Manager | NULL | 5000.00 | 1000.00 |
| 102 | Krishna Kumar | Developer | 101 | 4000.00 | NULL |
| 103 | Abdul Sattar | Salesperson | 102 | 3000.00 | 500.00 |
| 104 | Bob Johnson | Accountant | 101 | 4500.00 | NULL |
| 105 | Amartya Sen | HR Manager | 101 | 4800.00 | 800.00 |
+------------+--------------------+-----------------+------------+------------+---------------------+
5 rows in set (0.00 sec)
Rohith H P 17
(3). Updating Column Details (JOB) in the Employee Table
mysql> UPDATE Employee
-> SET JOB = 'Senior Developer’
-> WHERE EMPNO = 102; UPDATE TABLE_NAME
Query OK, 1 row affected (0.09 sec) SET COLUMN_NAME = ‘NEW VALUE’
Rows matched: 1 Changed: 1 Warnings: 0 WHERE CONDITIONS ;

mysql> SELECT * FROM Employee;

+----------+-------------------+--------------- -----+---------+---------+-------------------+
| EMPNO | ENAME | JOB | MGR | SAL | COMMISSION |
+----------+-------------------+---------------- ----+---------+---------+--------------------+
| 101 | Radha Bai | HR Manager | NULL | 5000.00 | 1000.00 |
| 102 | Krishna Kumar | Senior Developer | 101 | 4000.00 | NULL |
| 103 | Abdul Sattar | Salesperson | 102 | 3000.00 | 500.00 |
| 104 | Bob Johnson | Accountant | 101 | 4500.00 | NULL |
| 105 | Amartya Sen | HR Manager | 101 | 4800.00 | 800.00 |
+-----------+------------------+----------------------+---------+-----------+-----------------+
5 rows in set (0.00 sec)
Rohith H P 18
Renaming a Column in the Employee Table
(4). Rename MGR column to MANAGER_ID and JOB to Designation.

mysql> alter table employee

change column MGR MANAGER_ID int,
change column JOB DESIGNATION varchar(20);
Query OK, 0 rows affected (0.30 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> DESC Employee;

+--------------------+--------------------+-------+------+-----------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-------------------+--------+------+-----------+-------+
| EMPNO | int | YES | | NULL | |
| ENAME | varchar(255) | YES | | NULL | |
| JOB | varchar(255) | YES | | NULL | |
| MANAGER_ID | int | YES | | NULL | |
| SAL | decimal(10,2) | YES | | NULL | |
| COMMISSION | decimal(10,2) | YES | | NULL | |
+---------------------+-------------------+-------+------+----------+-------+P
Rohith H 19
 Deleting a Specific Employee (EMPNO = 105) from the Employee Table

(5). Syntax for delete operation delete from table_name where condition;
mysql> delete from Employee
-> where empno = 105;
Query OK, 1 row affected (0.14 sec)

mysql> SELECT * FROM Employee;

+------------+---------------------+-----------------------+----------------------+-----------+--------------------+
| EMPNO | ENAME | JOB | MANAGER_ID | SAL | COMMISSION |
+------------+---------------------+------------------------+--------------------+------------+--------------------+
| 101 | Radha Bai | Manager | NULL | 5000.00 | 1000.00 |
| 102 | Krishna Kumar | Senior Developer | 101 | 4000.00 | NULL |
| 103 | Abdul Sattar | Salesperson | 102 | 3000.00 | 500.00 |
| 104 | Bob Johnson | Accountant | 101 | 4500.00 | NULL |
+------------+----------------------+------------------------+-------------------+-------------+------------------+
4 rows in set (0.00 sec)
Rohith H P 20
Experiments -3
Queries using aggregate functions (COUNT, AVG, MIN, MAX, SUM),
Group by, Orderby.
Employee (E_id, E_name, Age, Salary)
1. Create Employee table containing all Records E_id, E_name, Age, Salary.
2. Count number of employee names from employee table
3. Find the Maximum age from employee table.
4. Find the Minimum age from employee table.
5. Find salaries of employee in Ascending Order.
6. Find grouped salaries of employees.

Rohith H P 21
• Procedure :
➢ Create the database COMPANY03.
➢ Switch to created database using USE command.

➢ Create Employee table.

CREATE TABLE Employee (

e_id INT PRIMARY KEY,
e_name VARCHAR(50),
age INT,
salary DECIMAL(10,2) );

➢ Display the structure of employee table using DESC command.

Rohith H P 22
➢ Populating the employee table with 12 records.

INSERT INTO Employee VALUES(1,’Samarth’,30,50000);

INSERT INTO Employee (E_id, E_name, Age, Salary) VALUES

(
(2, 'Ramesh Kumar', 25, 45000.00),
(3, 'Seema Banu', 35, 60000.00),
(4, 'Dennis Anil', 28, 52000.00),
(5, 'Rehman Khan', 32, 58000.00),
(6, 'Pavan Gowda', 40, 70000.00),
(7, 'Shruthi Bhat', 27, 48000.00),
(8, 'Sandesh Yadav', 29, 51000.00),
(9, 'Vikram Acharya', 33, 62000.00),
(10, 'Praveen Bellad', 26, 46000.00),
(11, 'Sophia Mary', 31, 55000.00),
(12, 'Darshan Desai', 34, 63000.00)
);
Rohith H P 23
➢ Retrieve all the rows from employee table using SELECT statements.

➢ To count number of employee Names in employee table [count the non-null

values in the e_name column]

SELECT COUNT(e_name) as Total_Employees

FROM employee;

➢ Find the maximum age of employee [display the maximum age among the
employees].

SELECT MAX(age) AS Max_Age

FROM employee;

Rohith H P 24
➢ Find the minimum age of employee [display the minimum age among the employees].

SELECT MIN(age) AS Min_Age

FROM employee;

➢ Find salaries of employees in ascending order [sorts the employees based on their salaries in
ascending order].

SELECT e_name, salary

FROM Employee
ORDER BY salary ASC;

➢ Find grouped salaries of employees [groups employees by their salaries and counts the
number of employees for each salary]

SELECT salary, COUNT(*) AS EmployeeCount

FROM employee
GROUP BY salary;
Rohith H P 25
Experiment - 4
Create a row level trigger for the customers table that
would fire for INSERT or UPDATE or DELETE
operations performed on the CUSTOMERS table. This
trigger will display the salary difference between the old
& new Salary.
CUSTOMERS(ID,NAME,AGE,ADDRESS,SALARY)

Rohith H P 26
➢ Login to mysql as root
➢ sudo mysql –u root
➢ Enter password : vcetputtur
➢ create a database
➢ use database
➢ create customer table
➢ check the table description
➢ Insert 5 records to customer table
➢ display the extension of customer table ( select * from customer; )
➢ create trigger for insert
➢ execute the trigger
➢ Insert new record
➢ Execute the after insert trigger
➢ create update trigger
➢ Update one row in the table using update command
➢ Execute after update trigger
➢ Create delete trigger
➢ delete one row from customer table and execute the after delete trigger.

Rohith H P 27
$ sudo mysql –u root
Enter password : vcetputtur

mysql>create database usn_SHOP;

Query OK, 1 row affected (0.14 sec)

mysql> show databases;

mysql> use usn_SHOP;

Database changed

Rohith H P 28
mysql> CREATE TABLE CUSTOMERS (
-> ID INT PRIMARY KEY,
-> NAME VARCHAR(25),
-> AGE INT,
-> ADDRESS VARCHAR(50),
-> SALARY DECIMAL(10, 2)
-> );
Query OK, 0 rows affected (0.49 sec)

Rohith H P 29
insert into CUSTOMERS(ID,NAME,AGE,ADDRESS,SALARY)
values(1,’Avinash’,24,’Mangaluru’,salary);

ID NAME AGE ADDRESS SALARY

1 Avinash 24 Mangaluru 20000
2 Bhavya 28 Bengaluru 30000
3 Chethan 25 Delhi 35000
4 Dhanush 29 Hyderabad 50000
5 Hemanth 28 Mumbai 40000

Rohith H P 30
To achieve the desired functionality of capturing changes
on INSERT, UPDATE, or DELETE operations
and displaying the salary difference in MySQL, you’ll need to create
separate row-level triggers for each operation
(INSERT, UPDATE, DELETE).
These triggers will capture the OLD and NEW values of
the SALARY column and display the salary difference when an INSERT,
UPDATE, or DELETE operation occurs.

Rohith H P 31
Create trigger for INSERT operations

mysql>-- INSERT TRIGGER

mysql> DELIMITER //

mysql> CREATE TRIGGER after_insert_salary_difference

->AFTER INSERT ON CUSTOMERS
->FOR EACH ROW
->BEGIN
-> SET @my_sal_diff = CONCAT('salary inserted is ', [Link]);
->END;
-> // DELIMITER ;
Query ok,….
-> //
Rohith H P 32
 Create trigger for update operations
-- UPDATE TRIGGER
mysql>DELIMITER //

mysql>CREATE TRIGGER after_update_salary_difference

->AFTER UPDATE ON CUSTOMERS
->FOR EACH ROW
->BEGIN
-> DECLARE old_salary DECIMAL(10, 2);
-> DECLARE new_salary DECIMAL(10, 2);

-> SET old_salary = [Link];

->SET new_salary = [Link];
-> SET @my_sal_diff = CONCAT('salary difference after update is ', [Link] - [Link]);
->END;
-> // DELIMITER ;
Query ok,..
// Rohith H P 33
Create Trigger for DELETE Operation
-- DELETE TRIGGER

DELIMITER //

CREATE TRIGGER after_delete_salary_difference

AFTER DELETE ON CUSTOMERS
FOR EACH ROW
BEGIN
SET @my_sal_diff = CONCAT('salary deleted is ', [Link]);
END;
// DELIMITER ;

Query ok,…
//
Rohith H P 34
mysql> -- test INSERT TRIGGER
mysql> INSERT INTO CUSTOMERS (ID,NAME, AGE, ADDRESS, SALARY)
-> VALUES (6,'Shankara', 35, ‘Puttur', 50000.00);
Query OK, 1 row affected (0.14 sec)

mysql>
mysql> SELECT @my_sal_diff AS SAL_DIFF;
//
+---------------------------------+
| SAL_DIFF |
+----------------------------------+
| salary inserted is 50000.00 |
+----------------------------------+
1 row in set (0.00 sec)

Rohith H P 35
mysql> -- test UPDATE TRIGGER
mysql> UPDATE CUSTOMERS
-> SET SALARY = 55000.00
-> WHERE ID = 1;
Query OK, 1 row affected (0.13 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT @my_sal_diff AS SAL_DIFF;

//
+------------------------------------------------+
| SAL_DIFF |
+------------------------------------------------+
| salary difference after update is 5000.00 |
+------------------------------------------------+
1 row in set (0.00 sec)
Rohith H P 36
mysql> -- test DELETE TRIGGER
mysql> DELETE FROM CUSTOMERS
-> WHERE ID = 1;
Query OK, 1 row affected (0.13 sec)

mysql>
mysql> SELECT @my_sal_diff AS SAL_DIFF;
//
+-------------------------------+
| SAL_DIFF |
+-------------------------------+
| salary deleted is 55000.00 |
+-------------------------------+
1 row in set (0.00 sec)

Rohith H P 37
Each operation (INSERT, UPDATE, DELETE) will trigger the respective trigger
(after_insert_salary_difference,
after_update_salary_difference,
after_delete_salary_difference), which will display the salary change or difference
associated with that operation.
By using separate triggers for each operation and utilizing
the OLD and NEW keywords appropriately within the trigger bodies, you can
effectively capture and handle changes to the SALARY column in
the CUSTOMERS table in MySQL. You can adjust the trigger logic and message
formatting as needed based on your specific requirements.

Rohith H P 38
 CREATE TRIGGER display_salaary_changes
AFTER DELETE OR INSERT OR UPDATE ON customeers
FOR EACH ROW
WHEN ([Link] > 0)
DECLARE sal_diff number;
BEGIN
sal_diff = [Link] - [Link];
dbms_output.put_line('Old salary: ‘ ,[Link]);
dbms_output.put_line('New salary: ‘ ,[Link]);
dbms_output.put_line('Salary difference: ‘ , sal_diff);
END;
//
Rohith H P 39
Experiment-5
Create cursor for Employee table & extract the values from the table.
Declare the variables, Open the cursor & extract the values from the
cursor. Close the cursor.
Employee(E_id, E_name, Age, Salary)

Rohith H P 40
Procedure
1. Creating the Employee Table and insert few records
mysql> CREATE DATABASE COMPANY05;

mysql> USE COMPANY05;

mysql> CREATE TABLE Employee (

E_id INT,
E_name VARCHAR(50,
Age INT,
Salary DECIMAL(10, 2)
);
mysql> desc Employee;
mysql> INSERT INTO Employee VALUES (1, 'Samarth', 30, 50000.00);
mysql> INSERT INTO Employee VALUES (2, 'Ramesh Kumar', 25, 45000.00);
mysql> INSERT INTO Employee VALUES (3, 'Seema Banu', 35, 62000.00);
mysql> INSERT INTO Employee VALUES (4, 'Dennis Anil', 28, 52000.00);
mysql> INSERT INTO Employee VALUES (5, 'Rehman Khan', 32, 58000.00);
mysql> select * from Employee; Rohith H P 41
2. Create a stored Procedure with cursor
Mysql> DELIMITER //

Mysql> CREATE PROCEDURE fetch_employee_data()

BEGIN
-- Declare variables to store cursor values
DECLARE emp_id INT;
DECLARE emp_name VARCHAR(255);
DECLARE emp_age INT;
DECLARE emp_salary DECIMAL(10, 2);

-- Declare a cursor for the Employee table

DECLARE emp_cursor CURSOR FOR
SELECT E_id, E_name, Age, Salary
FROM Employee;
Rohith H P 42
-- Declare a continue handler for the cursor
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET @finished = 1;

-- Open the cursor

OPEN emp_cursor;

-- Initialize a variable to control cursor loop

SET @finished = 0;

-- Loop through the cursor results

cursor_loop: LOOP
-- Fetch the next row from the cursor into variables
FETCH emp_cursor INTO emp_id, emp_name, emp_age, emp_salary;

Rohith H P 43
-- Check if no more rows to fetch
IF @finished = 1 THEN
LEAVE cursor_loop;
END IF;

-- Output or process each row (for demonstration, print the values)

SELECT CONCAT('Employee ID: ', emp_id, ', Name: ', emp_name, ', Age: ',
emp_age, ', Salary: ', emp_salary) AS Employee_Info;
END LOOP;

-- Close the cursor

CLOSE emp_cursor;
END

// DELIMITER ;
Rohith H P 44
3. Execute stored procedure
mysql> CALL fetch_employee_data();
+-------------------------------------------------------------------------------+
| Employee_Info |
+-------------------------------------------------------------------------------+
| Employee ID: 1, Name: Samarth, Age: 30, Salary: 50000.00 |
+-------------------------------------------------------------------------------+
1 row in set (0.07 sec)

+---------------------------------------------------------------------------------------+
| Employee_Info |
+---------------------------------------------------------------------------------------+
| Employee ID: 2, Name: Ramesh Kumar, Age: 25, Salary: 45000.00 |
+---------------------------------------------------------------------------------------+
1 row in set (0.07 sec)

+------------------------------------------------------------------------------------+
| Employee_Info |
+------------------------------------------------------------------------------------+
| Employee ID: 3, Name: Seema Banu, Age: 35, Salary: 62000.00 |
+------------------------------------------------------------------------------------+
1 row in set (0.07 sec)

+-----------------------------------------------------------------------------------+
| Employee_Info |
+-----------------------------------------------------------------------------------+
| Employee ID: 4, Name: Dennis Anil, Age: 28, Salary: 52000.00 |
+-----------------------------------------------------------------------------------+
1 row in set (0.07 sec)

+--------------------------------------------------------------------------------------+
| Employee_Info |
+--------------------------------------------------------------------------------------+
| Employee ID: 5, Name: Rehman Khan, Age: 32, Salary: 58000.00 |
+--------------------------------------------------------------------------------------+
1 row in set (0.07 sec)

Query OK, 0 rows affected (0.07 sec)

Rohith H P 45
A cursor is a select statement, defined in the declaration section in
mysql. Cursor is a database object that allow us to retrieve and
manipulate each row one at a time. It is nothing more than a row
pointer. It is always used alongside a SELECT command.
1. Declare the cursor and variables
DECLARE cursor_name CURSOR FOR
Select statement;
cursor_name: name of the cursor
select_statement: select query associated with the cursor

2. Open the cursor: Open cursor_name;

3. Fetch the data : After declaring and opening the cursor, the next step
is to fetch the cursor. It is used to fetch the row or the column.
FETCH [ NEXT [ FROM ] ] cursor_name INTO variable_list;
4. Close the cursor : Close cursor_name;
Rohith H P 46
-- Step 1: Declare the cursor and variables
DELIMITER //
CREATE PROCEDURE fetch_employee_data()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE empid INT;
DECLARE empname VARCHAR(50);
DECLARE empage INT;
DECLARE empsalary DECIMAL(10, 2);
-- Declare cursor
DECLARE employee_cursor CURSOR FOR SELECT E_id, E_name, Age, Salary FROM Employee;
-- Declare CONTINUE HANDLER to handle end of data
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- Step 2: Open the cursor
OPEN employee_cursor;
-- Step 3: Fetch the data
read_loop: LOOP
FETCH employee_cursor INTO empid, empname, empage, empsalary;
IF done THEN
LEAVE read_loop;
END IF;
-- Here you can do whatever you want with the fetched data -- For demonstration, let's select the values
SELECT empid, empname, empage, empsalary;
END LOOP;
-- Step 4: Close the cursor
CLOSE employee_cursor;
END // Rohith H P 47
DELIMITER ;
Explanation:
[Link] the cursor and variables:
• DECLARE done INT DEFAULT 0; to handle the end of the cursor data.
• Variables eid, ename, age, salary are declared to store the values fetched from the cursor.
• DECLARE employee_cursor CURSOR FOR SELECT eid, ename, age, salary FROM
Employee; declares the cursor.
[Link] the cursor:
• OPEN employee_cursor; opens the cursor for fetching data.
[Link] the data:
• The LOOP is used to fetch data row by row using FETCH employee_cursor INTO eid, ename,
age, salary;.
• IF done THEN LEAVE read_loop; END IF; checks if the cursor has no more data to fetch and
exits the loop.
• SELECT eid, ename, age, salary; demonstrates what you can do with the fetched data. You can
replace this with any other operations you need.
[Link] the cursor:
• CLOSE employee_cursor; closes the cursor.
You can execute this procedure in MySQL, and then call the procedure using:

CALL fetch_employee_data();

Rohith H P 48
Experiment-6
Write a PL/SQL block of code using parameterized
Cursor, that will merge the data available in the newly
created table N_RollCall with the data available in the
table O_RollCall.
If the data in the first table already exist in the second
table then that data should be skipped.

Rohith H P 49
Solution :
To accomplish this task in MySQL, we can use a stored
procedure with a parameterized cursor to merge data
from one table (N_RollCall) into another table (O_RollCall)
while skipping existing data.
We’ll iterate through the records of N_RollCall and insert
them into O_RollCall only if they do not already exist.

Rohith H P 50
1. Create the tables
Let’s create two tables N_RollCall and O_Rollcall with similar structures.

• CREATE DATABASE ROLLCALL;

• USE ROLLCALL;

-- Create N_RollCall table

CREATE TABLE N_RollCall (
student_id INT PRIMARY KEY,
student_name VARCHAR(25),
birth_date DATE
);

-- Create O_RollCall table with common data

CREATE TABLE O_RollCall (
student_id INT PRIMARY KEY,
student_name VARCHAR(25),
birth_date DATE
);
Rohith H P 51
2. Add sample records to both tables.
Let’s insert some data into the O_RollCall table.

INSERT INTO O_RollCall VALUES (1, 'Shivam', '1995-08-15’);

INSERT INTO O_RollCall VALUES (3, ‘Chethana', '1990-12-10’);

Let’s insert some sample data into the N_RollCall table, including records that are common with O_RolCall

INSERT INTO N_RollCall VALUES (1, 'Shivam', '1995-08-15’); -- Common record with O_RollCall
INSERT INTO N_RollCall VALUES(2, ‘Bharath', '1998-03-22’);
INSERT INTO N_RollCall VALUES (3, ‘Chethana', '1990-12-10’); -- Common record with O_RollCall
INSERT INTO N_RollCall VALUES(4, 'Devendra', '2000-05-18’);
INSERT INTO N_RollCall VALUES(5, 'Eshwar', '1997-09-03');

Rohith H P 52
3. Define the stored procedure
Let’s define the merge_rollcall_data stored procedure to merge records from
N_RollCall into O_RollCall, skipping existing records.

DELIMITER //

CREATE PROCEDURE merge_rollcall_data()

BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE n_id INT;
DECLARE n_name VARCHAR(25);
DECLARE n_birth_date DATE;

-- Declare cursor for N_RollCall table

DECLARE n_cursor CURSOR FOR
SELECT student_id, student_name, birth_date FROM N_RollCall;

Rohith H P 53
-- Declare handler for cursor
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET done = TRUE;

-- Open the cursor

OPEN n_cursor;
-- Start looping through cursor results
cursor_loop: LOOP
-- Fetch data from cursor into variables
FETCH n_cursor INTO n_id, n_name, n_birth_date;

-- Check if no more rows to fetch

IF done THEN
LEAVE cursor_loop;
END IF;
Rohith H P 54
-- Check if the data already exists in O_RollCall
IF NOT EXISTS (
SELECT 1 FROM O_RollCall WHERE student_id = n_id
) THEN

-- Insert the record into O_RollCall

INSERT INTO O_RollCall (student_id, student_name, birth_date) VALUES (n_id, n_name,
n_birth_date);
END IF;
END LOOP;

-- Close the cursor

CLOSE n_cursor;

END

//DELIMITER ;

Rohith H P 55
Rohith H P 56
4. Execute the stored procedures
Execute the merge_rollcall_data stored procedure to merge records from N_RollCall into
O_RollCall while skipping existing records.

mysql> CALL merge_rollcall_data();

5. Verify records in O_RollCall.

After executing the procedure , verify the records in the O_RollCall table to confirm that
new records from N_RollCall have been inserted, while existing common records have
been skipped.

-- Select all records from O_RollCall

mysql> SELECT * FROM O_RollCall;

Rohith H P 57
+----------------+--------------------+----------------+
| student_id | student_name | birth_date |
+----------------+--------------------+----------------+
| 1 | Shivam | 1995-08-15 | <-- Common record, not duplicated
| 2 | Bharath | 1998-03-22 | <-- New record from N_RollCall
| 3 | Chethana | 1990-12-10 | <-- Common record, not duplicated
| 4 | Devendra | 2000-05-18 | <-- New record from N_RollCall
| 5 | Eshwar | 1997-09-03 | <-- New record from N_RollCall
+---------------+---------------------+-----------------+
5 rows in set

Rohith H P 58