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
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.