- Part B Pl Sql Programming 70 Marks All Questions Are Based On The Following Tables And Data Student Sno Not Null Numb 1 (81.24 KiB) Viewed 88 times
PL/SQL Programming (70 marks)
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
NOT NULL
CHAR(4)
FNAME VARCHAR2(10)
LOC VARCHAR2(20)
Marks
Marks
Marks
Question 1 (10 marks)
Create a PL/SQL procedure to accept student number and display
student name and nationality.
Then run the procedure with your own student number.
Question 2 (15 marks)
Create a host variable and populate it with your own student name.
Write an anonymous block
that displays the student number, gender and major using a record
data type.
Question 3 (15 marks)
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.
Part B PL/SQL Programming (70 marks) ALL QUESTIONS ARE BASED ON THE FOLLOWING TABLES and DATA. STUDENT SNO NOT NULL NUMBER(10) VARCHAR2(20) SNAME AGE NUMBER(2) GENDER CHAR(1) NATIONALITY VARCHAR2(20) PHONE NUMBER(11) MAJOR VARCHAR2(10) FNO CHAR(4) FACULTY FNO NOT NULL CHAR(4) FNAME VARCHAR2(10) LOC VARCHAR2(20) Question 1 (10 marks) Marks Create a PL/SQL procedure to accept student number and display student name and nationality. Then run the procedure with your own student number. Question 2 (15 marks) Marks Create a host variable and populate it with your own student name. Write an anonymous block that displays the student number, gender and major using a record data type. Question 3 (15 marks) Marks 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.