SQL Arithmetic Operators with example
SQL arithmetic operators are used to perform mathematical operations on values in SQL statements. Here are some common arithmetic operators in SQL:
Addition (+): Adds two values.
Subtraction (-): Subtracts one value from another.
Multiplication (*): Multiplies two values.
Division (/): Divides one value by another.
Modulus (%): Returns the remainder of a division operation.
Here is an example of using these arithmetic operators in SQL:
Suppose we have a table named “products” with the following structure and data:
| product_id | product_name | unit_price |
|————|————–|————|
| 1 | Product A | 10 |
| 2 | Product B | 15 |
| 3 | Product C | 20 |
Addition (+):
SELECT product_id, unit_price + 5 AS new_price
FROM products;
Subtraction (-):
SELECT product_id, unit_price – 5 AS new_price
FROM products;
Multiplication (*):
SELECT product_id, unit_price * 2 AS double_price
FROM products;
Division (/):
SELECT product_id, unit_price / 2 AS half_price
FROM products;
SELECT product_id, unit_price / 2 AS half_price
FROM products;
Modulus (%):
SELECT product_id, unit_price % 3 AS remainder
FROM products;
These queries will perform the specified arithmetic operations on the “unit_price” column and return the results accordingly.
SQL ADD Operator Description with Example
In SQL, the + operator is used for addition, which is used to add numbers together. It can be applied in various SQL statements, such as SELECT, INSERT, UPDATE, and DELETE. When used in a SELECT statement, it can be used to add numerical values or columns.
Here’s an example of using the + operator in a SELECT statement:
Suppose we have a table named “students” with the following structure and data:
| student_id | student_name | marks |
|————|————–|——-|
| 1 | John | 85 |
| 2 | Mary | 90 |
| 3 | Alice | 75 |
To add a constant value to a column, you can use the + operator as shown below:
SELECT student_id, student_name, marks + 5 AS adjusted_marks
FROM students;
This query will add 5 to the “marks” column and display the result as “adjusted_marks” in the result set.
The output will be:
| student_id | student_name | adjusted_marks |
|————|————–|—————-|
| 1 | John | 90 |
| 2 | Mary | 95 |
| 3 | Alice | 80 |
The + operator is also used to concatenate strings in some SQL databases, but this behavior might differ depending on the specific database system.
QL Subtract Operator Description with Example
In SQL, the – operator is used for subtraction. It is used to subtract one value from another. It can be used in various SQL statements such as SELECT, INSERT, UPDATE, and DELETE. When used in a SELECT statement, it can be used to subtract numerical values or columns.
Here’s an example of using the – operator in a SELECT statement:
Suppose we have a table named “orders” with the following structure and data:
| order_id | product_name | unit_price |
|———-|————–|————|
| 1 | Product A | 50 |
| 2 | Product B | 30 |
| 3 | Product C | 25 |
To subtract a constant value from a column, you can use the – operator as shown below:
SELECT order_id, product_name, unit_price – 10 AS discounted_price
FROM orders;
This query will subtract 10 from the “unit_price” column and display the result as “discounted_price” in the result set.
The output will be:
| order_id | product_name | discounted_price |
|———-|————–|——————|
| 1 | Product A | 40 |
| 2 | Product B | 20 |
| 3 | Product C | 15 |
The – operator can also be used to perform arithmetic operations between columns or variables, depending on the specific database system.
SQL Multiply Operator Description with Example
In SQL, the * operator is used for multiplication. It is used to multiply two numerical values together. It can be used in various SQL statements such as SELECT, INSERT, UPDATE, and DELETE. When used in a SELECT statement, it can be used to multiply numerical values or columns.
Here’s an example of using the * operator in a SELECT statement:
Suppose we have a table named “products” with the following structure and data:
| product_id | product_name | unit_price |
|————|————–|————|
| 1 | Product A | 10 |
| 2 | Product B | 15 |
| 3 | Product C | 20 |
To multiply a column by a constant value, you can use the * operator as shown below:
SELECT product_id, product_name, unit_price * 2 AS double_price
FROM products;
This query will multiply the “unit_price” column by 2 and display the result as “double_price” in the result set.
The output will be:
| product_id | product_name | double_price |
|————|————–|————–|
| 1 | Product A | 20 |
| 2 | Product B | 30 |
| 3 | Product C | 40 |
SQL Divide Operator Description with Example
In SQL, the / operator is used for division. It is used to divide one value by another. It can be used in various SQL statements such as SELECT, INSERT, UPDATE, and DELETE. When used in a SELECT statement, it can be used to divide numerical values or columns.
Here’s an example of using the / operator in a SELECT statement:
Suppose we have a table named “sales” with the following structure and data:
| sales_id | product_name | total_sales |
|———-|————–|————-|
| 1 | Product A | 500 |
| 2 | Product B | 750 |
| 3 | Product C | 1000 |
To divide a column by a constant value, you can use the / operator as shown below:
SELECT sales_id, product_name, total_sales / 2 AS half_sales
FROM sales;
This query will divide the “total_sales” column by 2 and display the result as “half_sales” in the result set.
The output will be:
| sales_id | product_name | half_sales |
|———-|————–|————|
| 1 | Product A | 250 |
| 2 | Product B | 375 |
| 3 | Product C | 500 |
The / operator can also be used to perform arithmetic operations between columns or variables, depending on the specific database system.
SQL Modulo Operator Description with Example
In SQL, the modulo operator (%) returns the remainder of a division operation. It can be used in various SQL statements such as SELECT, INSERT, UPDATE, and DELETE. When used in a SELECT statement, it calculates the remainder of the division of two numbers.
Here’s an example of using the modulo operator in a SELECT statement:
Suppose we have a table named “numbers” with the following structure and data:
| number_id | number_value |
|———–|————–|
| 1 | 10 |
| 2 | 7 |
| 3 | 15 |
To get the remainder of the division of a column by a constant value, you can use the modulo (%) operator as shown below:
SELECT number_id, number_value, number_value % 3 AS remainder
FROM numbers;
This query will return the remainder of the division of the “number_value” column by 3 and display the result as “remainder” in the result set.
The output will be:
| number_id | number_value | remainder |
|———–|————–|———–|
| 1 | 10 | 1 |
| 2 | 7 | 1 |
| 3 | 15 | 0 |
The modulo operator is helpful for various applications, such as determining whether a number is even or odd, or in handling cyclic operations.
| number_id | number_value |
|———–|————–|
| 1 | 10 |
| 2 | 7 |
| 3 | 15 |
To get the remainder of the division of a column by a constant value, you can use the modulo (%) operator as shown below:
SELECT number_id, number_value, number_value % 3 AS remainder
FROM numbers;
This query will return the remainder of the division of the “number_value” column by 3 and display the result as “remainder” in the result set.
The output will be:
| number_id | number_value | remainder |
|———–|————–|———–|
| 1 | 10 | 1 |
| 2 | 7 | 1 |
| 3 | 15 | 0 |
The modulo operator is helpful for various applications, such as determining whether a number is even or odd, or in handling cyclic operations.