Operators in oracle
Operators are divided into six types:
1. Arithmetic Operator
2. Relational Operator
3. Logical Operator
4. Range searching operator
5. Set searching Operator
6. Character Operator
Arithmetic Operator
This operator are applied on numeric values stored in tables. They are divided as below:
1. Addition -> +
2. Subtraction -> -
3. Multiplication -> *
4. Division -> /
5. Enclosed operation -> ()
We will use product table to study use of this operator. Selecting product table
Now we will see example of these operators.
Use of + and * operator
Use of - and * operator
Relational Operator
This operators are also referred as comparison operator. It compares your values with the values stored in that column of table. They are divided as below:
1. Equals -> =
2. Not equals -> !=
3. Less than -> <
4. Less than or equal to -> <=
5. Greater than -> >
6. Greater than or equal to -> >=
Now we will see example of these operators.
Use of = operator
Use of > operator
Use of >= operator
Use of <= operator
Logical Operator
This operator works on logic and are referred as logical operators. They are divided as below:
1. AND – This operator is used to combine two or more conditions in WHERE and HAVING clauses. It will returns rows from table if all conditions are true.
2. OR – This operator is used to combine two or more conditions in WHERE and HAVING clauses. It will returns rows from table if any one condition from given condition is true.
3. NOT – This operator is used to combine two or more conditions in WHERE and HAVING clauses. It will returns rows from table that are not satisfying condition or group of condition.
Now we will see example of these operators.
Use of AND operator
Use of OR operator
Use of NOT operator
Range Searching Operator
This operator is used to select data from table in the given range condition. This is divided as below:
1. BETWEEN – This operator is used to select data from table in given upper and lower limit. It is important to note that AND operator is used in middle of upper and lower limit.
Syntax
.....Column name BETWEEN lower limit AND upper limit;
Now we will see example of these operators.
Use of BETWEEN operator
Use of BETWEEN operator
Set Searching Operator
This operator is used to select data from table from given set of values. This is divided as below:
1. IN – This operator is used to select data from given set of values specified in brackets in WHERE clause.
Syntax
.....Column name IN (value 1, value 2, …………, value N);
Now we will see example of these operators.
Use of IN operator
Use of IN operator
Use of IN operator
Character Operator
This operator is used to select data from table by matching character in condition and in data of table.This is divided as below:
1. LIKE – This operator is used to select data as per the pattern specified in WHERE clause. The pattern is designed using % and _ symbol.
Syntax
.....Column name LIKE pattern;
Example
D% - Find out data from table start with D after that if any character then it would not affect output.
%D - Find out data from table ends with D before that if any character then it would not affect output.
%D% - Find out data from table which contains D before and after that if any character then it would not affect output.
_D% - Find out data from table which contains D at second position after that if any character then it would not affect output.
_D_E% - Find out data from table which contains D at second position and E at fourth position after that if any character then it would not affect output.
Now we will see example of these operators.
Use of LIKE operator
Use of LIKE operator
Use of LIKE operator
Use of LIKE operator
Use of LIKE operator
2. || (Concatenation Operator) – This operator is used to combine data from two or more column as per our requirement.
Syntax
.....Column name 1 || Column name 2 || ..... || Column name N .....
Now we will see example of these operators.
Use of || operator
I hope this article will help you