SQL CEIL() function

CEIL() function

SQL CEIL() function is used to get the smallest integer which is greater than, or equal to, the specified numeric expression.

Syntax:

CEIL(expression)

Parameters:

MySQL, PostgreSQL, and Oracle

All of above platforms support the SQL syntax of CEIL().

SQL CEIL() function: Pictorial presentation

SQL CEIL() function on positive value

To get the ceiling or nearest rounded up value of 17.36 from the DUAL table , the following SQL statement can be used :

-- This SQL statement selects the ceiling value of a negative number from the 'dual' table.

SELECT CEIL(-17.36) 
-- SELECT statement retrieves data from the database
-- CEIL() function returns the smallest integer greater than or equal to the specified number
-- In this case, CEIL() is applied to -17.36, resulting in -17 being the smallest integer greater than or equal to -17.36

FROM dual;
-- Specifies the 'dual' table, which is a special one-row, one-column table present in Oracle database
-- The 'dual' table is typically used for performing calculations or returning a single result

Explanation:

• This SQL query is intended to retrieve the ceiling value of a negative number.


• The CEIL() function is used to find the smallest integer greater than or equal to the specified number.


• In this case, CEIL() is applied to -17.36, resulting in -17 being the smallest integer greater than or equal to -17.36.


• The 'dual' table is used here to execute the query as it provides a convenient way to perform calculations or return single results without needing an actual table with data.


• The result of this query will be -17, as CEIL(-17.36) rounds up to the nearest integer, which is -17 in this case.

Output:

(CEIL(17.36))
-------------
           18

Example:

To get the ceiling or nearest rounded up value of -17.36 from the DUAL table, the following SQL statement can be used :

-- This SQL statement calculates the ceiling value of a negative number using the CEIL function and selects the result from the 'dual' table.

SELECT CEIL(-17.36) 
-- SELECT statement retrieves data from the database
-- CEIL() function is used to find the smallest integer greater than or equal to the specified number
-- In this case, CEIL() is applied to -17.36

FROM dual;
-- Specifies the 'dual' table, a special one-row, one-column table present in Oracle database
-- The 'dual' table is often used for performing calculations or returning single results in SQL queries

Explanation:

• This SQL query is straightforward, as it's only a single statement.


• The purpose of this query is to find the ceiling value of a negative number, specifically -17.36.


• CEIL() is a mathematical function that rounds a number up to the nearest integer, returning the smallest integer that is greater than or equal to the given number.


• In this case, CEIL(-17.36) will return -17 because -17 is the smallest integer greater than or equal to -17.36.


• The 'dual' table is used here because it's a convenient way to execute single-row queries in Oracle SQL without needing to specify an actual table with data.

Output:

CEIL(-17.36)
------------
         -17

SQL CEIL() function with distinct

Sample table : agents

To get the unique ceiling or nearest rounded up value of the column 'commission' after multiplying by 75 from the 'agents' table, the following SQL statement can be used :

-- This SQL statement calculates the ceiling value of a specific expression involving commission amounts multiplied by 75, selecting distinct results from the 'agents' table.

SELECT DISTINCT(CEIL(commission*75)) "DISTINCT(CEIL())" 
-- SELECT statement retrieves data from the database
-- DISTINCT keyword ensures that only unique values are returned
-- CEIL() function is used to find the smallest integer greater than or equal to the specified number
-- The expression commission*75 is evaluated for each row, multiplying the commission by 75 before applying the CEIL() function

FROM agents;
-- Specifies the 'agents' table from which the data is being retrieved

Explanation:

• This SQL query is designed to calculate the ceiling value of a specific expression involving commission amounts multiplied by 75 for each row in the 'agents' table.


• The DISTINCT keyword ensures that only unique ceiling values are returned, preventing duplicates.


• The CEIL() function is used to find the smallest integer greater than or equal to the specified number.


• In this case, commission*75 calculates the commission amount multiplied by 75 before applying the CEIL() function.


• The query is executed against the 'agents' table to retrieve the required data.

Output:

DISTINCT(CEIL())
----------------
              11
              10
               9
              12

SQL: Comparing between CEIL() and FLOOR() function

Note: Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.

Here is a slide presentation which covers the SQL arithmetic functions.

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: ABS
Next: FLOOR