Home » FLOOR Function in SQL

FLOOR Function in SQL

by Online Tutorials Library

FLOOR Function in SQL

The FLOOR numeric function in Structured Query Language returns the largest integer value which is smaller than or equal to the given number.

Syntax of FLOOR Function

Syntax1: This syntax uses the FLOOR 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 FLOOR numeric function.

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

Examples of FLOOR function

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

Output:

floor_Value_of_0.5
0

Example 2: This example returns the floor value of the specified number:

Output:

floor_Value_of_10.9
10

Example 3: This example returns the floor value of 1.1:

Output:

floor_Value_of_1.1
1

Example 4: This example returns the floor 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
21 22 23 24 25 26 27 28 29 30

Example 5: This example uses the floor function with the SQL table.

In this example, we will create the new table through which we will perform the floor 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 floor function with the Product_ID column of the above Product_Details table:

SELECT Product_ID, FLOOR(Product_ID) AS floor_Value_of_Product_ID FROM Product_Details;

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

Output:

Product_ID floor_Value_of_Product_ID
0.1 0
0.2 0
0.3 0
0.4 0
1.5 1
2.6 2
0.7 0

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

SELECT Purchasing_Price, FLOOR(Purchasing_Price) AS floor_Value_of_PurchasingPrice, Selling_Price, FLOOR(Selling_Price) AS floor_Value_of_SellingPrice FROM Product_Details;

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

Output:

Purchasing_Price floor_Value_of_PurchasingPrice Selling_Price floor_Value_of_SellingPrice
15.5 15 NULL –
2.45 2 14.8 14
95.85 95 12.250 12
85.355 85 NULL –
0.5 0 0.500 0
112.110 112 9.95 9
999.550 999 0.258 0

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

SELECT floor(Product_Rating) AS floor_Value_of_productrating FROM Product_Details;

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

Output:

Product_Rating floor_Value_of_productrating
NULL –
9.25 9
9.15 9
8.45 8
NULL –
9.9 9
NULL –

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

SELECT Product_Quantity, FLOOR(Product_Quantity) AS floor_Value_of_Product_Quantity FROM Product_Details;

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

Output:

Product_Quantity floor_Value_of_Product_Quantity
36.250 36
75.500 75
68.350 68
48.850 48
35.900 35
20.750 20
12.250 12

You may also like