## Tuesday, 29 March 2016

### 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.

Arithmetic operators
Comparison operators
Logical operators

+, -, *, /

## Comparison operators

=, !=, >, <, >=, <=, <>
Between, not Between
in, not in
null, not null
like

## 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
---  -------           ---------
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

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
3 Ramesh
2 Saketh            200
2 Naren             400
1 Sudha             100

1 Jagan             300