EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials SQL Tutorial SQL Arithmetic Operators

SQL Arithmetic Operators

Priya Pedamkar
Article byPriya Pedamkar

Updated March 28, 2023

SQL Arithmetic Operators

Introduction to SQL Arithmetic Operators

The arithmetic operators in SQL are used to perform mathematical operations such as addition, subtraction, multiplication, division, and modulus, etc. on the data stored in the database tables and these arithmetic operators can be used along with a WHERE clause in an SQL statement if there are multiple conditions which need to be satisfied in the query, but in case there is any Null value present in the table, upon performing arithmetic operations on the Null value, we will get the result as Null.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Main Top 5 Arithmetic Operators in SQL

The various arithmetic operators in SQL are addition (+), subtraction (-), multiplication (*), division (/) and modulus (%) which are used to perform the mathematical operations on the data which is stored in the database tables. Let us go through the below examples to understand the working of various arithmetic operators in SQL.

1. Addition Operator (+)

The operator ‘+’ is used to perform addition operation on two operands. In the below example, we can see that the addition operator is used to add 100 and 220.

SELECT 100 + 220 as Addition;

Output:

SQL arithmetic operators - SELECT 100+220

Let us take the example of a table “EMPLOYEES” as below to understand how the operators work.

SQL arithmetic operators -EMPLOYEES

The table contains various employees and their details. Let us perform the addition operation on the column SALARY. In the below query, 10000 is added to the SALARY column.

SELECT SALARY+10000 as new_salary FROM EMPLOYEES;

Output:

SQL arithmetic operators -SALARY+10000

Let us perform the addition of two columns using the addition operator as shown in the below query.

SELECT SALARY+ID as added_salary FROM EMPLOYEES;

The result of the above query can be seen below where the salary and ID columns are added.

SQL arithmetic operators - SALARY+ID

2. Subtraction Operator (-)

The subtraction operator ‘-’ is used to subtract the right-hand operand from the left-hand operand. Let us take the below example to perform subtraction of 99 from 260.

SELECT 260-99 as Subtract;

Output:

SQL arithmetic operators -SELECT 260-99

Let us now take the example of the previously stated table “EMPLOYEES”. In the below query, we can see that 5500 is subtracted from the SALARY column.

SELECT SALARY-5500 as Subtracted_Sal FROM EMPLOYEES;

Output:

Subtracted_Sal FROM EMPLOYEES

In the below query, the subtraction operation of two columns i.e. SALARY and ID is shown.

SELECT SALARY-ID as New_Sal FROM EMPLOYEES;

We can see that in the above query, the ID column is subtracted from the SALARY columns and the result of the subtraction operation is as shown below.

New_Sal FROM EMPLOYEES

3. Multiplication Operator (*)

This operator performs the multiplication of two operands. In the below example, we can see the multiplication of 99 and 89.

SELECT 99*89 as Multiplication;

Output:

SQL arithmetic operators -as Multiplication

Considering the previously stated EMPLOYEES table, let us perform the multiplication of the column SALARY with 15 as shown below.

SELECT SALARY*15 as Multiplied_Salary FROM EMPLOYEES;

The result of the above query can be seen below and we can see that 15 is multiplied with the SALARY column.

Multiplied_Salary FROM EMPLOYEES 1

In the below query, we can see that the multiplication of two columns i.e. SALARY and ID from the table EMPLOYEES is done.

SELECT SALARY*ID as New_Salary FROM EMPLOYEES;

Output:

Multiplied_Salary FROM EMPLOYEES 2

4. Division Operator (/)

This operator performs the division of the left-hand side operand by the right-hand side operand. In the below example, the division operation is performed where 79 is divided by 6.

SELECT 79/6 as Division;

The below result shows that the above operation gives the quotient of the division i.e. 13 as output.

SQL arithmetic operators - as Division

Let us again consider the previously stated table “EMPLOYEES”. The below query shows the division operation performed where the column SALARY is divided by 50.

SELECT SALARY/50 as Sal_Div FROM EMPLOYEES;

Output:

division operation 2

5. Modulus Operator (%)

This arithmetic operator is used to get the remainder of the division of the left-hand side operand by the right-hand side operand.

SELECT 23%4 as result;

The above query shows that 23 when divided by 4, gives the remainder of the division which is 3 as output as displayed in the result.

Modulus

The modulus operation is performed on the SALARY column of the EMPLOYEES table below.

SELECT SALARY%100 as result FROM EMPLOYEES;

The above query performs the modulus operation and when the salary of the employees is divided by 100, the result shows the remainder values of the division which is 0.

Modulus- SALARY%100

If a table contains any Null value, then on performing any arithmetic operations on the Null values, we get the result as Null.

Let us consider the below EMPLOYEES table with Null value as shown below.

Modulus - EMPLOYEES

Let us now perform addition and multiplication operation on the SALARY column which also contains a Null value.

SELECT SALARY+500 as result FROM EMPLOYEES;

Modulus -SALARY+500

SELECT SALARY*5 as result FROM EMPLOYEES;

Modulus - SALARY*5

From the above output, we can see that in case a Null value is present, on performing multiplication or addition operation on it, we get the result as Null.

The arithmetic operators can be used along with the WHERE clause.

Let us take the example of the below table “STUDENTS”.

Modulus - STUDENTS

If we want to calculate the sum of the marks secured by the students greater than 150, then we can use the arithmetic operator along with the WHERE clause as shown in the below query.

SELECT NAME, MATHS_MARK+SCIENCE_MARK FROM STUDENTS WHERE MATHS_MARK+SCIENCE_MARK > 150;

The above query will give the output as below where the student who has secured the sum of the marks greater than 150 is displayed.

Greater than 150

Conclusion

The SQL arithmetic operators are vital for performing important mathematical operations on the data stored in the tables in a database. It is essential for the developers to have a good understanding of these operators.

Recommended Articles

We hope that this EDUCBA information on “SQL Arithmetic Operators” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. SQL Set Operators
  2. PostgreSQL Versions
  3. Custom SQL in Tableau
  4. SQL Clauses
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
All in One Data Science Bundle2000+ Hour of HD Videos | 80 Learning Paths | 400+ Courses | Verifiable Certificate of Completion | Lifetime Access
Financial Analyst Masters Training Program2000+ Hours of HD Videos | 43 Learning Paths | 550+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more