Operators In Oracle

What are Operators in Oracle:

The operators are the symbols used to perform operations on data.

Following is the list of Operators in Oracle

1. Arithmetic Operators:

Arithmetic operators are used to perform arithmetic operations.

The + Operator Used to perform addition of two numeric values 

The - operator is used to subtract 1 numeric value from another.

The *  Operator multiplies two numeric values

The / used to divide the numeric value.

The % modulus operator is used to get the remainder of division.

2. Comparison Operator:

The comparison operator is used to make comparisons between two numeric values.

The = equals to operator is used to check one numeric value is equals to another.

The <> or != Not Equals to is used to check both the values are not equals to each other.

The > Greater Than is used to check one numeric value is greater than another.

The < Less Than is used to check one numeric value is less than another value.

The >= Greater Than or Equals To be used to check one value is greater than or equals to another value.

The <= Less Than or Equals To used to check one value is less than or equals to another value.

3. Logical Operators:

Logical operators are used to combine two or more conditions.

AND: Logical AND return TRUE if Both the conditions are TRUE.

OR: Logical OR it returns TRUE if at least one condition is TRUE.

NOT: Logical NOT it reverse the conditions result.

Following are some examples using Logical operators:

Select * from student 

Where rollno > 10 AND CGPA >9;

This query will return all the rows where rollno is greater than 10 and CGPA greater than 9.

Select * from student 

Where rollno=15 OR CGPA>9;

In the above query it will return the rows where at least one condition will satisfies.

Select * from candidate

Where NOT married=1;

Above query will return all the candidates who are not married.

4. || Concatenation Operator:

The || is a concatenation operator used to combine two strings.

For example,

Suppose in your  student table you have First_name and Last_name column and you want to combine these two column as full_name 

Select First_name || ‘ ‘ || Last_name as Full_name

From student;

Here these two names are combined and stored in Full_name.

5. Membership Operators:

IN: It helps to check the value is included in a list of values.

For example,

Select * from student

Where Rollno IN(10, 50, 70);

This query will return all the students whose rollno matches with 10, 50 and 70.

Select * from student

Where Rollno NOT IN(10,50,70);

This query returns all the students whose Rollno does not match with 10, 50 and 70

6. Pattern Matching Operators in Oracle:

These operators are used to match a particular pattern or not match a pattern.

LIKE Operator: 

This operator is used to match a pattern with a particular character.  With the Like keyword some wildcard characters are used to check the pattern.

1. ‘%’ Wirldcard Character:

Select * from student

Where Name Like ‘M%’;

In the above query the Like operator is used with % wirldcard character and it returns all the names which start from M alphabet.

2. _ Underscore Wirldcard Character:

Select * from student 

Where Name Like ‘_ _sha’;

The Like Operator is used to search the name of students whose length will be 5 and last 3 alphabets will be sha. So it will search all the names as per given pattern and return all the names which match the pattern.

3. The [ ] wirldcard Character:

Select * from student

Where name Like  ‘M[ NI]A%’;

In this example the Like operator with [ ] searches the names of all the students which start from MA and the third character is either N or I. 

4. Like Operator with Multiple Wirldcard:

Select * from product

Where p_name Like ‘% pen%’;

Here the % wildcard character is used before and after the pen word. So the Like operator will search all the names with pen words and before and after any characters match.

Not Like:

Not Like operator searches the pattern which is Not like the given pattern.

For Example

Select * from employee

Where designation Not Like ‘%Manager%’;

The Not Like operator searches all the designation of all the employees where the designation does not contain the manager word. The designation column  which contains manager words will not return.