ABS and MOD in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of ABS and MOD with positive numbers

What is the output of the following MySQL query?

SELECT ABS(7), MOD(7, 3);

MySQL

SELECT ABS(7), MOD(7, 3);

A -7, 1

B 7, 0

C 7, 1

D -7, 0

💡 Hint

ABS returns the positive value of a number. MOD returns the remainder of division.

ABS(7) returns 7 because 7 is already positive. MOD(7, 3) returns 1 because 7 divided by 3 leaves remainder 1.

❓ query_result

intermediate

Output of ABS and MOD with negative numbers

What is the output of this MySQL query?

SELECT ABS(-10), MOD(-10, 4);

MySQL

SELECT ABS(-10), MOD(-10, 4);

A -10, 2

B 10, 2

C 10, -2

D -10, -2

💡 Hint

ABS makes numbers positive. MOD returns remainder, always non-negative in MySQL when divisor is positive.

ABS(-10) returns 10. MOD(-10, 4) returns 2 because MySQL MOD returns a non-negative remainder when the divisor is positive.

📝 Syntax

advanced

Identify the syntax error in MOD usage

Which option contains a syntax error in using the MOD function in MySQL?

A SELECT MOD(10);

B SELECT MOD('10', 3);

C SELECT MOD(10, 3);

D SELECT MOD(-10, 3);

💡 Hint

MOD requires exactly two arguments.

MOD function requires two arguments. Option A has only one argument, causing syntax error.

❓ optimization

advanced

Optimizing ABS and MOD in a WHERE clause

Which query is the most efficient to find rows where the absolute value of column 'score' modulo 5 equals 3?

A SELECT * FROM results WHERE MOD(score, 5) = 3 OR MOD(score, 5) = -3;

B SELECT * FROM results WHERE ABS(MOD(score, 5)) = 3;

C SELECT * FROM results WHERE MOD(ABS(score), 5) = 3;

D SELECT * FROM results WHERE MOD(score, 5) = 3;

💡 Hint

MOD in MySQL always returns a non-negative remainder when divisor is positive.

In MySQL, MOD(score, 5) returns values from 0 to 4. So checking MOD(score, 5) = 3 is enough and most efficient.

🧠 Conceptual

expert

Understanding MOD behavior with negative divisor

What is the output of this MySQL query?

SELECT MOD(10, -3), MOD(-10, -3);

MySQL

SELECT MOD(10, -3), MOD(-10, -3);

A -2, -1

B 1, 1

C 1, -1

D 2, 1

💡 Hint

MOD returns remainder with sign of divisor in MySQL.

MOD(10, -3) returns -2 because 10 = floor(10/-3)*(-3) + (-2) i.e., floor(-3.333)=-4, -4*(-3)=12, 10-12=-2. MOD(-10, -3) returns -1 because floor(-10/-3)=floor(3.333)=3, 3*(-3)=-9, -10 - (-9)=-1.

Practice

1. What does the ABS function do in MySQL?

easy

A. Returns the positive value of a number

B. Returns the remainder of a division

C. Returns the square root of a number

D. Returns the largest number in a column

Solution

1. Step 1: Understand the purpose of ABS

   The ABS function converts any number to its positive equivalent, removing any negative sign.

2. Step 2: Compare with other options

   MOD returns remainder, SQRT returns square root, and MAX returns largest value, so they are incorrect.

3. Final Answer:

   Returns the positive value of a number -> Option A

4. Quick Check:

   ABS(-5) = 5 [OK]

Hint: ABS always makes numbers positive, no matter the sign [OK]

Common Mistakes:

• Confusing ABS with MOD function
• Thinking ABS returns square root
• Assuming ABS finds max value

2. Which of the following is the correct syntax to get the remainder of 17 divided by 5 in MySQL?

easy

A. SELECT 17 / 5;

B. SELECT ABS(17, 5);

C. SELECT REMAINDER(17, 5);

D. SELECT MOD(17, 5);

Solution

1. Step 1: Identify correct function for remainder

   MOD is the MySQL function that returns the remainder after division.

