DBMS IMPORTANT QUESTIONS

Unit No: 01 DATABASE SYSTEM CONCEPT

Que Previously
Question Marks
. No. Asked
1 List disadvantages of typical file processing system. 2M W-18
2 Define i)Data Abstraction ii)Data Redundancy 2M W-18
3 Distinguish between network model and hierarchical model. 4M W-18 W-19
Draw an E-R diagram of library management system W-18
4 considering issue and return,fine calculation facility, also 4M
show primary key, weak entity and strong entity.
Define : S-19
5 2M
(i) Instance (ii) Schema
6 List any four advantages of DBMS. 2M S-19
Explain overall structure of DBMS with the help of S-19
7 4M
diagram.
8 State difference between relational and hierarchical model. 4M S-19
Draw an E-R diagram of library management system S-19
9 considering issue and return,Fine calculation facility. 6M
Consider appropriate entities
State any two advantages of DBMS over file processing W-19
10 2M
system.
12 Draw three level architecture of DBMS. 2M W-19
13 Define table and field. 2M W-19
14 Explain strong and weak entity set. 4M W-19
Draw an ER diagram for library management system. (Use
15 6M W-19
Books, Publisher & Member entities).
16 List any four applications of DBMS 2M W-18
17 State the four database users. 2M W-18
18 Explain the four roles of database administrator 4M W-18
Draw ER diagram for Hospital Management System
(Use DOCTOR,PATIENT,HOSPITAL and
19 6M W-18
MEDICAL_RECORD Entity).
Identify Primary Key and Foreign Key.
20 Define data model. List its types 2M S-22
21 State the components used in E-R diagram. 2M S-22
22 List any four data types in SQL 2M S-22
With neat diagram explain three level architecture of
23 4M S-22
database system
24 Explain advantages of DBMS over file processing system 4M S-22
Draw E-R diagram of Banking system considering deposite,
25 withdrawl facility. Also show primary key, weak entity, 6M S-22
strong entity
Unit No: 02 RELATIONAL DATA MODEL
Que Previously
Question Marks
. No. Asked
Define the term:
1 i) Candidate key 2M W-18
ii) ii) Primary key
2 List Four DDL commands with syntax. 2M W-18
3 Define Normalization, list its types. 2M W-18
 DBMS IMPORTANT QUESTIONS

4 Explain set Operators with example. 4M W-18 W-19

5 Describe commit and rollback with syntax and example. 4M W-18
Consider the following database
Employee(emp_id,emp_name,emp_city,emp_addr,emp_dep
t,join_date)
7 i) Display the emp_id of employee who live in city ‘Pune’ 6M W-18
or ‘Nagpur’.
ii) Change the employee name ‘Ayush’ to ‘Ayan’.
iii) Display the total number of employee whose dept is 50.
Consider the following schema Depositor (ACC_no, Name, 6M W-18
PAN, Balance). Create a view on Depositor having
8
attributes(ACC_No,PAN) where balance is greater than
100000
i) create user ‘Rahul’ 6M W-18
ii) grant create, select,insert,update, delete, drop privilege to
9
‘Rahul’
iii) Remove the select privilege from user ‘Rahul’
10 State any two E.F. Codd’s rule for RDBMS. 2M S-19
11 List DCL commands. 2M S-19
12 Define Normalization and list its types. 2M S-19
Explain difference between delete and truncate command
13 4M S-19
with example.
14 State and explain 2NF with example. 4M S-19
List the SQL operations and explain range searching
15 operations between and pattern matching operator ‘like’ 4M S-19
with example.
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 S-19
16 6M
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;
Draw ER diagram for library management system
17 considering issue and return, fine collection facility. 6M S-19
Consider appropriate entities.
Write SQL command for following :
(i) Create user
18 6M
(ii) Grant privileges to user S-19
(iii) Remove privileges from user
19 Enlist DML commands 2M W-19
20 Define primary key and foreign key. 2M W-19
21 State and explain 3NF with example. 4M W-19
22 Describe create & alter command with syntax & example. 4M W-19
 DBMS IMPORTANT QUESTIONS

