Decimal and floating-point types in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of SUM on FLOAT vs DECIMAL columns

Consider a table prices with two columns: price_float FLOAT(7,4) and price_decimal DECIMAL(7,4). It has three rows:

(10.1234, 10.1234), (20.5678, 20.5678), (30.0001, 30.0001)

What is the result of this query?

SELECT SUM(price_float), SUM(price_decimal) FROM prices;

MySQL

CREATE TABLE prices (price_float FLOAT(7,4), price_decimal DECIMAL(7,4));
INSERT INTO prices VALUES (10.1234, 10.1234), (20.5678, 20.5678), (30.0001, 30.0001);
SELECT SUM(price_float), SUM(price_decimal) FROM prices;

A (60.69129943847656, 60.6913)

B (60.6913, 60.6913)

C (60.6914, 60.6914)

D (60.6913, 60.69129943847656)

💡 Hint

FLOAT can introduce small precision errors; DECIMAL stores exact values.

FLOAT sums can have tiny floating-point rounding errors, so the sum is close but not exactly 60.6913. DECIMAL sums are exact and match the expected total.

🧠 Conceptual

intermediate

Choosing between FLOAT and DECIMAL for money values

Which statement best explains why DECIMAL is preferred over FLOAT for storing monetary values in MySQL?

A DECIMAL stores exact values without rounding errors, ensuring precise money calculations.

B FLOAT automatically rounds values to the nearest integer, which is unsuitable for money.

C FLOAT supports more decimal places than DECIMAL, so it is less precise for money.

D DECIMAL uses less storage space than FLOAT, making it more efficient for money.

💡 Hint

Think about accuracy and rounding in financial calculations.

DECIMAL stores exact fixed-point numbers, avoiding rounding errors common in FLOAT, which is a floating-point type.

📝 Syntax

advanced

Valid syntax for defining DECIMAL and FLOAT columns

Which of the following column definitions is syntactically valid in MySQL?

A price FLOAT(5,2) UNSIGNED ZEROFILL

B price DECIMAL(5,2) NOT NULL

C price DECIMAL(5) DEFAULT 0.00

D price FLOAT(5,2,1)

💡 Hint

Check the correct number of parameters and allowed attributes for DECIMAL and FLOAT.

DECIMAL(5,2) is valid: 5 total digits, 2 after decimal. FLOAT(5,2) is valid and UNSIGNED ZEROFILL is allowed for FLOAT; however, FLOAT(5,2,1) is invalid syntax. DECIMAL(5) without scale is allowed but defaults scale to 0, so default 0.00 is incompatible.

❓ optimization

advanced

Optimizing storage for large tables with decimal values

You have a large table storing prices with up to 6 digits and 2 decimal places. Which data type choice optimizes storage while preserving exact values?

A DECIMAL(8,2)

B FLOAT(8,2)

C DECIMAL(6,2)

D FLOAT

💡 Hint

Consider the total digits and scale needed for your data.

DECIMAL(6,2) stores exactly 6 digits total with 2 decimals, matching the requirement and using minimal storage. DECIMAL(8,2) uses more storage than needed. FLOAT types do not guarantee exact values.

🔧 Debug

expert

Unexpected rounding in FLOAT arithmetic

A developer runs this query:

SELECT (0.1 + 0.2) = 0.3 AS is_equal;

It returns 0 (false). Why does this happen?

A MySQL does not support decimal addition in SELECT statements.

B The query syntax is invalid and causes a silent error.

C The comparison operator '=' cannot be used with floating-point numbers.

D FLOAT arithmetic can introduce tiny precision errors, so 0.1 + 0.2 is not exactly 0.3.

💡 Hint

Think about how computers store floating-point numbers internally.

Floating-point numbers like 0.1 and 0.2 cannot be represented exactly in binary, causing small rounding errors. Thus, (0.1 + 0.2) is slightly different from 0.3, making the equality false.

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