Blog

Numeric Functions in SQL for Beginners

Have you ever wondered how to manipulate numbers effortlessly within your SQL database? Welcome to the world of Numeric Functions in SQL! These are powerful tools that allow you to perform various calculations and transformations on numerical data. Whether you’re rounding off decimal figures or calculating square roots, numeric functions make SQL a mathematician’s playground. From simple arithmetic to complex number crunching, these functions save both time and effort. If you’re ready to dive into the practical side of data manipulation and uncover the magic behind these fascinating functions, stick around as we unravel them step by step!

1. ROUND Function in SQL

Syntax and Purpose

The ROUND() function rounds a numeric value to the specified number of decimal places.

Syntax:

ROUND(number, decimal_places)
  • number: The value to be rounded.
  • decimal_places: The number of decimal places to round to. If omitted, it defaults to 0.

Examples

Rounding to Two Decimal Places:

SELECT ROUND(123.456, 2);

Output:

123.46

Rounding to Whole Number:

SELECT ROUND(123.789);

Output:

124

Use Cases in Financial Calculations

  • Currency rounding: Ensuring amounts have two decimal places in banking applications.
  • Tax calculations: Rounding tax percentages for accurate billing.
  • Stock prices: Adjusting prices to standard formats in financial databases.

2. CEIL (CEILING) Function in SQL

Explanation of CEIL Function

The CEIL() function rounds a number up to the nearest integer.

Syntax and Usage

CEIL(number)
  • number: The value to be rounded up.

Examples

Rounding Up Positive Numbers:

SELECT CEIL(12.3);

Output:

13

Rounding Up Negative Numbers:

SELECT CEIL(-12.7);

Output:

-12

Applications in Inventory Management and Allocations

  • Stock allocation: Ensuring whole units are assigned when partial values occur.
  • Shipping calculations: Rounding package weights to the next whole number.
  • Seat booking systems: Ensuring minimum full seats are occupied.

3. FLOOR Function in SQL

Definition and Purpose

The FLOOR() function rounds a number down to the nearest integer.

Syntax and Usage

FLOOR(number)
  • number: The value to be rounded down.

Examples

Rounding Down Positive Numbers:

SELECT FLOOR(12.8);

Output:

12

Rounding Down Negative Numbers:

SELECT FLOOR(-12.3);

Output:

-13

Relevance in Scenarios Like Pagination and Batching

  • Pagination: Calculating total pages for results (ensuring page numbers don’t exceed limits).
  • Batch processing: Assigning a whole number of batches for manufacturing processes.
  • Discount calculations: Rounding down percentage discounts to prevent over-reduction.

4. ABS Function in SQL

Purpose of the ABS Function

The ABS() function returns the absolute (non-negative) value of a given number, removing any negative sign.

Syntax and Usage

ABS(number)
  • number: The numeric value whose absolute value is needed.

Examples

Finding Absolute Values:

SELECT ABS(-25);

Output:

25

SELECT ABS(10.5);

Output:

10.5

Use Cases in Calculating Distances and Differences

  • Distance calculations: Ensuring distances remain positive when computing differences in latitude/longitude.
  • Stock market analysis: Measuring absolute price changes regardless of increase or decrease.
  • Error margin calculations: Determining deviation from expected values without negative results.

5. TRUNCATE Function in SQL

Explanation of the TRUNCATE Function

The TRUNCATE() function removes decimal places from a number without rounding it.

Syntax and Usage

TRUNCATE(number, decimal_places)
  • number: The value to be truncated.
  • decimal_places: The number of decimal places to keep.

Examples

Truncating to Two Decimal Places:

SELECT TRUNCATE(123.456, 2);

Output:

123.45

Truncating to an Integer:

SELECT TRUNCATE(98.76, 0);

Output:

98

Applications in Data Reporting and Formatting

  • Financial reporting: Formatting currency values without rounding errors.
  • Scientific data processing: Removing unnecessary decimal precision.
  • Leaderboards: Displaying scores without rounding inaccuracies.

6. MOD Function in SQL

Definition and Purpose

The MOD() function returns the remainder of a division operation.

Syntax and Usage

MOD(dividend, divisor)
  • dividend: The number to be divided.
  • divisor: The number to divide by.

Examples

Finding Remainders:

SELECT MOD(10, 3);

Output:

1

SELECT MOD(15, 5);

Output:

0

Use Cases in Cyclic Events and Grouping Data

  • Distributing items into groups: Assigning employees to shifts based on MOD(employee_id, total_shifts).
  • Detecting even or odd numbers: Checking if MOD(number, 2) = 0 (even) or 1 (odd).
  • Scheduling events: Determining recurring events using modular arithmetic.

Here’s a detailed breakdown of the POWER and SQRT functions in SQL:

7. POWER Function in SQL

