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> SELECT * FROM student;
NO NAME MARKS
--- ------ --------
1 Sudha 100
2 Saketh 200
1 Jagan 300
2 Naren 400
3 Ramesh
4 Madhu
5 Visu
6 Rattu
SQL> SELECT no, name, marks FROM student;
NO NAME MARKS
--- ------ --------
1 Sudha 100
2 Saketh 200
1 Jagan 300
2 Naren 400
3 Ramesh
4 Madhu
5 Visu
6 Rattu
SQL> SELECT no, name FROM student;
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> 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
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> SELECT * FROM student WHERE no = 2 AND marks >= 200;
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> SELECT * FROM student WHERE no = 2 OR marks >= 200;
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> SELECT * FROM student WHERE marks BETWEEN 200 AND 400;
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> SELECT * FROM student WHERE marks NOT BETWEEN 200 AND 400;
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> 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
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> SELECT * FROM student WHERE no NOT IN (1, 2, 3);
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> SELECT * FROM student WHERE marks IS NULL;
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> SELECT * FROM student WHERE marks IS NOT NULL;
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> 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 ( _ ).
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> 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
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