Class Task
BSCS-4 Semester
Spring 2025.
Subject: Adv. Database
Task
Submitted to:
Maam Noor
Saba
Submitted By:
Name Ayesha Waqar
Reg No 24-cs-170
Department of CS
HITEC University, Taxila
Task 1: Student Grade using CASE
Code:
DELIMITER //
CREATE PROCEDURE StudentGrade(
IN Name VARCHAR(50),
IN Score INT,
IN RegNo VARCHAR(20)
)
BEGIN
DECLARE Grade CHAR(1);
SET Grade = CASE
WHEN Score >= 90 THEN 'A'
WHEN Score >= 80 THEN 'B'
WHEN Score >= 70 THEN 'C'
ELSE 'F'
END;
SELECT
Name AS Name,
RegNo AS RegNo,
Score AS Score,
Grade AS Grade;
END //
DELIMITER ;
CALL StudentGrade('Ayesha Waqar',95,'24-CS-170');
Output:
Explanation:
This stored procedure takes student details as input such as name, score and registration
number. It uses a CASE statement to determine a grade based on the score. Finally, it
displays all the student information along with the calculated grade.
Task 2: Employee Bonus Calculation using IF, ELSEIF and Nested IF
Code:
CREATE TABLE Employees (
EmpID INT,
EmpName VARCHAR(50),
Salary INT
);
INSERT INTO Employees VALUES
(1, 'Ali', 6000),
(2, 'Sara', 5500),
(3, 'Hamza', 4800);
DELIMITER //
CREATE PROCEDURE EmpBonus(
IN p_EmpID INT,
IN p_Performance CHAR(1)
)
BEGIN
DECLARE v_Name VARCHAR(50);
DECLARE v_Salary INT;
DECLARE v_Bonus INT DEFAULT 0;
DECLARE v_Tier VARCHAR(10);
SELECT EmpName, Salary
INTO v_Name, v_Salary
FROM Employees
WHERE EmpID = p_EmpID;
IF v_Salary > 6000 THEN
SET v_Bonus = v_Salary * 0.15;
ELSEIF v_Salary > 5000 THEN
SET v_Bonus = v_Salary * 0.10;
ELSE
SET v_Bonus = v_Salary * 0.05;
END IF;
IF p_Performance = 'A' THEN
IF v_Salary > 5000 THEN
SET v_Bonus = v_Bonus + 500;
END IF;
END IF;
SET v_Tier = CASE
WHEN v_Salary > 6000 THEN 'Tier 1'
WHEN v_Salary > 5000 THEN 'Tier 2'
ELSE 'Tier 3'
END;
SELECT
p_EmpID AS EmpID,
v_Name AS EmpName,
v_Salary AS OldSalary,
v_Bonus AS Bonus,
v_Tier AS Tier,
'Ayesha Waqar' AS StudentName,
'24-CS-170' AS RollNo;
END //
DELIMITER ;
CALL EmpBonus(1, 'A');
Output:
Explanation:
This stored procedure calculates employee bonus based on salary using IF and ELSEIF
conditions. It also uses a nested IF to give an extra bonus if performance is ‘A’ and salary is
above 5000. The result shows employee details, bonus, and salary tier.
Task 3: Number Classification using WHILE Loop and CASE
Statement
Code:
DELIMITER //
CREATE PROCEDURE LoopTask()
BEGIN
DECLARE counter INT DEFAULT 1;
DECLARE v_Type VARCHAR(10);
WHILE counter <= 5 DO
SET v_Type = CASE
WHEN counter BETWEEN 1 AND 2 THEN 'Low'
WHEN counter BETWEEN 3 AND 4 THEN 'Medium'
WHEN counter = 5 THEN 'High'
END;
SELECT
counter AS Number,
v_Type AS Category,
'Ayesha Waqar' AS StudentName,
'170' AS RollNo,
'24-CS-170' AS RegNo;
SET counter = counter + 1;
END WHILE;
END //
DELIMITER ;
CALL LoopTask();
Output:
Exaplanation:
This stored procedure uses a WHILE loop to iterate from 1 to 5. For each number, a CASE
statement classifies it as Low, Medium, or High. The result displays each iteration along with
student details.