MOD Function in SQL

The MOD is a string function in SQL which returns the remainder from the division of first number by second number.

Syntax of MOD Function

In the MOD syntax, Number1 is the dividend and Number2 is the divisor.

In the Structured Query Language, we can also use the MOD function with the columns of the table as shown in the following block:

In this syntax, we have to define the name and columns of that table on which we want to perform the MOD function.

Examples of MOD function

Example 1: This example gets the remainder by dividing 101 by 4:

Output:

Division_of_101by4
1

Example 2: This example divides 101 by 4 and returns the remainder in result:

Output:

Division_of_2by2
0

Example 3: This example divides 8 by 5 and returns the remainder in result:

Output:

Division_of_8by5
3

Example 4: This example divides 255 by 200 and returns the remainder in result:

Output:

Division_of_255by200
55

Example 5: This example uses the MOD function with the SQL table.

In this example, we will create the new table through which we will perform the MOD function on the columns of the table:

The following shows the syntax to create the new table in SQL:

The following CREATE statement creates the Product_Details table for storing the price and quantity of products:

The following multiple INSERT queries insert the records of products with their selling and purchasing price into the Product_Details table:

The following SELECT statement displays the inserted records of the above Product_Details table:

104P110945NULL2022-04-30NULL
202P41545752022-01-285
103P21825NULL2022-02-184
111P7255152021-12-259
210P61550702021-10-15NULL
212P8191102502022-01-284
112P10105508352022-04-11NULL

Query 1: The following SELECT query uses the MOD function with the Product_Quantity column of the above Product_Details table:

This query divides each product_id by 100 and returns the remainder after division.

Output:

Product_IDDivision_of_ProductID_by100
1044
2022
1033
11111
21010
21212
11212

Query 2: The following SELECT query uses the MOD function with the and Purchasing_Price and Selling_Price column of the above Product_Details table:

This query divides the purchasing price and selling price of each product by product quantity and returns the remainder.

Output:

945105NULL10-
4515075150
25187NULL18-
5255152515
50155701510
1101915250193
550100835105

Query 3: The following SELECT query uses the MOD function with the Product_Rating column of the above Product_Details table:

This query divides each rating of product by 2 and returns the remainder after division.

Output:

Product_RatingDivision_ofratingby2
NULL-
51
40
91
NULL-
40
NULL-