# SQL | Arithmetic Operators

Prerequisite: Basic Select statement, Insert into clause, Sql Create Clause, SQL Aliases

We can use various Arithmetic Operators on the data stored in the tables.

Arithmetic Operators are:

+[Addition]-[Subtraction]/[Division]*[Multiplication]%[Modulus]

**Addition** (+) :

It is used to perform **addition operation** on the data items, items include either single column or multiple columns.

**Implementation**:

SELECT employee_id, employee_name, salary, salary + 100 AS "salary + 100" FROM addition;

Output:

employee_id | employee_name | salary | salary+100 |
---|---|---|---|

1 | alex | 25000 | 25100 |

2 | rr | 55000 | 55100 |

3 | jpm | 52000 | 52100 |

4 | ggshmr | 12312 | 12412 |

Here we have done addition of 100 to each Employee’s salary i.e, addition operation on single column.

Let’s perform **addition of 2 columns**:

SELECT employee_id, employee_name, salary, salary + employee_id AS "salary + employee_id" FROM addition;

Output:

employee_id | employee_name | salary | salary+employee_id |
---|---|---|---|

1 | alex | 25000 | 25001 |

2 | rr | 55000 | 55002 |

3 | jpm | 52000 | 52003 |

4 | ggshmr | 12312 | 12316 |

Here we have done addition of 2 columns with each other i.e, each employee’s employee_id is added with its salary.

**Subtraction** (-) :

It is use to perform **subtraction operation** on the data items, items include either single column or multiple columns.

**Implementation**:

SELECT employee_id, employee_name, salary, salary - 100 AS "salary - 100" FROM subtraction;

Output:

employee_id | employee_name | salary | salary-100 |
---|---|---|---|

12 | Finch | 15000 | 14900 |

22 | Peter | 25000 | 24900 |

32 | Warner | 5600 | 5500 |

42 | Watson | 90000 | 89900 |

Here we have done subtraction of 100 to each Employee’s salary i.e, subtraction operation on single column.

Let’s perform **subtraction of 2 columns**:

SELECT employee_id, employee_name, salary, salary - employee_id AS "salary - employee_id" FROM subtraction;

Output:

employee_id | employee_name | salary | salary – employee_id |
---|---|---|---|

12 | Finch | 15000 | 14988 |

22 | Peter | 25000 | 24978 |

32 | Warner | 5600 | 5568 |

42 | Watson | 90000 | 89958 |

Here we have done subtraction of 2 columns with each other i.e, each employee’s employee_id is subtracted from its salary.

**Division** (/) : For **Division** refer this link- Division in SQL

**Multiplication** (*) :

It is use to perform **multiplication** of data items.

**Implementation**:

SELECT employee_id, employee_name, salary, salary * 100 AS "salary * 100" FROM addition;

Output:

employee_id | employee_name | salary | salary * 100 |
---|---|---|---|

1 | Finch | 25000 | 2500000 |

2 | Peter | 55000 | 5500000 |

3 | Warner | 52000 | 5200000 |

4 | Watson | 12312 | 1231200 |

Here we have done multiplication of 100 to each Employee’s salary i.e, multiplication operation on single column.

Let’s perform **multiplication of 2 columns**:

SELECT employee_id, employee_name, salary, salary * employee_id AS "salary * employee_id" FROM addition;

Output:

employee_id | employee_name | salary | salary * employee_id |
---|---|---|---|

1 | Finch | 25000 | 25000 |

2 | Peter | 55000 | 110000 |

3 | Warner | 52000 | 156000 |

4 | Watson | 12312 | 49248 |

Here we have done multiplication of 2 columns with each other i.e, each employee’s employee_id is multiplied with its salary.

**Modulus** ( % ) :

It is use to get **remainder** when one data is divided by another.

**Implementation**:

SELECT employee_id, employee_name, salary, salary % 25000 AS "salary % 25000" FROM addition;

Output:

employee_id | employee_name | salary | salary % 25000 |
---|---|---|---|

1 | Finch | 25000 | 0 |

2 | Peter | 55000 | 5000 |

3 | Warner | 52000 | 2000 |

4 | Watson | 12312 | 12312 |

Here we have done modulus of 100 to each Employee’s salary i.e, modulus operation on single column.

Let’s perform **modulus operation between 2 columns**:

SELECT employee_id, employee_name, salary, salary % employee_id AS "salary % employee_id" FROM addition;

Output:

employee_id | employee_name | salary | salary % employee_id |
---|---|---|---|

1 | Finch | 25000 | 0 |

2 | Peter | 55000 | 0 |

3 | Warner | 52000 | 1 |

4 | Watson | 12312 | 0 |

Here we have done modulus of 2 columns with each other i.e, each employee’s salary is divided with its id and corresponding remainder is shown.

Basically, **modulus** is use to check whether a number is **Even** or **Odd**. Suppose a given number if divided by 2 and gives 1 as remainder, then it is an *odd number* or if on dividing by 2 and gives 0 as remainder, then it is an *even number*.

**Concept of NULL** :

If we perform any arithmetic operation on **NULL**, then answer is *always* null.

**Implementation**:

SELECT employee_id, employee_name, salary, type, type + 100 AS "type+100" FROM addition;

Output:

employee_id | employee_name | salary | type | type + 100 |
---|---|---|---|---|

1 | Finch | 25000 | NULL | NULL |

2 | Peter | 55000 | NULL | NULL |

3 | Warner | 52000 | NULL | NULL |

4 | Watson | 12312 | NULL | NULL |

Here output always came null, since performing any operation on null will always result in a *null value*.

**Note**: Make sure that NULL is **unavailable**, **unassigned**, **unknown**. Null is **not** same as *blank space* or *zero*.

To get in depth understanding of NULL, refer THIS link.

**References**: Oracle Docs