Oracle Arithmetic Operators

This SQL tutorial focuses on SQL Arithmetic Operators in Oracle, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.

This tutorial is a part of several posts explaining how to write basic queries in Oracle. To read additional posts regarding this subject, please use the following links:

Oracle Arithmetic Operators

You may not always want to retrieve the data as is. In some cases, you may want to display your data with certain calculation, or look at a what-if scenarios (for example, you may want to know how the prices would look like after adding VAT).

Arithmetic Operations on Numeric Values

The Oracle query in this example calculates the product’s price after an addition of 20%.

SELECT product_name , supplier_id, unit_price,  unit_price * 1.2
FROM   products

It is advisable to specify the original column without the change (unit_price) in addition to the column with the calculation (unit_price *1.2), in order to make the difference – in this example, the increase – visible.

It is possible to perform any mathematical calculation:

SELECT product_name , supplier_id , unit_price,
       unit_price * 0.14 / 2.74 + 52.36
FROM   products

In order to define operator precedence, simply use round brackets:

SELECT product_name , supplier_id , unit_price,
       unit_price * 0.14 / (2.74 + 52.36)
FROM   products

In Oracle, You can also use arithmetic operators on multiple columns:

SELECT Product_name , unit_price , Discount
       unit_price - Discount
FROM   products

Note : Oracle syntax Ignores blank spaces before and after the arithmetic operator.

Arithmetic Operations on String Values

In Oracle It is not possible to perform any calculations on String values (such as Varchar or Char).

SELECT product_name , supplier_id + 300
FROM products  

(error)

Arithmetic Operations on Date Values

In Oracle The plus (+) and minus (-) operators can be used to perform arithmetic operations on Date values. These operators will allow you to add or subtract days.

SELECT order_id , customer_name , order_date, order_date + 1
FROM orders

Result: Order date (+) one day

SELECT order_id , customer_name , order_date, order_date - 1
FROM orders

Result: Order date (-) one day

In Oracle It is not possible to carry out any calculation of (multiplication) or (division) on Date values.

SELECT order_id , customer_name , order_date, order_date * 2
FROM orders

SELECT order_id, customer_name, order_date, order_date * 3 
FROM orders

Arithmetic Operations on NULL Values

In Oracle A NULL value is a value that indicates an empty field in a table. This value does not equal zero (0), nor does it equal space (‘ ‘); When a certain calculation is carried out on a column that contains NULL values, any calculation performed on the NULL value returns NULL.

SELECT product_name, unit_price, discount,
       discount + 1
FROM products 

SELECT product_name, unit_price, discount ,
       discount * unit_price
FROM products

The discount column is a column that contains NULL values. Each calculation on a field in this column that contains NULL value returns NULL.

General Guidelines

In Oracle calculations on columns (of any data type) do not modify the contents (actual data) of the column in the table, but only display the column’s contents for that specific query, in another way.
In Oracle Calculations on columns change the column’s header’s in the output (this issue will be addressed in the next post – Column Aliases)

SELECT product_name, unit_price, unit_price  + 500
FROM products

Result :

product_name    unit_price   unit_price + 500
------------    ----------   -----------------
Tea             30           530

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…

Oracle Arithmetic Operators