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

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

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

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

Need a hint?

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