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
Last Updated :
21 Mar, 2018
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...