SQL Arithmetic Operators

<<PreviousNext>>

Description

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.

Syntax

 SELECT <Expression>[arithmetic operator]<expression>...
 FROM [table_name] 
 WHERE [expression];
 

dfad

Table of Contents

• Plus Operator (+)
• Minus Operator (-)
• Divide(/), Modulo(%) Operator
• Multiply Operator (*)
• Using plus minus divide and multiply operator

Example

This is a simple example of using SQL arithmetic operators :

SELECT 15+10-5*5/5 FROM dual;

Sql plus (+) operator

Description

The SQL plus (+) operator is used to add two or more expressions or numbers.

Example

Sample table : customer

To get data of 'cust_name', 'opening_amt', 'receive_amt',  ('opening_amt' + 'receive_amt') from the 'customer' table with following condition -

1. sum of 'opening_amt' and  'receive_amt' is greater than 15000,

the following sql statement can be used :

SELECT cust_name, opening_amt, 
receive_amt, (opening_amt + receive_amt)
FROM customer
WHERE (opening_amt + receive_amt)>15000;

Pictorial presentation

Output

Sql minus (-) operator

The SQL minus (-) operator is used to subtract one expression or number from another expression or number.

Example

To get data of 'cust_name', 'opening_amount', 'payment_amount' and 'oustanding_amount' from the 'customer' table with following condition -

1. 'outstanding_amt' - 'payment_amt' is equal to the  'receive_amt',

the following sql statement can be used :

SELECT cust_name,opening_amt, payment_amt, outstanding_amt
FROM customer  

WHERE(outstanding_amt-payment_amt)=receive_amt;

Output

Sql multiply ( * ) operator

The SQL multiply ( * ) operator is used to multiply two or more expressions or numbers.

Example

Sample table : agents

To get data of 'agent_code', 'agent_name', 'working_area' and  ('commission'*2) from the 'agents' table with following condition -

1. two times of the default 'commission' is greater than  0.25,

the following sql statement can be used :

SELECT agent_code, agent_name, 
working_area, (commission*2)
FROM agents
WHERE (commission*2) > 0.25;

Output

Sql divide ( / ) operator

The SQL divide ( / ) operator is used to divide one expressions or numbers by another.

Example

To get data of 'cust_name',  'opening_amt',  'receive_amt',  'outstanding_amt' and ('receive_amt'*5/ 100) as a column heading  'commission' from the customer table with following condition -

1. 'outstanding_amt' is less than or equal to 4000,

the following sql statement can be used :

SELECT cust_name, opening_amt, receive_amt,
outstanding_amt, (receive_amt*5/ 100) commission
FROM customer
WHERE outstanding_amt<=4000;

Output

Sql modulo ( % ) operator

Usage of Sql modulo ( % ) operator

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

Example

To get the modulus of a division of 150 by 7 from the DUAL table, the following sql statement can be used :

Output

Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.

See our Model Database.