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.