Scribd
Upload
26 views4 pages

A5 New

The document outlines the creation of two tables, Stud_Marks and Result, and a stored procedure named proc_Grade to categorize students based on their examination marks. It includes SQL code for creating the database, tables, inserting data, and defining the grading logic. Additionally, it demonstrates how to call the procedure for multiple students and select data from the tables.

Uploaded by

Aishwarya Salunke
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
26 views4 pages

A5 New

The document outlines the creation of two tables, Stud_Marks and Result, and a stored procedure named proc_Grade to categorize students based on their examination marks. It includes SQL code for creating the database, tables, inserting data, and defining the grading logic. Additionally, it demonstrates how to call the procedure for multiple students and select data from the tables.

Uploaded by

Aishwarya Salunke
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 4

NAME: Salunke Aishwarya Santosh

ROLLNO: B-18

TITLE:

PL/SQL Stored Procedure and Stored Function.

Create the following tables.

 Stud_Marks(name, total_marks)
 Result(Roll, Name, Class)

Write a Stored Procedure namely proc_Grade for the categorization of students. If marks scored by a
student in examination is <=1500 and marks>=990 then a student will be placed in distinction
category if marks scored are between 989 and 900 category is first class, if marks 899 and 825
category is Higher Second Class otherwise fail.

1. Write a PL/SQL block to use the procedure created with the above requirement. Insert the data in
both the tables by calling the above procedure.

2. Write a function which will return the total students in a given class.

CODE:

SET sql_safe_updates = 0;

-- Create the database and use it

CREATE DATABASE a_5;

USE a_5;

-- Create the student_marks table

CREATE TABLE IF NOT EXISTS student_marks (

name VARCHAR(20),

marks INT

);

-- Create the result table

CREATE TABLE IF NOT EXISTS result (

name VARCHAR(20),

rollno INT AUTO_INCREMENT PRIMARY KEY,

class VARCHAR(20)
);

-- Insert data into student_marks table

INSERT INTO student_marks (name, marks)

VALUES

('Aishwarya', 1000),

('Preeti', 990),

('Sanika', 850),

('Rohit', 1200),

('Priyanshu', 1300);

-- Insert data into result table

INSERT INTO result (name, class)

VALUES

('Aishwarya', NULL),

('Preeti', NULL),

('Sanika', NULL),

('Rohit', NULL),

('Priyanshu', NULL);

-- Create the procedure for grading

DELIMITER $$

CREATE PROCEDURE proc_grade (IN student_name VARCHAR(20))

BEGIN

DECLARE n VARCHAR(20);

DECLARE m INT;

DECLARE category VARCHAR(20);

SET n = student_name;
-- Fetch the marks for the given student

SELECT marks INTO m FROM student_marks WHERE student_marks.name = n;

-- Determine the category based on marks

IF m >= 990 AND m <= 1500 THEN

SET category = 'distinction';

ELSEIF m >= 900 AND m < 990 THEN

SET category = 'first_class';

ELSEIF m >= 825 AND m <= 899 THEN

SET category = 'high_second_class';

ELSE

SET category = 'no_class';

END IF;

-- Update the result table with the category

UPDATE result SET class = category WHERE result.name = n;

END $$

DELIMITER ;

-- Call the procedure for each student

CALL proc_grade('Aishwarya');

CALL proc_grade('Preeti');

CALL proc_grade('Sanika');

CALL proc_grade('Rohit');

CALL proc_grade('Priyanshu');

-- Select data from the student_marks and result tables

SELECT * FROM student_marks;

SELECT * FROM result;


OUTPUT:

You might also like