SQL | Arithmetic Operators

Arithmetic operators in SQL are used to perform mathematical operations on table data. These operators help in calculating totals, differences, percentages, and other numeric transformations directly in queries.

These operations can be applied to:

• A single column
• Two or more columns
• Constant values with column data

List of Arithmetic Operators in SQL

1. Addition (+)

The addition operator is used to sum values. Performs addition between:

• Column and constant
• Two columns

Example 1: Addition with constant

SELECT employee_id, employee_name, salary, 
       salary + 100 AS "salary + 100"
FROM addition;

Output:

Example 2: Addition of two columns

SELECT employee_id, employee_name, salary, 
       salary + employee_id AS "salary + employee_id"
FROM addition;

Output:

2. Subtraction (-)

The subtraction operator deducts one value from another. Performs subtraction between:

• Column and constant
• Two columns

Example 1: Subtracting a constant

SELECT employee_id, employee_name, salary, 
       salary - 100 AS "salary - 100"
FROM subtraction;

Output:

Example 2: Subtracting one column from another

SELECT employee_id, employee_name, salary, 
       salary - employee_id AS "salary - employee_id"
FROM subtraction;

Output:

3. Multiplication (*)

The multiplication operator multiplies values by constants or other column values. It multiplies:

• Column and constant
• Two columns

Example 1: Multiplying with a constant

SELECT employee_id, employee_name, salary, 
       salary * 100 AS "salary * 100"
FROM addition;

Output:

Example 2: Multiplying two columns

SELECT employee_id, employee_name, salary, 
       salary * employee_id AS "salary * employee_id"
FROM addition;

Output:

5. Division (/)

The division operator divides one value by another. Example for division is similar to multiplication but returns quotient instead of product.

Example:

SELECT employee_id, employee_name, salary, 
       salary / 100 AS "salary / 100"
FROM addition;

Output:

6. Modulus (%)

The modulus operator returns the remainder of a division.

Useful for:

• Even/Odd check
• Pattern-based calculations

Example 1: Modulus with constant

SELECT employee_id, employee_name, salary, 
       salary % 25000 AS "salary % 25000"
FROM addition;

Output:

Example 2: Modulus between columns

SELECT employee_id, employee_name, salary, 
       salary % employee_id AS "salary % employee_id"
FROM addition;

Output:

7. Arithmetic Operations with NULL

When any arithmetic operation is performed on a NULL value, the result is always NULL.

Example:

SELECT employee_id, employee_name, salary, type, 
       type + 100 AS "type + 100"
FROM addition;

Output:

Key Notes on NULL:

• NULL means unknown/unavailable
• It is not the same as 0 or empty string
• Any operation with NULL results in NULL

Suggested Quiz

Edit Quiz

6 Questions

Which of the following is NOT a SQL arithmetic operator?

• A

   Ternary

• B

  Modulus

• C

  Addition

• D

   Subtraction

Explanation:

Addition, Subtraction, and Modulus are all SQL Arithmetic Operators.

The number of operands used by a Unary Operator is

• A

  1

• B

  2

• C

  3

• D

  4

Explanation:

The number of operands used by Unary Operator is 1.

What is the result of any arithmetic operation involving NULL?

• A

  0

• B

  Error

• C

  Empty string

• D

  NULL

Explanation:

Any arithmetic with NULL returns NULL because NULL represents an unknown value.

Which operator is used to calculate the remainder of a division in SQL?

• A

  /

• B

  %

• C

  *

• D

  -

Explanation:

The modulus operator (%) returns the remainder, as shown in the salary % 25000 example.

What does the following query compute?
SELECT salary + 100 FROM addition;

• A

  Subtracts 100 from salary

• B

  Multiplies salary by 100

• C

  Adds 100 to each salary value

• D

  Divides salary by 100

Explanation:

The article’s Addition section shows salary + constant increases the salary value by that constant.

Which arithmetic operator would you use to check whether a salary is even or odd?

• A

  %

• B

  *

• C

  +

• D

  /

Explanation:

The modulus operator helps identify even/odd by checking salary % 2.

Quiz Completed Successfully

Your Score :   2/6

Accuracy :  0%

Login to View Explanation

1/6 1/6 < Previous Next >