Special Operators of Oracle in SQL
https://www.computersprofessor.com/2016/04/special-operators-of-oracle-in-sql.html
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 +,-,*,,
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 <, <,>=,<=,<>,
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
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 ! =,
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 3rd 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’;