SQL Standard

SQL Arithmetic Operators

Arithmetic operations are widespread for developers. Whether you calculate the total for a product purchase, the number of comments, followers, etc., you will use arithmetic operators.Databases are not left behind. For example, in SQL, you can perform basic arithmetic operations thanks to the four operators.

This tutorial will discuss the four arithmetic operators in Standard SQL and how to use them.

SQL Arithmetic Operators

In Standard SQL, there are four main types of arithmetic operators. They include:

  1. Addition operator (+)
  2. Subtraction operator (-)
  3. Multiplication operator (*)
  4. Division operator (/)

Using SQL Arithmetic operators

Using arithmetic operators in SQL is pretty straightforward. Moreover, it conforms to the rules of basic arithmetics.

However, we can express the general syntax as shown in the example below:

left_operand (expression) operator right_operand;

In this case, the left and right operands refer to either a single numeric value or an expression that evaluates a numerical result.

Let us discuss various examples for using arithmetic operators in SQL.

Addition

To perform an addition operation in SQL, we can run an example as shown:

select 8 + 9 as sum;

We add two operands together in the above query using the addition operator. The resulting set is as shown:

sum

-----

17

(1 row)

In more realistic scenarios, you can add values from a database as shown in the example query below:

select (products * price) + (products*0.16) as total_inclusive;

The example above shows you can use arithmetic operators to calculate the total price of items inclusive of tax.

Subtraction

There is not much difference between the usage of the addition and subtraction operator in SQL. For example:

SELECT 100 - 56 as difference;

The above query should return the difference between the specified operands.

difference

------------

44

Multiplication

The same case goes for the multiplication and division operators. An example usage is as shown below:

SELECT 100 * 5 as product;

SELECT 454 / 45 as quotient;

The above should return the product and quotient of the specified operands, respectively.

Modulo Operator

There is another type of operator in SQL. This may not be fully supported, depending on the database engine.

However, as of writing this article, most relational database engines support it.

The modulo operator is denoted with a % sign and returns the remainder of the division between two operands.

An example is as shown:

SELECT 454 % 45 as remainder;

This should return the value as:

remainder

-----------

4

Conclusion

This article explored how to use basic arithmetic operators in Standard SQL. Keep practicing and check our tutorials to learn more.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list