CONDITIONAL SELECTIONS AND OPERATORS

We have two clauses used in this

Ø  Where

Ø  Order by

USING WHERE

Syntax:

select * from <table_name> where <condition>;

the following are the different types of operators used in where clause.

v  Arithmetic operators

v  Comparison operators

v  Logical operators

v Arithmetic operators     -- highest precedence

+, -, *, /

v  Comparison operators

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

Ø  between, not between

Ø  in, not in

Ø  null, not null

Ø  like

v  Logical operators

Ø  And

Ø Or                              -- lowest precedence

Ø  not

a)  USING =, >, <, >=, <=, !=, <>

Ex:

SQL> select * from student where no = 2;

 NO NAME MARKS --- ------- --------- 2 Saketh 200 2 Naren 400

SQL> select * from student where no < 2;

 NO NAME MARKS --- ------- ---------- 1 Sudha 100 1 Jagan 300

SQL> select * from student where no > 2;

NO NAME              MARKS

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

3     Ramesh

5     Visu

6     Rattu

SQL> select * from student where no <= 2;

 NO NAME MARKS --- ------- ---------- 1 Sudha 100 2 Saketh 200 1 Jagan 300 2 Naren 400

SQL> select * from student where no >= 2;

 NO NAME MARKS --- ------- --------- 2 Saketh 200

 2  Naren 400

3     Ramesh

5     Visu

6     Rattu

SQL> select * from student where no != 2;

 NO NAME MARKS --- ------- ---------- 1 Sudha 100 1 Jagan 300

3     Ramesh

5     Visu

6     Rattu

SQL> select * from student where no <> 2;

 NO NAME MARKS --- ------- ---------- 1 Sudha 100 1 Jagan 300

3     Ramesh

5     Visu

6     Rattu

b) USING AND

This will gives the output when all the conditions become true.

Syntax:

select * from <table_name> where <condition1> and <condition2> and .. <conditionn>;

Ex:

SQL> select * from student where no = 2 and marks >= 200;

 NO NAME MARKS --- ------- -------- 2 Saketh 200 2 Naren 400

c) USING OR

This will gives the output when either of the conditions become true.

Syntax:

select * from <table_name> where <condition1> and <condition2> or .. <conditionn>;

Ex:

SQL> select * from student where no = 2 or marks >= 200;

 NO NAME MARKS --- ------- --------- 2 Saketh 200 1 Jagan 300 2 Naren 400

d) USING BETWEEN

This will gives the output based on the column and its lower bound, upperbound.

Syntax:

select * from <table_name> where <col> between <lower bound> and <upper bound>;

Ex:

SQL> select * from student where marks between 200 and 400;

 NO NAME MARKS --- ------- --------- 2 Saketh 200 1 Jagan 300 2 Naren 400

e) USING NOT BETWEEN

This will gives the output based on the column which values are not in its lower bound, upperbound.

Syntax:

select * from <table_name> where <col> not between <lower bound> and <upper bound>;

Ex:

SQL> select * from student where marks not between 200 and 400;

 NO NAME MARKS --- ------- --------- 1 Sudha 100

f) USING IN

This will gives the output based on the column and its list of values specified.

Syntax:

select * from <table_name> where <col> in ( value1, value2, value3 … valuen);

Ex:

SQL> select * from student where no in (1, 2, 3);

NO NAME              MARKS

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

1  Sudha                100

 2 Saketh 200 1 Jagan 300 2 Naren 400 3 Ramesh

g) USING NOT IN

This will gives the output based on the column which values are not in the list of values specified.

Syntax:

select * from <table_name> where <col> not in ( value1, value2, value3 … valuen);

Ex:

SQL> select * from student where no not in (1, 2, 3);

 NO NAME MARKS --- ------- --------- 4 Madhu 5 Visu 6 Rattu

h) USING NULL

This will gives the output based on the null values in the specified column.

Syntax:

select * from <table_name> where <col> is null;

Ex:

SQL> select * from student where marks is null;

 NO NAME MARKS --- ------- --------- 3 Ramesh

5     Visu

6     Rattu

i) USING NOT NULL

This will gives the output based on the not null values in the specified column.

Syntax:

select * from <table_name> where <col> is not null;

Ex:

SQL> select * from student where marks is not null;

 NO NAME MARKS --- ------- --------- 1 Sudha 100 2 Saketh 200 1 Jagan 300 2 Naren 400

j) USING LIKE

This will be used to search through the rows of database column based on the pattern you specify.

Syntax:

select * from <table_name> where <col> like <pattern>;

Ex:

i)  This will give the rows whose marks are 100.

SQL> select * from student where marks like 100;

 NO NAME MARKS --- ------- ---------

 1  Sudha 100

SQL> select * from student where name like 'S%';

 NO NAME MARKS --- ------- --------- 1 Sudha 100 2 Saketh 200

iii)  This will give the rows whose name ends with ‘h’.

SQL> select * from student where name like '%h';

 NO NAME MARKS --- ------- --------- 2 Saketh 200 3 Ramesh

iV) This will give the rows whose name’s second letter start with ‘a’.

SQL> select * from student where name like '_a%';

 NO NAME MARKS --- ------- -------- 2 Saketh 200 1 Jagan 300 2 Naren 400

3     Ramesh

6     Rattu

V)  This will give the rows whose name’s third letter start with ‘d’.

SQL> select * from student where name like '__d%';

 NO NAME MARKS --- ------- --------- 1 Sudha 100 4 Madhu

Vi) This will give the rows whose name’s second letter start with ‘t’ from ending.

SQL> select * from student where name like '%_t%';

 NO NAME MARKS --- ------- --------- 2 Saketh 200 6 Rattu

Vii) This will give the rows whose name’s third letter start with ‘e’ from ending.

SQL> select * from student where name like '%e__%';

 NO NAME MARKS --- ------- --------- 2 Saketh 200 3 Ramesh

Viii) This will give the rows whose name cotains 2 a’s.

SQL> select * from student where name like '%a% a %';

NO NAME              MARKS

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

1  Jagan                300

* You have to specify the patterns in like using underscore ( _ ).

USING ORDER BY

This will be used to ordering the columns data (ascending or descending).

Syntax:

Select * from <table_name> order by <col> desc; By default oracle will use ascending order.

If you want output in descending order you have to use desc keyword after the column.

Ex:

SQL> select * from student order by no;

 NO NAME MARKS --- ------- --------- 1 Sudha 100 1 Jagan 300 2 Saketh 200 2 Naren 400

3     Ramesh

5     Visu

6     Rattu

SQL> select * from student order by no desc;

 NO NAME MARKS --- ------- --------- 6 Rattu 5 Visu 4 Madhu 3 Ramesh 2 Saketh 200 2 Naren 400 1 Sudha 100 1 Jagan 300