2. Step 2: Check syntax correctness

   MOD takes two arguments: dividend and divisor. '/' operator performs division (17/5 = 3.4), ABS takes one argument, REMAINDER is not a MySQL function.

3. Final Answer:

   SELECT MOD(17, 5); -> Option D

4. Quick Check:

   MOD(17, 5) = 2 [OK]

Hint: Use MOD(a, b) to get remainder in MySQL [OK]

Common Mistakes:

• Using ABS instead of MOD for remainder
• Trying to use '/' operator for remainder
• Using non-existent REMAINDER function

3. What is the output of this query?

SELECT ABS(-12) AS abs_val, MOD(29, 6) AS mod_val;

medium

A. abs_val = 12, mod_val = 5

B. abs_val = -12, mod_val = 5

C. abs_val = 12, mod_val = 23

D. abs_val = -12, mod_val = 23

Solution

1. Step 1: Calculate ABS(-12)

   ABS returns the positive value, so ABS(-12) = 12.

2. Step 2: Calculate MOD(29, 6)

   29 divided by 6 is 4 remainder 5, so MOD(29, 6) = 5.

3. Final Answer:

   abs_val = 12, mod_val = 5 -> Option A

4. Quick Check:

   ABS(-12) = 12, MOD(29,6) = 5 [OK]

Hint: ABS removes sign; MOD gives remainder after division [OK]

Common Mistakes:

• Forgetting ABS makes negative positive
• Mixing up MOD result with division quotient
• Assuming MOD returns division result

4. Identify the error in this MySQL query:

SELECT ABS(-7, 3), MOD(10);

medium

A. Both functions are used correctly

B. ABS function has too many arguments; MOD function missing second argument

C. ABS function missing second argument; MOD function has too many arguments

D. MOD function should be ABS

Solution

1. Step 1: Check ABS function arguments

   ABS takes only one argument, but here it has two (-7, 3), which is incorrect.

2. Step 2: Check MOD function arguments

   MOD requires two arguments, but only one (10) is provided, so it's missing the second argument.

3. Final Answer:

   ABS function has too many arguments; MOD function missing second argument -> Option B

4. Quick Check:

   ABS(-7) valid; MOD(10, x) needs two args [OK]

Hint: ABS needs 1 arg; MOD needs 2 args [OK]

Common Mistakes:

• Passing multiple arguments to ABS
• Using MOD with only one argument
• Assuming MOD can be called like ABS

5. You want to select all rows from a table numbers where the absolute value of the column val divided by 4 leaves a remainder of 1. Which query correctly achieves this?

hard

A. SELECT * FROM numbers WHERE ABS(MOD(val, 4)) = 1;

B. SELECT * FROM numbers WHERE MOD(val, 4) = 1;

C. SELECT * FROM numbers WHERE MOD(ABS(val), 4) = 1;

D. SELECT * FROM numbers WHERE ABS(val) / 4 = 1;

Solution

1. Step 1: Understand the condition

   We want the remainder after dividing the absolute value of val by 4 to be 1.

2. Step 2: Analyze each option

   SELECT * FROM numbers WHERE MOD(val, 4) = 1; uses val directly, ignoring absolute value. SELECT * FROM numbers WHERE MOD(ABS(val), 4) = 1; correctly applies ABS(val) inside MOD. SELECT * FROM numbers WHERE ABS(MOD(val, 4)) = 1; applies ABS after MOD, which can give wrong results if val is negative. SELECT * FROM numbers WHERE ABS(val) / 4 = 1; uses '/' which performs division, not remainder.

3. Final Answer:

   SELECT * FROM numbers WHERE MOD(ABS(val), 4) = 1; -> Option C

4. Quick Check:

   MOD(ABS(val), 4) = 1 filters correctly [OK]

Hint: Apply ABS inside MOD to get remainder of positive value [OK]

Common Mistakes:

• Applying ABS after MOD instead of before
• Using '/' operator for remainder
• Ignoring absolute value in condition