SQL

Comparison and Logical Condition in SQL

Comparison Condition:-

Comparison conditions are used in conditions that compare one expression to another value or expression. These operator are used in WHERE condition.

Comparison Operator:-

 

1. Equal to (=):-

SELECT *

FROM EMPLOYEES

WHERE EMPLOYEE_ID = 100;

 

2. GREATER THAN (>):-

SELECT *

FROM EMPLOYEES

WHERE SALARY > 4000;

 

3. LESS THAN (<):-

SELECT *

FROM EMPLOYEES

WHERE SALARY < 5000;

 

4. GREATER THAT OR EQUAL TO (>=)

SELECT *

FROM EMPLOYEES

WHERE SALARY >= 4000;

 

5. LESS THAN OR EQUAL TO (<=):-

SELECT *

FROM EMPLOYEES

WHERE SALARY <= 5000;

 

6. NOT EQUAL TO (<>):-

SELECT *

FROM EMPLOYEES

WHERE DEPARTMENT_ID <> 50;

 

 

Others Comparison Operator:-

1. BETWEEN .. AND ..:- Between two value(include)

SELECT *

FROM EMPLOYEES

WHERE DEPARTMENT_ID BETWEEN (200 AND 300);

 

2. IN:- Match any of the list of values

SELECT *

FROM EMPLOYEES

WHERE EMPLOYEE_ID IN(101, 110, 200, 220, 109);

3. LIKE:- Match a character pattern.

SELECT *

FROM EMPLOYEES

WHERE LAST_NAME LIKE ‘%A%’;

 

4. IS NULL:- Is a null value.

SELECT *

FROM EMPLOYEES

WHERE COMMISSION_PCT IS NULL;

 

 

Logical Condition:-

1. AND:- If both compare condition are TRUE then it return TRUE.

SELECT *

FROM EMPLOYEES

WHERE SALARY < 20000

AND DEPARTMENT_ID = 20;

2. OR:- If any one compare condition is TRUE then it return TRUE.

 

SELECT *

FROM EMPLOYEES

WHERE SALARY < 20000

OR DEPARTMENT_ID = 20;

 

3. NOT:- If condition is false then it return TRUE.

SELECT *

FROM EMPLOYEES

WHERE DEPARTMENT_ID NOT IN(10,50);

 

 

 

Advertisements

Sql, PL/SQL Special Query

Ques:-

TEAM RESULT

A        WIN

B        WIN

A        LOSS

A        WIN

B        DRAW

C        WIN

C        LOSS

D        LOSS

E        DRAW

EXPECTED OUTPUT

TEAM  WIN  LOSS DRAW

A       2    1    0

B       1    0    1

C       1    1    0

D       0    1    0

E       0    0    1

ANS:-

select TEAM,SUM(CASE RESULT when ‘WIN’ then 1

else 0 end) WIN

,SUM(CASE RESULT when ‘LOSS’ then 1

else 0 end) LOSS

,SUM(CASE RESULT when ‘DRAW’ then 1

else 0 end) DRAW

,SUM((CASE RESULT when ‘WIN’ then 1 else 0 end)+(CASE RESULT when ‘LOSS’ then 1 else 0 end)+(CASE RESULT when ‘DRAW’ then 1 else 0 end)) TOTAL

from xxc05_test1

GROUP BY TEAM

order by 1

 

 

———————————————————————-

 

Ques:- Display the salary of each and every employee in 1991,1992 and 1993.

(hint: use ‘DECODE’ function)

Zigzag

NAME YEAR ATM_NO

————- ———– —————–

JOHN 1991 1000

JOHN 1992 2000

JOHN 1993 3000

JACK 1991 1500

JACK 1992 1200

JACK 1993 1340

MARY 1991 1250

MARY 1992 2323

MARY 1993 8700

EXPECTED OUTPUT

YEAR JOHN JACK MARY

———— ———- ———- ———-

1991 1000 1500 1250

1992 2000 1200 2323

1993 3000 1340 8700

