SQL Tutorial

Arithmetic Operators in SQL

In SQL, arithmetic operators are used to perform mathematical operations on numeric data types. These operators are often used in SELECT queries, UPDATE statements, or even in conditions to perform calculations.

Basic Arithmetic Operators

SQL supports several arithmetic operators, which are as follows:

• + : Addition
• - : Subtraction
• * : Multiplication
• / : Division
• % : Modulus (Remainder)

Example 1: Adding Two Columns

Let's say we have a table Products with columns Price and Tax. We want to calculate the total cost of each product by adding these two columns.

SELECT ProductName, Price, Tax, (Price + Tax) AS TotalCost FROM Products;
      

In this example, the Price and Tax columns are added together using the + operator, and the result is displayed as TotalCost.

Example 2: Subtracting Two Columns

Here, we subtract the Discount from the Price to calculate the final price after discount.

SELECT ProductName, Price, Discount, (Price - Discount) AS FinalPrice FROM Products;
      

In this query, we use the - operator to subtract the Discount from the Price.

Example 3: Multiplying and Dividing

If we need to calculate the total cost by multiplying quantity and price, or calculate the average price by dividing total cost by quantity, here's how we do it:

SELECT ProductName, Quantity, Price, (Quantity * Price) AS TotalCost FROM Products;
      

In this example, we use the * operator to multiply Quantity by Price.

SELECT ProductName, (TotalCost / Quantity) AS AveragePrice FROM Products;
      

Here, we use the / operator to calculate the average price of a product by dividing the TotalCost by the Quantity.

Example 4: Modulus Operator

The % operator returns the remainder of a division. Let's calculate the remainder when dividing the StockQuantity by 2 to check if it's an odd or even number.

SELECT ProductName, StockQuantity, (StockQuantity % 2) AS IsOdd FROM Products;
      

In this example, if the remainder is 0, the product's stock quantity is even; otherwise, it's odd.

Tips for Using Arithmetic Operators

• Arithmetic operators work on numerical data types such as INT, DECIMAL, FLOAT, and more.
• Be mindful of data types when performing arithmetic to avoid unexpected results, especially with division.
• When dividing, ensure that you're not dividing by zero, which will throw an error.