1/28/16

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

4     Madhu

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

4     Madhu

5     Visu

6     Rattu


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


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

3     Ramesh

4     Madhu

5     Visu

6     Rattu


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


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

3     Ramesh

4     Madhu

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






4     Madhu

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


ii) This will give the rows whose name start with ‘S’.


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

4     Madhu

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

4     Madhu

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

0 comments:

Post a Comment