Skip to content
Related Articles

Related Articles

SQL | Arithmetic Operators
  • Difficulty Level : Easy
  • Last Updated : 21 Mar, 2018

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_idemployee_namesalarysalary+100
1alex2500025100
2rr5500055100
3jpm5200052100
4ggshmr1231212412

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_idemployee_namesalarysalary+employee_id
1alex2500025001
2rr5500055002
3jpm5200052003
4ggshmr1231212316

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_idemployee_namesalarysalary-100
12Finch1500014900
22Peter2500024900
32Warner56005500
42Watson9000089900

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_idemployee_namesalarysalary – employee_id
12Finch1500014988
22Peter2500024978
32Warner56005568
42Watson9000089958

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_idemployee_namesalarysalary * 100
1Finch250002500000
2Peter550005500000
3Warner520005200000
4Watson123121231200

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_idemployee_namesalarysalary * employee_id
1Finch2500025000
2Peter55000110000
3Warner52000156000
4Watson1231249248

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_idemployee_namesalarysalary % 25000
1Finch250000
2Peter550005000
3Warner520002000
4Watson1231212312

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_idemployee_namesalarysalary % employee_id
1Finch250000
2Peter550000
3Warner520001
4Watson123120

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_idemployee_namesalarytypetype + 100
1Finch25000NULLNULL
2Peter55000NULLNULL
3Warner52000NULLNULL
4Watson12312NULLNULL

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

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up
Recommended Articles
Page :