Advanced SQL (April – 2014) [IDOL - Revised Course | Question Paper]

Click Here: (PDF Link 1) | Click Here: (PDF Link 2) | Click Here: (PDF Link 3)

Click Here: (Image View) | Click Here: (Text View)

Time: 3 Hours
Total Marks: 100

N.B.:    (1) All Questions are Compulsory.
             (2) Figure on right indicate maximum marks.

Q.1 Attempt Both The Question: (10 Marks)

(A) Write differences between procedure and function.

(B) Write a program using PL/SQL to calculate factorial of number entered by user.

Q.2 Attempt Any Three From The Following: (15 Marks)

(A) What are joins? Explain left join and right join with example.

(B) What are aggregate functions? How are they used? Explain with example.

(C) Explain different types of constraints in SQL.

(D) What is View? What are the advantages of view?

Q.3 Attempt Any Three From The Following: (15 Marks)

(A) What is a privilege? Explain the different types of privileges.

(B) What is correlated subquery? Explain with suitable example.

(C) Explain set operators with example.

(D) Explain roll up and cube operators with syntax and example.

Q.4 Attempt Any Three From The Following: (15 Marks)

(A) What is transaction? Explain ROLLBACK, COMMIT and SAVEPOINT in transaction.

(B) Explain PL.SQL Block Structure.

(C) Explain with example the %TYPE attribute.

(D) How you can create and Display Bind Variables? Explain.

Q.5 Attempt Any Three From The Following: (15 Marks)

(A) Write a PL/SQL program to insert 3 new location_id for the country code ‘IND’ and city ‘Mumbai’ using For…loop in Locations table. (Location: location_id, City, Country_code).

(B) Explain %ROWTYPE attribute with example.

(C) Explain IF-THEN-ELSE statement in PL/SQL with example.

(D) What are Cursors? Explain the classification of cursors.

Q.6 Attempt Any Three From The Following: (15 Marks)

(A) What is Stored Procedure? How to create and call stored procedure?

(B) What are advantages of using stored functions?

(C) What is a packages? How to create a package specification?

(D) What are stored functions? Write its creation and calling example.

Q.7 Attempt Any Three From The Following: (15 Marks)

(A) Write short note on Dynamic SQL.

(B) Create a trigger to restrict insert into the employee table to certain business hours, (8.00-18.00), Monday through Friday.

(C) Define triggers. Explain types of triggers.

(D) Differentiate between BEFORE and AFTER Triggers.