Understanding Numeric Functions in SQL

SQL provides various numeric functions that help perform mathematical operations on numeric data. These functions are useful for calculations, rounding, and other numerical transformations.

Common Numeric Functions

ABS(): Returns the absolute value of a number.
CEILING(): Rounds a number up to the nearest integer.
FLOOR(): Rounds a number down to the nearest integer.
ROUND(): Rounds a number to a specified number of decimal places.
POWER(): Returns the value of a number raised to a given power.
SQRT(): Returns the square root of a number.
EXP(): Returns the exponential value of a number.
LOG(): Returns the natural logarithm of a number.
LOG10(): Returns the base-10 logarithm of a number.
RAND(): Returns a random float value between 0 and 1.
SIGN(): Returns the sign of a number (-1, 0, or 1).
PI(): Returns the value of PI (3.14159265358979).
DEGREES(): Converts radians to degrees.
RADIANS(): Converts degrees to radians.
MOD(): Returns the remainder of a division.
TRUNCATE(): Truncates a number to a specified decimal place.

Example Usage of Numeric Functions

1. Using ABS() Function

SELECT ABS(-15) AS AbsoluteValue;

Output. 15

2. Using CEILING() and FLOOR() Functions

SELECT CEILING(4.3) AS CeilValue, FLOOR(4.7) AS FloorValue;

Output

CeilValue	FloorValue
5	4

3. Using ROUND() and TRUNCATE() Functions

SELECT ROUND(123.456, 2) AS RoundedValue, TRUNCATE(123.456, 2) AS TruncatedValue;

Output

RoundedValue	TruncatedValue
123.46	123.45

4. Using POWER() and SQRT() Functions

SELECT POWER(5, 3) AS PowerValue, SQRT(25) AS SquareRoot;

Output

PowerValue	SquareRoot
125	5

5. Using MOD() Function

SELECT MOD(10, 3) AS ModResult;

Output. 1

6. Using PI(), DEGREES(), and RADIANS() Functions

SELECT 
    PI() AS PiValue, 
    DEGREES(PI()) AS DegreesValue, 
    RADIANS(180) AS RadiansValue;

Output

PiValue	DegreesValue	RadiansValue
3.141593	180	3.141593

When to Use Numeric Functions?

Financial Calculations: Useful for interest rates, tax calculations, and rounding amounts.
Data Analysis: Helps in statistical computations and mathematical transformations.
Scientific Computing: Essential for performing complex mathematical calculations.
Random Value Generation: Used for sampling, simulations, and random selections.

Advantages of Numeric Functions

Simplifies mathematical computations in SQL.
Enhances query efficiency by using built-in SQL functions.
Provides precise and accurate results for calculations.

Numeric functions play a crucial role in SQL for performing various mathematical operations.