Special Operators of Oracle in SQL

Operator is a Symbol that is used to perform a particular task. 

The operators are classified into 4 types they are,

·         Arithmetical operator

·         Relation operator

·         Logical operator

·         Assignment operator

Arithmetic operators

The Arithmetic operators are used to perform arithmetic calculations such as addition, Subtraction, Multiplication, division.  The operators are +,-,*,,

Eg: 10+5=

       10-5=5   

      10*5=50

      8/3=2.66

Relational operators

The relational operators are used to check a single condition gives only are value either true or false. The relational operators are <, <,>=,<=,<>,

Eg: 10< 15=1

It evaluates

      10 is >15=0

      10is <15 o:p="">

      10 is >=15=0

      10=15=0

      10<>=15=1

Logical operators

The logical operators are used between two or more conditions. The true relational operator give one value, either true or false. The logical operator are AND,OR,NOT

Assignment operator

The assignment operator is used to assign value to the variable- In oracle the assignment operate is ! =,

Eg: A=50;

      C= 10:

  

ADDITIONAL OPERATORS

The pattern can connection wild card character such as % percentage and underscore(-).

The % is used to denote any number of unknown character and ‘-‘ is denotes  only one unknown character, you can use the number of unknown character that must be earned to the number of unknown character.

For example retrieve rows where the state, name beings with ‘K’ and followed by another character, we give the query as

SQL>= Select first- name, last- name, city from customer where state 

Where state like ‘K’;

Will displays all the rows where states are either Kerala& Karnataka.

Similarly to retrieves rows where the first name contains the word RAJ embedded in the attribute first- name

Eg: select first- name, last- name, city, state from customer where first- name likes (y. RAJY.);

In the same way to retrieve rows where ADD2 contains the wordupon or udipi, in which the 3^rd character may be anything. This is done using underscore(-) as,

SQL>=select first- name, last- name, city, from customer where ADD2 like Ud_pi;

Between N1 and N2:

Between N1 and N2 operator can be used to retrieve the rows which fall within the range.

Eg: select * from customer where custno between 1003 and 1009 ;

that will retrieve the rows where the customer- no column has data representing any value between 1003 and 1009 including both no.

Select* from employee where emp- no >=9013 and emp. No<=9431;

IN(L1,L2,L3):

The in operator is used to specify a list of possible values for an attributer.

Eg: select*form customer where city in (UDP, MNG,BNG,MAR);

That will retrieve the rows.  where the city column has data i.e= udp or MNG or BNG or MAR

The same commands to query can also be given as,  select * from customer where city = ‘udp’ or ’MNG’ or city= ‘BNG’ or city= ‘MAR’;