FLOOR

 1. What is the FLOOR Function?

The FLOOR function in Oracle SQL is used to return the largest integer less than or equal to a given number. In other words, it rounds a number down to the nearest integer, regardless of the decimal part. The FLOOR function is also known as the floor function.

2. Syntax of the FLOOR Function

The syntax for the FLOOR function is:

FLOOR(number)

• number: The numeric expression or value that you want to round down. This can be a literal number, a column, or the result of an expression or calculation.

3. How Does the FLOOR Function Work?

The FLOOR function takes the given number and rounds it down to the largest integer that is less than or equal to the number.

• For positive numbers, the function will remove the decimal part and round down to the next smaller integer.
• For negative numbers, it rounds down towards the most negative integer.

4. Examples of Using the FLOOR Function

• Example 1: Using FLOOR with a Positive Number

If you use FLOOR with a positive decimal number, it removes the decimal part and rounds down to the next lower integer.

SELECT FLOOR(5.7) FROM dual;

-- Output: 5

Here, 5.7 is rounded down to 5.

• Example 2: Using FLOOR with a Negative Number

If you use FLOOR with a negative decimal number, it rounds down to the next more negative integer.

SELECT FLOOR(-5.7) FROM dual;

-- Output: -6

In this case, -5.7 is rounded down to -6, not -5.

• Example 3: Using FLOOR with an Integer

If the value is already an integer, the FLOOR function returns the same integer.

SELECT FLOOR(6) FROM dual;

-- Output: 6

Here, 6 remains 6, since it's already an integer.

• Example 4: Using FLOOR with Zero

For 0 or 0.0, the FLOOR function returns 0.

SELECT FLOOR(0) FROM dual;

-- Output: 0

5. Use Cases for the FLOOR Function

• Rounding Down for Pricing or Financial Calculations: You may want to round down monetary values, such as reducing the price of an item to the next lower integer or handling discounts.
• Resource Allocation: When dividing resources and you need to ensure you're using the largest whole number, you can use FLOOR to round down.
• Handling Time Intervals: You may need to round down time-related values (like hours or minutes) for scheduling or billing purposes.

6. Performance Considerations

The FLOOR function is generally efficient and optimized in Oracle. Its performance impact is negligible unless applied to extremely large datasets. Always ensure that indexes are used properly for columns involved in FLOOR operations, especially in complex queries.

7. Differences Between FLOOR and CEIL Functions

The FLOOR function rounds down, while the CEIL function rounds up.

• FLOOR Example: FLOOR(5.7) returns 5.
• CEIL Example: CEIL(5.7) returns 6.
• Negative numbers: FLOOR rounds down further into negative numbers, whereas CEIL rounds up towards zero.
• FLOOR with -5.7: Returns -6.
• CEIL with -5.7: Returns -5.

8. Compatibility

The FLOOR function works with numeric data types, including integers, decimals, and floating-point numbers. It can be applied to columns, expressions, or any valid numeric value.

9. Using FLOOR with Other Functions

You can combine the FLOOR function with other mathematical or aggregate functions to perform more complex calculations.

Example: Using FLOOR in a SELECT Query

SELECT FLOOR(AVG(salary)) FROM employees;

This query will round down the average salary to the nearest integer.

Example: Using FLOOR with a CASE Statement

SELECT employee_name,

       FLOOR(salary * 0.1) AS ten_percent

FROM employees;

This query calculates 10% of the salary and rounds it down to the nearest integer.

10. Common Mistakes and Troubleshooting

• Confusing FLOOR and CEIL: Ensure you understand that FLOOR rounds down (towards negative infinity), while CEIL rounds up.
• Invalid Data Types: The FLOOR function expects a numeric value. Passing a non-numeric value will result in an error.

·        SELECT FLOOR('text') FROM dual; -- Error: invalid number

• Handling Negative Numbers: Remember that with negative numbers, FLOOR moves to the more negative integer, which can be confusing at times.

11. Using FLOOR for Time-Based Calculations

You can use the FLOOR function when you want to round down time values. For example, when dividing a number of minutes by 60 to get the number of hours, and you want to round down to the nearest hour:

SELECT FLOOR(125 / 60) AS hours FROM dual;

-- Output: 2

This query rounds down the result of dividing 125 minutes by 60, giving 2 hours.