Decimal and floating-point types in MySQL - Mini Project: Build & Apply

Working with Decimal and Floating-Point Types in MySQL

📖 Scenario: You are managing a small online store's database. You need to store product prices accurately, including cents, and also store product ratings which can have decimal values.

🎯 Goal: Create a MySQL table to store product information with prices using the DECIMAL type for exact values and ratings using the FLOAT type for approximate decimal values.

📋 What You'll Learn

Create a table named products with columns for id, name, price, and rating.

Use DECIMAL(10,2) for the price column to store prices with two decimal places.

Use FLOAT for the rating column to store approximate decimal values.

Set id as the primary key and auto-increment it.

Insert sample data with exact prices and decimal ratings.

💡 Why This Matters

🌍 Real World

Storing prices and ratings accurately is important for e-commerce and financial applications.

💼 Career

Understanding how to use decimal and floating-point types is essential for database developers and data analysts working with monetary and measurement data.

1

Create the products table with id and name columns

Write a CREATE TABLE statement to create a table named products with an id column as INT and primary key with auto-increment, and a name column as VARCHAR(100).

MySQL

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100)
);
-- Your code here

Hint

Use CREATE TABLE products and define id INT PRIMARY KEY AUTO_INCREMENT and name VARCHAR(100).

2

Add price and rating columns with correct data types

Modify the products table creation to add a price column with type DECIMAL(10,2) and a rating column with type FLOAT.

MySQL

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    price DECIMAL(10,2),
    rating FLOAT
);
-- Your code here

Hint

Add price DECIMAL(10,2) and rating FLOAT columns separated by commas.

3

Insert sample products with prices and ratings

Write an INSERT INTO products statement to add these products: ('Laptop', 999.99, 4.5) and ('Headphones', 199.95, 4.2) with columns name, price, and rating.

MySQL

INSERT INTO products (name, price, rating) VALUES
    ('Laptop', 999.99, 4.5),
    ('Headphones', 199.95, 4.2);
-- Your code here

Hint

Use INSERT INTO products (name, price, rating) VALUES followed by the two product tuples.

4

Query the products to see prices and ratings

Write a SELECT statement to retrieve all columns from the products table.

MySQL

SELECT * FROM products;
-- Your code here

Hint

Use SELECT * FROM products; to see all data.

Practice

1. Which MySQL data type should you use to store exact monetary values without rounding errors?

easy

A. VARCHAR

B. FLOAT

C. DECIMAL

D. DOUBLE

Solution

1. Step 1: Understand the purpose of DECIMAL

   DECIMAL stores exact decimal numbers with fixed digits, ideal for money.

2. Step 2: Compare with FLOAT and DOUBLE

   FLOAT and DOUBLE store approximate values, which can cause rounding errors.

3. Final Answer:

   DECIMAL -> Option C

4. Quick Check:

   Exact decimal storage = DECIMAL [OK]

Hint: Use DECIMAL for exact money values, not FLOAT or DOUBLE [OK]

Common Mistakes:

• Choosing FLOAT or DOUBLE for money causing rounding errors
• Using VARCHAR which stores text, not numbers

2. Which of the following is the correct syntax to define a DECIMAL column with 8 digits total and 2 digits after the decimal point in MySQL?

easy

A. DECIMAL(8,2)

B. DECIMAL(2,8)

C. DECIMAL(10,2)

D. DECIMAL(8)

Solution

1. Step 1: Recall DECIMAL syntax

   DECIMAL(M,D) means M total digits, D digits after decimal point.

2. Step 2: Match given digits

   We want 8 total digits and 2 after decimal, so DECIMAL(8,2) is correct.

3. Final Answer:

   DECIMAL(8,2) -> Option A

4. Quick Check:

   DECIMAL(total, decimals) = DECIMAL(8,2) [OK]

Hint: DECIMAL(M,D): M=total digits, D=digits after decimal [OK]

Common Mistakes:

• Swapping M and D values
• Using DECIMAL(8) which lacks decimal precision

3. Given the table prices with a column cost FLOAT(7,2), what will be the result of inserting 12345.6789 and then selecting cost?

medium

A. 12345.679

B. 12345.68

C. 12345.67

D. 12345.000

Solution

1. Step 1: Understand FLOAT(7,2) meaning

   FLOAT(7,2) means 7 digits total, 2 after decimal, but FLOAT is approximate.

2. Step 2: Insert and round value

   12345.6789 rounds to 12345.68 when stored with 2 decimals.

3. Final Answer:

   12345.68 -> Option B

4. Quick Check:

   FLOAT rounds to 2 decimals = 12345.68 [OK]

Hint: FLOAT rounds approximate values to specified decimals [OK]

Common Mistakes:

• Expecting exact storage like DECIMAL
• Ignoring rounding behavior of FLOAT

4. You defined a column as FLOAT(5,2) but notice values like 1234.567 are stored as 1234.56. What is the likely cause and how to fix it?

medium

A. MySQL truncates decimals by default; enable rounding mode

B. FLOAT(5,2) syntax is invalid; use FLOAT without parameters

C. The value exceeds FLOAT range; use DOUBLE instead

D. FLOAT stores approximate values; use DECIMAL(7,3) for exact decimals

Solution

1. Step 1: Recognize FLOAT behavior

   FLOAT stores approximate values and rounds to specified decimals, causing truncation.

2. Step 2: Choose correct type for exact decimals

   DECIMAL stores exact decimals and preserves precision, so use DECIMAL(7,3).

3. Final Answer:

   FLOAT stores approximate values; use DECIMAL(7,3) for exact decimals -> Option D

4. Quick Check:

   Approximate FLOAT -> switch to DECIMAL for precision [OK]

Hint: Use DECIMAL for exact decimals, FLOAT is approximate [OK]

Common Mistakes:

• Thinking FLOAT(5,2) is invalid syntax
• Assuming DOUBLE fixes decimal truncation
• Expecting MySQL to round automatically without changing type

5. You need to store scientific measurements with very large or very small values and some decimal precision. Which MySQL data type is best and why?

hard

A. FLOAT or DOUBLE, because they store approximate floating-point numbers with wide range

B. DECIMAL, because it stores exact fixed-point numbers

C. VARCHAR, to store numbers as text for precision

D. INT, because it stores whole numbers efficiently

Solution

1. Step 1: Understand scientific measurement needs

   Scientific data often requires wide range and floating precision, not exact fixed decimals.

2. Step 2: Choose FLOAT or DOUBLE for wide range

   FLOAT and DOUBLE support very large/small values with approximate precision, ideal for science.

3. Final Answer:

   FLOAT or DOUBLE, because they store approximate floating-point numbers with wide range -> Option A

4. Quick Check:

   Wide range + approximate decimals = FLOAT/DOUBLE [OK]

Hint: Use FLOAT/DOUBLE for scientific data, DECIMAL for money [OK]

Common Mistakes:

• Choosing DECIMAL which limits range
• Using VARCHAR which wastes space and loses numeric features
• Using INT which cannot store decimals