SQL Arithmetic Operators

SQL Arithmetic Operators

SQL arithmetic operators are used to perform mathematical calculations on numeric values in SQL queries. These operators can be used in the SELECT statement to manipulate data, perform calculations, or generate computed columns.

In this tutorial, we will explore SQL arithmetic operators, their syntax, and practical examples.

---

List of SQL Arithmetic Operators

• Addition Operator
• Subtraction Operator
• Multiplication Operator
• Division Operator
• Modulus Operator

Operator	Description
+	Addition – Adds two numbers
-	Subtraction – Subtracts the right operand from the left
*	Multiplication – Multiplies two numbers
/	Division – Divides the left operand by the right
%	Modulus – Returns the remainder of division

---

Syntax of SQL Arithmetic Operators

Arithmetic operators are used in SQL queries with numeric data types. The general syntax for using an arithmetic operator in a query is:

SELECT column_name_1, value1 arithmetic_operator value2
FROM table_name;

value1 and value2 can be column names or numeric values.

Alternatively, arithmetic operations can be performed directly on numbers:

SELECT 10 + 5 AS result;

---

Step-by-Step Examples Using SQL Arithmetic Operators

1. Using Arithmetic Operators in a Query

Let’s create a products table to demonstrate arithmetic operations:

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    price DECIMAL(10,2),
    quantity INT
);

Insert some sample data:

INSERT INTO products (name, price, quantity)
VALUES 
('Laptop', 800.50, 5),
('Phone', 400.25, 10),
('Tablet', 250.75, 7);

Now, let’s use arithmetic operators to calculate the total price of each product:

SELECT name, price, quantity, (price * quantity) AS total_price
FROM products;

Explanation:

• price * quantity calculates the total price for each product.
• The result is displayed under the alias total_price.

---

2. Using Modulus Operator in SQL

Let’s demonstrate the % modulus operator with an example:

SELECT 17 % 4 AS remainder;

Explanation:

• The % operator returns the remainder of 17 ÷ 4, which is 1.
• This is useful for determining divisibility in queries.

---

Conclusion

SQL arithmetic operators allow mathematical computations in queries, making them essential for data manipulation and analysis. In this tutorial, we covered:

1. The five arithmetic operators: +, -, *, /, and %.
2. Using arithmetic operators in SQL queries.
3. Examples demonstrating calculations on data from a database.