SQL Operator: Comparison and Arithmetic

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.

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
• Arithmetic Operator

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

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

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

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

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

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

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

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.