Assignment No.
1. What is view and how to create it? Can you update view? If yes, how? If not, why not?
2. Defined stored procedure. Explain the creating and calling stored procedure with
example.
3. Consider following schema.
4. Student_fee_details (rollno, name, fee_deposited, date) Write a trigger to preserve old
values of student fee details before updating in the table.
5. Consider the following schemes
Supplier(SNO, Sname, Status, City)
Parts (PNO, Pname, Color, Weight, City)
Shipments(SNO,PNO,QTY)
Write SQLqueries for the following:
i) Find shipment information (SNO, Sname, PNO, Pname, QTY) for those having
quantity less than 157.
ii) List SNO, Sname, PNO, Pname for those suppliers who made shipments of parts
whose quantity islarger than the average quantity
iii) Find aggregate quantity of PNO 1692 of color green for which shipments made by
supplier number who residing Mumbai.
6. What is an index? What are the advantages and disadvantages of using index on a table?
7. What is a trigger? How to create it? Discuss various types of triggers.
8. Write a PL/SQL block of code which accepts the rollno from user. The attendance of
rollno entered by user will be checked in
student_attendance(RollNo,Attendance) table and display on the screen.
