Scribd
Upload
33 views3 pages

Student Marks Analysis with PL/SQL

The document outlines a PL/SQL program that uses a cursor to analyze student marks from a database. It calculates total and average marks for each student and categorizes their performance into Distinction, First Class, Second Class, or Fail. The program includes steps for declaring variables, processing records, and displaying results, along with an example of creating a student table and inserting data.

Uploaded by

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

Student Marks Analysis with PL/SQL

The document outlines a PL/SQL program that uses a cursor to analyze student marks from a database. It calculates total and average marks for each student and categorizes their performance into Distinction, First Class, Second Class, or Fail. The program includes steps for declaring variables, processing records, and displaying results, along with an example of creating a student table and inserting data.

Uploaded by

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

Algorithm: Student Mark Analysis Using Cursor

Aim:

To write a PL/SQL program using a cursor to retrieve student marks from a database, calculate the total and
average marks for each student, and categorize their performance as Distinction, First Class, Second Class,
or Fail based on the computed average.

Step 1: Start

Step 2: Declare Variables

 Declare a cursor to select student details (student_id, name, mark1, mark2, mark3) from the students
table.
 Declare variables to store:
o Student ID, name, marks
o Total marks (v_total)
o Average marks (v_avg)
o Result category (v_result)

Step 3: Open Cursor

 Open the declared cursor to start processing rows one by one.

Step 4: Fetch Records in Loop

 Repeat until all rows are fetched:


1. Fetch one record into the declared variables.
2. If no more records, exit the loop.

Step 5: Process Each Student

For each student:

1. Calculate total marks:


total = mark1 + mark2 + mark3
2. Calculate average:
average = total / 3
3. Determine result category:
o If average >= 75: Result = "Distinction"
o Else if average >= 60: Result = "First Class"
o Else if average >= 50: Result = "Second Class"
o Else: Result = "Fail"
4. Display the student details with total, average, and result.

Step 6: Close Cursor

 After all records are processed, close the cursor.

Step 7: End

ER DIAGRAM
+---------------------+
| Student |
+---------------------+
| student_id (PK) |
| name |
| mark1 |
| mark2 |
| mark3 |
+---------------------+
PROGRAM :

CREATE TABLE students (


student_id NUMBER PRIMARY KEY,
name VARCHAR2(100),
mark1 NUMBER,
mark2 NUMBER,
mark3 NUMBER
);

INSERT INTO students VALUES (1, 'Alice', 85, 90, 88);


INSERT INTO students VALUES (2, 'Bob', 60, 70, 65);
INSERT INTO students VALUES (3, 'Charlie', 40, 35, 45);
INSERT INTO students VALUES (4, 'David', 95, 98, 100);
COMMIT;

--PL/SQL PROGRAM
-- SAVE THE PROGRAM AS [Link]
DECLARE
CURSOR student_cursor IS
SELECT student_id, name, mark1, mark2, mark3 FROM students;

v_id students.student_id%TYPE;
v_name [Link]%TYPE;
v_m1 students.mark1%TYPE;
v_m2 students.mark2%TYPE;
v_m3 students.mark3%TYPE;
v_total NUMBER;
v_avg NUMBER;
v_result VARCHAR2(20);
BEGIN
OPEN student_cursor;
LOOP
FETCH student_cursor INTO v_id, v_name, v_m1, v_m2, v_m3;
EXIT WHEN student_cursor%NOTFOUND;

v_total := v_m1 + v_m2 + v_m3;


v_avg := v_total / 3;

IF v_avg >= 75 THEN


v_result := 'Distinction';
ELSIF v_avg >= 60 THEN
v_result := 'First Class';
ELSIF v_avg >= 50 THEN
v_result := 'Second Class';
ELSE
v_result := 'Fail';
END IF;

DBMS_OUTPUT.PUT_LINE('ID: ' || v_id || ', Name: ' || v_name || ', Total: ' || v_total ||
', Avg: ' || ROUND(v_avg, 2) || ', Result: ' || v_result);
END LOOP;
CLOSE student_cursor;
END;

Common questions

Powered by AI

