Forum

Forums
PL SQL
Interview Questions...
PL/SQL Code to Assi...
 
Share:
Notifications
Clear all

PL/SQL Code to Assign Grades Based on Marks | Student Grading System Example

 
    Last Post
RSS

Posts: 46
 Priya Roy
Guest
Topic starter
Jun 03, 2021 8:48 am
(@Priya Roy)
Eminent Member
Joined: 5 years ago

Create a table for Student (Sid, Marks, grade). Insert 5 records containing values of Sid and Marks. Write PL/SQL code to assign grades to every student based on their marks according to the following rules.

90 and above : O
80 to 89 : E
70 to 79 : A
60 to 69 : B
Otherwise: C

How Can we achieve this? 

This topic was modified 3 months ago by SQL Admin
Topic Tags
student marks student grade sqlquerystudentgrade createtablestudentgrade
4 Replies
Posts: 97
 SQL Admin
Admin
Dec 29, 2021 4:08 pm
(@sql-admin)
Estimable Member
Joined: 5 years ago

PL/SQL Code to Assign Grades Based on Marks | Student Grading System

In this tutorial, we will learn how to implement a simple Student Grading System using PL/SQL. The logic is straightforward: we will assign grades to students based on their marks using the CASE statement in PL/SQL.

Step 1: Create the Student Table

The first step is to create a Student table that holds student IDs, marks, and the corresponding grades.

-- Create the Student table
CREATE TABLE Student (
    Sid NUMBER PRIMARY KEY,
    Marks NUMBER(3),
    Grade CHAR(1)
);

Step 2: Insert Sample Records

Next, we insert some sample data into the Student table.

-- Insert sample student records
INSERT INTO Student (Sid, Marks) VALUES (1, 95);
INSERT INTO Student (Sid, Marks) VALUES (2, 87);
INSERT INTO Student (Sid, Marks) VALUES (3, 73);
INSERT INTO Student (Sid, Marks) VALUES (4, 65);
INSERT INTO Student (Sid, Marks) VALUES (5, 58);

-- Save changes
COMMIT;

Step 3: PL/SQL Block to Assign Grades

The following PL/SQL block updates the Grade column based on student marks:

BEGIN
    FOR student_record IN (SELECT Sid, Marks FROM Student) LOOP
        UPDATE Student
        SET Grade = CASE 
            WHEN student_record.Marks >= 90 THEN 'O'
            WHEN student_record.Marks >= 80 THEN 'E'
            WHEN student_record.Marks >= 70 THEN 'A'
            WHEN student_record.Marks >= 60 THEN 'B'
            ELSE 'C' 
        END
        WHERE Sid = student_record.Sid;
    END LOOP;
    
    -- Save changes
    COMMIT;
END;
/

Explanation of the PL/SQL Block

  • FOR student_record IN (SELECT Sid, Marks FROM Student): Iterates over each student record.
  • UPDATE Student SET Grade = CASE ...: Uses the CASE statement to assign grades.
  • WHERE Sid = student_record.Sid: Ensures updates apply only to the correct record.
  • COMMIT: Saves the updates to the database.

Result After Execution

Sid Marks Grade
1 95 O
2 87 E
3 73 A
4 65 B
5 58 C

Conclusion

We successfully implemented a PL/SQL student grading system that assigns grades based on marks. This approach ensures efficient and automated grade allocation. If you found this article helpful, share it with others!

This post was modified 2 months ago by SQL Admin
Reply
Posts: 97
 SQL Admin
Admin
Nov 23, 2024 1:54 pm
(@sql-admin)
Estimable Member
Joined: 5 years ago

How to Create a Student Table and Assign Grades Using PL/SQL

If you’re preparing for an interview or enhancing your database skills, understanding how to create tables and implement business logic using PL/SQL is essential. In this tutorial, we’ll go through a practical example of creating a Student table, inserting sample data, and dynamically assigning grades.

Problem Statement

We need to accomplish the following tasks:

  • Create a Student table with Sid, Marks, and Grade columns.
  • Insert sample records into the table.
  • Use PL/SQL to assign grades based on the following conditions:
Marks Range Grade
90 and above O
80 to 89 E
70 to 79 A
60 to 69 B
Below 60 C

Step 1: Create the Student Table

CREATE TABLE Student (
    Sid NUMBER PRIMARY KEY,
    Marks NUMBER(3),
    Grade CHAR(1)
);

Step 2: Insert Sample Records

INSERT INTO Student (Sid, Marks) VALUES (1, 95);
INSERT INTO Student (Sid, Marks) VALUES (2, 82);
INSERT INTO Student (Sid, Marks) VALUES (3, 75);
INSERT INTO Student (Sid, Marks) VALUES (4, 67);
INSERT INTO Student (Sid, Marks) VALUES (5, 45);

