SQL Arithmetic Operators

In Structured Query Language, the arithmetic operators are used to perform mathematical operations on the numerical values stored in the database tables.

We can use these operators with the SELECT statement in SQL. We can also use the WHERE clause in the SELECT statement for performing operations on particular rows.

These types of operators are used between two numerical operands for performing addition, subtraction, multiplication, and division operations.

The arithmetic operators in SQL are categorized into the following five types:

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

SQL Addition Operator (+)

The SQL Addition Operator performs the addition on the numerical columns in the table.

If you want to add the values of two numerical columns in the table, then you have to specify both columns as the first and second operand. You can also add the new integer value in the value of the integer column.

Syntax of SQL Addition Operator:

SELECT Column_Name_1 Addition_Operator Column_Name2 FROM Table_Name;

Addition Operator with WHERE Clause

The addition operator can also be used with the WHERE clause in the SQL SELECT query.

The syntax for using the WHERE clause with the addition operator is given below:

SELECT Column_Name_1 Addition_Operator Column_Name2 FROM Table_Name WHERE Condition;

Implementation of Addition operator in SQL:

The following CREATE query creates the Employee table with five fields:

CREATE TABLE Employee
(
Employee_ID INT AUTO_INCREMENT PRIMARY KEY,
Emp_Name VARCHAR (50),
Emp_City VARCHAR (20),
Emp_Salary INT NOT NULL,
Emp_Bonus INT NOT NULL
) ;

The following INSERT query inserts the record of employees into the Employee table:

INSERT INTO Employee (Employee_ID, Emp_Name, Emp_City, Emp_Salary, Emp_Bonus) VALUES (101, Anuj, Ghaziabad, 25000, 2000),
(102, Tushar, Lucknow, 29000, 1000),
(103, Vivek, Kolkata, 35000, 2500),
(104, Shivam, Goa, 22000, 3000);

The following SELECT query shows the data of the Employee table:

SELECT * FROM Employee;

Employee_Id	Emp_Name	Emp_City	Emp_Salary	Emp_bonus
101	Anuj	Ghaziabad	25000	2000
102	Tushar	Lucknow	29000	1000
103	Vivek	Kolkata	35000	2500
104	Shivam	Goa	22000	3000

The following query adds the Emp_Salary and Emp_Bonus of each employee of the Employee table using the addition operator:

SELECT Emp_Salary + Emp_Bonus AS Emp_Total_Salary FROM Employee;

Output:

SQL Arithmetic Operators

The following query adds 15000 to the salary of each employee in the Emp_Salary column of the Employee table:

SELECT Emp_Salary + 15000 AS Emp_Updated_Salary FROM Employee;

Output:

SQL Arithmetic Operators

The following query performs the addition operation on the above Employee table with the WHERE clause:

SELECT Emp_Salary + Emp_Bonus AS Emp_Total_Salary FROM Employee WHERE Emp_Salary > 25000;

It shows only records of those employees whose Emp_Salary is greater than 25000:

Output:

SQL Arithmetic Operators

SQL Subtraction Operator (-)

The SQL Subtraction Operator performs the subtraction on the numerical columns in the table.

If we want to subtract the values of one numerical column from the values of another numerical column, then we have to specify both columns as the first and second operand. We can also subtract the integer value from the values of the integer column.

Syntax of SQL Subtraction Operator:

SELECT Column_Name_1 Subtraction_Operator Column_Name2 FROM Table_Name;

Subtraction Operator with WHERE Clause

The subtraction operator can also be used with the WHERE clause in the SELECT query.

The syntax for using the WHERE clause with the subtraction operator is given below:

SELECT Column_Name_1 Subtraction_Operator Column_Name2 FROM Table_Name WHERE Condition;

Implementation of Subtraction operator in SQL:

The following CREATE query creates the Employee table with five fields:

