ROUND, CEIL, FLOOR in SQL - Mini Project: Build & Apply

Using ROUND, CEIL, and FLOOR Functions in SQL

📖 Scenario: You work for an online store that tracks product prices with decimals. You want to prepare a report that shows prices rounded in different ways for better display and analysis.

🎯 Goal: Create SQL queries that use the ROUND, CEIL, and FLOOR functions to transform product prices.

📋 What You'll Learn

Create a table called products with columns id (integer) and price (decimal).

Insert exactly these rows into products: (1, 10.75), (2, 23.40), (3, 5.99), (4, 100.01).

Write a query that selects id and the price rounded to the nearest whole number using ROUND(price).

Write a query that selects id and the price rounded up to the next whole number using CEIL(price).

Write a query that selects id and the price rounded down to the previous whole number using FLOOR(price).

💡 Why This Matters

🌍 Real World

Online stores and financial reports often need to display prices rounded in different ways for clarity and customer understanding.

💼 Career

Knowing how to use ROUND, CEIL, and FLOOR in SQL helps database analysts and developers prepare clean, user-friendly reports and data summaries.

1

Create the products table and insert data

Create a table called products with columns id as integer and price as decimal. Then insert these exact rows: (1, 10.75), (2, 23.40), (3, 5.99), and (4, 100.01).

SQL

-- Create the products table and insert the rows
-- Your code here

Hint

Use CREATE TABLE to make the table and INSERT INTO to add rows.

2

Set up a query to round prices

Write a SQL query that selects id and the price rounded to the nearest whole number using ROUND(price) from the products table.

SQL

CREATE TABLE products (id INT, price DECIMAL(5,2));
INSERT INTO products (id, price) VALUES
(1, 10.75),
(2, 23.40),
(3, 5.99),
(4, 100.01);

-- Write a query selecting id and ROUND(price)
-- Your code here

Hint

Use SELECT id, ROUND(price) and FROM products.

3

Write a query to round prices up using CEIL

Write a SQL query that selects id and the price rounded up to the next whole number using CEIL(price) from the products table.

SQL

CREATE TABLE products (id INT, price DECIMAL(5,2));
INSERT INTO products (id, price) VALUES
(1, 10.75),
(2, 23.40),
(3, 5.99),
(4, 100.01);

SELECT id, ROUND(price) AS rounded_price FROM products;

-- Write a query selecting id and CEIL(price)
-- Your code here

Hint

Use SELECT id, CEIL(price) and FROM products.

4

Write a query to round prices down using FLOOR

Write a SQL query that selects id and the price rounded down to the previous whole number using FLOOR(price) from the products table.

SQL

CREATE TABLE products (id INT, price DECIMAL(5,2));
INSERT INTO products (id, price) VALUES
(1, 10.75),
(2, 23.40),
(3, 5.99),
(4, 100.01);

SELECT id, ROUND(price) AS rounded_price FROM products;

SELECT id, CEIL(price) AS ceil_price FROM products;

-- Write a query selecting id and FLOOR(price)
-- Your code here

Hint

Use SELECT id, FLOOR(price) and FROM products.

Practice

1. What does the SQL function CEIL(4.3) return?

easy

A. 5

B. 4

C. 4.3

D. 3

Solution

1. Step 1: Understand CEIL function behavior

   The CEIL function rounds a number up to the nearest integer.

2. Step 2: Apply CEIL to 4.3

   Since 4.3 is between 4 and 5, CEIL rounds it up to 5.

3. Final Answer:

   5 -> Option A

4. Quick Check:

   CEIL(4.3) = 5 [OK]

Hint: CEIL always rounds up to next integer [OK]

Common Mistakes:

• Confusing CEIL with FLOOR
• Thinking CEIL rounds to nearest integer
• Assuming CEIL returns decimal part

2. Which of the following is the correct syntax to round a number to 2 decimal places in SQL?

easy

A. ROUND(123.456.2)

B. ROUND(123.456, 2)

C. ROUND(123.456 TO 2)

D. ROUND(123.456; 2)

Solution

1. Step 1: Recall ROUND function syntax

   ROUND(number, decimal_places) is the correct syntax in SQL.

2. Step 2: Check each option

   Only ROUND(123.456, 2) uses ROUND with a comma separating number and decimal places.

3. Final Answer:

   ROUND(123.456, 2) -> Option B

4. Quick Check:

   ROUND(number, decimals) syntax [OK]

Hint: Use comma between number and decimals in ROUND [OK]

Common Mistakes:

• Using dot instead of comma
• Using semicolon instead of comma
• Adding extra keywords like TO

3. What is the result of the SQL query:
SELECT FLOOR(7.9), CEIL(7.1), ROUND(7.5) FROM dual;

medium

A. (7, 8, 7)

B. (8, 7, 7)

C. (7, 7, 8)

D. (7, 8, 8)

Solution

1. Step 1: Evaluate FLOOR(7.9)

   FLOOR rounds down to 7.

2. Step 2: Evaluate CEIL(7.1)

   CEIL rounds up to 8.

3. Step 3: Evaluate ROUND(7.5)

   ROUND rounds to nearest integer; 7.5 rounds up to 8.

4. Final Answer:

   (7, 8, 8) -> Option D

5. Quick Check:

   FLOOR=7, CEIL=8, ROUND=8 [OK]

Hint: FLOOR down, CEIL up, ROUND nearest integer [OK]

Common Mistakes:

• Mixing FLOOR and CEIL results
• Assuming ROUND always rounds down
• Confusing ROUND behavior at .5

4. Identify the error in this SQL query:
SELECT CEIL(5.7 2) FROM dual;

medium

A. CEIL does not accept decimal numbers

B. CEIL requires two arguments

C. Missing comma between arguments

D. Syntax is correct

Solution

1. Step 1: Check CEIL function syntax

   CEIL takes only one argument, a number, with no second argument.

2. Step 2: Analyze given query

   The query has '5.7 2' without a comma, which is invalid syntax.

3. Final Answer:

   Missing comma between arguments -> Option C

4. Quick Check:

   CEIL needs one number, no extra arguments [OK]

Hint: Check commas between function arguments [OK]

Common Mistakes:

• Adding extra arguments to CEIL
• Forgetting commas between arguments
• Assuming CEIL rounds decimals differently

5. You have a table prices with a column cost containing decimal values. You want to create a new column cost_rounded that rounds cost to the nearest integer but always rounds .5 up. Which SQL expression should you use?

hard

A. FLOOR(cost + 0.5)

B. CEIL(cost - 0.5)

C. ROUND(cost)

D. CEIL(cost)

Solution

1. Step 1: Understand rounding .5 up requirement

   Standard ROUND rounds .5 up, but behavior can vary by SQL dialect.

2. Step 2: Analyze options

   FLOOR(cost + 0.5) uses FLOOR(cost + 0.5), which shifts values so .5 rounds up correctly.

3. Step 3: Verify other options

   ROUND(cost) may round .5 up but depends on implementation; B and D do not correctly round .5 up.

4. Final Answer:

   FLOOR(cost + 0.5) -> Option A

5. Quick Check:

   Use FLOOR(x + 0.5) to round .5 up reliably [OK]

Hint: Add 0.5 then FLOOR to round .5 up [OK]

Common Mistakes:

• Assuming ROUND always rounds .5 up
• Using CEIL directly without adjustment
• Using FLOOR without adding 0.5