MOD() function in SQL

Syntax of MOD() Function in SQL

These are the syntax that can carry out the MOD() function in SQL.

 MOD(P, Q)

  Or

  P%Q

  Or 

  P mod Q

‘%’ is used to carry out the function of MOD() function in databases which don’t support the MOD() function.

Parameter of MOD() Function in SQL

In SQL, the MOD() function is used to find the remainder of a division operation. Its parameters include the dividend and the divisor. Here's a breakdown:

• Dividend: This is the numeric value you want to find the remainder for. It's the number you're dividing. 
   
• Divisor: The divisor is the numeric value you're dividing by. It represents the number of equal parts you're dividing the dividend into.

Examples of MOD function 

To see the working and use of the MOD() function in SQL, let’s look at some examples.

Example 1:

It can be used to find the remainder when one number is divided by another number.

Code

Select MOD(22,4)

Output

MOD(22,4)
2

Example 2:

It can also be used to find the remainder of a floating point number.

Code

select MOD(11.35, 3)

Output

MOD(11.35, 3)
2.35

Example 3:

MOD() function in SQL can be used in queries with clauses such as WHERE, SELECT, IF, etc.

Let’s Take an example where we create a Table Student and insert Data into the table. We can use the MOD() function to find out the students who have even roll numbers.

Table Creation

CREATE TABLE Student
{
  rollno int NOT NULL,
  FirstName VARCHAR(100) NOT NULL,
  LastName VARCHAR(100) NOT NULL,
  PRIMARY KEY(rollno)
};

Insertion

INSERT INTO Student (rollno, FirstName, LastName)
VALUES
(1, 'Apurv', 'Singh'),
(2, 'Rekha', 'Singh'),
(3, 'Himanshu', 'Singhania'),
(4, 'Sarvesh', 'Dubey'),
(5, 'Aditya', 'Badayalya'),
(6, 'Rajkumar', 'Kavuri'),
(7, 'Jinesh', 'Dunga'),
(8, 'Prajwal', 'Vellpula'),
(9, 'Rishav', 'Choudhuri'),
(10, 'Dheeraj', 'Pal');

Student

Query to find out the names of students having even roll no.

select FirstName, LastName from Student
where rollno%2=0

Output

Recommended Topic, DCL Commands in SQL
 

Frequently Asked Questions

What does MOD () do? 

MOD() in SQL returns the remainder of a division operation.

When to use MOD in SQL?

You can use MOD in SQL when you need to find the remainder in a division operation, like checking for even or odd numbers.

What is the alternative of MOD in SQL?

The alternative to MOD in SQL is the '%' symbol, which serves as the modulo operator.

What is the mod operator in SQL Plus?

In SQL Plus, the mod operator is '%', used for calculating remainders in arithmetic operations.

Conclusion 

In this article, we learned the use of the MOD() function in SQL and its Different Syntaxes. The MOD() function in SQL proves invaluable for tasks involving remainders in division operations. Whether identifying even or odd numbers or implementing intricate calculations, MOD() enhances precision and efficiency. 

Also, check out some of the Guided Paths, Contests, and Interview Experiences to gain an edge only on Coding Ninjas Studio.