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

Need a 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)

Need a 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)

Need a 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)

Need a hint?

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