The loop structure in the PL/SQL program allows for the sequential retrieval and processing of multiple student records, iterating over each row retrieved by the cursor. This enables the program to compute total and average marks for each student individually and categorize their performance in real-time. Without the loop, the program would be unable to process more than one record, limiting its ability to handle multiple entries efficiently. The loop ensures that the end of the cursor data set is detected through the 'EXIT WHEN student_cursor%NOTFOUND' condition, terminating processing appropriately .

The PL/SQL program demonstrates the use of a cursor to sequentially fetch rows from the 'students' table. A cursor named 'student_cursor' is declared to select student details. The program begins with opening the cursor and enters a loop to fetch records one by one. For each student, it calculates the total marks by summing 'mark1', 'mark2', and 'mark3', then computes the average marks by dividing the total by three. The program uses conditional statements to determine the student's academic result as 'Distinction', 'First Class', 'Second Class', or 'Fail' based on the average. Finally, it displays the computed values and closes the cursor once all records are processed .

To enhance the PL/SQL program for larger datasets, optimizations could include using bulk operations, such as BULK COLLECT and FORALL, to reduce the overhead of repetitive fetch operations and improve performance. Additionally, implementing exception handling could improve resilience by managing possible errors like data type mismatches or division by zero. Optimizing index usage and restructuring queries might also improve execution speed. Introducing parallel processing techniques could further allow simultaneous handling of multiple records, effectively scaling performance with dataset size .

The PL/SQL program reflects modular programming principles by structuring tasks into distinct stages, such as data fetching, processing, categorizing, and output display. Declaring a cursor separates data retrieval logic from processing, promoting clarity and reusability. Variables are declared up-front, encapsulating data handling within controlled loops. These practices facilitate maintenance and make future enhancements easier without disrupting the core logic, while ensuring that components like the categorization process can be individually verified and refined .

The provided program lacks explicit handling for null or unexpected values in student records; therefore, unanticipated nulls in 'mark1', 'mark2', or 'mark3' could cause incorrect total and average calculations, potentially leading to inaccurate categorizations. To address this, additional logic could involve checking for nulls before computations or defaulting null values to zero. Implementing EXCEPTION blocks could catch arithmetic errors like division by zero when dealing with missing data. This would ensure robustness against incomplete or erroneous data input .

Inserting sample data, such as the records for 'Alice', 'Bob', 'Charlie', and 'David', provides real input to test the PL/SQL program's functionality and accuracy. This data represents diverse performance categories, enabling comprehensive testing of the program's logic for calculating totals, averages, and correctly categorizing results. Such diversity ensures that all conditional branches are evaluated, verifying the program's robust handling of different scenarios and correctness in processing and output display .

The program ensures accuracy in averaging and categorization using precise arithmetic operations for calculating totals and averages directly from the marks fetched per student. It then applies a clear logical categorization using conditional statements, which are straightforward and unambiguous, minimizing error potential. The displayed average is rounded to two decimal places using the 'ROUND' function, enhancing clarity and precision in output. The structured approach to fetching and processing data reinforces result accuracy consistently for each student processed .

The ER diagram provides a visual representation of the 'students' table structure that the PL/SQL program outputs from. It displays the primary key 'student_id' and attributes 'name', 'mark1', 'mark2', and 'mark3', which correspond to the table columns utilized in the cursor's SELECT statement. The diagram highlights that 'student_id' is a unique identifier for each record, ensuring each student's data is distinctly retrievable and processable in the program. The correspondence between the diagram and program ensures that data retrieval and processing match the database schema structure .

In the PL/SQL program, students' performance is categorized into four categories: 'Distinction', 'First Class', 'Second Class', and 'Fail'. These are determined based on the average marks calculated from their total scores. Specifically, if the average is 75 or above, the result is 'Distinction'. If the average is 60 or above but less than 75, it is 'First Class'. Averages of 50 or above but less than 60 are categorized as 'Second Class'. Averages below 50 result in a 'Fail'. The program implements this categorization using a series of IF-ELSEIF conditions .

The declared variables in the PL/SQL program serve as placeholders for storing data fetched from the cursor and for performing computations. Variables such as 'v_id', 'v_name', 'v_m1', 'v_m2', and 'v_m3' temporarily hold individual student details from each fetched row. 'v_total' and 'v_avg' are utilized for storing computed total and average marks, while 'v_result' holds the performance category. These variables ensure that data retrieved from the table can be manipulated and analyzed effectively before being outputted in the program .

You might also like