Assignment 4
Student Name: Maitreyee Kaushik Shendye
USN Number: UCE2023460
Division: A
Batch: A3
Problem Statement: To implement the stored procedures and cursors
using student database
Query statements and output:
1. Write a stored procedure to fill the class of each student.
mysql> create database student_details;
Query OK, 1 row affected (0.01 sec)
mysql> use student_details;
Database changed
mysql> CREATE TABLE result(
-> rollNumber INT PRIMARY KEY AUTO_INCREMENT,
-> student_name VARCHAR(30) NOT NULL,
-> total_marks INT NOT NULL,
-> class VARCHAR(30),
-> CONSTRAINT validate_marks CHECK (total_marks>=0 AND
total_marks<=1500));
Query OK, 0 rows affected (0.05 sec)
mysql> ALTER TABLE results AUTO_INCREMENT=1;
ERROR 1146 (42S02): Table 'student_details.results' doesn't exist
mysql> ALTER TABLE result AUTO_INCREMENT=1;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> INSERT INTO result (student_name,total_marks)
-> VALUES
-> ('Vivaan Sharma', 1350),
-> ('Aditya Kumar', 1100),
-> ('Viran Singh', 800),
-> ('Reyansh Verma', 1250),
-> ('Ayaan Gupta', 1300),
-> ('Krishna Mishra', 1050),
-> ('Sai Pandey', 1280),
-> ('Lakshya Tiwari', 180),
-> ('Anaya Tripathi', 1420),
-> ('Saanvi Srivastava', 1000),
-> ('Ishaan Thakur', 1370),
-> ('Riya Sahu', 1490),
-> ('Diya Jaiswal', 1000),
-> ('Pooja Chouhan', 1330),
-> ('Sneha Maurya', 940),
-> ('Karan Rathod', 700),
-> ('Neha Pandey', 1260),
-> ('Mohit Dwivedi', 845),
-> ('Rohan Yadav', 920);
Query OK, 19 rows affected (0.01 sec)
Records: 19 Duplicates: 0 Warnings: 0mysql>
SELECT * FROM result;
9 rows in set (0.00 sec)
mysql> DELIMITER $$
mysql> CREATE PROCEDURE set_class1()
-> BEGIN
-> UPDATE result
-> SET class = CASE
-> WHEN total_marks <= 1500 AND total_marks > 990 THEN 'Distinction'
-> WHEN total_marks <= 990 AND total_marks > 900 THEN 'First Class'
-> WHEN total_marks <= 900 AND total_marks > 825 THEN 'Higher second
class'
-> WHEN total_marks <= 825 AND total_marks > 600 THEN 'Second class'
-> ELSE 'Fail'
-> END;
-> SELECT * FROM result;
-> END $$
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
mysql> CALL set_class1();
19 rows in set (0.01 sec)
2. Write a stored procedure and make use of cursor to fill the
class of each student.
mysql> UPDATE result SET class = NULL;
Query OK, 19 rows affected (0.01 sec)
Rows matched: 19 Changed: 19 Warnings: 0
mysql> SELECT * FROM result;
19 rows in set (0.00 sec)
mysql> DELIMITER ##
mysql> CREATE PROCEDURE set_class_cursor()
-> BEGIN
-> DECLARE v_finished INT DEFAULT 0;
-> DECLARE v_marks INT DEFAULT 0;
-> DECLARE v_class VARCHAR(20);
-> DECLARE v_id INT;
-> DECLARE marks_cursor CURSOR FOR SELECT rollNumber, total_marks
FROM result;
-> DECLARE CONTINUE HANDLER
-> FOR NOT FOUND SET v_finished = 1;
-> OPEN marks_cursor;
-> get_marks: LOOP
-> FETCH marks_cursor INTO v_id, v_marks;
-> IF v_finished = 1 THEN
-> LEAVE get_marks;
-> END IF;
-> IF v_marks <= 1500 AND v_marks > 990 THEN
-> SET v_class = 'Distinction';
-> END IF;
-> IF v_marks <= 990 AND v_marks > 900 THEN
-> SET v_class = 'First Class';
-> END IF;
-> IF v_marks <= 900 AND v_marks > 825 THEN
-> SET v_class = 'Higher second class';
-> END IF;
-> IF v_marks <= 825 AND v_marks > 600 THEN
-> SET v_class = 'Second class';
-> END IF;
-> IF v_marks <= 600 THEN
-> SET v_class = 'Fail';
-> END IF;
-> UPDATE result SET class = v_class WHERE rollNumber = v_id;
-> END LOOP get_marks;
-> CLOSE marks_cursor;
-> END ##
Query OK, 0 rows affected (0.03 sec)
mysql> DELIMITER ;
mysql> CALL set_class_cursor();
Query OK, 0 rows affected (0.06 sec)
mysql> SELECT * FROM result;
