Numeric Functions

Overview

Numeric functions execute mathematical, rounding, and exponential calculations on integer or decimal columns.

Learning Objectives

• Use ROUND(), CEIL(), and FLOOR() to adjust floating point values.
• Calculate absolute differences with ABS() and remainders using MOD().
• Perform advanced mathematical exponential calculations with POWER() and SQRT().

Detailed Concept Explanation

• ROUND(numeric, decimals): Rounds a value to the specified number of decimal places.
• CEIL(numeric) (or CEILING): Rounds upward to the nearest integer.
• FLOOR(numeric): Rounds downward to the nearest integer.
• ABS(numeric): Returns the absolute positive value of a number.
• MOD(dividend, divisor) (or %): Returns the remainder of a division.
• POWER(base, exponent): Raises a number to the power of another.

Code Examples

SQL

-- Round average item rating and calculate floor/ceil limits
SELECT 
  price,
  ROUND(price, 1) AS rounded_price,
  CEIL(price) AS ceiling_price,
  FLOOR(price) AS floor_price
FROM products;

-- Identify odd-numbered transactions using modulo
SELECT transaction_id, amount
FROM transactions
WHERE MOD(transaction_id, 2) != 0;

Best Practices

• Numeric Precision: When dividing integers, cast columns explicitly to decimal types (e.g., CAST(col AS DECIMAL)) to avoid automatic integer truncation (where 1 / 2 yields 0).

Interview Perspective

Interactive Challenges

Summary

Numeric functions handle mathematical rounding, absolute values, and division operations to control numerical precision.