CREATE TABLE Employee
(
Employee_ID INT AUTO_INCREMENT PRIMARY KEY,
Emp_Name VARCHAR (50),
Emp_City VARCHAR (20),
Emp_Salary INT NOT NULL,
Emp_Panelty INT NOT NULL
) ;

The following INSERT query inserts the record of employees into the Employee table:

INSERT INTO Employee (Employee_ID, Emp_Name, Emp_City, Emp_Salary, Emp_Bonus) VALUES (101, Anuj, Ghaziabad, 25000, 500),
(102, Tushar, Lucknow, 29000, 1000),
(103, Vivek, Kolkata, 35000, 700),
(104, Shivam, Goa, 22000, 500);

The following SELECT query shows the data of the Employee table:

SELECT * FROM Employee;

Employee_Id	Emp_Name	Emp_City	Emp_Salary	Emp_Panelty
101	Anuj	Ghaziabad	25000	500
102	Tushar	Lucknow	29000	1000
103	Vivek	Kolkata	35000	700
104	Shivam	Goa	22000	500

The following query subtracts the values of the Emp_Panelty column from the Emp_Salary column of the Employee table using the subtraction operator:

SELECT Emp_Salary - Emp_Panelty AS Emp_Total_Salary FROM Employee;

Output:

SQL Arithmetic Operators

The following query performs the subtraction operation on the above Employee table with the WHERE clause:

SELECT Emp_Panelty - Emp_Salary AS Emp_Total_Salary FROM Employee WHERE Employee_ID = 104;

It shows only records of those employees whose Employee_ID is 103:

Output:

SQL Arithmetic Operators

The following query subtracts 10000 from the salary of each employee of the Employee table:

SELECT Emp_Salary - 10000 AS Emp_Updated_Salary FROM Employee;

Output:

SQL Arithmetic Operators

SQL Multiplication Operator (*)

The SQL Multiplication Operator performs the multiplication on the numerical columns in the table.

If you want to multiply the values of two numerical columns, then you have to specify both columns as the first and second operand. You can also multiply the integer value with the values of an integer column.

Syntax of SQL Multiplication Operator:

SELECT Column_Name_1 Multiplication_Operator Column_Name2 FROM Table_Name;

Multiplication Operator with WHERE Clause

The multiplication operator (*) can also be used with the WHERE clause in the SELECT query.

The syntax for using the WHERE clause with the multiplication operator is given below:

SELECT Column_Name_1 Multilplication_Operator Column_Name2 FROM Table_Name WHERE Condition;

Implementation of Multiplication operator in SQL:

The following CREATE query creates the Cars table with four fields:

CREATE TABLE Cars
(
Car_Number INT PRIMARY KEY,
Car_Name VARCHAR (50),
Car_Price INT NOT NULL,
Car_AmountINT NOT NULL
) ;

The following INSERT query inserts the record of cars into the Cars table:

INSERT INTO Cars (Car_Number, Car_Name, Car_Amount, Car_Price)
VALUES (2578, Creta, 3, 1500000),
(9258, Audi, 2, 3000000),
(8233, Venue, 6, 900000),
(6214, Nexon, 7, 1000000);

The following SELECT query shows the data of the Cars table:

SELECT * FROM Cars;

Car_Number	Car_Name	Car_Amount	Car_Price
2578	Creta	3	1500000
9258	Audi	2	3000000
8233	Venue	6	900000
6214	Nexon	7	1000000

The following query multiplies the values of the Car_Amount column with the Car_Price column of the Cars table using the Multiplication operator:

SELECT Car_Amount * Car_Price AS Car_Total_Price FROM Cars;

Output:

SQL Arithmetic Operators

The following query performs the multiplication operation on the above Cars table with the WHERE clause:

SELECT Car_Amount * Car_Price AS Car_Total_Price FROM Cars WHERE Car_Price >= 1000000;

It shows only those records of cars whose Car_Price is greater than and equal to 1000000.

Output:

