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

Using ROUND, CEIL, and FLOOR Functions in MySQL

📖 Scenario: You work at an online store that records product prices with many decimal places. You want to prepare a simple report showing prices rounded in different ways for easy reading.

🎯 Goal: Create a MySQL table with product prices, then write queries using ROUND, CEIL, and FLOOR functions to show prices rounded normally, rounded up, and rounded down.

📋 What You'll Learn

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

Insert exactly these three rows: (1, 12.345), (2, 7.891), (3, 15.678).

Write a SELECT query that shows id and price_rounded using ROUND(price, 2).

Write a SELECT query that shows id and price_ceil using CEIL(price).

Write a SELECT query that shows id and price_floor using FLOOR(price).

💡 Why This Matters

🌍 Real World

Rounding prices is common in billing systems, reports, and user displays to make numbers easier to read and understand.

💼 Career

Database developers and analysts often use rounding functions to prepare data for reports, dashboards, and financial calculations.

1

Create the products table and insert data

Create a table called products with columns id as INTEGER and price as DECIMAL(5,3). Then insert these rows exactly: (1, 12.345), (2, 7.891), and (3, 15.678).

MySQL

-- Create table products with id and price columns
-- Insert the three rows

Hint

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

2

Write a query to round prices to 2 decimal places

Write a SELECT query that shows id and a new column price_rounded which uses ROUND(price, 2) to round the price to 2 decimal places from the products table.

MySQL

CREATE TABLE products (id INT, price DECIMAL(5,3));
INSERT INTO products (id, price) VALUES (1, 12.345), (2, 7.891), (3, 15.678);

-- Write SELECT query with ROUND(price, 2)

Hint

Use ROUND(price, 2) in the SELECT clause and alias it as price_rounded.

3

Write a query to get the ceiling of prices

Write a SELECT query that shows id and a new column price_ceil which uses CEIL(price) to round the price up to the nearest integer from the products table.

MySQL

CREATE TABLE products (id INT, price DECIMAL(5,3));
INSERT INTO products (id, price) VALUES (1, 12.345), (2, 7.891), (3, 15.678);

SELECT id, ROUND(price, 2) AS price_rounded FROM products;

-- Write SELECT query with CEIL(price)

Hint

Use CEIL(price) in the SELECT clause and alias it as price_ceil.

4

Write a query to get the floor of prices

Write a SELECT query that shows id and a new column price_floor which uses FLOOR(price) to round the price down to the nearest integer from the products table.

MySQL

CREATE TABLE products (id INT, price DECIMAL(5,3));
INSERT INTO products (id, price) VALUES (1, 12.345), (2, 7.891), (3, 15.678);

SELECT id, ROUND(price, 2) AS price_rounded FROM products;

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

-- Write SELECT query with FLOOR(price)

Hint

Use FLOOR(price) in the SELECT clause and alias it as price_floor.

Practice

1. Which MySQL function rounds a number to the nearest whole number or specified decimal place?

easy

A. CEIL

B. ROUND

C. FLOOR

D. TRUNCATE

Solution

1. Step 1: Understand the function purpose

   ROUND changes a number to the nearest integer or decimal place, unlike CEIL or FLOOR which only round up or down.

2. Step 2: Compare with other functions

   CEIL always rounds up, FLOOR always rounds down, TRUNCATE cuts decimals without rounding.

3. Final Answer:

   ROUND -> Option B

4. Quick Check:

   ROUND = nearest rounding [OK]

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

Common Mistakes:

• Confusing CEIL with ROUND
• Thinking FLOOR rounds to nearest
• Assuming TRUNCATE rounds

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

easy

A. ROUND(3.14159, 2)

B. CEIL(3.14159, 2)

C. FLOOR(3.14159, 2)

D. ROUND(3.14159. 2)

Solution

1. Step 1: Check function usage

   ROUND accepts two arguments: the number and the decimal places to round to.

