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:
| Name | Description |
|---|---|
| expression | An expression which is a numeric value or numeric data type.The bit data type is not allowed. |
MySQL, PostgreSQL, and Oracle
All of above platforms support the SQL syntax of CEIL().
SQL CEIL() function: Visual 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+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | | A006 | McDen | London | 0.15 | 078-22255588 | | | A004 | Ivan | Torento | 0.15 | 008-22544166 | | | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
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.