ABS and MOD in MySQL - Mini Project: Build & Apply

Using ABS and MOD Functions in MySQL

📖 Scenario: You are managing a small store's sales data. Some sales amounts are recorded as negative numbers due to returns. You want to clean up the data by converting all sales amounts to positive numbers and find out which sales amounts are even or odd.

🎯 Goal: Build a MySQL query that uses the ABS function to get absolute values of sales amounts and the MOD function to check if the sales amounts are even or odd.

📋 What You'll Learn

Create a table called sales with columns id (integer) and amount (integer).

Insert the exact sales amounts: -15, 22, -7, 40, and -3 with ids 1 to 5 respectively.

Write a SELECT query that uses ABS(amount) to show positive sales amounts.

Add a column in the SELECT query that uses MOD(amount, 2) to show if the amount is even (0) or odd (1).

💡 Why This Matters

🌍 Real World

Stores often need to clean and analyze sales data, especially when returns cause negative values. Using ABS helps get clean positive sales figures, and MOD helps categorize sales amounts.

💼 Career

Database analysts and developers use ABS and MOD functions to prepare and analyze numeric data for reports and business decisions.

1

Create the sales table and insert data

Create a table called sales with columns id as INTEGER and amount as INTEGER. Then insert these exact rows: (1, -15), (2, 22), (3, -7), (4, 40), and (5, -3).

MySQL

-- Create the sales table and insert the data
-- Your code here

Hint

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

2

Add a query to select all sales

Write a SELECT query to get all columns from the sales table.

MySQL

CREATE TABLE sales (id INT, amount INT);
INSERT INTO sales (id, amount) VALUES
(1, -15),
(2, 22),
(3, -7),
(4, 40),
(5, -3);

-- Select all columns from sales
-- Your code here

Hint

Use SELECT * FROM sales; to get all rows and columns.

3

Use ABS to get positive sales amounts

Modify the SELECT query to show id and the absolute value of amount using ABS(amount) as positive_amount.

MySQL

CREATE TABLE sales (id INT, amount INT);
INSERT INTO sales (id, amount) VALUES
(1, -15),
(2, 22),
(3, -7),
(4, 40),
(5, -3);

-- Select id and ABS(amount) as positive_amount
-- Your code here

Hint

Use ABS(amount) to convert negative amounts to positive.

4

Add MOD to check even or odd amounts

Extend the SELECT query to also include MOD(amount, 2) as is_odd to show if the original amount is even (0) or odd (1).

MySQL

CREATE TABLE sales (id INT, amount INT);
INSERT INTO sales (id, amount) VALUES
(1, -15),
(2, 22),
(3, -7),
(4, 40),
(5, -3);

-- Select id, ABS(amount) as positive_amount, and MOD(amount, 2) as is_odd
-- Your code here

Hint

Use MOD(amount, 2) to find if the amount is even or odd.

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