CEILING Function in SQL
The CEILING function in Structured Query Language returns the smallest integer value which is greater than or equal to the given number.
Syntax of CEILING Function
Syntax1: This syntax uses the CEILING function with the column name of the SQL table:
In this syntax, we have to specify the name of that integer column on which we want to execute the CEILING numeric function.
Syntax2: This syntax uses the CEILING function with the integer or decimal value:
Examples of CEILING function
Example 1: This example returns the ceiling value of the specified number:
Output:
| ceiling_Value_of_0.5 |
|---|
| 1 |
Example 2: This example returns the ceiling value of all the numbers between 21 and 30:
Output:
| 21.8 | 22.9 | 23.58 | 24.55 | 25.05 | 26 | 27.125 | 28.98 | 29.89 | 30.02 |
|---|---|---|---|---|---|---|---|---|---|
| 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 |
Example 3: This example uses the ceiling function with the SQL table.
In this example, we will create the new table through which we will perform the ceiling function on the columns of the table:
The following shows the syntax to create the new table in SQL:
The following CREATE statement creates the Product_Details table for storing the price and quantity of products:
The following multiple INSERT queries insert the records of products with their selling and Purchasing price into the Product_Details table:
The following SELECT statement displays the inserted records of the above Product_Details table:
| Product_ID | Product_Name | Product_Quantity | Purchasing_Price | Selling_Price | Release_Date | Product_Rating |
|---|---|---|---|---|---|---|
| 0.1 | P1 | 36.250 | 15.5 | NULL | 2022-04-30 | NULL |
| 0.2 | P4 | 75.500 | 2.45 | 14.8 | 2022-01-28 | 9.25 |
| 0.3 | P2 | 68.350 | 95.85 | 12.250 | 2022-02-18 | 9.15 |
| 0.4 | P7 | 48.850 | 85.355 | NULL | 2021-12-25 | 8.45 |
| 1.5 | P6 | 35.900 | 0.5 | 0.500 | 2021-10-15 | NULL |
| 2.6 | P8 | 20.750 | 112.110 | 9.95 | 2022-01-28 | 9.9 |
| 0.7 | P10 | 12.250 | 999.550 | 0.258 | 2022-04-11 | NULL |
Query 1: The following SELECT query uses the ceiling function with the Product_ID column of the above Product_Details table:
This query shows the ceiling value of product id of each product.
Output:
| Product_ID | ceiling_Value_of_Product_ID |
|---|---|
| 0.1 | 1 |
| 0.2 | 1 |
| 0.3 | 1 |
| 0.4 | 1 |
| 1.5 | 2 |
| 2.6 | 3 |
| 0.7 | 1 |
Query 2: The following SELECT query uses the ceiling function with the and Purchasing_Price and Selling_Price column of the above Product_Details table:
This query shows the ceiling value of Purchasing and selling price of each product from the above table.
Output:
| Purchasing_Price | ceiling_Value_of_PurchasingPrice | Selling_Price | ceiling_Value_of_SellingPrice |
|---|---|---|---|
| 15.5 | 16 | NULL | – |
| 2.45 | 3 | 14.8 | 15 |
| 95.85 | 96 | 12.250 | 13 |
| 85.355 | 86 | NULL | – |
| 0.5 | 1 | 0.500 | 1 |
| 112.110 | 113 | 9.95 | 10 |
| 999.550 | 1000 | 0.258 | 1 |
Query 3: The following SELECT query uses the ceiling function with the Product_Rating column of the above Product_Details table:
This query shows the ceiling value of rating of each product from the above table.
Output:
| Product_Rating | ceiling_Value_of_productrating |
|---|---|
| NULL | – |
| 9.25 | 10 |
| 9.15 | 10 |
| 8.45 | 9 |
| NULL | – |
| 9.9 | 10 |
| NULL | – |
Query 4: The following SELECT query uses the ceiling function with the Product_Quantity column of the above Product_Details table:
This query shows the ceiling value of product quantity of each product.
Output:
| Product_Quantity | ceiling_Value_of_Product_Quantity |
|---|---|
| 36.250 | 37 |
| 75.500 | 76 |
| 68.350 | 69 |
| 48.850 | 79 |
| 35.900 | 36 |
| 20.750 | 21 |
| 12.250 | 13 |