SQL Operators: Arithmetic Operators

Arithmetic operators in SQL are used to perform arithmetic operations on numerical values in the database tables. These operators include addition (+), subtraction (-), multiplication (*), and division (/).

Syntax

The syntax for using arithmetic operators in SQL is as follows:

SELECT column1 + column2 AS result FROM table_name;
SELECT column1 - column2 AS result FROM table_name;
SELECT column1 * column2 AS result FROM table_name;
SELECT column1 / column2 AS result FROM table_name;

Example

Let's consider a table "sales" with the following data:

|  ID |  Date       |  Product  |  Quantity  |  Price  |
| --- | -----------| ---------| -----------| ------- |
|  1  |  2022-07-01|  Apple   |  10        |  2      |
|  2  |  2022-07-01|  Orange  |  20        |  1.5    |
|  3  |  2022-07-02|  Apple   |  15        |  2.5    |

Addition

SELECT Quantity + Price AS Total_Price FROM sales;

Output:

| Total_Price |
| ----------- |
| 20          |
| 50          |
| 37.5        |

Explanation: Adds the "Quantity" and "Price" columns for each row and displays the total price.

Subtraction

SELECT Price - Quantity AS Profit FROM sales;

Output:

| Profit      |
| ----------- |
| -18         |
| -27         |
| -22.5       |

Explanation: Subtracts "Quantity" from "Price" for each row, which results in profit or loss.

Multiplication

SELECT Quantity * Price AS Total_Sales FROM sales;

Output:

| Total_Sales |
| ----------- |
| 20          |
| 30          |
| 37.5        |

Explanation: Multiplies "Quantity" and "Price" for each row and displays the total sales.

Division

SELECT Price / Quantity AS Price_Unit FROM sales;

Output:

| Price_Unit |
| -----------|
| 0.2        |
| 0.075      |
| 0.167      |

Explanation: Divides "Price" by "Quantity" for each row and displays the price per unit.

Use

Arithmetic operators are used to perform mathematical calculations on numerical values in the database tables. These operators are extensively used in financial, inventory, and sales related applications.

Important Points

• The arithmetic operators can be used in conjunction with other SQL operators.
• Division by zero is not allowed and will result in an error.

Summary

Arithmetic operators (+, -, *, /) are used for performing mathematical calculations in SQL. These operators are used to add, subtract, multiply, and divide numerical values in the database tables. The result of these operations can be retrieved using the SELECT statement.