Please login

Prime

Prepinsta Prime

Video courses for company/skill based Preparation

(Check all courses)
Get Prime Video
Prime

Prepinsta Prime

Purchase mock tests for company/skill building

(Check all mocks)
Get Prime mock

SQL Operators in DBMS

SQL Operators in DBMS

 

In this article, we will learn about SQL Operators in DBMS.

In this article, we will learn about SQL Operations in DBMS.

Operators are used to perform some action on the operands(i.e input data) for performing comparisons and computations and filter the data as required.

SQL mainly provides the following
set of operators

  • SQL Arithmetic Operators
  • SQL Comparison Operators
  • SQL Logical Operators
  • SQL Special operators
SQL Operators in DBMS

SQL Arithmetic operators

SQL provides five basic arithmetic operators, assume if a =10, b=20.

OperatorExample
+a + b will give 30
a – b will give -10
*a * b will give 200
/b / a will give 2
%b % a will give 0

 

Consider the sample table ‘emp’

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7839KINGPRESIDENT17-NOV-81500010
7698BLAKEMANAGER783901-MAY-81285050030
7782CLARKMANAGER783909-JUN-81245050010
7566JONESMANAGER783902-APR-81297520
7788SCOTTANALYST756619-APR-87300020
7902FORDANALYST756603-DEC-81300020
7369SMITHCLERK790217-DEC-8080050020

Example

select empno,ename,sal,sal*12 as annsal,sal/12 as comm
from emp;

O/P

7 rows selected.
EMPNOENAMESALANNUALCOMM
7839KING500060000416.66
7698BLAKE285034200237.5
7782CLARK245029400204.16
7566JONES297535700247.91
7788SCOTT300036000250
7902FORD300036000250
7369SMITH800960066.66

The above query performs multiplication and division operation on each and every value of the salary column and displayed.

 

SQL Bitwise operators

Bitwise operators apply true false conditions on the individual binary bits of numbers

  • Bitwise AND(&): Returns true you only are both input bits are true otherwise false
  • Bitwise OR(|): Returns true if either of the input bits is true otherwise false
  • Bitwise XOR(^): Returns true only if both the input bits are different

 

SQL comparison(Relational)  operators

Comparison operators are generally used along with where clause for filtering the data as per the required condition specified

Assume variable a holds 10 and variable b holds 20 then

OperatorExample
=(a = b) is not true.
!=(a != b) is true.
>(a > b) is not true.
<(a < b) is true.
>=(a >= b) is not true.
<=(a <= b) is true.
!<(a !< b) is false.
!>(a !> b) is true.

Example

select empno,ename,sal,job
from emp
where sal>2500;

O/P

5 rows selected.
EMPNOENAMESALJOB
7839KING5000PRESIDENT
7698BLAKE2850MANAGER
7566JONES2975MANAGER
7788SCOTT3000ANALYST
7902FORD3000ANALYST

The above displays the details of employees whose salary is greater than 2500.

 

SQL Logical operators

AND operator

The AND results true only when all the conjunction of conditions specified after the where clause are satisfied

Example

select * from emp
where job='MANAGER' AND sal>2500;

O/P

2 rows selected.
EMPNOENAMEJOBMGRHIREDATESALDEPTNO
7698BLAKEMANAGER783901-MAY-81285030
7566JONESMANAGER783902-APR-81297520

Here two conditions first employee must be a manager at the same time he must be getting salary greater than 2500 then only condition becomes true and the record gets displayed.

 

OR operator

Among multiple conditions specified in the where clause  the transaction is performed if any of the condition becomes true

Example
select * from emp
where JOB='ANALYST' OR JOB='MANAGER';

O/P

5 rows selected 
EMPNOENAMEJOBMGRHIREDATESALDEPTNO
7698BLAKEMANAGER783901-MAY-81285030
7782CLARKMANAGER783909-JUN-81245010
7566JONESMANAGER783902-APR-81297520
7788SCOTTANALYST756619-APR-87300020
7902FORDANALYST756603-DEC-81300020

