SQL Operator: Comparison and Arithmetic

SQL Operator: Comparison and Arithmetic

4 mins read297 Views Comment
clickHere
Vikram
Vikram Singh
Assistant Manager - Content
Updated on Nov 22, 2022 19:48 IST

While working with a large dataset you have to perform a different kind of operation to extract or manipulate the the data from the dataset. To perform these operation we nee different types of operators. In this article we will discuss Comparison and Arithmetic Operator in SQL.

2022_07_SQL-OPERATORS.jpg

Similar to the other programming languages SQL has different types of operators. Operators are the reserved keywords that are use to specify the conditions in an SQL statement.

In SQL there are mainly three different types of Operators

  • Comparison Operator
  • Arithmetic Operator
  • Logical Operator

Table of Content

Comparison Operator

Comparison operators are one of the most basic ways to put filters during writing queries to extract the data, and these operators are used with the WHERE clause in SQL.

Here is the list of comparison operators in SQL

Operators Description
= Equal to
< > , != Not Equal to
> Greater than
>= Greater than or Equal
< Less than
<= Less than or Equal

Must Read: SQL SELECT

Must Read: SQL WHERE

Must Read: SQL LIMITS

Now we will take the Employee dataset (that contains Employee ID, Name, Gender, Department, Education, Month of Joining, and CTC) to do some example to get more understanding about these comparison operators.

Employee ID Name Gender Department Education Month of Joining CTC(in Lacs)
1001 Ajay M Engineering Doctoral January 25
1002 Babloo M Engineering UG February 23
1003 Chhavi F HR PG March 15
1004 Dheeraj M HR UG January 12
1005 Evina F Marketing UG March 16
1006 Fredy M Sales UG December 10
1007 Garima F Sales PG March 10
1008 Hans M Admin PG November 8
1009 Ivanka F Admin Intermediate April 7
1010 Jai M Peon December 4

Must Read: Introduction to SQL

Example 1: Find the employee detail who joined in the month of January.

Query

 
SELECT *
FROM Employee
WHERE Month of Joining = ‘January’;
Copy code

Output

Employee ID Name Gender Department Education Month of Joining CTC(in Lacs)
1001 Ajay M Engineering Doctoral January 25
1004 Dheeraj M HR UG January 12

Must Read: What is the difference between SQL and MySQL?

Example 2: Find the employee details where the CTC is greater than or equal to 16.

Query

 
SELECT *
FROM Employee
WHERE CTC >= 16;
Copy code

Output

Employee ID Name Gender Department Education Month of Joining CTC(in Lacs)
1001 Ajay M Engineering Doctoral January 25
1002 Babloo M Engineering UG February 23
1003 Chhavi F HR PG March 15
1005 Evina F Marketing UG March 16

Must Read: How to Create, Insert, and Delete SQL views?

Example 3: Find the employee details where the CTC is less than 8.

Query

 
SELECT *
FROM Employee
WHERE CTC < 8;
Copy code

Output

Employee ID Name Gender Department Education Month of Joining CTC(in Lacs)
1009 Ivanka F Admin Intermediate April 7
1010 Jai M Peon December 4

Must Read: Types of Keys in Database

Must Read: Introduction to Normalization

Example 4: Find the employee detail who have not joined in the month of January.

Query

 
SELECT *
FROM Employee
WHERE Month of Joining != ‘January’;
Copy code

Output

Employee ID Name Gender Department Education Month of Joining CTC(in Lacs)
1002 Babloo M Engineering UG February 23
1003 Chhavi F HR PG March 15
1005 Evina F Marketing UG March 16
1006 Fredy M Sales UG December 10
1007 Garima F Sales PG March 10
1008 Hans M Admin PG November 8
1009 Ivanka F Admin Intermediate April 7
1010 Jai M Peon December 4

Now, let’s discuss Arithmetic operators in SQL.

Must Read: SQL Joins

Must Read: Window Function in SQL

Arithmetic Operators

Similar to Comparison Operator, SQL has five different Arithmetic Operators, let’s explore them:

Operator Description
+ Add
Subtract
* Multiply
/ Divide
% Modulo

Now we will take the Sales dataset (that contains Month and Region-wise Sales in North, East, West, and South region) to do some examples to get more understanding about these arithmetic operators.

Month North (in lacs) East (in lacs) West (in lacs) South (in lacs)
January 10.4 11.5 12.3 12.1
February 11.3 13.4 17.6 19.3
March 10.7 32.3 23.3 21.7
April 12.6 21.2 23.5 7.45
May 13.8 23.4 33.7 43.4
June 12.3 31.4 11.5 11.4
July 15.8 12.3 12.02 14.7
August 13.4 17.3 23.5 22.7
September 23.7 43.8 45.6 44.9
October 34.1 24.7 23.8 22.7
November 32.5 25.6 31.7 41.9
December 30.6 29.8 28.7 32.5

Example 1: Find the sum of sales of the region North and South.

Query

 
SELECT Month, North, South, North + South
FROM Sales;
Copy code

Output

Month North South North + South
January 10.4 12.1 22.5
February 11.3 19.3 30.6
March 10.7 21.7 32.4
April 12.6 7.45 20.05
May 13.8 43.4 57.2
June 12.3 11.4 23.7
July 15.8 14.7 30.5
August 13.4 22.7 36.1
September 23.7 44.9 68.6
October 34.1 22.7 56.8
November 32.5 41.9 74.4
December 30.6 32.5 63.1

Must Read: Subqueries in SQL

Must Read: Aggregate and Scalar Function in SQL

Example 2: Subtract 10 from the combined sales of North + South and then multiply 2 in the final result.

Query

 
SELECT Month, North, South,
(North + South -10) * 2 AS New Record
FROM Sales;
Copy code

Output

Month North South New Record
January 10.4 12.1 25
February 11.3 19.3 41.2
March 10.7 21.7 44.8
April 12.6 7.45 20.10
May 13.8 43.4 94.4
June 12.3 11.4 27.4
July 15.8 14.7 41
August 13.4 22.7 52.2
September 23.7 44.9 117.2
October 34.1 22.7 93.6
November 32.5 41.9 128.8
December 30.6 32.5 106.2

Example 3: Find the average sales of North and South region.

Query

 
SELECT Month, North, South
(North + South) / 2 AS Avg Sales
FROM Sales;
Copy code

Output

Month North South Avg Sales
January 10.4 12.1 11.25
February 11.3 19.3 15.3
March 10.7 21.7 16.2
April 12.6 7.45 10.025
May 13.8 43.4 28.6
June 12.3 11.4 11.85
July 15.8 14.7 15.25
August 13.4 22.7 18.05
September 23.7 44.9 34.3
October 34.1 22.7 28.4
November 32.5 41.9 37.2
December 30.6 32.5 31.55

Must Read: Difference between SQL and NoSQL

Must Check: SQL Online Course and Certifications

Conclusion

In this article, we have discussed comparison and arithmetic operators in SQL in the most simplified way with the help of examples.

Hope this article will help you in Data Science/Data Analysis journey.

About the Author
author-image
Vikram Singh
Assistant Manager - Content

Vikram has a Postgraduate degree in Applied Mathematics, with a keen interest in Data Science and Machine Learning. He has experience of 2+ years in content creation in Mathematics, Statistics, Data Science, and Mac... Read Full Bio

Comments