BSc IT Sem III 2015 2016 2016 SQL Question Paper - Mumbai University | munotes

Questions asked in this paper

• ii) Figures to the right indicate full marks

1. Q1 Attempt any Two. What is the use of nextval and currval attribute in sequence? 10 marks
   • i) Uniqueness Constraint Explain all Single-Row Functions used in
   • d) Consider following Solve following queries —
   • i) Find out the book ordered in minimum quantity
   • ii) Find out the total number of books in each category
   • iii) Create a view to display the details of book whose order is placed having the author ‘Henry Korth’ and publisher ‘TMG’
   • iv) Find out the book whose price is less than the average price of all books. (Solve
   • v) Display the distributors city wise
2. Q2 Attempt any Two. 10 marks
   • a) Write short note on SQL Factoring clause
   • b) Write a short note on hierarchical retrieval
   • c) Write a short note on
   • i) UNION clause
   • d) Explain all date-time functions used in oracle
3. Q3 Attempt any Two. 10 marks
   • a) How PL/SQL blocks are nested? Illustrate with example
   • b) Explain all scalar data types available in PL/SQL
   • c) What do you mean by anonymous PL/SQL block? Explain with example
   • d) Consider a table Room(room_id, room_type, capacity, location). Write a PL/SQL block t display the details room whose room id is entered by the user
4. Q4 Attempt any Two. 10 marks
   • a) Explain FOR UPDATE and WHERE CURRENT clause of cursors
   • b) What do you mean by parameterized cursor? How to declare it and use it? What is a cursor? What are the attributes used in Implicit
   • d) Consider a table Parts(pno,pname,color). Write PL/SQL block which will display
5. Q5 Attempt any Two. 10 marks
   • a) Explain the ways to call a function in PL/SQL
   • b) What do you mean by package? What are the advantages of using package?
   • c) How to create, call and remove a stored procedure?
   • d) Consider table Persons(Person_id,name,city). Create a function which will return total number of persons from the table
   • a) Compare database trigger and a stored procedure
   • b) Explain following steps to execute dynamic SQL with DBMS_SQL package
   • ii) Bind columns to cursor columns ili) Execute query to fetch data from tables into cursor columns What do you mean by Trigger? Explain the syntax of creating trigger with all keywords
   • d)Write short note on instead of trigger
6. Q7 Attempt any Three. 15 marks
   • a) How to change the structure of created table? Explain with example
   • b) How to grant and revoke the privileges from the user?
   • c) What are identifiers? Explain with its types
   • d) What do you mean by an exception? Explain oracle named exception handlers
   • e) What do you mean by function? What are the different keywords and parameters used fot
   • f) List out the steps in order to execute dynamic SQL with DBMS_SQL package,

Read from the scan above, so a character or two may differ. The scan is the original.