Advanced SQL (June – 2016) [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)

B.Sc.IT: Semester – III

[Advanced SQL]

Question Paper (June – 2016) [Revised Course]

Time: 3 Hours
Total Marks: 100

N.B.:       (1) All Questions are Compulsory.

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

(A) What is cursor? Explain the types of cursors.

(B) What is view? Explain different types of view with example.

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

(A) What is a sequence? Explain syntax for creating a sequence.

(B) List and explain the different types of constraints with example.

(C) Write SQL statements for the following database:

       student(Student-ID, Student-name, Percentage, Course-ID)

       course(Course-ID, Course-name, Course-head)

       (i) List all the students whose name's second charcter is 'r'.

       (ii) List all the students whose percentage is in between 40 to 60.

       (iii) List the course name whose head is 'Mathur'.

       (iv) List the student name who scored highest percentage in course-id 'USPTIA'

       (v) List all the students whose name start with 'j'.

(D) Explain GROUP BY clause along with example.

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

(A) What is a privilege? Explain Grant and Revoke commands with example.

(B) Explain multiple column subqueries with example.

(C) What are set operators? List and explain the any two set operators with example.

(D) Using Date Time functions, how to calculate age from date of birth?

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

(A) Explain the different data types used in PL/SQL.

(B) List and explain advantages of PL/SQL.

(C) Discuss the rules about the block structure in PL/SQL.

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

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

(A) Explain the PL/SQL control structure with its types and syntax.

(B) Write a PLISQL block of code for area of Triangle two times with different values. Store the values in table.

(C) Write short note on %NOTFOUND and %ROWCOUNT attributes on the same table.

(D) What is exception? Explain the syntax of exception handling in PLISQL.

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

(E) How to create and call Stored Procedure? Explain with the help of examples.

(F) What is subprogram? How to create Modularized and Layered subprogram?

(G) What are package in PLISQL? List the benefits of packages.

(H) Write short note on Data Dictionary and PLISQL Source Code.

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

(A) What is Trigger? Explain the syntax of creating trigger with example in PL/SQL.

(B) Write short note on %TYPE and %ROWTYPE attribute.

(C) What are Dynamic Queries? How to execute PL/SQL Block Dynamically?

(D) Distinguish between BEFORE and AFTER Triggers with example.