SQL Operators in DBMS

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

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.`
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.`
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

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