ABS and MOD functions in SQL - Mini Project: Build & Apply

Using ABS and MOD Functions in SQL

📖 Scenario: You are working with a sales database that tracks daily sales amounts, including some negative values representing returns. You want to analyze the absolute sales values and find which sales amounts are even or odd.

🎯 Goal: Build SQL queries that use the ABS function to get absolute values of sales amounts and the MOD function to find even or odd sales amounts.

📋 What You'll Learn

Create a table called daily_sales with columns sale_id (integer) and amount (integer).

Insert the exact sales data with negative and positive amounts.

Write a query to select sale_id and the absolute value of amount using ABS.

Write a query to select sale_id and amount where the amount is even using MOD.

💡 Why This Matters

🌍 Real World

Businesses often need to analyze sales data including returns (negative values) and categorize sales amounts as even or odd for reporting or processing.

💼 Career

Knowing how to use ABS and MOD functions in SQL helps database analysts and developers clean and analyze numeric data efficiently.

1

Create the daily_sales table and insert data

Create a table called daily_sales with columns sale_id as integer and amount as integer. Then insert these exact rows: (1, -50), (2, 75), (3, -20), (4, 33), (5, 40).

SQL

-- Create the daily_sales table and insert the given rows
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add the rows.

2

Write a query to select sale_id and absolute amount

Write a SQL query to select sale_id and the absolute value of amount using the ABS function from the daily_sales table.

SQL

CREATE TABLE daily_sales (sale_id INTEGER, amount INTEGER);
INSERT INTO daily_sales (sale_id, amount) VALUES
(1, -50),
(2, 75),
(3, -20),
(4, 33),
(5, 40);

-- Select sale_id and absolute amount using ABS
-- Your code here

Hint

Use SELECT sale_id, ABS(amount) AS absolute_amount FROM daily_sales;

3

Write a query to select sale_id and amount where amount is even

Write a SQL query to select sale_id and amount from daily_sales where the amount is even. Use the MOD function to check if amount modulo 2 equals 0.

SQL

CREATE TABLE daily_sales (sale_id INTEGER, amount INTEGER);
INSERT INTO daily_sales (sale_id, amount) VALUES
(1, -50),
(2, 75),
(3, -20),
(4, 33),
(5, 40);

SELECT sale_id, ABS(amount) AS absolute_amount FROM daily_sales;

-- Select sale_id and amount where amount is even using MOD
-- Your code here

Hint

Use WHERE MOD(amount, 2) = 0 to filter even amounts.

4

Write a query to select sale_id and amount where amount is odd

Write a SQL query to select sale_id and amount from daily_sales where the amount is odd. Use the MOD function to check if amount modulo 2 equals 1.

SQL

CREATE TABLE daily_sales (sale_id INTEGER, amount INTEGER);
INSERT INTO daily_sales (sale_id, amount) VALUES
(1, -50),
(2, 75),
(3, -20),
(4, 33),
(5, 40);

SELECT sale_id, ABS(amount) AS absolute_amount FROM daily_sales;

SELECT sale_id, amount FROM daily_sales WHERE MOD(amount, 2) = 0;

-- Select sale_id and amount where amount is odd using MOD
-- Your code here

Hint

Use WHERE MOD(amount, 2) <> 0 to filter odd amounts.

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