use the table to answer the questions
- Pl Sql Use The Table To Answer The Questions Question 1 Create A Pl Sql Procedure To Accept Student Number And Display 1 (33.51 KiB) Viewed 71 times
Question 1
Create a PL/SQL procedure to accept student number and display student name and nationality.
Then run the procedure with your own student number. (student number: 9200214019)
Question 2
Create a host variable and populate it with your own student name (student name : Tartor Atoator) Write an anonymous block
that displays the student number, gender and major using a record data type. (student number : 9200214019, gender : female)
Question 3
Create a PL/SQL function to return the total number of students belonging to a faculty. The
function should have a single IN parameter as a faculty. You should then use an explicit cursor to
count the number of students in that faculty and return the final count.
You must NOT use any implicit cursors, table joins, subqueries, set operators, group functions or SQL
functions (such as COUNT).
Finally, call the function using ITEE as Faculty.
Question 4
Write ONE PL/SQL program (anonymous block) that provides a report showing student nameand information regarding their ordering history.The detailed specification is as follows:- Using an explicit cursor, retrieve all students (SNO) from the STUDENT table. Your cursor willneed to group by students together for processing.- In the appropriate looping structure you will need to-Call the function created in question 3. Store the total count in a local variable. - Processing block will need to make the following decisions. If the total number of students in a faculty is more than 200, we classify the action as "Large Faculty". If the total number of students in a faculty is between 100 and 200, we classify the action as "Medium Faculty". For all other students in a faculty less than 100, we classify them as "Small Faculty". Declare another local variable to store the classification.- You need to process all the information for a student to produce the following output for eachstudent- Finally, create an exception handler which fires when no rows are found. The exception handlershould output the following message to the screen: "No rows found"Important Note:The program must use ONE block only. Do NOT write a block to perform each task of the specificationanove
Write ONE PL/SQL program (anonymous block) that provides a report showing student name
and information regarding their ordering history.
The detailed specification is as follows:
- Using an explicit cursor, retrieve all students (SNO) from the STUDENT table. Your cursor will
need to group by students together for processing.
- In the appropriate looping structure you will need to
-Call the function created in question 3. Store the total count in a local variable.
- Processing block will need to make the following decisions. If the total number of students in a faculty is more than 200, we classify the action as "Large Faculty". If the total number of students in a faculty is between 100 and 200, we classify the action as "Medium Faculty". For all other students in a faculty less than 100, we classify them as "Small Faculty". Declare another local variable to store the classification.
- You need to process all the information for a student to produce the following output for each
student
- Finally, create an exception handler which fires when no rows are found. The exception handler
should output the following message to the screen: "No rows found"
Important Note:The program must use ONE block only. Do NOT write a block to perform each task of the specification
anove
Question 5
Write an appropriate trigger fires after a DML statement on the table STUDENT. The triggershould check whether the faculty number (FNO) has been changed. If there has been a change,insert the old and the new values into the LOGLINE table. Use the mtext1 column.
Write an appropriate trigger fires after a DML statement on the table STUDENT. The trigger
should check whether the faculty number (FNO) has been changed. If there has been a change,
insert the old and the new values into the LOGLINE table. Use the mtext1 column.
ALL QUESTIONS ARE BASED ON THE FOLLOWING TABLES and DATA. STUDENT SNO NOT NULL NUMBER(10) SNAME VARCHAR2(20) AGE NUMBER(2) GENDER CHAR(1) NATIONALITY VARCHAR2(20) PHONE NUMBER(11) MAJOR VARCHAR2(10) FNO CHAR(4) FACULTY FNO FNAME LOC NOT NULL CHAR(4) VARCHAR2(10) VARCHAR2(20)