Write a command to crate table student (rollno,

Stud_name,branch, class, DOB, City, Contact_no) and write
down queries for following:
(i) Insert one row into the table
23 (ii) Save the data 6M W-19
(iii) Insert second row into the table
(iv) Undo the insertion of second row
(v) Create save point S1.
(vi) Insert one row into the table.
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.
24 ii) Display employee name & employee number whose 6M W-19
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.
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.
25 6M
(iii) Display all books whose price is between 500 & 800. W-19
(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.
Describe database privileges. Write down the procedure for
26 granting & revoking privileges in database objects to the 6M W-19
users.
27 Enlist DDL and DML commands. 2M W-19
Explain grant and revoke command with syntax and W-19
28 4M
example.
Explain create and rename command with syntax and W-19
29 4M
example.
30 List and explain DCL commands 4M W-19
31 Explain 2NF with example. 4M W-19
Normalize database 6M W-19
32 Employee(emp_id,emp_name,phone,skill,salary,deptno,dep
t_name,jobno,job_title) upto 3NF
Write SQL query for following consider table
33 EMP(empno , deptno, ename ,salary, Designation,
joiningdate, DOB,city)
 DBMS IMPORTANT QUESTIONS

i) Display names of employees whose experience is more 6M W-19

than 10 years
ii) Display age of employees
iii) Display average salary of all employee
iv) Display name of employee who earned highest salary
Create table
EMP( empno , deptno, ename ,salary, Designation,
joiningdate, DOB,city).
i) Insert one row into the table
ii) Save the data
34 iii) Insert second row into the table 6M W-19
iv) Undo the insertion of second row
v) Insert two rows into the table
vi) Create Savepoint s1
vii) Insert one row into the table
viii) Undo upto savepoint s1
Write SQL query for following consider table
EMP(empno , deptno, ename ,salary, Designation,
joiningdate, DOB,city)
i) Display employees name and number in an increasing
order of salary
ii) Display employee name and employee number dept wise
35
iii) Display total salary of all employee 6M W-19
iv) Display number of employees dept wise
v) Display employee name having experience more than 3
years
vi) Display employee name staring with “S” and working in
deptno 1002
36 Define Normalization. Explain 2NF with example 4M S-22
37 Explain any two DDL commands along with example 4M S-22
Consider the following table employee (Emp_id, 6M S-22
Emp_name,
Emp_age)
i) Display details of employees whose age is less than 30.
ii) Display details of employees whose age is in between
38 the range 30 to 60.
iii) Display total number of employee whose age is 60.
iv) Display names of employees whose name starts with 'S'.
v) Display details of employees whose name end with 'd'.
vi) Display details of employees whose age is greater than
50 and whose name contain 'e'
i) Create table Student (S_id, S_name, S_addr, S_marks)
with proper data type and size. ii) Insert row (5, ‘ABC’,
39 6M S-22
‘RRRRR’, 79) into student table. iii) Update marks of
student 85 where S_id is 5
40 Explain. i) Candidate key ii) Foreign key 4M S-22
Unit No: 03 INTERACTIVE SQL AND ADVANCE SQL
Que. Previously
Question Marks
No. Asked
1 Enlist four aggregate functions. 2M W-18
 DBMS IMPORTANT QUESTIONS

2 Explain any four String functions with example. 4M W-18,S-22

