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 Prasad 200
2 Lucky 400
SQL> select * from student where no < 2;
NO NAME MARKS
---
------- ----------
1 Rama 100
1 RESHWANTH 300
SQL> select * from student where no > 2;
NO
NAME MARKS
---
------- ----------
3 Ramesh
4 Madhu
5 Mahesh
6 Ravi
SQL> select * from student where no <= 2;
NO
NAME MARKS
---
------- ----------
1 Rama 100
2 Prasad 200
1 RESHWANTH 300
2 Lucky 400
SQL> select * from student where no >= 2;
NO
NAME MARKS
---
------- ---------
2 Prasad 200
2 Lucky 400
3 Ramesh
4 Madhu
5 Mahesh
6 Ravi
SQL> select * from student where no != 2;
NO
NAME MARKS
---
------- ----------
1 Rama 100
1 RESHWANTH 300
3 Ramesh
4 Madhu
5 Mahesh
6 Ravi
SQL> select * from student where no <> 2;
NO
NAME MARKS
---
------- ----------
1 Rama 100
1 RESHWANTH 300
3 Ramesh
4 Madhu
5 Mahesh
6 Ravi
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 Prasad 200
2 Lucky
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 Prasad 200
1 RESHWANTH 300
2 Lucky 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 Prasad 200
1 RESHWANTH 300
2 Lucky 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 Rama 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 Rama 100
2 Prasad 200
1 RESHWANTH 300
2 Lucky 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 Mahesh
6 Ravi
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 Mahesh
6 Ravi
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 Rama
100
2 Prasad 200
1 RESHWANTH 300
2 Lucky 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 Rama 100
ii) This will give the rows whose
name start with ‘S’.
SQL> select * from
student where name like 'S%';
NO
NAME MARKS
---
------- ---------
1 Rama 100
2 Prasad 200
iii) This will give the rows whose name ends with ‘h’.
SQL> select * from
student where name like '%h';
NO NAME MARKS
---
------- ---------
2 Prasad 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 Prasad 200
1 RESHWANTH 300
2 Lucky 400
3 Ramesh
4 Madhu
6 Ravi
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 Rama 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 Prasad 200
6 Ravi
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 Prasad 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 RESHWANTH 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 Rama 100
1 RESHWANTH 300
2 Prasad 200
2 Lucky 400
3 Ramesh
4 Madhu
5 Mahesh
6 Ravi
SQL> select * from student order by no desc;
NO NAME MARKS
---
------- ---------
6 Ravi
5 Mahesh
4 Madhu
3 Ramesh
2 Prasad 200
2 Lucky 400
1 Rama 100
1 RESHWANTH 300
No comments:
Post a Comment