ABS and MOD functions in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to get the absolute value of the column 'balance'.

SQL

SELECT [1](balance) FROM accounts;

Drag options to blanks, or click blank then click option'

AABS

BMOD

CROUND

DSUM

Attempts:

3 left

💡 Hint

Common Mistakes

Using MOD instead of ABS will return the remainder, not the absolute value.

Using SUM or ROUND does not change the sign of the number.

The ABS function returns the absolute value of a number, removing any negative sign.

2fill in blank

medium

Complete the code to find the remainder when 'total' is divided by 5.

SQL

SELECT [1](total, 5) FROM sales;

Drag options to blanks, or click blank then click option'

AABS

BMOD

CROUND

DFLOOR

Attempts:

3 left

💡 Hint

Common Mistakes

Using ABS will give the absolute value, not the remainder.

ROUND and FLOOR change the number but do not give remainders.

The MOD function returns the remainder of division between two numbers.

3fill in blank

hard

Fix the error in the code to correctly get the absolute value of 'debt'.

SQL

SELECT [1](debt) FROM customers;

Drag options to blanks, or click blank then click option'

AMOD

BSUM

CABS

DAVG

Attempts:

3 left

💡 Hint

Common Mistakes

Missing closing parenthesis causes syntax error.

Using MOD instead of ABS returns remainder, not absolute value.

The ABS function requires parentheses around the column name. The correct syntax is ABS(debt).

4fill in blank

hard

Fill both blanks to calculate the remainder of 'score' divided by 10 and get its absolute value.

SQL

SELECT [1]([2](score, 10)) FROM results;

Drag options to blanks, or click blank then click option'

AABS

BMOD

CROUND

DFLOOR

Attempts:

3 left

💡 Hint

Common Mistakes

Reversing the order of functions causes errors.

Using ROUND or FLOOR instead of MOD for remainder.

First, MOD(score, 10) gets the remainder. Then ABS(...) ensures the result is positive.

5fill in blank

hard

Fill all three blanks to select the absolute value of the remainder when 'amount' is divided by 7, naming the result 'mod_result'.

SQL

SELECT [1]([2](amount, 7)) AS [3] FROM payments;

Drag options to blanks, or click blank then click option'

AABS

BMOD

Cmod_result

Dtotal

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong alias name causes confusion.

Swapping ABS and MOD changes meaning.

MOD(amount, 7) gets the remainder. ABS makes it positive. AS names the column 'mod_result'.

Practice

1. What does the SQL function ABS(-15) return?

easy

A. 15

B. -15

C. 0

D. Error

Solution

1. Step 1: Understand the ABS function

   The ABS function returns the absolute value, which means it removes any negative sign from a number.

2. Step 2: Apply ABS to -15

   Applying ABS to -15 gives 15 because it converts the negative number to positive.

3. Final Answer:

   15 -> Option A

4. Quick Check:

   ABS(-15) = 15 [OK]

Hint: ABS always returns a positive number or zero [OK]

Common Mistakes:

• Thinking ABS changes positive numbers
• Confusing ABS with MOD
• Expecting ABS to return negative values

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

easy

A. SELECT MOD(17, 5);

B. SELECT REMAINDER(17, 5);

C. SELECT 17 % 5;

D. SELECT ABS(17, 5);

Solution

1. Step 1: Identify the correct function for remainder

   In standard SQL, the MOD function returns the remainder after division.

2. Step 2: Check syntax correctness

   MOD takes two arguments: dividend and divisor, so MOD(17, 5) is correct syntax.

3. Final Answer:

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

4. Quick Check:

   MOD syntax = MOD(dividend, divisor) [OK]

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

Common Mistakes:

• Using ABS instead of MOD
• Using % which is not standard SQL
• Using REMAINDER which is not standard SQL

3. What is the result of this SQL query?

SELECT ABS(-7) + MOD(20, 6) AS result;

medium

A. 2

B. 8

C. 14

D. 9

Solution

1. Step 1: Calculate ABS(-7)

   ABS(-7) returns 7 because it converts negative to positive.

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

   20 divided by 6 is 3 with remainder 2, so MOD(20, 6) = 2.

3. Step 3: Add the two results

   7 + 2 = 9.

4. Final Answer:

   9 -> Option D

5. Quick Check:

   ABS(-7) + MOD(20, 6) = 9 [OK]

Hint: Calculate ABS and MOD separately, then add [OK]

Common Mistakes:

• Adding MOD result incorrectly
• Confusing ABS with MOD
• Ignoring negative sign in ABS

4. Identify the error in this SQL query:

SELECT ABS(10, -5) FROM dual;

medium

A. MOD function should be used instead

B. ABS function takes only one argument

C. Missing FROM clause

D. ABS cannot be used with negative numbers

Solution

1. Step 1: Check ABS function argument count

   ABS accepts only one argument, but here it has two (10, -5), which is invalid.

2. Step 2: Understand correct usage

   To get remainder, MOD should be used, but the question asks about error in ABS usage.

3. Final Answer:

   ABS function takes only one argument -> Option B

4. Quick Check:

   ABS single argument only [OK]

Hint: ABS needs exactly one number inside parentheses [OK]

Common Mistakes:

• Using multiple arguments in ABS
• Confusing ABS with MOD
• Ignoring function argument rules

5. You want to find all rows in a table numbers where the absolute value of value is less than 10 and the remainder when value is divided by 3 is 1. Which SQL WHERE clause is correct?

hard

A. WHERE MOD(ABS(value), 3) = 1 AND value < 10

B. WHERE ABS(value) > 10 OR MOD(value, 3) = 1

C. WHERE ABS(value) < 10 AND MOD(value, 3) = 1

D. WHERE ABS(value) < 10 AND MOD(value, 3) = 0

Solution

1. Step 1: Understand the conditions

   We want rows where absolute value of 'value' is less than 10 and remainder of 'value' divided by 3 equals 1.

2. Step 2: Translate conditions into SQL

   ABS(value) < 10 ensures absolute value less than 10; MOD(value, 3) = 1 ensures remainder 1.

3. Step 3: Check options

   WHERE ABS(value) < 10 AND MOD(value, 3) = 1 matches both conditions exactly with AND.

4. Final Answer:

   WHERE ABS(value) < 10 AND MOD(value, 3) = 1 -> Option C

5. Quick Check:

   Use AND to combine ABS and MOD conditions [OK]

Hint: Combine ABS and MOD with AND for multiple conditions [OK]

Common Mistakes:

• Using OR instead of AND
• Mixing up MOD values
• Applying MOD to ABS result incorrectly