SQL Arithmetic Operators

SQL arithmetic operators are used to perform mathematical operations on numeric data in SQL. These operators can be used in SQL SELECT, UPDATE, and DELETE statements to perform calculations on the data in the columns of a table. The most commonly used SQL arithmetic operators are:

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

Examples:

• Using the - operator to find the difference between the values in two columns:

SELECT order_total, shipping_cost, order_total - shipping_cost AS net_cost FROM orders;

• Using the * operator to multiply the values in a column by a constant:

SELECT quantity, price, quantity * price AS total_cost FROM products;

• Using the / operator to divide the values in a column by a constant:

SELECT total_hours, hourly_rate, total_hours / 8 AS total_days FROM employees;

• Using the % operator to find the remainder of a division:

SELECT order_id, quantity, order_id % 5 AS remainder FROM orders;

• Using multiple operators in a single statement:

SELECT price, discount, price - (price * (discount / 100)) AS final_price FROM products;

• Using the arithmetic operator in the WHERE clause:

SELECT * FROM employee WHERE salary > 80000 + 30000;

• You can also use the arithmetic operator with subquery and join:

SELECT e.first_name, e.last_name, e.salary, d.budget, e.salary + d.budget as total_budget
FROM employee e
JOIN department d
ON e.department_id = d.department_id;

• You can also use the arithmetic operator in the GROUP BY clause:

SELECT product_name, SUM(quantity*price) as total_sales
FROM products
GROUP BY product_name;

• Using the + operator to concatenate columns:

SELECT first_name, last_name, first_name + ' ' + last_name AS full_name FROM employees;

• Using the - operator to find the difference between current date and a date column:

SELECT order_date, CURRENT_DATE, CURRENT_DATE - order_date AS days_since_order FROM orders;

• Using the * operator to find the product of columns:

SELECT width, height, width * height AS area FROM rooms;

• Using the % operator to find the remainder of a division of columns:

SELECT value1, value2, value1 % value2 AS remainder FROM table;

• Using the / operator to find the ratio of columns:

SELECT total_sales, total_costs, total_sales / total_costs AS profit_margin FROM company;

• Using the + operator to increment a value by a certain amount:

UPDATE employees SET salary = salary + 5000;

• Using the - operator to decrement a value by a certain amount:

UPDATE products SET stock = stock - 10 WHERE product_name = 'Widget A';

In conclusion, SQL arithmetic operators are an essetntial tool for performing mathematical operations on data in a database.