70 Marks 22318
3 Hrs
---‐---------------------------------------------------------------------
Instructions – (1) All Questions are Compulsory.
(2) Answer each next main Question on a new page.
(3) Illustrate your answers with neat sketches
wherever necessary.
(4) Figures to the right indicate full marks.
(5) Assume suitable date, if necessary.
(6) Mobile Phone, Pager and any other Electronic
Communication devices are not permissible in
Examination Hall.
1. Attempt any FIVE of the following: 10 Marks
a) List any four data types in SQL.
b) List any four string functions in SQL.
c) State any two advantages of functions in PL/SQL.
d) Define :
(i) Instance (ii) Schema
e) Define the term :
i) Candidate key
ii) Primary key
f) List DCL commands.
g) Define i) data abstraction, ii) data redundancy.
2. Attempt any three of the following : 12 Marks
a) Describe commit and rollback with syntax and example.
b) Explain overall structure of DBMS with the help of diagram
c) Explain block structure of PL/SQL.
d) With neat sketch describe Transaction states diagram.
3. Attempt any THREE of the following: 12 Marks
a) Describe the concept of view with example. State its purpose
b) Explain function in PL/SQL with example.
c) Describe commit and rollback with syntax and example.
d) Define Normalization. Explain 3NF with example.
4. Attempt any THREE of the following: 12 Marks
a) Explain advantages of DBMS over file processing system
b) Explain transaction ACID propertiDBMS
c) Write and explain syntax for creating Trigger.
d) Explain any two control structure in PL/SQL with example.
e) Describe database backups with it’s types.
5. Attempt any TWO of the following : 12 Marks
a) Consider following schema :
EMP (empno, deptno, ename, salary, designation, join_date, DOB,
dept_location). Write down SQL queries for following :
(i) Display employees name & number in decreasing order of salary.
(ii) Display employee name & employee number whose designation is
Manager.
(iii) Display age of employees with ename.
(iv) Display total salary of all employees.
(v) Display employee names having deptno as 20 and dept_location is
Mumbai.
(vi) Display name of employee who earned lowest salary.
b) Draw ER diagram for library management system considering issue and return, fine collection
facility. Consider appropriate entities.
c) Consider the table
Student (name, marks, dept, age, place, phone, birthdate) Write SQL query for
following :
(i) To list students having place as ‘Pune’ or ‘Jalgaon’.
(ii) To list students having same department (dept) as that of ‘Rachana’.
(iii) To change marks of ‘Rahul’ from 81 to 96.
(iv) To list student name and marks from ‘Computer’ dept.
(v) To list student name who have marks less than 40.
(vi) To list students who are not from ‘Mumbai’.
6. Attempt any TWO of the following : 12 Marks
a) Write SQL command for following :
(i) Create user
(ii) Grant privileges to user
(iii) Remove privileges from user
b) Write a PL/SQL program, which accept the number from user. If user enters an odd number then
exception invalid number is raised using user defined exception handling.
c) Consider the structure for book table as Book-Master (bookid, bookname,
author, no_of copies, price)
Write down SQL queries for following
(i) Write a command to create Book_master table.
(ii) Get authorwise list of all books.
(iii) Display all books whose price is between ` 500 & ` 800.
(iv) Display all books with details whose name start with ‘D’.
(v) Display all books whose price is above ` 700.
(vi) Display all books whose number of copies are less than 10.
