SQL - Arithmetic Operators

---

---

The SQL Arithmetic operators are used to perform basic mathematical operations on numeric data types in a database. The most common arithmetic operators used in SQL are:

Here is a list of all the arithmetic operators available in SQL.

In this chapter we will learn about each operator one-by-one with examples showing its usage. Before proceeding further, let us create a table named EMPLOYEE using the following query −

CREATE TABLE EMPLOYEE(
   ID INT NOT NULL,
   NAME VARCHAR(15) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(10, 2),
   JOIN_DATE DATE,
   PRIMARY KEY(ID)
);

Once the table is created, we can insert some values into the table using the following queries −

INSERT INTO EMPLOYEE(ID, NAME, AGE, ADDRESS, SALARY, JOIN_DATE) VALUES(2, 'Ramesh', 21, 'Hyderabad', 25550.12, '2023/01/02');
INSERT INTO EMPLOYEE(ID, NAME, AGE, ADDRESS, SALARY, JOIN_DATE) VALUES(1, 'khilan', 22, 'Nijamabad', 57500.84, '2022/01/14');
INSERT INTO EMPLOYEE(ID, NAME, AGE, ADDRESS, SALARY, JOIN_DATE) VALUES(4, 'Kaushik', 18, 'Bangolore', 47275.43, '2023/03/15');
INSERT INTO EMPLOYEE(ID, NAME, AGE, ADDRESS, SALARY, JOIN_DATE) VALUES(5, 'chaitali', 23, 'Ranchi', 40700.76, '2022/04/18');
INSERT INTO EMPLOYEE(ID, NAME, AGE, ADDRESS, SALARY, JOIN_DATE) VALUES(6, 'Hardhik', 19, 'Noida', 44200.09, '2023/06/04');
INSERT INTO EMPLOYEE(ID, NAME, AGE, ADDRESS, SALARY, JOIN_DATE) VALUES(3, 'komal', 23, 'Chennai', 44200.09, '2023/10/08');

Let us verify whether the table is created or not using the following query −

SELECT * FROM EMPLOYEE;

As we can see in the below output, the table has been created in the database.

Now, let us perform all the SQL comparison operations using the above table.

SQL Addition (+) Operator

The addition operator in SQL is used to add two or more numeric values. It is similar to the "plus" symbol in basic mathematics.

Example

In the following example, we are trying to perform the addition operation to give all employees a bonus of 5000 to their existing salary.

SELECT ID, NAME, SALARY, SALARY + 5000 AS "SALARY_BONUS" FROM employee;

Output

When we execute the above query, the output is obtained as follows −

SQL Subtraction (-) Operator

The subtraction operator in SQL is used to subtract one numeric value from another. It is similar to the "minus" symbol in basic mathematics.

Example

In here, we are using the - operator to find the salary difference between the highest-paid and lowest-paid employees.

SELECT MAX(salary), MIN(salary), MAX(salary)-MIN(salary) AS salary_difference FROM employee;

Output

On executing the above query, the output is displayed as follows −

SQL Multiplication (*) Operator

The multiplication operator in SQL is used to perform mathematical multiplication on numeric values. It allows us to multiply two or more columns or numeric expressions together, resulting in a new value representing the product of the operands.

Example

Now, we are performing * operation to give all employees a 10% bonus on their salary.

SELECT NAME, AGE, ADDRESS, SALARY * 1.10 AS new_salary_with_bonus FROM employee;

Output

The table for the above query produced as given below −

SQL Division (/) Operator

The division operator (/) in SQL is used to perform mathematical division on numeric values. It allows us to divide one numeric operand by another, resulting in a new value representing the quotient of the division.

Example

Here, we are using the / operator to calculate the average salary of all employees.

SELECT AVG(SALARY) AS average_salary FROM employee;

Output

Following is the output of the above query −

SQL Modulus (%) Operator

The modulus operator (%) in SQL is used to find the remainder after dividing one numeric value by another. It returns the integer remainder of the division operation.

Example

In the following example, we are using the % operator to find out the employees having an even employee ID.

SELECT * FROM employee WHERE ID % 2 = 0;

Output

The output produced is as shown below −