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
ReplyDeleteOracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check https://www.oracleappstechnical.com for Never Before Offers and Discount Coupon Codes.
ReplyDelete