2. Step 2: Identify correct syntax

   ROUND(3.14159, 2) is correct; CEIL and FLOOR do not take decimal places, and ROUND(3.14159. 2) has a syntax error with a dot instead of a comma.

3. Final Answer:

   ROUND(3.14159, 2) -> Option A

4. Quick Check:

   ROUND(number, decimals) syntax [OK]

Hint: ROUND(number, decimals) uses comma, CEIL/FLOOR no decimals [OK]

Common Mistakes:

• Using CEIL or FLOOR with decimal places
• Using dot instead of comma between arguments
• Missing second argument for ROUND

3. What is the result of the query: SELECT CEIL(4.2), FLOOR(4.8), ROUND(4.5);?

medium

A. 5, 4, 5

B. 4, 4, 4

C. 5, 5, 5

D. 4, 5, 4

Solution

1. Step 1: Evaluate CEIL(4.2)

   CEIL rounds up to the next whole number, so CEIL(4.2) = 5.

2. Step 2: Evaluate FLOOR(4.8) and ROUND(4.5)

   FLOOR rounds down, so FLOOR(4.8) = 4. ROUND rounds to nearest integer, 4.5 rounds up to 5.

3. Final Answer:

   5, 4, 5 -> Option A

4. Quick Check:

   CEIL=5, FLOOR=4, ROUND=5 [OK]

Hint: CEIL up, FLOOR down, ROUND nearest (0.5 rounds up) [OK]

Common Mistakes:

• Thinking FLOOR(4.8) is 5
• Rounding 4.5 down instead of up
• Confusing CEIL and FLOOR

4. Identify the error in this query: SELECT CEIL(5.7, 1), FLOOR(3.2), ROUND(2.718);

medium

A. ROUND requires two arguments

B. FLOOR requires two arguments

C. CEIL does not accept two arguments

D. No error, query is correct

Solution

1. Step 1: Check CEIL function arguments

   CEIL only accepts one argument (the number). Passing two arguments causes an error.

2. Step 2: Verify FLOOR and ROUND usage

   FLOOR accepts one argument, ROUND can accept one or two arguments. Both FLOOR(3.2) and ROUND(2.718) are valid.

3. Final Answer:

   CEIL does not accept two arguments -> Option C

4. Quick Check:

   CEIL = 1 argument only [OK]

Hint: CEIL/FLOOR take 1 argument; ROUND can take 1 or 2 [OK]

Common Mistakes:

• Passing two arguments to CEIL or FLOOR
• Assuming ROUND always needs two arguments
• Thinking query has no errors

5. You have a table prices with a column cost containing decimal values. You want to create a new column rounded_cost that rounds cost to the nearest integer but always rounds up if the decimal part is exactly 0.5. Which MySQL expression achieves this?

hard

A. ROUND(cost)

B. IF(cost - FLOOR(cost) = 0.5, FLOOR(cost), ROUND(cost))

C. CEIL(cost - 0.5)

D. CEIL(cost)

Solution

1. Step 1: Understand rounding rules

   In MySQL, ROUND rounds to nearest integer; halfway cases (.5) round away from zero (up for positive numbers).

2. Step 2: Select the correct expression

   CEIL(cost) always rounds up, so it rounds up when decimal part is exactly 0.5, fulfilling the requirement.

3. Step 3: Analyze other options

   A rounds .5 up normally, but does not always round up if decimal is exactly 0.5 (it rounds to nearest). B rounds down for exactly .5, which is incorrect. D fails on .5 cases as CEIL(cost - 0.5) can round down.

4. Final Answer:

   CEIL(cost) -> Option D

5. Quick Check:

   CEIL(cost) always rounds up [OK]

Hint: CEIL(cost) always rounds up, including .5 cases [OK]

Common Mistakes:

• Assuming ROUND always rounds .5 up as needed
• Using conditional that rounds .5 down
• CEIL(cost - 0.5) fails on .5 cases