Decimal and floating-point types in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the main difference between DECIMAL and FLOAT types in MySQL?

DECIMAL stores exact numeric values with fixed precision and scale, ideal for financial data. FLOAT stores approximate values using floating-point representation, which can introduce rounding errors.

beginner

How do you define a DECIMAL column with 10 digits total and 2 digits after the decimal point?

Use DECIMAL(10,2). This means 10 digits total, with 2 digits to the right of the decimal point.

intermediate

Why might FLOAT or DOUBLE types cause rounding errors in calculations?

FLOAT and DOUBLE use binary floating-point representation, which cannot exactly represent some decimal fractions, causing small rounding errors in calculations.

beginner

Which data type should you use for storing currency values in MySQL and why?

Use DECIMAL because it stores exact values without rounding errors, which is important for accurate financial calculations.

intermediate

What happens if you insert a value with more decimal places than defined in a DECIMAL column?

MySQL rounds the value to fit the defined scale (number of decimal places). For example, inserting 12.345 into DECIMAL(5,2) stores 12.35.

Which MySQL data type stores exact numeric values with fixed precision?

A VARCHAR

B FLOAT

C DECIMAL

D DOUBLE

DECIMAL stores exact numeric values with fixed precision and scale.

What does DECIMAL(8,3) mean in MySQL?

A 8 digits total, 3 digits after decimal point

B 3 digits total, 8 digits after decimal point

C 8 digits after decimal point

D 3 digits total

DECIMAL(8,3) means 8 digits total with 3 digits after the decimal point.

Which data type is more likely to cause rounding errors?

A DATE

B DECIMAL

C CHAR

D FLOAT

FLOAT uses approximate floating-point representation, which can cause rounding errors.

If you need to store money values accurately, which type should you choose?

A DECIMAL

B DOUBLE

C FLOAT

D TEXT

DECIMAL stores exact values, making it best for money.

What happens if you insert 123.4567 into DECIMAL(6,2)?

A Stored as 123.45

B Stored as 123.46

C Error: too many decimal places

D Stored as 123.4567

MySQL rounds to fit the scale, so 123.4567 becomes 123.46.

Explain the difference between DECIMAL and FLOAT types in MySQL and when to use each.

Think about precision and rounding.

Describe how MySQL handles values with more decimal places than defined in a DECIMAL column.

Consider what happens to extra digits.