COMMIT;

Step 3: Write PL/SQL Code to Assign Grades

BEGIN
    FOR student_rec IN (SELECT Sid, Marks FROM Student) LOOP
        UPDATE Student
        SET Grade = CASE 
            WHEN student_rec.Marks >= 90 THEN 'O'
            WHEN student_rec.Marks BETWEEN 80 AND 89 THEN 'E'
            WHEN student_rec.Marks BETWEEN 70 AND 79 THEN 'A'
            WHEN student_rec.Marks BETWEEN 60 AND 69 THEN 'B'
            ELSE 'C' 
        END
        WHERE Sid = student_rec.Sid;
    END LOOP;
    COMMIT;
END;
/

Result Verification

Run the following query to check the assigned grades:

SELECT * FROM Student;

Expected Output

Sid Marks Grade
1 95 O
2 82 E
3 75 A
4 67 B
5 45 C

Why Use PL/SQL?

  • Efficient bulk processing of records.
  • Dynamic updates using conditional logic.
  • Enhanced maintainability for complex database operations.

Learn More

Looking for more SQL and PL/SQL challenges? Visit our SQL Queries Forum to explore detailed discussions, tips, and queries shared by database experts.

Final Thoughts

By following these steps, you have successfully created a PL/SQL script to assign grades dynamically based on marks. This example is frequently asked in interviews, so practicing it will boost your confidence in writing efficient SQL queries.

For more SQL and PL/SQL tutorials, visit our forum and stay updated with the latest database tips!

This post was modified 2 months ago by SQL Admin
Reply
Posts: 97
 SQL Admin
Admin
Jan 09, 2025 3:43 pm
(@sql-admin)
Estimable Member
Joined: 5 years ago

Here I’ll walk you through a step-by-step guide to writing a PL/SQL code block that assigns grades to students based on their marks. This task is common in student management systems, where automation is essential for efficiency.

Rules for Assigning Grades:

  • Marks ≥ 90: Grade A
  • Marks ≥ 80 and < 90: Grade B
  • Marks ≥ 70 and < 80: Grade C
  • Marks ≥ 60 and < 70: Grade D
  • Marks < 60: Grade F

PL/SQL Code to Assign Grades

Here’s the complete PL/SQL script that demonstrates how to assign grades to every student in a table named STUDENT_MARKS.

sql
 
DECLARE CURSOR student_cursor IS SELECT student_id, marks 
FROM STUDENT_MARKS; v_student_id 
STUDENT_MARKS.student_id%TYPE; v_marks 
STUDENT_MARKS.marks%TYPE; v_grade CHAR(1); BEGIN FOR student_rec IN student_cursor 
LOOP v_student_id := student_rec.student_id; 
v_marks := student_rec.marks; -- Assign grades based on marks IF v_marks >= 90 THEN v_grade := 'A'; ELSIF v_marks >= 80 
THEN v_grade := 'B'; ELSIF v_marks >= 70 THEN v_grade := 'C'; ELSIF v_marks >= 60 THEN v_grade := 'D'; ELSE 
v_grade := 'F'; END IF; -- Update the table with the assigned grade UPDATE STUDENT_MARKS SET grade = v_grade 
WHERE student_id = v_student_id; -- Optional: Log progress for debugging DBMS_OUTPUT.PUT_LINE
('Student ID: ' || v_student_id || ' Assigned Grade: ' || v_grade); 
END LOOP; -- Commit changes to make them permanent COMMIT; END;

Explanation of the Code

  1. Cursor Declaration: The student_cursor fetches student_id and marks from the STUDENT_MARKS table.
  2. Grade Assignment Logic: Uses a series of IF-ELSIF conditions to determine the grade based on the student's marks.
  3. Updating Grades: The UPDATE statement updates the grade column in the STUDENT_MARKS table for each student.
  4. Debugging: DBMS_OUTPUT.PUT_LINE helps in tracking the progress during execution.
  5. Transaction Handling: The COMMIT statement ensures all changes are saved to the database.

Sample Table Structure

For this code to work, your STUDENT_MARKS table should have the following structure:

sql
 
CREATE TABLE STUDENT_MARKS (
student_id NUMBER PRIMARY KEY,
marks NUMBER,
grade CHAR(1)
);

Testing the Code

Here’s how you can test the PL/SQL block:

  1. Insert sample data:
    sql
     
    INSERT INTO STUDENT_MARKS (student_id, marks) 
VALUES (1, 95); INSERT INTO STUDENT_MARKS (student_id, marks) VALUES (2, 85); 
INSERT INTO STUDENT_MARKS (student_id, marks)
 VALUES (3, 75); INSERT INTO STUDENT_MARKS (student_id, marks) 
