SQL Arithmetic Operators | Arithmetic Operators with examples

SQL Arithmetic Operators :

In previous article i have explained the Functions in SQL with real life examples.In this article i would like to give idea about SQL Arithmetic Operators.I will try to explain each and every operator with examples.The SQL Arithmetic Operators are most used operators to perform arithmetic operations.First , i would like to give you idea about operator and different types of operators.There are 4 types of operators in SQL.This article will give you idea about the SQL Arithmetic Operators with different real industry examples.

“Operators are nothing but the the statement which are used to perform SQL Operations”

There are following different types of Operators in SQL:

1. Arithmetic Operators

2.Logical Operators

3.Comparison Operators

4.Negation Operators

SQL Arithmetic Operators

SQL Arithmetic Operators with Real Examples :

Arithmetic operators are basically used to perform the arithmetic calculations between 2 variables or numbers.The important is how to use this operators in SQL.This section will give you idea about different SQL Arithmetic Operators with real examples of each one,Arithmetic operators can perform arithmetical operations on numeric operands involved. Arithmetic operators are addition(+), subtraction(-), multiplication(*) and division(/). The + and – operators can also be used in date arithmetic.

1.Plus Operator (+ Operator):

Plus Operator is used to perform the addition of two numbers or variables.Let me first explain the syntax of Plus operator in SQL.

Syntax :

SELECT <Expression>[Plus operator]<expression>… FROM [table_name] WHERE [expression];

Here,

Expression  :

Expression made up of a single constant, variable, scalar function, or column name and can also be the pieces of a SQL query that compare values against other values or perform arithmetic calculations.

Plus Operator:

This is actual plus operators lies between two different expressions.

Example 1 :

Select 1+1 as Sum from Dual;

Will give answer as Sum column and value is 2.

Example 2:

select column1+column2 as Sum From Employee;

Here the column1 and column2 should be the numeric fields to perform the addition. The output of this statement is the Sum column with addition of two columns.If these columns are not numeric type user needs to convert it to numeric type and then using plus operator do the addition.

2.Minus Operator (- Operator):

Minus Operator is used to perform the subtraction of two numbers or variables.

Syntax :

SELECT <Expression>[Minus operator]<expression>… FROM [table_name] WHERE [expression];

Here,

Expression  :

Expression made up of a single constant, variable, scalar function, or column name and can also be the pieces of a SQL query that compare values against other values or perform arithmetic calculations.

Minus Operator:

This is actual minus operators lies between two different expressions.

Example 1:

Select 1-1 as Subtract from Dual;

Will give answer as Subtract column and value is 0.

Example 2:

select column1- column2 as Subtract From Employee;

Here the column1 and column2 should be the numeric fields to perform the subtraction. The output of this statement is the Subtract column with subtraction of two columns.If these columns are not numeric type user needs to convert it to numeric type and then using plus operator do the subtraction.

3.Multiplication Operator (* Operator):

Multiplication Operator is used to perform the multiplication of two numbers or variables.

Syntax :

SELECT <Expression>[Multiplication operator]<expression>… FROM [table_name] WHERE [expression];

Here,

Expression  :

Expression made up of a single constant, variable, scalar function, or column name and can also be the pieces of a SQL query that compare values against other values or perform arithmetic calculations.

Multiplication Operator:

This is actual Multiplication operators lies between two different expressions.

Example:

Select 1*1 as Multiply from Dual;

Will give answer as Multiply column and value is 1.

Example 2:

select column1*column2 as Multiply From Employee;

Here the column1 and column2 should be the numeric fields to perform the multiplication. The output of this statement is the Multiply column with multiplication of two columns.If these columns are not numeric type user needs to convert it to numeric type and then using plus operator do the multiplication.

 

4.Division Operator (/ Operator):

Division Operator is used to perform the division of two numbers or variables.

Syntax :

SELECT <Expression>[division operator]<expression>… FROM [table_name] WHERE [expression];

Here,

Expression  :

Expression made up of a single constant, variable, scalar function, or column name and can also be the pieces of a SQL query that compare values against other values or perform arithmetic calculations.

Division Operator:

This is actual division operators lies between two different expressions.

Example:

Select 1/1 as Division from Dual;

Will give answer as Division column and value is 1.

Example 2:

select column1/column2 as Division From Employee;

Here the column1 and column2 should be the numeric fields to perform the Division Operation. The output of this statement is the Division column with division of two columns.If these columns are not numeric type user needs to convert it to numeric type and then using plus operator do the division.

5.SQL Modulo Operator (% Operator):

The SQL MODULO operator returns the remainder (an integer) of the division.

Syntax :

SELECT <Expression>[Modulo operator]<expression>… FROM [table_name] WHERE [expression];

Here,

Expression  :

Expression made up of a single constant, variable, scalar function, or column name and can also be the pieces of a SQL query that compare values against other values or perform arithmetic calculations.

Modulo Operator:

The modulo operator between 2 expressions are used to calculate the remainder of the two expressions.

Example:

Select 3%2 as Remainder from Dual;

Will give answer as Remainder column and value is 1.

Example 2:

select column1% column2 as Remainder From Employee;

Here the column1 and column2 should be the numeric fields to perform the Modulus Operation. The output of this statement is the Remainder column with modulus of two columns.If these columns are not numeric type user needs to convert it to numeric type and then using plus operator do the Modulus operation.

Hope everyone will get the proper idea about the SQL Arithmetic operators. If you have any suggestions kindly comment in comment section.

Leave a Reply