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
Arithmetic
operators --
highest precedence
+,
-, *, /
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
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
No comments:
Post a Comment