SQL Operators in DBMS

January 24, 2023

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 Operators in DBMS

SQL mainly provides the following
set of operators

SQL Arithmetic Operators
SQL Comparison Operators
SQL Logical Operators
SQL Special operators

What is Database

Participation Constraint

DBMS Architecture

Data Models

Cardinality

SQL Arithmetic operators

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

Operator	Example
+	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’

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7839	KING	PRESIDENT	–	17-NOV-81	5000	–	10
7698	BLAKE	MANAGER	7839	01-MAY-81	2850	500	30
7782	CLARK	MANAGER	7839	09-JUN-81	2450	500	10
7566	JONES	MANAGER	7839	02-APR-81	2975	–	20
7788	SCOTT	ANALYST	7566	19-APR-87	3000	–	20
7902	FORD	ANALYST	7566	03-DEC-81	3000	–	20
7369	SMITH	CLERK	7902	17-DEC-80	800	500	20

Example

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

O/P

7 rows selected.

EMPNO	ENAME	SAL	ANNUAL	COMM
7839	KING	5000	60000	416.66
7698	BLAKE	2850	34200	237.5
7782	CLARK	2450	29400	204.16
7566	JONES	2975	35700	247.91
7788	SCOTT	3000	36000	250
7902	FORD	3000	36000	250
7369	SMITH	800	9600	66.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

Operator	Example
=	(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.

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	DEPTNO
7698	BLAKE	MANAGER	7839	01-MAY-81	2850	30
7566	JONES	MANAGER	7839	02-APR-81	2975	20

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

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	DEPTNO
7698	BLAKE	MANAGER	7839	01-MAY-81	2850	30
7782	CLARK	MANAGER	7839	09-JUN-81	2450	10
7566	JONES	MANAGER	7839	02-APR-81	2975	20
7788	SCOTT	ANALYST	7566	19-APR-87	3000	20
7902	FORD	ANALYST	7566	03-DEC-81	3000	20

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	JOB	SAL	HIREDATE
7566	MANAGER	2975	02-APR-81
7788	ANALYST	3000	19-APR-87
7902	ANALYST	3000	03-DEC-81
7369	CLERK	800	17-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.

EMPNO	JOB	SAL	HIREDATE
7698	MANAGER	2850	01-MAY-81
7782	MANAGER	2450	09-JUN-81
7369	CLERK	800	17-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.

EMPNO	ENAME	HIREDATE	SAL	JOB
7788	SCOTT	19-APR-87	3000	ANALYST
7369	SMITH	17-DEC-80	800	CLERK

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.

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7839	KING	PRESIDENT	–	17-NOV-81	5000	–	10
7566	JONES	MANAGER	7839	02-APR-81	2975	–	20
7788	SCOTT	ANALYST	7566	19-APR-87	3000	–	20
7902	FORD	ANALYST	7566	03-DEC-81	3000	–	20

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.

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7698	BLAKE	MANAGER	7839	01-MAY-81	2850	500	30
7782	CLARK	MANAGER	7839	09-JUN-81	2450	500	10
7369	SMITH	CLERK	7902	17-DEC-80	800	500	20

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.

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7839	KING	PRESIDENT	–	17-NOV-81	5000	–	10
7566	JONES	MANAGER	7839	02-APR-81	2975	–	20
7788	SCOTT	ANALYST	7566	19-APR-87	3000	–	20
7902	FORD	ANALYST	7566	03-DEC-81	3000	–	20

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.

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7839	KING	PRESIDENT	–	17-NOV-81	5000	–	10
7698	BLAKE	MANAGER	7839	01-MAY-81	2850	500	30
7782	CLARK	MANAGER	7839	09-JUN-81	2450	500	10
7566	JONES	MANAGER	7839	02-APR-81	2975	–	20
7902	FORD	ANALYST	7566	03-DEC-81	3000	–	20

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

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7839	KING	PRESIDENT	–	17-NOV-81	5000	–	10
7698	BLAKE	MANAGER	7839	01-MAY-81	2850	500	30
7782	CLARK	MANAGER	7839	09-JUN-81	2450	500	10
7566	JONES	MANAGER	7839	02-APR-81	2975	–	20
7902	FORD	ANALYST	7566	03-DEC-81	3000	–	20

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

Prime Course Trailer

Related Banners

Get PrepInsta Prime & get Access to all 200+ courses offered by PrepInsta in One Subscription

Get over 200+ course One Subscription

Courses like AI/ML, Cloud Computing, Ethical Hacking, C, C++, Java, Python, DSA (All Languages), Competitive Coding (All Languages), TCS, Infosys, Wipro, Amazon, DBMS, SQL and others

Unlock this article for Free,
by logging in

SQL Operators in DBMS

SQL Operators in DBMS

SQL Operators in DBMS

SQL Arithmetic operators

Example

SQL Bitwise operators

SQL comparison(Relational) operators

Example

OR operator

Example

SQL Special Operators

IN operator

Example

BETWEEN Operator

Example

LIKE operator

Example

IS NULL operator

Example

NOT operator

IS NOT NULL

NOT BETWEEN

NOT LIKE

NOT IN

Prime Course Trailer

Related Banners

Get over 200+ course One Subscription

Checkout list of all the video courses in PrepInsta Prime Subscription

Checkout list of all the video courses in PrepInsta Prime Subscription

30+ Companies are Hiring

Unlock this article for Free, by logging in

SQL Operators in DBMS

SQL Operators in DBMS

SQL Operators in DBMS

SQL Arithmetic operators

Example

SQL Bitwise operators

SQL comparison(Relational) operators

Example

OR operator

Example

SQL Special Operators

IN operator

Example

BETWEEN Operator

Example

LIKE operator

Example

IS NULL operator

Example

NOT operator

IS NOT NULL

NOT BETWEEN

NOT LIKE

NOT IN

Prime Course Trailer

Related Banners

Get over 200+ course One Subscription

Checkout list of all the video courses in PrepInsta Prime Subscription

Checkout list of all the video courses in PrepInsta Prime Subscription

30+ Companies are Hiring

Unlock this article for Free,
by logging in