Home » CEIL Function in SQL

CEIL Function in SQL

by

CEIL Function in SQL

The CEIL function in Structured Query Language returns the smallest integer value which is greater than or equal to the given number.

Syntax of CEIL Function

Syntax1: This syntax uses the CEIL function with the column name of the SQL table:

In this first syntax, we have to specify the name of that integer column on which we want to execute the CEIL numeric function.

Syntax2: This syntax uses the CEIL function with the integer or decimal value:

Examples of CEIL function

Example 1: This example returns the ceil value of the specified number:

Output:

ceil_Value_of_0.5
1

Example 2: This example returns the ceil value of all the numbers between 21 and 30:

Output:

21.822.923.5824.5525.052627.12528.9829.8930.02
22232425262728293031

Example 3: This example uses the ceil function with the SQL table.

In this example, we will create the new table through which we will perform the ceil 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_IDProduct_NameProduct_QuantityPurchasing_PriceSelling_PriceRelease_DateProduct_Rating
0.1P136.25015.5NULL2022-04-30NULL
0.2P475.5002.4514.82022-01-289.25
0.3P268.35095.8512.2502022-02-189.15
0.4P748.85085.355NULL2021-12-258.45
1.5P635.9000.50.5002021-10-15NULL
2.6P820.750112.1109.952022-01-289.9
0.7P1012.250999.5500.2582022-04-11NULL

Query 1: The following SELECT query uses the ceil function with the Product_ID column of the above Product_Details table:

SELECT Product_ID, CEIL(Product_ID) AS ceil_Value_of_Product_ID FROM Product_Details;

This query shows the ceil value of product id of each product.

Output:

Product_IDceil_Value_of_Product_ID
0.11
0.21
0.31
0.41
1.52
2.63
0.71

Query 2: The following SELECT query uses the ceil function with the and Purchasing_Price and Selling_Price column of the above Product_Details table:

SELECT Purchasing_Price, CEIL(Purchasing_Price) AS ceil_Value_of_PurchasingPrice, Selling_Price, CEIL(Selling_Price) AS ceil_Value_of_SellingPrice FROM Product_Details;

This query shows the ceil value of Purchasing and selling price of each product from the above table.

Output:

Purchasing_Priceceil_Value_of_PurchasingPriceSelling_Priceceil_Value_of_SellingPrice
15.516NULL
2.45314.815
95.859612.25013
85.35586NULL
0.510.5001
112.1101139.9510
999.55010000.2581

Query 3: The following SELECT query uses the ceil function with the Product_Rating column of the above Product_Details table:

SELECT ceil(Product_Rating) AS ceil_Value_of_productrating FROM Product_Details;

This query shows the ceil value of rating of each product from the above table.

Output:

Product_Ratingceil_Value_of_productrating
NULL
9.2510
9.1510
8.459
NULL
9.910
NULL

Query 4: The following SELECT query uses the ceil function with the Product_Quantity column of the above Product_Details table:

SELECT Product_Quantity, CEIL(Product_Quantity) AS ceil_Value_of_Product_Quantity FROM Product_Details;

This query shows the ceil value of product quantity of each product.

Output:

Product_Quantityceil_Value_of_Product_Quantity
36.25037
75.50076
68.35069
48.85079
35.90036
20.75021
12.25013

You may also like