SQL Operators

Operator

The purpose operators is to perform the manipulations with attributes. These Operators are following

1. Relational Operators - for comparing purpose

2. Arithmetic Operators - for mathematical operations

3. Logical Operators - for compare with multiple conditions or opposition conditions

3. Special Operator - for special purpose comparison

Relational Operators

>, <, >=, <=, =, !=, <>

Examples:

Display the details about employee whose name is SMITH

select *from emp where ename='SMITH';

Display the details about employees whose salary is greater than 3000

select *from emp where sal > 3000;

Display the details about employees except MANAGER

select *from emp where job <>'MANAGER' ;

or

select *from emp where job !='MANAGER' ;

Arithmetic Operators

+, -, *, /

Logical Operators

and, or , not

Example

Display the details about MANAGERS whose salary is greater than 2500

select ename, job, sal from emp where job='MANAGER' and sal>2500;

Display the details about MANAGERS or Employee BLAKE details

select ename, job from emp where job='MANAGER' or ename='BLAKE';

Special Operators

These operators are following

1. like

2. in

3. is null

4. between

like operator

to compare with particular text pattern. to work with this operators we can use to special characters %, _(under score)

% represent multiple characters, _ represent single character

if you don't know the length of the string we can use %

if you know the length of the string we can use _

Example

Display the details about employees whose name starting with 'S'

select *from emp where ename like 'S%';

Display the details about employees whose name exactly contains 4 characters

select *from emp where ename like '____';

in operator

to compare with multiple conditions on single attriute

Example

Display the details about MANAGERS and CLERKS

select *from emp where job in('MANAGER','CLERK');

is null operator

to compare with null values

Example

Display the details about employee who are not taking the commissions

Select *from emp where comm is  null;

between operator

to display the records in a range based on particular attribute

Example

Display the details about employee whose salary is starting from 1600 to 3000

select *from emp where sal between 1600 and 3000;

Order By clause

to display the records in sorting order on particular attribute, by default order is ascending order

Example

Select *from emp order by ename;

we can use order by on any number of columns

Example

Select *from emp order by ename, sal desc;

while working with order by instead of using column name we can use column number

select *from emp order by 2;

order by clause is the last clause in select statement

select from emp where job='MANAGER; order by ename