BSc IT Sem III 2016 2017 2017 ASQL Question Paper - Mumbai University | munotes

Questions asked in this paper

• li) Figures to the right indicate full marks

1. Q1 Attempt any Two. What are views? Explain the difference between a view and a table 10 marks
   • b) Write a short note on
   • i) GROUP BY clause What do you mean by sequence? Explain the keywords and parameters used
   • d) Consider following tables Solve following queries
   • i)Display the details of book whose order is placed having the author
   • ii) Find out maximum of ordered quantity by each distributor
   • iii) Create table Book with bookid as primary key
2. Q2 Attempt any Two
   • a) Write a short note on hierarchical retrieval
   • b) Explain WITH clause with suitable example. '
   • c) Write a short note on
   • i) UNION clause Explain all date-time functions used in oracle
3. Q3 Attempt any Two
   • a) Write a short note on Substitution variable. Explain with proper example
   • b) What do you mean by anonymous PL/SQL block?-Explain with example Explain the role of commit and rollback With respect to transaction. Also explain the concept of savepoint in it
   • d) Consider a table Patient(pno, pname, Write a PL/SQL block to display the details of patients who are suffering from ‘Malaria’
4. Q4 Attempt any Two
   • a) What do you mean by parameterized cursor? How to declare it and use it?
   • b) What are the steps involved in explicit cursor management?
   • c) What is a cursor? What are the attributes used in Implicit cursor?
   • d) Consider a table Employee(empno, fname, Iname). Write parameterized cursor to accept employee number and print its first name and last name
5. Q5 Attempt any Two
   • a) What do you mean by procedure? What are the different keywords and parameters used for
   • b) What do you mean by package? What are the advantages of using
   • c) Explain the advantages of using functions
   • d) Consider a table Student( rollno, sname, city). Create a function to return the name of
6. Q6 Attempt any Two. | 10 marks
   • a) What do you mean by Trigger? Explain the syntax of creating trigger with all keywords
   • b) Write a short note on Instead of trigger for a view
   • c) Explain all types of Trigger
   • d) Consider a table Employee(empno,ename,city). Create a trigger that displays a ‘Inserted Successfully’ after inserting a row in the Employee
7. Q7 Attempt aay Three. Explain ALTER TABLE command with syntax and proper 15 marks
   • b) Write short note on hierarchical retrieval
   • c) What are the advantages of PL/SQL?
   • d) Explain the use of EXIT and EXIT WHEN in PL/SQL block What are the ways to call a function in PL/SQL?
   • f) Explain following steps to execute dynamic SQL with DBMS_ SQL package
   • iii) Execute the SQL statement

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