Purpose of the POWER Function

The POWER() function calculates the exponentiation of a number, raising it to a specified power.

Syntax and Usage

POWER(base, exponent)
  • base: The number to be raised to a power.
  • exponent: The power to which the base is raised.

Examples

Squaring a Number:

SELECT POWER(5, 2);

Output:

25

Calculating a Cube:

SELECT POWER(3, 3);

Output:

27

Using a Fractional Exponent (Square Root Equivalent):

SELECT POWER(16, 0.5);

Output:

4

Applications in Scientific Computations and Growth Projections

  • Compound interest calculations: SELECT 1000 * POWER(1.05, 5) AS future_value; This calculates the future value of ₹1000 invested at 5% annual interest over 5 years.
  • Physics and engineering formulas: Computing force, energy, or wave frequencies.
  • Data growth predictions: Modeling exponential user growth in applications.

8. SQRT Function in SQL

Explanation of the SQRT Function

The SQRT() function calculates the square root of a given number.

Syntax and Usage

SQRT(number)
  • number: The value whose square root needs to be calculated.

Examples

Finding the Square Root of a Number:

SELECT SQRT(25);

Output:

5

Square Root of a Non-Perfect Square:

SELECT SQRT(50);

Output:

7.071

Relevance in Statistical Analysis and Geometry

  • Statistical analysis: Used in standard deviation and variance calculations.
  • Geometry and physics: Calculating distances using the Pythagorean theorem.
  • Machine learning: Normalizing data in clustering and regression models.

Practical Applications of Numeric Functions in SQL

1. Combining Multiple Numeric Functions in Complex Queries

SQL numeric functions can be used together in queries to enhance data processing and analysis.

Example: Calculating Compound Interest

SELECT ROUND(1000 * POWER(1.05, 5), 2) AS future_value;

Output:

1276.28

🔹 Functions used: POWER() for exponentiation, ROUND() for formatting.
🔹 Use case: Financial forecasting for investments.

Example: Normalizing Data in a Dataset

SELECT 
    (value - MIN(value) OVER ()) / NULLIF(ABS(MAX(value) OVER () - MIN(value) OVER ()), 0) AS normalized_value 
FROM data_table;

🔹 Functions used: ABS(), MIN(), MAX().
🔹 Use case: Preparing data for machine learning models.

2. Real-World Scenarios Where Numeric Functions Enhance Data Processing

A. E-commerce & Inventory Management

🔹 Ensuring whole units for packaging:

SELECT CEIL(quantity / 10) AS total_boxes FROM orders;

📌 Use case: Allocating shipping boxes for bulk orders.

🔹 Calculating discounts dynamically:

SELECT price, MOD(price, 100) AS discount_applied FROM products;

📌 Use case: Applying discounts based on price brackets.

B. Banking & Finance

🔹 Rounding financial transactions:

SELECT ROUND(balance, 2) FROM accounts;

📌 Use case: Ensuring monetary values remain accurate in statements.

🔹 Calculating monthly EMI payments:

SELECT ROUND((loan_amount * POWER(1 + rate/100, tenure) * rate/100) / 
      (POWER(1 + rate/100, tenure) - 1), 2) AS EMI
FROM loans;

📌 Use case: EMI calculation for personal and home loans.

C. Statistical Analysis & Scientific Research

🔹 Calculating standard deviation:

SELECT SQRT(AVG(POWER(salary - avg_salary, 2))) AS std_dev FROM employees;

📌 Use case: Determining salary variations within an organization.

🔹 Finding the closest integer to an experimental value:

SELECT FLOOR(temperature) FROM sensor_readings;

📌 Use case: Converting continuous temperature values into whole numbers for reports.


From helping banks predict profits to optimizing online store prices, Numeric Functions in SQL have significant applications. Isn’t it fascinating how these simple functions power such diverse tasks? Understanding and harnessing them can truly revolutionize how you handle data. Happy coding!

Curious to test this out yourself? With our AI-powered sql online compiler, you can instantly write, run, and test your code. It’s interactive and gives you instant results―making learning SQL a breeze! Isn’t that handy for practice?

Conclusion

Numeric Functions in SQL are crucial for managing data effectively and simplifying complex calculations. Harness these powerful tools to elevate your SQL skills. For a deeper dive into programming, visit Newtum. Explore, learn, and start mastering databases today!

Edited and Compiled by

This blog was compiled and edited by @rasikadeshpande, who has over 4 years of experience in content creation. She’s passionate about helping beginners understand technical topics in a more interactive way.

About The Author

Rasika Deshpande

Rasika Deshpande, a passionate writer with over 4 years of experience in the EdTech industry. At Newtum, she writes, edit, and compile engaging blog and other content. Writing is not just a profession for her; it’s a passion that fuels creativity and dedication to the field of writing.

See author's posts