In the example above, the data is fetched if  the employee is an analyst or a manager i.e data is fetched even if any one condition is satisfied.

 

SQL Special Operators

IN operator

It is used to specify a set of values and operation n is performed on all the values specified in the set and if any of the value that is present in the list matches with the values present in a table then it returns true and is operation is performed

Example
select empno,job,sal,hiredate from emp
where ename in('SCOTT','FORD','SMITH','JONES')

O/P

4 rows selected.
 EMPNO
JOBSALHIREDATE
7566MANAGER297502-APR-81
7788ANALYST300019-APR-87
7902ANALYST300003-DEC-81
7369CLERK80017-DEC-80

Records of all those employees that are specified in the list of in are displayed.

 

BETWEEN Operator

It is used to perform data comparison and manipulation over a range of values present in the database table

Example

select empno,job,sal,hiredate 
from emp
where sal between 800 and 2900;

O/P

3 rows selected.
EMPNOJOBSALHIREDATE
7698MANAGER285001-MAY-81
7782MANAGER245009-JUN-81
7369CLERK80017-DEC-80
  • The details of those employees whose salary is present in the range between 800 to 2900 retrieved and it also considered specified values inclusive of the range
  • In the case of between operator lower value is first specified and followed by the higher value  & and operator in between this higher and lower values.

 

LIKE  operator

The like operator is a pattern matching operator and returns those records that match the specified pattern

Example
select empno,ename,hiredate,sal,job
from emp
where ename like 'S%';

O/P

2 rows selected.
EMPNOENAMEHIREDATESALJOB
7788SCOTT19-APR-873000ANALYST
7369SMITH17-DEC-80800CLERK

All the records of employees whose name starting with letter ‘S’ are displayed.

 

IS NULL operator

All operations upon null values present in the table must be done using this ‘is null’ operator .we cannot compare null value using the assignment operator

Example
select * from emp
where comm is null

O/P

4 rows selected.
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7839KINGPRESIDENT17-NOV-81500010
7566JONESMANAGER783902-APR-81297520
7788SCOTTANALYST756619-APR-87300020
7902FORDANALYST756603-DEC-81300020

The details of those employees whose commission value is Null are displayed.

 

NOT  operator

Not operator is a negation operator which is used along with like between, is null, in operators, It performs  reverse r action of all these operators.

 

IS NOT NULL
select * from emp
where comm is not null;

O/P

3 rows selected.
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7698BLAKEMANAGER783901-MAY-81285050030
7782CLARKMANAGER783909-JUN-81245050010
7369SMITHCLERK790217-DEC-8080050020

Details of all those employees whose Commission value is not null value are displayed.

 

NOT BETWEEN 
select * from emp
where sal NOT between 800 and 2900

O/P

4 rows selected.
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7839KINGPRESIDENT17-NOV-81500010
7566JONESMANAGER783902-APR-81297520
7788SCOTTANALYST756619-APR-87300020
7902FORDANALYST756603-DEC-81300020

The details of those employees whose salary does not fall in the range between 800 to 2900 displayed.

 

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

O/P

5 rows selected.
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7839KINGPRESIDENT17-NOV-81500010
7698BLAKEMANAGER783901-MAY-81285050030
7782CLARKMANAGER783909-JUN-81245050010
7566JONESMANAGER783902-APR-81297520
7902FORDANALYST756603-DEC-81300020

The details of all those employees whose name doesn’t start with letter ‘S’ are displayed.

 

NOT IN
select * from emp
where ename not in('SCOTT','FORD','SMITH','JONES');

O/P

5 rows selected
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7839KINGPRESIDENT17-NOV-81500010
7698BLAKEMANAGER783901-MAY-81285050030
7782CLARKMANAGER783909-JUN-81245050010
7566JONESMANAGER783902-APR-81297520
7902FORDANALYST756603-DEC-81300020

The details of all employees whose names are not among the list  are displayed

Learn more about DBMS here on this page.