Practicals of INDEX and SEQUENCE

                                                PRACTICAL – 6

1. What is Index ?

Ans :- An Index is a copy of selected columns of data from a table that can be searched very efficiently that also includes a low-level disk block address or direct link to the complete row of data it was copied from. Some databases extend the power of indexing by letting developers create indexes on functions or expressions.

2. Create a Simple Index idx_order on order amount from the orders table.

Ans:-create index idx_order on ordered(atm);

3. Create a unique index idx_order1 number and customer number columns of the orders table.

Ans:- create unique index idx_order1 on ordered(atm,cnum);

4. What is sequence?

Ans :- A Sequence is a set of integers 1,2,3… that are generated in order on demand. Sequences are frequently used in databases because many applications require each row in a table to contain a unique value and sequences provide an easy way to generate them.

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.

Ans:- create sequence order_seq1 start with 3 increment by 3  Minvalue 1 Maxvalue 9999 cache 4;

6. Select the current value of the order_seq.

Ans:-select  order_seq1.Currval from dual;

7. Select the next value of the order_seq.

Ans:-select  order_seq1.nextval  from dual;


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.

Ans:-insert into order1(onum,odate)  values(order_seq1.nextval,sysdate);

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.

Ans:-insert into order1(onum,odate) values(To_char(sysdate,’mm-yy’)||order_seq1.nextval,sysdate)

10. Change the interval 3 to1 of the sequence order_seq.

Ans:-alter sequence order_seq1 increment by 1;

Comments

Post a Comment

Popular posts from this blog

SQL QUERY FOR PRIMARY AND FOREIGN KEY

Practical-3 1. Create table Account_key Column name Data Type Size Attributes Acc_no Varchar2 5 Primary key / first letter must start with ‘A’ Name Varchar2 30 NOT NULL City Varchar2 20 NOT NULL Balance Number 10,2 Balance >=500 Loan_taken Varchar2 3 Values(‘NO’,’YES’) ANS . create table account_key(acc_no varchar2(5) primary key check(acc_no like 'A%'),name varchar2(30) not null,city varchar2(20) not null,balance number(10,2) check(balance>=500),loan_taken varchar2(3) check(loan_taken='yes' or loan_taken='no')); acc_no Name City Balance loan_taken A001 Patel Jigar Mehsana 50000 YES A002 Patel Ramesh Mehsana 50000 YES A003 Dave Hardik Ahmeda...
Read more

SQL QUERY

                                Prac tical-2 1. Retrieve specified information for the account holder who are not in ‘Ahmedabad’. Ans: select * from account where city not in ‘Ahemdabad’; 2. Retrieve specified information for the account holder who are not in ‘Ahmedabad or ‘Vadodara’. Ans: select * from account where city not in('Ahemadabad', 'Vadodara'); 3. Retrieve those records of Account holder whose balance between is 50000 and 100000. Ans: select * from account where balance between 50000 and   1000000; .4. Retrieve those records of Account holder whose balance not between is 50000 and 100000. Ans: select * from account where balance not between 50000 and 100000; 5. Display only those records whose amount is 5000, 25000, 30000. Ans: select * from Transaction where amount in (5000, 25...
Read more

SQL QUERY practical-5

                                     PRACTICAL-5 CREATE TABLE SALESMEN(SNUM VARCHAR2(6) PRIMARY KEY CHECK(SNUM LIKE 'S%'), SNAME VARCHAR2(20) NOT NULL, CITY VARCHAR2(15), COMM NUMBER(5,2)); INSERT INTO SALESMEN VALUES('S1001','PIYUSH','LONDON',0.12); INSERT INTO SALESMEN VALUES('S1002','NIRAJ','SAN JOSE',0.13); INSERT INTO SALESMEN VALUES('S1003','MITI','LONDON',0.11); INSERT INTO SALESMEN VALUES('S1004','RAJESH','BARCELONA',0.15); INSERT INTO SALESMEN VALUES('S1005','HARESH','NEW YORK',0.10); INSERT INTO SALESMEN VALUES('S1006','RAM','BOMBAY',0.10); INSERT INTO SALESMEN VALUES('S1007','NEHAL','DELHI',0.09); CREATE TABLE CUSTOMER(CNUM VARCHAR2(6) PRIMARY KEY CHECK(CNUM LIKE 'C%'), CNAME VARCHAR2(20) NOT NULL, CITY VARCHAR2(15), RATING NUMBER(5), SNUM VARCHAR2(6)); INSERT INTO CUSTOMER VALUES(...
Read more

Practicals of RollBack, Commit and Cursor programs

Image
                                                                 Practical-10 AIM :- RollBack   , Commit and Cursor bases Programs. 1. Analyze output of following scripts: A.      Begin                               Insert into new_salary values ('6', '6000');                               Update new_salary set salary=4000 where id=4;    ...
Read more

Programs Based on Exception Handling and Trigger.

                                                       PRACTICAL-11 Aim :-   Programs Based on Exception Handling and Trigger. 1. Table: STUDENT (sr_no, name, address). Write a pl/sql code to retrieve the data from                student table whose sr.no is 02ce33. if     data not found then show the message “data not                found “  (Use EXCEPTION in pl/sql code). Ans :- declare no int; nm varchar2(20); begin no:=&no; select NAME into nm from student where ROLLNO=no; dbms_output.put_line('Name of student is '||nm); exception when NO_DATA_FOUND then dbms_output.put_line('No d...
Read more