Basic Select Operations

1) Arithmetic Operators

" + ", " - ", " * " , " / "

2) Relational Operators

>, < , = , >=, <=, !=, <>

3) Logical Operator

and , not , or

4) Special Operators

in, not in , between, not between, is null, is not null, like , not like

Functions

1) Arithmetic

2) Character

3) Date function

4) Group Functions

5) General Functions

Arithmetic

1) abs(n)

2) power(m,n)

3) sqrt(n)

4) mod(m,n)

5) ln(n)

6) log (m,n)

7) sin(n),cos(n)

8) exp(n)

9) round(m,n) nearest rounding figure

10) trunc(m,n) exact figure

11) ceil(n)

12) floor(n)

13) sign(n)

General

1) initcap(s)

2) uppoer(s)

3) lower(s)

4) length(s)

5) reverse(s)

6) ascii(c)

7) chr(n)

8) cpad(s,n,c)

9) rpad(s,n,c)

10) ltrim(s,c)

11) rtrum(s,c)

12) substring(s,m,n)

13) instr(s,c,m,n)

14) concat(s1,s2)

15) replace(s,c1,c2)

Character functions

select lpad('Rahul',10,'*') from dual; => *****Rahul

select lpad('Rahul,10,'?') from dual => ?????Rahul

select rpad('Rahul',10,'#') from dual => Rahul####

select ltrim(' rahul') from dual => rahul;

select rtrim('rahul ') fromdual => rahul;

select rtrim('ramsss,'s') from dual => ram;

select substr('ramkumar',1,3) from dual => ram;

select instr('ANAND','A',1,2) from dual => 3, gives the position of A starting from 1st occurrence.

select concat('oracle','10g') from dual; => oracle10g

select 'oracle' || '10g' from dual; => oracle10g

select replace('jack and jue', 'j','bl') => black and blue;