ASSIGNMENT 5
Practical 5: Write a Stored Procedure namely proc_Grade for the categorization of
student.
If marks scored by students in examination is <=1500 and marks>=990 then student
will be
placed in distinction category if marks scored are between 989 and900 category is
first class,
if marks899and 825 category is Higher Second Class.
Write a PL/SQLblock to use procedure created with above requirement.
Stud_Marks(name, total_marks) Result (Roll, Name, Class)
...................................................................................
................
mysql> create database studentinf;
Query OK, 1 row affected (0.03 sec)
mysql> use studentinf;
Database changed
mysql> create table student_marks(name varchar(20), total_marks int);
Query OK, 0 rows affected (0.06 sec)
mysql> insert into student_marks values('swati',1000);
Query OK, 1 row affected (0.02 sec)
mysql> insert into student_marks values('prachi',900);
Query OK, 1 row affected (0.01 sec)
mysql> insert into student_marks values('Ram',699);
Query OK, 1 row affected (0.01 sec)
mysql> insert into student_marks values('Radha',768);
Query OK, 1 row affected (0.01 sec)
mysql> insert into student_marks values('Madhu',670);
Query OK, 1 row affected (0.01 sec)
mysql> insert into student_marks values('jyoti',1300);
Query OK, 1 row affected (0.01 sec)
mysql> select*from student_marks;
+-------+-----------+
| name |total_marks|
+-------+-----------+
|swati | 1000 |
|prachi | 900 |
| Ram | 699 |
|Radha | 768 |
|Madhu | 670 |
|Jyoti | 1300 |
+-------+-----------+
6 rows in set (0.01 sec)
mysql> create procedure proc_grade(in roll_no int, in name varchar(20), in marks
int)
-> begin
-> declare Class varchar(20);
->if marks>=990 and marks<=1500 then set class='Distinction';
-> elseif marks>=990 and marks<=989 then set class='First class;
-> elseif marks>=825 and marks<=899 then set class='Second class;
-> elseif marks>=500 and marks<=824 then set class="Pass';
-> else set class='Fail';
end if;
-> insert into student_marks values(name,marks);
-> insert into result values(roll_no, name,class);
-> end;
->&
Query OK, 0 rows affected (0.02 sec)
mysql> call proc_grade(63,'swati',1000);
->&
Query OK, 1 row affected (0.02 sec)
mysql> call proc_grade(56,'samarth',689);
->&
Query OK, 1 row affected (0.02 sec)
mysql> select*from result;&
+-------+-------+-----------+
|roll_no| name | class |
+-------+-------+-----------+
| 63 |swati |Distinction|
| 56 |samarth| Pass |
+-------+-------+-----------+
2 rows in set (0.00 sec)
mysql> call proc_grade(51,'prachi',900);&
Query OK, 1 row affected (0.01 sec)
mysql> call proc_grade(67, 'ram',699);&
Query OK, 1 row affected (0.01 sec)
mysql> call proc_grade(90,'radha', 768);&
Query OK, 1 row affected (0,01 sec)
mysql> call proc_grade(57,'madhu',670);
Query OK, 1 row affected (0.01 sec)
mysql> cali proc_grade 34, jyoti, 1300);&
Query OK, 1 row affected (0.01 sec)
mysql> select from result;&
+-------+-------+-----------+
|roll_no| name | class |
+-------+-------+-----------+
| 63 |swati |Distinction|
| 56 |samarth| Pass |
+-------+-------+-----------+
OTHER ENTRIES ASWELL
7 rows in set (0.00 sec)
