Writing a PL/SQL code for Student Transcript Analyzer in Oracle Apex

This PL/SQL block appears to be designed to retrieve and process information from a database table called utas_transcript. Let’s break it down step by step:

1. DECLARE: This keyword is used to declare variables that will be used within the PL/SQL block. In this block, two variables are declared: v_student_id and v_course_id, both of type varchar2(100).
2. BEGIN: Marks the beginning of the executable part of the PL/SQL block.
3. FOR student_rec IN (…): This is a loop that iterates over each distinct seat_no from the utas_transcript table. It retrieves a unique student ID in each iteration and assigns it to the variable v_student_id.
4. FOR course_rec IN (…): Within the outer loop, there’s an inner loop. This loop iterates over each course attempted by the current student (v_student_id). It retrieves the course number (course_no) and the count of attempts for that course, grouping them by course number. The course number and attempt count are assigned to v_course_id and v_course_attempts variables respectively.
5. DBMS_OUTPUT.PUT_LINE: This statement prints out a message indicating the student ID (v_student_id), the course attempted (v_course_id), and the number of attempts (v_course_attempts). This line essentially prints each course attempted by each student along with the number of attempts.
6. END LOOP: Ends the inner loop that iterates over courses attempted by each student.
7. END LOOP: Ends the outer loop that iterates over each student.
8. END: Marks the end of the executable part of the PL/SQL block.
9. /: This is a forward slash indicating the end of the PL/SQL block.

Overall, this block is fetching information about the courses attempted by each student from the utas_transcript table and printing out the course number along with the number of attempts for each student. The queries within the loops are filtered based on certain conditions, such as filtering out records where next_sem_proj is null and filtering out certain values in the note column.

This script creates or replaces a view named “UTAS_CGPA7”. Let’s break down its components:

1. CREATE OR REPLACE FORCE EDITIONABLE VIEW: This statement creates or replaces a view in the database. A view is a virtual table that presents data from one or more tables or other views. The FORCE keyword is used to create the view even if the base tables do not exist or have errors. The EDITIONABLE keyword indicates that the view can be part of an editioned object.
2. “UTAS_CGPA7”: This is the name of the view being created or replaced.
3. (“LEVELID”, “SEAT_NO”, “SUM_CHP”, “COMP_CGPA”, “CGPA”): These are the columns of the view. The view will have columns named “LEVELID”, “SEAT_NO”, “SUM_CHP”, “COMP_CGPA”, and “CGPA”.
4. AS: This keyword indicates that the SELECT statement following it will be used to define the view.
5. select DISTINCT utas_cgpa5.levelid, utas_cgpa5.seat_no, utas_cgpa5.sum_chp, utas_cgpa3.computed_cgpa AS comp_cgpa, ROUND(( utas_cgpa5.sum_chp / utas_cgpa3.computed_cgpa),2) AS CGPA from utas_CGPA5,utas_cgpa3: This SELECT statement retrieves data from two tables, utas_CGPA5 and utas_cgpa3. It selects distinct values of “levelid”, “seat_no”, and “sum_chp” from utas_cgpa5. It also selects “computed_cgpa” from utas_cgpa3, aliasing it as “comp_cgpa”. Additionally, it calculates the CGPA by dividing “sum_chp” by “computed_cgpa” and rounds it to two decimal places.
6. WHERE utas_CGPA5.SEAT_NO = utas_cgpa3.SEAT_NO AND utas_cgpa5.levelid = utas_cgpa3.levelid: This WHERE clause filters the rows where “seat_no” and “levelid” match between utas_CGPA5 and utas_cgpa3 tables.
7. ORDER BY utas_cgpa5.levelid: This ORDER BY clause sorts the result set by the “levelid” column in ascending order.

In summary, the view “UTAS_CGPA7” is created by selecting distinct values of “levelid”, “seat_no”, and “sum_chp” from utas_cgpa5 table, along with “computed_cgpa” from utas_cgpa3 table. It calculates the CGPA and orders the result set by “levelid”. There are no left joins or other complex operations in this view definition.

Oracle Apex App Development for current Sem GPA, CGPA, and Over All CGPA Calculations:

Course Projection for Grade Scenario:

Transcript Data Form Grid:

Degree Audit Check List Mapping (Level and Specialization Wised)