Consider the following table to answer any two of the following: 14
student(stud_ID, subl, sub2, sub3, percentage, grade)
(1) Write a procedure to display the details of students who have scored
more than 70%
(2) Create a cursor to update the marks of all students by 5 numbers who
have got less than 45 marks.
(3) Create a stored procedure that will take student id as an input and display
result information<\/p>\n\n\n\n
You can write a stored procedure to retrieve the details of students whose percentage is greater than 70%. Here\u2019s the SQL procedure:<\/p>\n\n\n\n
CREATE OR REPLACE PROCEDURE Get_Students_Above_70 AS\nBEGIN\n -- Select query to get details of students with percentage > 70\n FOR student IN\n (SELECT stud_ID, sub1, sub2, sub3, percentage, grade\n FROM student\n WHERE percentage > 70)\n LOOP\n -- Display each student's details\n DBMS_OUTPUT.PUT_LINE('Student ID: ' || student.stud_ID || \n ' Subject 1: ' || student.sub1 || \n ' Subject 2: ' || student.sub2 || \n ' Subject 3: ' || student.sub3 || \n ' Percentage: ' || student.percentage || \n ' Grade: ' || student.grade);\n END LOOP;\nEND;\n\/<\/code><\/pre>\n\n\n\n2) Create a cursor to update the marks of all students by 5 marks who have got less than 45 marks<\/strong><\/h3>\n\n\n\nFor this, we need to create a cursor that fetches students who have a percentage of less than 45% and then updates their marks by increasing each subject’s marks by 5. Here is an example of the cursor and update operation:<\/p>\n\n\n\n
DECLARE\n CURSOR low_score_cursor IS\n SELECT stud_ID, sub1, sub2, sub3\n FROM student\n WHERE percentage < 45;\n \n -- Declare variables to hold the marks of each student\n v_sub1 NUMBER;\n v_sub2 NUMBER;\n v_sub3 NUMBER;\n \nBEGIN\n -- Open the cursor and loop through the students with low marks\n FOR student IN low_score_cursor LOOP\n -- Fetch the current marks for the student\n v_sub1 := student.sub1;\n v_sub2 := student.sub2;\n v_sub3 := student.sub3;\n \n -- Update the student's marks by adding 5 to each subject\n UPDATE student\n SET sub1 = v_sub1 + 5, sub2 = v_sub2 + 5, sub3 = v_sub3 + 5\n WHERE stud_ID = student.stud_ID;\n \n -- Optionally, print the updated result (if using DBMS_OUTPUT)\n DBMS_OUTPUT.PUT_LINE('Updated marks for Student ID: ' || student.stud_ID);\n END LOOP;\n \n -- Commit the changes to the database\n COMMIT;\nEND;\n\/<\/code><\/pre>\n\n\n\n3) Create a stored procedure that will take student ID as input and display result information<\/strong><\/h3>\n\n\n\nThis stored procedure will take a stud_ID<\/code> as input and return the student’s information such as subjects, percentage, and grade. Here’s an example of the procedure:<\/p>\n\n\n\nCREATE OR REPLACE PROCEDURE Get_Student_Result (p_stud_ID IN NUMBER) AS\n v_sub1 NUMBER;\n v_sub2 NUMBER;\n v_sub3 NUMBER;\n v_percentage NUMBER;\n v_grade CHAR(1);\nBEGIN\n -- Select the student's details based on the input student ID\n SELECT sub1, sub2, sub3, percentage, grade\n INTO v_sub1, v_sub2, v_sub3, v_percentage, v_grade\n FROM student\n WHERE stud_ID = p_stud_ID;\n\n -- Display the result for the given student ID\n DBMS_OUTPUT.PUT_LINE('Student ID: ' || p_stud_ID);\n DBMS_OUTPUT.PUT_LINE('Subject 1: ' || v_sub1);\n DBMS_OUTPUT.PUT_LINE('Subject 2: ' || v_sub2);\n DBMS_OUTPUT.PUT_LINE('Subject 3: ' || v_sub3);\n DBMS_OUTPUT.PUT_LINE('Percentage: ' || v_percentage);\n DBMS_OUTPUT.PUT_LINE('Grade: ' || v_grade);\nEXCEPTION\n WHEN NO_DATA_FOUND THEN\n DBMS_OUTPUT.PUT_LINE('No student found with the given ID.');\nEND;\n\/<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"Consider the following table to answer any two of the following: 14student(stud_ID, subl, sub2, sub3, percentage, grade)(1) Write a procedure to display the details of students who have scoredmore than 70%(2) Create a cursor to update the marks of all students by 5 numbers whohave got less than 45 marks.(3) Create a stored procedure that […]<\/p>\n","protected":false},"author":1,"featured_media":101,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,38],"tags":[39,41,40],"class_list":["post-114","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-bscit","category-rdbms-ii","tag-rdbms","tag-rdbms-paper-solution","tag-relational-database-management-system"],"_links":{"self":[{"href":"https:\/\/alpeshconnect.in\/blog\/wp-json\/wp\/v2\/posts\/114","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/alpeshconnect.in\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/alpeshconnect.in\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/alpeshconnect.in\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/alpeshconnect.in\/blog\/wp-json\/wp\/v2\/comments?post=114"}],"version-history":[{"count":1,"href":"https:\/\/alpeshconnect.in\/blog\/wp-json\/wp\/v2\/posts\/114\/revisions"}],"predecessor-version":[{"id":115,"href":"https:\/\/alpeshconnect.in\/blog\/wp-json\/wp\/v2\/posts\/114\/revisions\/115"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/alpeshconnect.in\/blog\/wp-json\/wp\/v2\/media\/101"}],"wp:attachment":[{"href":"https:\/\/alpeshconnect.in\/blog\/wp-json\/wp\/v2\/media?parent=114"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/alpeshconnect.in\/blog\/wp-json\/wp\/v2\/categories?post=114"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/alpeshconnect.in\/blog\/wp-json\/wp\/v2\/tags?post=114"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}