Ans:-

SELECT YEAR,SUM(DECODE(NAME,’JOHN’,ATM_NO,0)) JOHN

,SUM(DECODE(NAME,’JACK’,ATM_NO,0)) JACK

,SUM(DECODE(NAME,’MERRY’,ATM_NO,0)) MERRY

FROM XXC05_TEST2

GROUP BY YEAR

ORDER BY 1

———————————————————————-

Ques:-

Write a Query to display each letter of the world “Happy” in a separate row.

(Hint: use ‘SUBSTRING and CONNECT BY LEVEL’)

Answer table should be

Output

result

H

A

P

P

Y

Ans:-

select substr(‘HAPPY’,LEVEL,1) FROM DUAL

CONNECT BY LEVEL<=LENGTH(‘HAPPY’);

———————————————————————-

Ques:-

My table have some records

NAME    ACT_NO

SAJAL    1234567890

AMAL    2345678901

NAVAL    9876542211

but i want to print Like thet

******7890

******8901

******2211

Ans:-

SELECT LPAD(SUBSTR(ACT_NO,LENGTH(ACT_NO)-3),LENGTH(ACT_NO),’*’) FROM DUAL

———————————————————————-

Ques:- Convert number to cahr

Ans:-

select to_char(to_date(999999,’j’),’jsp’) from dual;

range is 1 to 5373484

———————————————————————-

Ques:-

Find Nth Highest Salary

Ans:-

select * from

EMPloyees x

where &no=(

select count(*)

from EMPloyees y

where y.employee_id>=x.employee_id

)

 

 

———————————————————————-

Ques:- Count the no of Male and Female Candidate in a table.

Ans:-

select SUM(case sex when ‘MALE’ then 1

end )”MALE”

,SUM(case sex when ‘FEMALE’ then 1

end)”FEMALE”

from XXC05_TEST3;

———————————————————————-

Ques:- how to insert a column in a table which is already existing in other table

First you add column name in second table then

Run that query.

DECLARE

BEGIN

FOR I IN (SELECT ID,DEPARTMENT_NAME FROM XXC05_T1)

LOOP

UPDATE XXC05_T2 SET NAME = I.DEPARTMENT_NAME

WHERE ID = I.ID;

END LOOP;

COMMIT;

END;

———————————————————————-

QUES:- HOW TO PRINT MAX,MIN,AVG,SUM OF SALARY AND PRINT ALL COLUMN NAME.

ANS:-

SELECT E.*,MAX(SALARY) OVER() MAX_SALARY ,MIN(SALARY) OVER() MIN_SALARY,ROUND(AVG(SALARY) OVER()) AVG_SALARY,SUM(SALARY) OVER() SUM_SALARY FROM EMPLOYEES E

or

SELECT

e.*,

(SELECT MAX(salary)

FROM Employees) MAXSAL,

(SELECT SUM(salary)

FROM Employees) SUMSAL

FROM Employees e;

———————————————————————-

Ques:-

HI FRND HOW TO REPLACE

col

a*b@c#d

o/p

a,b,c,d

ANS:-

select ‘a*b@c#d’,regexp_replace(‘a*b@c#d’, ‘[^0-9a-zA-Z]’, ‘,’ ) from dual;

or

