SQL

🗓️ Day 3: SQL Number & Null Functions

Photo of Mathimaa Mathimaa3 weeks ago
0 1 2 minutes read

🗓️ Day 3: SQL Number & Null Functions

Welcome to Day 3 of your SQL learning series!
In this session, we’ll explore Number Functions and Null Functions — two important categories that help us handle numeric values and missing data effectively in SQL.

🔢 Number Functions in SQL

Number functions allow you to perform mathematical operations on numeric data.
Let’s look at some of the most commonly used ones.

1. ROUND()

Purpose: Rounds a number to the nearest integer or specified decimal place.

Logic:

  • If the next decimal is ≥ 5 → rounds up

  • If the next decimal is < 5 → remains same

Example:

SELECT ROUND(200.4), ROUND(200.6) FROM dual;
-- Output: 200 | 201

SELECT ROUND(987.5) FROM dual;
-- Output: 988

2. TRUNC()

Purpose: Truncates (removes) the decimal part of a number without rounding.

Example:

SELECT TRUNC(980.873526) FROM dual;
-- Output: 980

3. ABS()

Purpose: Returns the absolute value of a number — removes any negative sign.

Example:

SELECT ABS(900), ABS(-900) FROM dual;
-- Output: 900 | 900

4. CEIL()

Purpose: Returns the next highest integer greater than or equal to the number.

Example:

SELECT CEIL(400.2), CEIL(350.9) FROM dual;
-- Output: 401 | 351

5. FLOOR()

Purpose: Returns the next lowest integer less than or equal to the number.

Example:

SELECT FLOOR(600.7324) FROM dual;
-- Output: 600

6. SIGN()

Purpose: Identifies whether a number is positive, negative, or zero.

Input Output Meaning
Positive number 1 Greater than 0
Negative number -1 Less than 0
Zero 0 Equal to 0

Example:

SELECT SIGN(10000000), SIGN(-2356204797104) FROM dual;
-- Output: 1 | -1

🧩 NULL Functions in SQL

NULL represents a missing or unknown value in SQL.
These functions help handle such null values effectively in queries.

1. NVL()

Purpose: Replaces a NULL value with a specified value.

Syntax:

NVL(expression, replacement_value)

Example:

SELECT commission_pct, NVL(commission_pct, 0) FROM employees;
-- If commission_pct is NULL, it shows 0

2. NVL2()

Purpose: Returns one value if not null, otherwise another value.

Syntax:

NVL2(expression, value_if_not_null, value_if_null)

Example:

SELECT commission_pct, NVL2(commission_pct, 1000, 100000) FROM employees;
-- If commission_pct has a value → 1000
-- If NULL → 100000

3. NULLIF()

Purpose: Compares two expressions.
If they are equal, returns NULL.
If different, returns the first expression.

Example:

SELECT NULLIF('PowerBI', 'PowerBI') FROM dual; -- Output: NULL
SELECT NULLIF('PowerBI', 'ORACLE') FROM dual; -- Output: PowerBI

4. COALESCE()

Purpose: Returns the first non-null value from a list of expressions.

Example:

SELECT COALESCE(NULL, NULL, NULL, 'DA', NULL, 'PowerBI') FROM dual;
-- Output: DA

If all values are NULL → output will be NULL.

💻 Practice Examples

Here are some simple queries to test what you’ve learned:

SELECT * FROM employees;

SELECT ROUND(200.4), ROUND(200.6) FROM dual;
SELECT TRUNC(980.873526) FROM dual;
SELECT ABS(-900) FROM dual;
SELECT CEIL(400.2), FLOOR(400.2) FROM dual;
SELECT SIGN(-250), SIGN(500), SIGN(0) FROM dual;

SELECT commission_pct, NVL(commission_pct, 0) FROM employees;
SELECT NVL2(commission_pct, 1000, 100000) FROM employees;
SELECT NULLIF('PowerBI','ORACLE') FROM dual;
SELECT COALESCE(NULL,NULL,'SQL','PowerBI') FROM dual;

🧠 Quick Summary

Category Function Description
Number ROUND Rounds a number
TRUNC Removes decimals
ABS Removes negative sign
CEIL Rounds up
FLOOR Rounds down
SIGN Identifies sign
Null NVL Replace NULL with a value
NVL2 Conditional replacement
NULLIF Returns NULL if both same
COALESCE Returns first non-null value
Photo of Mathimaa Mathimaa3 weeks ago
0 1 2 minutes read

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button