SQL Arithmetic Operators

SQL Division Operator (/)

The SQL Division operator divides the numerical values of one column by the numerical values of another column.

Syntax of SQL Division Operator:

SELECT Column_Name_1 Division_Operator Column_Name2 FROM Table_Name;

Division Operator with WHERE Clause

The SQL division operator can also be used with the WHERE clause in the SELECT query.

The syntax for using the WHERE clause with the division operator is given below:

SELECT Column_Name_1 Division_Operator Column_Name2 FROM Table_Name <strong>WHERE Condition;</strong>

Implementation of Division operator in SQL:

The following CREATE query creates the Cars table with four fields:

CREATE TABLE Cars
(
Car_Number INT PRIMARY KEY,
Car_Name VARCHAR (50),
Car_Price INT NOT NULL,
Car_AmountINT NOT NULL
) ;

The following INSERT query inserts the record of cars into the Cars table:

INSERT INTO Cars (Car_Number, Car_Name, Car_Amount, Car_Price)
VALUES (2578, Creta, 3, 1500000),
(9258, Audi, 2, 3000000),
(8233, Venue, 6, 900000),
(6214, Nexon, 10, 1000000);

The following SELECT query shows the data of the Cars table:

SELECT * FROM Cars;

Car_Number	Car_Name	Car_Amount	Car_Price
2578	Creta	3	1500000
9258	Audi	2	3000000
8233	Venue	6	900000
6214	Nexon	10	1000000

The following query divides the values of the Car_Price column by the Car_Amount column of the Cars table using the Multiplication operator:

SELECT Car_Price / Car_Amount AS One_Car_Price FROM Cars;

Output:

SQL Arithmetic Operators

The following query performs the division operation on the above Cars table with the WHERE clause:

SELECT Car_Price / Car_Amount AS One_Car_Price FROM Cars WHERE Car_Number = 9258;

It shows the record of those cars whose Car_Number is 9258 from the Cars table.

Output:

SQL Arithmetic Operators

SQL Modulus Operator (%)

The SQL Modulus Operator provides the remainder when the numerical values of one column are divided by the numerical values of another column.

Syntax of Modulus Operator in SQL:

SELECT Column_Name_1 Modulus_Operator Column_Name2 FROM Table_Name;

Implementation of Modulus operator in SQL:

The following CREATE query creates the Student table with four fields:

CREATE TABLE Student
(
Student_ID INT PRIMARY KEY,
Student_Name VARCHAR (50),
Student_MathsINT,
Student_English INT NOT NULL
) ;

The following INSERT query inserts the record of the student into the Student table:

INSERT INTO Student (Student_ID, Student_Name, Student_Maths, Student_English) VALUES (201, Anuj, 30, 60),
(202, Tushar, 25, 100),
(203, Vivek, 30, 90),
(204, Shivam, 40, 80);

The following SELECT query shows the data of the Student table:

SELECT * FROM Student;

Student_Id	Student_Name	Student_Maths	Student_English
201	Anuj	30	60
202	Tushar	25	100
203	Vivek	30	90
204	Shivam	40	80

The following query divides the marks Student_English column by Marks of Student_Maths of each student in the Student table:

SELECT Student_English % Student_Maths AS Remainder FROM Student;

Output:

SQL Arithmetic Operators

The following query performs the modulus operation on the above Student table with the WHERE clause:

SELECT Student_English % Student_Maths AS Remainder FROM Student WHERE Student_Id >202;

It shows the record of those students whose Student_Id is greater than 202.

Output:

SQL Arithmetic Operators

SQL Tutorial

SQL Database

SQL Table

SQL Select

SQL Clause

SQL Order By

SQL Insert

SQL Update

SQL Delete

SQL Join

SQL Keys

Difference

SQL Injection

SQL String Functions

Miscl

SQL MCQ

PL/SQL Tutorial

Sql Interview Question

SQl Quiz