SELECT translate(‘a*b@c#d’,’*@#’,’,,,’) FROM dual

or

select replace (replace (replace (‘a*b@c#d’,’*’,’,’),’@’,’,’),’#’,’,’) N from dual

———————————————————————-

Ques:- Without use rowid delete duplicate record.

ANS:-

select *

from (select e.empno, rank() over(order by e.deptno desc) rk

from scott.emp e)

———————————————————————-

Ques:- How to find greated, smallest number

Table is

c1 c2  c3

1    4    10

2    5    15

10    20    28

Ouput is 28,1

ANS:-

SELECT MAX(GREATEST(C1,C2,C3)) FROM T1;

Or

SELECT GREATEST(MAX(C1),MAX(C2), MAX(C3)) FROM T1

SELECT LEAST(MIN(C1),MIN(C2),MIN(C3)) FROM  T1;

Or

SELECT MIN(LEAST(C1,C2,C3)) FROM T1;

———————————————————————-

QUES:- hi…frd….suppose a number is 2642.how we get output 14 (2+6+4+2) by the use of sql.

ANS:- select sum(s) total from(

select substr(num,level,1) s from(select &g num from dual)

connect by level<=length(num))

 

 

SELECT SUBSTR(7654,1,1)+SUBSTR(7654,2,1)+SUBSTR(7654,3,1)+SUBSTR(7654,4,1) FROM DUAL

 

———————————————————————-

 

QUES:- HOW TO CHACK STRING IS PALINDROM OR NOT

DECLARE

LEN NUMBER;

PALSTR VARCHAR2(20) := ‘&PALSTR’;

CHKSTR VARCHAR2(20);

BEGIN

LEN := LENGTH(PALSTR);

FOR I IN REVERSE 1..LEN LOOP

CHKSTR := CHKSTR||SUBSTR(PALSTR,I,1);

END LOOP;

IF CHKSTR = PALSTR THEN

DBMS_OUTPUT.PUT_LINE(PALSTR||’ IS A PALINDROME’);

ELSE

DBMS_OUTPUT.PUT_LINE(PALSTR||’ IS NOT A PALINDROME’);

END IF;

END;

 

 

———————————————————————-

 

DIFFERENCE BETWEEN DATASET AND DATATABLE

DataSet:- DataSet is the Collection of DataTables

DataSet can Fetch multiple TablesRows at a time

In DataSet DataTable objects can be related to each other like(primary key, forign key, unique key etc)

 

DataTable:- DataTable is single datbase table

DataTable Fetch data from only one table.

As DataTable is a single database table, so there is no Data relation object in it.

 

———————————————————————-

QUES:-  How to find Those record in which and special symbol.

SELECT * FROM T11

WHERE REGEXP_LIKE(NAME,’%’);

OR

SELECT * FROM T11

WHERE NAME LIKE ‘%\%%’ ESCAPE ‘\’;

———————————————————————-

QUES:- Given String is   11223344556677 or give it at run time

output is:- 11**********77

Ans:-

SELECT RPAD(SUBSTR(&A,1,2),LENGTH(&&A)-2,’*’)||SUBSTR(&&A,-2,2) FROM DUAL

———————————————————————-

Ques:- In table data is

1  SAJAL     MALE

2  AMAL        MALE

3  MEENA    FEMALE

4  SONAM    FEMALE

5  RAHUL    MALE

6  SONIA    FEMALE

7  NANCY    FEMALE

8  NAVAL    MALE

My requirement is print record alternative of Gender

1  SAJAL     MALE

3  MEENA    FEMALE

2  AMAL        MALE

4  SONAM    FEMALE

5  RAHUL    MALE

6  SONIA    FEMALE

8  NAVAL    MALE

7  NANCY    FEMALE

ANS:-

select ROW_NUM,id,name,gender  from (

select (rownum*2) ROW_NUM,id,name,gender from XXC05_GENDER

where gender = ‘M’

union

select (rownum*2-1) ROW_NUM,id,name,gender from XXC05_GENDER

where gender = ‘F’

)

order by ROW_NUM

———————————————————————-

QUES:- Print 1 to 100 number in sql

ANS:-

Select Rownum

From dual

Connect By Rownum <= 100

OR

SELECT LEVEL

FROM DUAL

CONNECT BY LEVEL <= 100;

Pattern Printing in Oracle SQL

1 ————

*
**
***
****
*****

DECLARE
P_STAR VARCHAR2(5);
BEGIN
FOR I IN 1..5
LOOP
P_STAR := P_STAR||’*’;
DBMS_OUTPUT.PUT_LINE(P_STAR);
END LOOP;
END;

OR

DECLARE
STAR VARCHAR2(5):=’*’;
P_STAR VARCHAR2(5);
BEGIN
FOR I IN 1..5
LOOP
P_STAR := LPAD(STAR,I,STAR);
DBMS_OUTPUT.PUT_LINE(P_STAR);
END LOOP;
END;

2 ————————————–

&
&*
&*&
&*&*
&*&*&

DECLARE
P_STAR VARCHAR2(5);
BEGIN
FOR I IN 1..5
LOOP
IF MOD(I,2)=0
THEN
P_STAR := P_STAR||’*’;
DBMS_OUTPUT.PUT_LINE(P_STAR);
ELSE
P_STAR:=   P_STAR||’&’;
DBMS_OUTPUT.PUT_LINE(P_STAR);
END IF;
END LOOP;
END;

3 —————————————

****
****
***
**
*

DECLARE
P_STAR VARCHAR2(5):= ‘*****’;
S_STAR VARCHAR2(5);
BEGIN
FOR I IN 1..5
LOOP
S_STAR := SUBSTR(P_STAR,I);
DBMS_OUTPUT.PUT_LINE(S_STAR);
END LOOP;
END;

4 ———————————

*
**
***
****
*****
****
***
**
*

DECLARE
V_STAR VARCHAR2(1):= ‘*’;
P_STAR VARCHAR2(5);
S_STAR VARCHAR2(5);
BEGIN
FOR I IN 1..5
LOOP
P_STAR:= LPAD(V_STAR,I,V_STAR);
DBMS_OUTPUT.PUT_LINE(P_STAR);
END LOOP;
FOR I IN 1..5
LOOP
S_STAR := SUBSTR(P_STAR,I+1);
DBMS_OUTPUT.PUT_LINE(S_STAR);
END LOOP;
END;

5 —————————————————-

*****
****
***
**
*
*
**
***
****
*****

DECLARE
V_STAR VARCHAR2(5):= ‘*****’;
P_STAR VARCHAR2(5);
S_STAR VARCHAR2(5);
BEGIN
FOR I IN 1..5
LOOP
P_STAR := SUBSTR(V_STAR,I);
DBMS_OUTPUT.PUT_LINE(P_STAR);
END LOOP;
FOR I IN 1..5
LOOP
S_STAR := LPAD(P_STAR,I,P_STAR);
DBMS_OUTPUT.PUT_LINE(S_STAR);
END LOOP;
END;

6 ——————————————

*****
****
***
**
*
**
***
****
*****

DECLARE
V_STAR VARCHAR2(5):= ‘*****’;
P_STAR VARCHAR2(5);
S_STAR VARCHAR2(5);
BEGIN
FOR I IN 1..5
LOOP
P_STAR := SUBSTR(V_STAR,I);
DBMS_OUTPUT.PUT_LINE(P_STAR);
END LOOP;
FOR I IN 1..4
LOOP
P_STAR := P_STAR||’*’;
DBMS_OUTPUT.PUT_LINE(P_STAR);
END LOOP;
END;

7 ——————————

*****
****
***
**
*

DECLARE
SPACE VARCHAR2(1):= ‘ ‘;
V_STAR VARCHAR2(5):= ‘*****’;
P_STAR VARCHAR(5);
BEGIN
FOR I IN 1..5
LOOP
P_STAR:= LPAD(SUBSTR(V_STAR,I),5,SPACE);
DBMS_OUTPUT.PUT_LINE(P_STAR);
END LOOP;
END;

8 ——————————
*
**
***
****
*****

DECLARE
STAR VARCHAR2(1):= ‘*’;
SPACE VARCHAR2(1):= ‘ ‘;
P_NO VARCHAR2(5);
P_STAR VARCHAR2(5);
BEGIN
FOR I IN 1..5
LOOP
P_STAR:= P_STAR||STAR;
FOR J IN 1..5
LOOP
P_NO:= LPAD(P_STAR,5,SPACE);
END LOOP;
DBMS_OUTPUT.PUT_LINE(P_NO);
END LOOP;
END;

9 ——————————
*****
****
***
**
*
*
**
***
****
*****

DECLARE
SPACE VARCHAR2(1):= ‘ ‘;
V_STAR VARCHAR2(5):= ‘*****’;
P_STAR VARCHAR(5);
S_STAR VARCHAR(5);
BEGIN
FOR I IN 1..5
LOOP
P_STAR:= LPAD(SUBSTR(V_STAR,I),5,SPACE);
DBMS_OUTPUT.PUT_LINE(P_STAR);
END LOOP;
FOR J IN 1..5
LOOP
S_STAR:= RPAD(SUBSTR(P_STAR,J),5,’*’);
DBMS_OUTPUT.PUT_LINE(S_STAR);
END LOOP;
END;

10 ——————————

*****
****
***
**
*
**
***
****
*****

DECLARE
SPACE VARCHAR2(1):= ‘ ‘;
V_STAR VARCHAR2(5):= ‘*****’;
P_STAR VARCHAR(5);
S_STAR VARCHAR(5);
BEGIN
FOR I IN 1..5
LOOP
P_STAR:= LPAD(SUBSTR(V_STAR,I),5,SPACE);
DBMS_OUTPUT.PUT_LINE(P_STAR);
END LOOP;
FOR J IN 1..5
LOOP
S_STAR:= RPAD(SUBSTR(P_STAR,J+1),5,’*’);
DBMS_OUTPUT.PUT_LINE(S_STAR);
END LOOP;
END;

11 ——————————

**** ****
***   ***
**     **
*       *

DECLARE
STAR VARCHAR2(1):= ‘*’;
SPACE VARCHAR2(1):= ‘ ‘;
P_PRINT VARCHAR2(9);
S_SPACE VARCHAR2(9);
P_SPACE VARCHAR2(9);
BEGIN
FOR I IN 1..4
LOOP
P_SPACE:= S_SPACE;
S_SPACE:= S_SPACE||SPACE;
P_PRINT:= RPAD(LPAD(S_SPACE,5,STAR)||P_SPACE,9,STAR);
DBMS_OUTPUT.PUT_LINE(P_PRINT);
END LOOP;
END;

12 ——————————

*        *
**      **
***    ***
****  ****
**********

13 ——————————

****  ****
***    ***
**      **
*        *
**      **
***    ***
****  ****

DECLARE
STAR VARCHAR2(1):= ‘*’;
SPACE VARCHAR2(1):= ‘ ‘;
P_PRINT VARCHAR2(9);
S_SPACE VARCHAR2(9);
P_SPACE VARCHAR2(9);
BEGIN
FOR I IN 1..4
LOOP
P_SPACE:= S_SPACE;
S_SPACE:= S_SPACE||SPACE;
P_PRINT:= RPAD(LPAD(S_SPACE,5,STAR)||P_SPACE,9,STAR);
DBMS_OUTPUT.PUT_LINE(P_PRINT);
END LOOP;
FOR I IN 1..3
LOOP
S_SPACE:= P_SPACE;
P_SPACE:= SUBSTR(P_SPACE,1,4-(I+1));
P_PRINT:= RPAD(LPAD(S_SPACE,5,STAR)||P_SPACE,9,STAR);
DBMS_OUTPUT.PUT_LINE(P_PRINT);
END LOOP;
END;

14 ———————————-

*
***
*****
*******
*********

DECLARE
STAR VARCHAR2(1):= ‘*’;
SPACE VARCHAR2(1):= ‘ ‘;
P_PRINT VARCHAR2(9);
S_STAR VARCHAR2(9);
P_STAR VARCHAR2(9);
BEGIN
FOR I IN 1..5
LOOP
P_STAR:= S_STAR;
S_STAR:= S_STAR||STAR;
P_PRINT:= RPAD(LPAD(S_STAR,5,SPACE)||P_STAR,9,SPACE);
DBMS_OUTPUT.PUT_LINE(P_PRINT);
END LOOP;
END;

 

 

How to print calendar from SQL Query

SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,”Sun”, “Mon”, “Tue”,
“Wed”, “Thu”, “Fri”, “Sat”
FROM (SELECT TO_CHAR(dt,’fmMonthfm YYYY’) MONTH,TO_CHAR(dt+1,’iw’) week,
MAX(DECODE(TO_CHAR(dt,’d’),’1′,LPAD(TO_CHAR(dt,’fmdd’),2))) “Sun”,
MAX(DECODE(TO_CHAR(dt,’d’),’2′,LPAD(TO_CHAR(dt,’fmdd’),2))) “Mon”,
MAX(DECODE(TO_CHAR(dt,’d’),’3′,LPAD(TO_CHAR(dt,’fmdd’),2))) “Tue”,
MAX(DECODE(TO_CHAR(dt,’d’),’4′,LPAD(TO_CHAR(dt,’fmdd’),2))) “Wed”,
MAX(DECODE(TO_CHAR(dt,’d’),’5′,LPAD(TO_CHAR(dt,’fmdd’),2))) “Thu”,
MAX(DECODE(TO_CHAR(dt,’d’),’6′,LPAD(TO_CHAR(dt,’fmdd’),2))) “Fri”,
MAX(DECODE(TO_CHAR(dt,’d’),’7′,LPAD(TO_CHAR(dt,’fmdd’),2))) “Sat”
FROM ( SELECT TRUNC(SYSDATE,’y’)-1+ROWNUM dt
FROM all_objects
WHERE ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) – TRUNC(SYSDATE,'y'))
GROUP BY TO_CHAR(dt,'fmMonthfm YYYY'), TO_CHAR( dt+1, 'iw' ))
ORDER BY TO_DATE( MONTH, 'Month YYYY' ), TO_NUMBER(week);

Multiple Insert Statement in Oracle SQL

Multiple Insert Statement:-
In multiple INSERT statement, you insert data into more then one tables as a part of single DML statement.

Type of Multiple Insert Statement:

1.  Unconditional INSERT
2.  Conditional INSERT
3.  Conditional FIRST INSERT
4.  Pivoting INSERT

Syntax:-

INSERT [ALL] [CONDITION_INSERT_CLAUSE]
[insert_into_clause values_clause ] (subquery);

=> Conditional_insert_clause

[ALL] [FIRST]
[WHEN condition THEN] [insert_into_clause values_clause]
[else] [insert_into_clause values_clause];

Unconditional INSERT ALL:-
This INSERT statement is referred to as as unconditional INSERT because no further restriction is applied to the rows that are retrieved by SELECT statement.

Example:-

INSERT ALL
INTO XXC05_EMP1(EMP_ID, L_NAME, SAL)
INTO XXC05_EMP2(EMP_ID, F_NAME, MGR_ID)
SELECT EMPLOYEE_ID EMP_ID,
LAST_NAME L_NAME,
FIRST_NAME F_NAME,
SALARY SAL,
MANAGER_ID MGR_ID
FROM EMPLOYEES;

Note:- Insert data into XXC05_EMP1, XXC05_EMP2 tables by a single SELECT statement.

Conditional INSERT ALL:-
This INSERT statement is referred to as as conditional INSERT because a further restriction is applied to the rows that are retrieved by SELECT statement.

Example:-

INSERT ALL
WHEN EMPLOYEE_ID 200
INTO XXC05_EMP2(EMP_ID, F_NAME, MGR_ID)
SELECT EMPLOYEE_ID EMP_ID,
LAST_NAME L_NAME,
FIRST_NAME F_NAME,
SALARY SAL,
MANAGER_ID MGR_ID
FROM EMPLOYEES;

Conditional FIRST INSERT:-
This INSERT statement is referred to as as conditional FIRST INSERT because a further restriction is applied to the rows that are retrieved by SELECT statement. In that statement first true condition find and insert data in those table, rest of conditions or statement are switched.

Example:-

INSERT FIRST
WHEN EMPLOYEE_ID <= 24000
THEN
INTO employee1(EMPLOYEE_ID, LAST_NAME, SALARY)
WHEN EMPLOYEE_ID = 2388
THEN
INTO employee2(EMPLOYEE_ID, LAST_NAME, SALARY)
WHEN EMPLOYEE_ID =0
THEN
INTO employee4(EMPLOYEE_ID, LAST_NAME, SALARY)
SELECT EMPLOYEE_ID,LAST_NAME,SALARY
FROM EMPLOYEES;

Note:- In above example First condition is true so data insert into only EMPLOYEE1 table.It do not check any other conditions.

Example:-

INSERT FIRST
WHEN EMPLOYEE_ID <= -24000
THEN
INTO employee1(EMPLOYEE_ID, LAST_NAME, SALARY)
WHEN EMPLOYEE_ID = -2388
THEN
INTO employee2(EMPLOYEE_ID, LAST_NAME, SALARY)
WHEN EMPLOYEE_ID =0
THEN
INTO employee4(EMPLOYEE_ID, LAST_NAME, SALARY)
SELECT EMPLOYEE_ID,LAST_NAME,SALARY
FROM EMPLOYEES;

Note:- In above example THIRD condition is true so data insert into only EMPLOYEE3 table. It do not check FORTH condition.

Pivoting INSERT:-
Using pivoting INSERT, convert the set of sales records from nonrelational database table to relational format.

Index and its Type in Oracle with Syntax

Index:-

CREATE INDEX XXC05_INDEX
ON
XXC05_TEST(ID);

Composite Index:- it is also called concatenated Index, is an index on multiple columns in a table.

CREATE INDEX XXC05_INDEX
ON
XXC05_TEST(ID,NAME);

Unique Index:-  Unique indexes guarantee that no two rows of a table have duplicate values in the key column or column.

NonUnique Index:-  NonUnique indexes permit duplicate value in a column of table.

Types of Index:

B-Tree Index:-

These indexes are the standard index type. They are excellent for primary key and highly-selective indexes

By default B-tree Index created when we create index.

Syntax:-    create index XXC05_INDEX on XXC05_TEST(id,name)

Bitmap Index:- The indexed columns have low cardinality, that is, the number of distinct values is small compared to the number of table rows.

Syntax:-    create bitmap index XXC05_INDEX on XXC05_TEST(id,name)

Unique Index:-

create unique index XXC05_INDEX on XXC05_TEST(id);

Function Based Index:-

create unique index XXC05_INDEX on XXC05_TEST (UPPER(id));

** Maxinum no of column use in Index:-    32

Rename Index:-

Alter index XXC05_INDEX
rename to XXC05_INDEX1;

Drop Index:- DROP INDEX INDEX_NAME;

Drop index XXC05_INDEX;

Index in Oracle

Index:-
Index is a schema object. Index is a performance-tuning method. Using Index retrieval of data from database is fast.

Syntax:-
CREATE INDEX index_name
ON table_name(column_name1,column_name2,column_name3,…);

Rename an INDEX:-
Alter INDEX old_index_name
RENAME TO new_index_name;

Drop an INDEX:-
DROP INDEX index_name;

Type of Index:-

1. Normal Index(B-Tree Index)
2. Bitmap Index
3. Function-based Index

Normal Index:-
It is also called B-TREE Index, By Default B-TREE index is created. It manage data in tree form.

Bitmap Index:-
Bitmap index put on that column which contain low cardinality. Low Cardinality means more duplicate data and less unique data.

Function-Based Index:-
The index expression can be an arithmetic expression or an expression that contains a SQL function, PL/SQL function, package function.

Example of Normal Index:-

CREATE INDEX xxc05_vendor_id
ON xxc05_ap_supplier(vendor_id);

Example of Bitmap Index:-

CREATE BITMAP INDEX xxc05_emp_gender
ON xxc05_employees(gender);

Example of Functional Index:-

CREATE INDEX xxc05_vendor_id
ON xxc05_ap_supplier(UPPER(vendor_name));