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

Need a 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

Need a 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

Need a 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

Need a hint?

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