SQL | Arithmetic Operators

In SQL, arithmetic operators are used to perform mathematical operations on numeric values. These operators can be used in various parts of an SQL statement, such as the SELECT, WHERE, and HAVING clauses.

Here are the basic arithmetic operators available in SQL:

Addition (+): Adds values on either side of the operator.
```
SELECT 5 + 3;  -- Returns 8
```
Subtraction (-): Subtracts the right operand from the left operand.
```
SELECT 8 - 3;  -- Returns 5
```
Multiplication (*): Multiplies values on either side of the operator.
```
SELECT 4 * 3;  -- Returns 12
```
Division (/): Divides the left operand by the right operand.
```
SELECT 8 / 2;  -- Returns 4
```

Modulo (% or MOD in some databases): Returns the remainder of the division of the left operand by the right operand.

-- Using % (like in SQL Server)
SELECT 8 % 3;  -- Returns 2

-- Using MOD function (like in Oracle)
SELECT MOD(8, 3) FROM dual;  -- Returns 2

Examples using columns:

Suppose you have a table named orders with columns price and discount.

To calculate the final price after discount:

SELECT price - discount AS final_price FROM orders;

To calculate the price including a 10% tax:

SELECT price * 1.10 AS price_with_tax FROM orders;

Points to Note:

Ensure the correct order of operations by using parentheses where needed. For example, if you want to add two values together and then divide by a third value, use parentheses: (value1 + value2) / value3.
Beware of the possibility of division by zero, which can cause errors. Some databases provide functions to handle this scenario, like SQL Server's NULLIF function.
Data type considerations: The result of an arithmetic operation can sometimes result in a different data type or might require rounding or truncating. For instance, dividing two integers might produce a decimal or float result.
Always be cautious about potential data overflow or underflow, especially when working with operations that can significantly increase the magnitude of values.

Lastly, as with all SQL functionalities, the exact support, behavior, and available features might vary slightly between different database systems. Always refer to the database system's documentation when in doubt.

Addition (+):

SELECT column1 + column2 AS sum_result
FROM your_table;

Subtraction (-):

SELECT column1 - column2 AS difference_result
FROM your_table;

Multiplication (*):

SELECT column1 * column2 AS product_result
FROM your_table;

Division (/):

SELECT column1 / column2 AS quotient_result
FROM your_table;

Modulo (%):

SELECT column1 % column2 AS modulo_result
FROM your_table;

Using Parentheses:

SELECT (column1 + column2) * column3 AS result
FROM your_table;

Arithmetic Operations with NULL Values:

SELECT column1 + COALESCE(column2, 0) AS result
FROM your_table;

SQL Floor and Ceiling Functions:

SELECT FLOOR(column1 / column2) AS floor_result,
       CEILING(column1 / column2) AS ceiling_result
FROM your_table;

Aggregate Functions with Arithmetic Expressions:

SELECT AVG(column1 * column2) AS avg_result,
       SUM(column1 + column2) AS sum_result
FROM your_table;

Combining Arithmetic with Logical Operators:

SELECT column1 * column2 AS product_result
FROM your_table
WHERE column3 > 10 AND (column4 = 'A' OR column4 = 'B');

Complex Calculations:

SELECT (column1 + column2) * column3 / column4 AS complex_result
FROM your_table
WHERE column5 IS NOT NULL;