3 Explain joins in SQL with examples. 4M W-18
4 State and Explain 1 NF and 2 NF with example. 4M W-18
Create a sequence 6M W-18
i) Sequence name is Seq_1, Start with 1, increment by 1,
minimum value 1,
maximum value 20.
5 ii) Use a seq_1 to insert the values into table Student( ID
Number(10), Name char
(20));
iii) Change the Seq_1 max value 20 to 50.
iv) Drop the sequence.
6 Write and explain syntax for creating view with example. 4M S-19
7 Explain any four aggregate functions with example 4M S-19
Create simple and composite index. Write command to 6M S-19
8
drop above index.
i) Write a command to create table student(RNO,name
marks ,dept) with proper data types and RNo as primary S-19
9 6M
key
ii) Write a command to create and drop sequence.
10 List any four string functions in SQL. 2M W-19
11 Describe Views and write a command to create view. 4M W-19,S-22
12 Define index. Explain it‟s types. 4M W-19
13 Explain aggregate function with syntax and example. 4M W-19
Write and explain the syntax for creating and dropping W-19
14 4M
synonyms with an example.
Consider the schema Customer (Cust-id, Cust_name,
Cust_addr,
Cust_city)
15 6M W-19
i) Create a view on Customer (Cust_id, Cust_name) where
Cust_city is ‘Pune’
ii) Create a sequence on Cust_id
16 State the use of Avg function with example 2M S-22
17 Write syntax for i) Create Index ii) Drop Index 4M S-22
Unit No: 04 PL/ SQL PROGRAMMING
Que Previously
Question Marks
. No. Asked
1 Define Cursor. List the two types of cursor. 2M W-18
2 Describe exception handling in brief. 4M W-18
3 Explain function in PL/SQL with example. 4M W-18
Draw the block structure of PL/SQL. List advantages of 4M W-18
4
PL/SQL.
Write step by step syntax to create, open and close cursor in 4M W-18
5
PL/SQL.
Write a PL/SQL program which accepts the customer_ID 6M W-18
from the user. If theenters an invalid ID then the exception
6
invalid_id is raised using exception
handling.
7 State any four PL/SQL data types. 2M S-19
 DBMS IMPORTANT QUESTIONS

8 Explain PL/SQL block structure with the help of diagram. 4M S-19,S-22

9 Draw PL/SQL block structure 2M S-19
10 Explain exception handling in PL/SQL with example. 4M S-19
11 Explain cursor with example. 6M S-19
State the use of database trigger and also list types of
12 6M S-19
trigger.
Write a PL/SQL program to calculate factorial of a given
13 6M S-19
number.
14 State any two advantages of functions in PL/SQL. 2M W-19,S-22
15 Explain implicit and explicit cursors. 4M W-19
16 Explain Exception handling with it‟s types. 4M W-19
17 Define database trigger. How to create and delete trigger? 4M W-19
18 Explain any one control structure in PL/SQL with example. 4M W-19
Write a PL/SQL program to print n even numbers using For W-19
19 6M
Loop.
20 Write and explain syntax for creating procedure. 4M W-19
Explain predefined and user defined exception handling W-19
21 4M
with the help of example.
22 Write and explain syntax for creating function 4M W-19
Explain steps of cursor implementation with syntax and 4M W-19
23
example.
Write a PL/SQL program to check whether specified 6M W-19
employee is present in EMP
24 table or not. Accept emp no from user. If employee does not
exist display message
using exception handling.
25 Explain conditional control in PL/SQL with example. 4M S-22
26 Write and explain syntax for creating Trigger 4M S-22
Write a PL/SQL program, which accept the number from 6M
27 user. If user enters an odd number then exception invalid S-22
number is raised using user defined exception handling
Unit No. 5 : DATABASE SECURITY AND TRANSACTION PROCESSING
Que Previously
Question Marks
. No. Asked
1 Explain database security with its requirements in detail. 4M W-18
2 Explain Transaction ACID properties 4M W-18 W-19
3 Explain states of transaction with the help of diagram 4M S-19,S-22
4 Explain recovery techniques with example. 6M S-19,
5 Describe database backups with it’s types. 4M W-19
6 State the concept of database recovery. 2M S-22
7 State types of database user 2M S-22
a) i) Create user ‘RAM’. ii) Grant create, select, insert,
8 update, delete privileges to user ‘RAM’. iii) Remove update 6M S-22
privilege from user ‘RAM’.