It is used to retrieve the data
for read only purpose
Syntax:
Select * from <table_name>; -- here * indicates all columns
or
Select col1, col2, … coln from <table_name>;
Examples:
SQL>
NO NAME MARKS
--- ------ --------
1 Sudha 100
2 Saketh 200
1 Jagan 300
2 Naren 400
3 Ramesh
4 Madhu
5 Visu
6 Rattu
SQL>
NO NAME MARKS
--- ------ --------
1 Sudha 100
2 Saketh 200
1 Jagan 300
2 Naren 400
3 Ramesh
4 Madhu
5 Visu
6 Rattu
SQL>
NO NAME
--- -------
1 Sudha
2 Saketh
1 Jagan
2 Naren
3 Ramesh
4 Madhu
5 Visu
6 Rattu
We have two clauses used in this
- Where
- Order by
SELECT STATEMENT WITH WHERE CLAUSE |
Syntax:
select * from <table_name> where <condition>;
The following are the different types of operators used in where clause.
Arithmetic
|
Relational
|
Logical
|
Special
|
+
|
=
|
And
|
In,Not In
|
-
|
! = or <>
|
Or
|
Between, Not Between
|
*
|
>
|
Not
|
Like, Not Like
|
/
|
<
|
Is null,Is not null
|
|
>=
|
|||
<=
|
SELECT STATEMENT WITH RELATIONAL OPERATORS |
Example:
SQL>
NO NAME MARKS
--- ------- ---------
2 Saketh 200
2 Naren 400
SQL>
NO NAME MARKS
--- ------- ----------
1 Sudha 100
1 Jagan 300
SQL>
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>
NO NAME MARKS
--- ------- ---------
2 Saketh 200
2 Naren 400
3 Ramesh
4 Madhu
5 Visu
6 Rattu
SQL>
NO NAME MARKS
--- ------- ----------
1 Sudha 100
1 Jagan 300
3 Ramesh
4 Madhu
5 Visu
6 Rattu
SQL>
NO NAME MARKS
--- ------- ----------
1 Sudha 100
1 Jagan 300
3 Ramesh
4 Madhu
5 Visu
6 Rattu
SELECT STATEMENT WITH 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>
NO NAME MARKS
--- ------- --------
2 Saketh 200
2 Naren 400
SELECT STATEMENT WITH 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>
NO NAME MARKS
--- ------- ---------
2 Saketh 200
1 Jagan 300
2 Naren 400
SELECT STATEMENT WITH 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>
NO NAME MARKS
--- ------- ---------
2 Saketh 200
1 Jagan 300
2 Naren 400
SELECT STATEMENT WITH 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>
NO NAME MARKS
--- ------- ---------
1 Sudha 100
SELECT STATEMENT WITH 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>
NO NAME MARKS
--- ------- ---------
1 Sudha 100
2 Saketh 200
1 Jagan 300
2 Naren 400
3 Ramesh
SELECT STATEMENT WITH 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>
NO NAME MARKS
--- ------- ---------
4 Madhu
5 Visu
6 Rattu
SELECT STATEMENT WITH 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>
NO NAME MARKS
--- ------- ---------
3 Ramesh
4 Madhu
5 Visu
6 Rattu
SELECT STATEMENT WITH 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>
NO NAME MARKS
--- ------- ---------
1 Sudha 100
2 Saketh 200
1 Jagan 300
2 Naren 400
SELECT STATEMENT WITH 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>
NO NAME MARKS
--- ------- ---------
1 Sudha 100
ii) This will give the rows whose name start with ‘S’.
SQL>
NO NAME MARKS
--- ------- ---------
1 Sudha 100
2 Saketh 200
iii) This will give the rows whose name ends with ‘h’.
SQL>
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>
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>
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>
NO NAME MARKS
--- ------- ---------
2 Saketh 200
3 Ramesh
Viii) This will give the rows whose name cotains 2 a’s.
SQL>
NO NAME MARKS
--- ------- ----------
1 Jagan 300
* You have to specify the patterns in like using underscore ( _ ).
SELECT STATEMENT WITH 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>
NO NAME MARKS
--- ------- ---------
1 Sudha 100
1 Jagan 300
2 Saketh 200
2 Naren 400
3 Ramesh
4 Madhu
5 Visu
6 Rattu
SQL>
NO NAME MARKS
--- ------- ---------
6 Rattu
5 Visu
4 Madhu
3 Ramesh
2 Saketh 200
2 Naren 400
1 Sudha 100
1 Jagan 300
Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check oracleappstechnical.com
ReplyDelete