VALUES (4, 65); INSERT INTO STUDENT_MARKS (student_id, marks) VALUES (5, 50); COMMIT;
    Execute the PL/SQL block.
  2. Query the STUDENT_MARKS table to verify the grades:
    sql
     
    SELECT * FROM STUDENT_MARKS;

Best Practices for Implementation

  • Ensure the STUDENT_MARKS table has a proper backup before running the script.
  • Use a test environment to validate the PL/SQL block before deploying it in production.
  • Add error-handling mechanisms (EXCEPTION block) to handle unexpected scenarios.

Conclusion

This PL/SQL code simplifies the grade assignment process, making it an efficient solution for student management systems. By following this guide, you can handle dynamic scenarios and customize the grading logic as per your requirements.

Reply
Posts: 97
 SQL Admin
Admin
Jan 18, 2025 10:11 am
(@sql-admin)
Estimable Member
Joined: 5 years ago

PL/SQL Code to Assign Grades Based on Marks | Student Grading System Example

Introduction

Grading students based on their marks is a common requirement in academic databases. In this post, we will create a PL/SQL program that assigns grades to students based on their marks.

We will:
✔ Create a Student Table with Sid, Marks, and Grade columns.
✔ Insert sample records with student marks.
✔ Write a PL/SQL block to update grades based on predefined conditions.

Let’s get started! 🚀

1. Creating the Student Table

The first step is to create a table that stores student ID (Sid), marks, and the assigned grade.

 
CREATE TABLE Student ( Sid NUMBER PRIMARY KEY, Marks NUMBER(3), Grade CHAR(1) );

2. Inserting Sample Data

We insert five student records with different marks to test the grading logic.

 
INSERT INTO Student (Sid, Marks) VALUES (101, 95); 
INSERT INTO Student (Sid, Marks) VALUES (102, 85); INSERT INTO Student (Sid, Marks) 
VALUES (103, 75); INSERT INTO Student (Sid, Marks) 
VALUES (104, 65); INSERT INTO Student (Sid, Marks) 
VALUES (105, 50); COMMIT;

3. Writing PL/SQL Code to Assign Grades

The following PL/SQL block updates the Grade column based on the grading rules.

 
BEGIN UPDATE Student SET Grade = CASE WHEN 
Marks >= 90 THEN 'O' WHEN Marks BETWEEN 80 
AND 89 THEN 'E' WHEN Marks BETWEEN 70 AND 79 
THEN 'A' WHEN Marks BETWEEN 60 AND 69 THEN 'B' 
ELSE 'C' END; COMMIT; END; /

🔹 Explanation:

  • The CASE statement evaluates the marks and assigns a grade accordingly.
  • The BETWEEN condition ensures that students within a specific range get the right grade.
  • The COMMIT statement saves the changes permanently.

4. Verifying the Results

After running the script, we can check the results using:

 
SELECT * FROM Student;

🔽 Expected Output:

Sid Marks Grade
101 95 O
102 85 E
103 75 A
104 65 B
105 50 C

This confirms that the PL/SQL grading logic is working correctly. ✅

5. Alternative Approach Using a PL/SQL Procedure

For better reusability, we can create a stored procedure that dynamically assigns grades.

 
CREATE OR REPLACE PROCEDURE Assign_Grades
 IS BEGIN UPDATE Student SET Grade = CASE 
WHEN Marks >= 90 THEN 'O' 
WHEN Marks BETWEEN 80 AND 89 THEN 'E' 
WHEN Marks BETWEEN 70 AND 79 THEN 'A' 
WHEN Marks BETWEEN 60 AND 69 THEN 'B' 
ELSE 'C' END; COMMIT; END; /

💡 Usage: Execute the procedure with:

 
BEGIN Assign_Grades; END; /

6. Performance Optimization Tips

To optimize performance, consider the following:
✅ Indexing: Add an index on Marks for faster query execution.
✅ Triggers: Use a trigger to automatically assign grades when marks are inserted.
✅ Bulk Processing: For large datasets, use BULK COLLECT and FORALL statements.

Conclusion

In this post, we:
✔ Created a Student Table
✔ Inserted sample records
✔ Used a PL/SQL block to assign grades
✔ Optimized the solution with stored procedures & best practices

🚀 Next Steps: Try modifying the grading rules or integrating this logic into a university database project.

💬 Have questions? Drop them in the comments below!

This post was modified 3 months ago 2 times by SQL Admin
Reply

Leave a reply

Author Name

Author Email

Title *

Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Topic Tags:  student marks (1) , student grade (1) , sqlquerystudentgrade (1) , createtablestudentgrade (1) ,
Share: