Using Oracle TRIM Function.

Oracle TRIM function are used to remove all specific characters either from the beginning, ending or both side of strings.

Note :- trim set should have only one character.

Syntax :
----------
TRIM( [ [ LEADING | TRAILING | BOTH ] trim_character FROM ] string )

LEADING :- Remove trim_character from the front of string.

TRAILING :- Remove trim_character from the end of string.

BOTH :- Remove trim_character from the both (start and end) side of string.

SQL> SELECT TRIM('K' FROM 'KINKGK') RESULT FROM DUAL;

RESU
----
INKG

SQL> SELECT TRIM( LEADING 'K' FROM 'KINKGK') RESULT FROM DUAL;

RESUL
-----
INKGK

SQL> SELECT TRIM( BOTH 'K' FROM 'KINKGK') RESULT FROM DUAL;

RESU
----
INKG

SQL> SELECT TRIM( TRAILING 'K' FROM 'KINKGK') RESULT FROM DUAL;

RESUL
-----
KINKG

SQL>

Shorting Rows using ORDER BY Clause.

ORDER BY Clause are used to short retrieved data. The default shorting is ascending order. If you want to short data in descending order you have to write ORDER BY DESC.

1. 

SQL> select last_name, salary, department_id, commission_pct
  2  from employees
  3  where last_name like 'K%'
  4  order by salary;

LAST_NAME                     SALARY DEPARTMENT_ID COMMISSION_PCT
------------------------- ---------- ------------- --------------
Khoo                            3100            30
Kumar                           6100            80             .1
Kaufling                        7900            50
King                           10000            80            .35
Kochhar C                      17000            90
King                           24000            90

6 rows selected.

2. 

SQL> select last_name, salary, department_id, commission_pct
  2  from employees
  3  where last_name like 'K%'
  4  order by salary desc;

LAST_NAME                     SALARY DEPARTMENT_ID COMMISSION_PCT
------------------------- ---------- ------------- --------------
King                           24000            90
Kochhar C                      17000            90
King                           10000            80            .35
Kaufling                        7900            50
Kumar                           6100            80             .1
Khoo                            3100            30

6 rows selected.

3.

SQL> select last_name, salary, department_id, commission_pct
  2  from employees
  3  where last_name like 'K%'
  4  order by 2;

LAST_NAME                     SALARY DEPARTMENT_ID COMMISSION_PCT
------------------------- ---------- ------------- --------------
Khoo                            3100            30
Kumar                           6100            80             .1
Kaufling                        7900            50
King                           10000            80            .35
Kochhar C                      17000            90
King                           24000            90

6 rows selected.

4.

SQL> select last_name, salary, department_id, commission_pct
  2  from employees
  3  where last_name like 'K%'
  4  order by 2 desc;

LAST_NAME                     SALARY DEPARTMENT_ID COMMISSION_PCT
------------------------- ---------- ------------- --------------
King                           24000            90
Kochhar C                      17000            90
King                           10000            80            .35
Kaufling                        7900            50
Kumar                           6100            80             .1
Khoo                            3100            30

6 rows selected.

SQL>

1 and 3 number query working similar. 2 and 4 number query also working similar.

Using Comparison Condition to filter rows.

Oracle provides the following comparison operator to filter rows.

Operator	Operation
=	Equal
>	Greater Than
<	Less than
>=	Greater Than or Equal
<=	Less than or Equal
BETWEEN (Lowest Value)  AND (Highest Value)	Between Two Value
IN	List of value
LIKE	Match pattern
IS NULL	Value is null
IS NOT NULL	Value is not null.

----- This query shows data from employees table those employees department id is 90.

SQL> select last_name, salary, department_id

  2  from employees

  3  where department_id=90;

LAST_NAME                     SALARY DEPARTMENT_ID

------------------------- ---------- -------------

King                           24000            90

Kochhar C                      17000            90

De Haan                        17000            90

SQL>

----- 

SQL> select last_name, salary, department_id

  2  from employees

  3  where salary > 20000;

LAST_NAME                     SALARY DEPARTMENT_ID

------------------------- ---------- -------------

King                           24000            90

SQL>

----- 

SQL> select last_name, salary, department_id

  2  from employees

  3  where salary >=24000;

LAST_NAME                     SALARY DEPARTMENT_ID

------------------------- ---------- -------------

King                           24000            90

SQL>

-----

SQL> select last_name, salary, department_id

  2  from employees

  3  where salary between 17000 and 20000;

LAST_NAME                     SALARY DEPARTMENT_ID

------------------------- ---------- -------------

Kochhar C                      17000            90

De Haan                        17000            90

SQL>

-----

SQL> select last_name, salary, department_id, commission_pct

  2  from employees

  3  where department_id = 90

  4  and commission_pct is null;

LAST_NAME                     SALARY DEPARTMENT_ID COMMISSION_PCT

------------------------- ---------- ------------- --------------

King                           24000            90

Kochhar C                      17000            90

De Haan                        17000            90

SQL>

----- 

SQL> select last_name, salary, department_id, commission_pct

  2  from employees

  3  where department_id in (80,90);

LAST_NAME                     SALARY DEPARTMENT_ID COMMISSION_PCT

------------------------- ---------- ------------- --------------

King                           24000            90

Kochhar C                      17000            90

De Haan                        17000            90

Russell                        14000            80             .4

Partners                       13500            80             .3

Errazuriz                      12000            80             .3

------ This query show data from employees those last name start with K.

SQL> select last_name, salary, department_id, commission_pct

  2  from employees

  3  where last_name like 'K%';

LAST_NAME                     SALARY DEPARTMENT_ID COMMISSION_PCT

------------------------- ---------- ------------- --------------

Kaufling                        7900            50

Khoo                            3100            30

King                           10000            80            .35

King                           24000            90

Kochhar C                      17000            90

Kumar                           6100            80             .1

6 rows selected.