SUBJECT : DBMS SEM-3 23012011133
Practical-10
AIM: Perform & Learn QUERIES BASED ON INDEX, VIEW and
SEQUENCE
1) What is INDEX? Explain it with example.
Answer :
● Indexing is a way to optimize the performance of a
database by minimizing the number of disk accesses
required when a query is processed. It is a data structure
technique which is used to quickly locate and access the
data in a database.
● Indexing refers to sorting of records in database. Indexing
helps in reducing the time consumed to search the Record
as sorted database is much easier to be searched hence
resulting in faster Execution.
There are two types of index
1) SIMPLE INDEX : uses Single Column Database
Management System Semester-3 CE-IT-CE(AI).
2) COMPOSITE INDEX : Index created on more than 1
column.
Each of the types are further divided into
a) SIMPLE : Duplicate data in the Fields can be inserted
b) UNIQUE : Denies Duplicate Data.
BATCH-B1 PAGE | 1
SUBJECT : DBMS SEM-3 23012011133
2) CREATE a simple INDEX idx_order ON order amount FROM
the orders TABLE.
Code & Output:
3) CREATE a UNIQUE INDEX idx_order1 number and customer
number columns of the orders TABLE.
Code & Output:
4) What is SEQUENCE? Explain it with example.
Answer :
Sequence is used to generate the series of no. Sequential
column has machine generated values thus eliminating the
possible human error in insertion or updatation of Record.
For Example : Consider a sequence SQ_ROLL which is a
sequence starting at 1 having max value 60.
BATCH-B1 PAGE | 2
SUBJECT : DBMS SEM-3 23012011133
For inserting the roll nos. of students in student table, instead of
manual insertion we directly use sq_roll.nextval (Returns the
next value in sequence).
INSERT INTO Student VALUES (sq_roll.nextval, ’manish’).
5) CREATE a SEQUENCE order_seq with the following
parameters, Incremented by 3, cache 4 and which will
generate the numbers FROM 1 TO 9999 in Ascending order.
Code & Output :
6) SELECT the current value of the order_seq.
Code & Output :
7) SELECT the next value of the order_seq.
Code & Output :
BATCH-B1 PAGE | 3
SUBJECT : DBMS SEM-3 23012011133
8) CREATE TABLE order1 (Onum varchar2 (5), odate date),
INSERT the VALUES for Onum, odate in the TABLE order1.
The order_seq must be used TO generate Onum and odate
must be SET TO system date.
Code & Output :
9) Consider the same query AS above but you have TO
INSERT Onum like [MMYY (seq_no)] ex: if current date is
22-SEP-06 & the value generated by SEQUENCE is 1 then
your order number must be 22091.
Code & Output :
BATCH-B1 PAGE | 4
SUBJECT : DBMS SEM-3 23012011133
10) Change the interval 3 TO1 of the SEQUENCE order_seq.
Code & Output :
11) DROP the SEQUENCE order_seq.
Code & Output :
12) DROP the INDEX idx_order.
Code & Output :
BATCH-B1 PAGE | 5
SUBJECT : DBMS SEM-3 23012011133
13) What is VIEW?
Answer :
● Any relation that is not the part of the logical model, but is
made visible to the user as a virtual relation, is called a
view. It is possible to support a large no. of views on top of
any given set of actual relations.
● When we create a view, it will store only the definition of the
view and no data is stored in the system catalogue i.e data
exists only in the base table. Whenever a call is made to
the view, data is scanned from the base table.
There are Two types of views:
1) UPDATABLE VIEW: Data can be inserted into the table by
the VIEW user. For creating a updatable view, all the primary
keys and NOT NULL keys must be included in the view.
2) NON-UPDATABLE VIEW: Data in the base table cannot be
inserted by the view user although the user can perform deletion
and updation operation with the help of referencing clause.
14) CREATE a VIEW vw_order ON order date and amount of the
orders TABLE.
Code & Output :
15) CREATE a VIEW vw_cust ON customer number, customer
name, city of the customer TABLE.
Code & Output :
16) INSERT the VALUES (’21-Sep-06’, 6000) in a VIEW vw_order,
explain the error if any.
BATCH-B1 PAGE | 6
SUBJECT : DBMS SEM-3 23012011133
Code & Output :
Reason :
onum is a primary key which is not included in vw_order view
making it a non-updatable view therefore it is not possible to
insert values through view vw_order.
17) INSERT the VALUES (‘C2008’, ‘Harsh’, ’Mehsana’) in a VIEW
vw_cust.
Code & Output :
18) Delete a record WHERE order_number is O3001 using VIEW
vw_order.
Code & Output :
19) CHANGE the name ‘kavish’ TO ‘Deep’ using VIEW vw_cust.
Code & Output :
20) Give the user hsm permission Only TO VIEW records in the
TABLEs salesman and customer along with an option TO
further GRANT permission ON these TABLEs TO other users.
Code & Output :
BATCH-B1 PAGE | 7
SUBJECT : DBMS SEM-3 23012011133
21) Give the user hsm all data manipulation privileges ON the
TABLE customer without an option TO further GRANT
permission TO other users.
Code & Output :
22) Take back all privileges given TO the user hsm ON the
TABLE customer.
Code & Output :
BATCH-B1 PAGE | 8
