✏️ Explanatory Question
Level: Hard — A financial-grade bug; storing money as FLOAT causes rounding errors that fail audits and lose real money.
Scenario: An invoice system stores prices as FLOAT. A customer buys three items at 0.10 each, but the invoice shows 0.30000000000000004. Worse, summing thousands of orders drifts by a few paise, and the finance team's totals never reconcile. Why does this happen, and how do you store money correctly?
FLOAT and DOUBLE store numbers in binary (base-2), following the IEEE 754 standard. Many decimal fractions — like 0.1 — cannot be represented exactly in binary, just as 1/3 cannot be written exactly in decimal. So the stored value is a tiny bit off, and those errors accumulate across calculations.
$$ 0.1_{10} = 0.0001100110011001100..._{2} \text{ (repeating, never exact)} $$
The golden rule for money: Never use FLOAT or DOUBLE for currency. Use DECIMAL(p, s) (also called NUMERIC) — a fixed-point, exact type that stores the value precisely, digit for digit, with no binary approximation.
| Expression | FLOAT/DOUBLE result | DECIMAL result |
|---|---|---|
| 0.1 + 0.2 | 0.30000000000000004 | 0.3 |
| Sum of 10,000 x 0.01 | 99.99999999 drift | 100.00 |
| Equality: 0.1+0.2 = 0.3 | FALSE | TRUE |
-- THE BUG: money stored as FLOAT drifts
CREATE TABLE invoices_bad (
id INT PRIMARY KEY,
amount FLOAT
);
INSERT INTO invoices_bad VALUES (1, 0.1), (2, 0.2);
SELECT SUM(amount) FROM invoices_bad; -- 0.30000000000000004
-- Equality comparison fails unexpectedly
SELECT 0.1 + 0.2 = 0.3; -- returns 0 (FALSE) with float math!
-- THE FIX: DECIMAL for exact money
CREATE TABLE invoices (
id INT PRIMARY KEY,
amount DECIMAL(12, 2) -- 12 total digits, 2 after the decimal
);
INSERT INTO invoices VALUES (1, 0.10), (2, 0.20);
SELECT SUM(amount) FROM invoices; -- exactly 0.30DECIMAL(p, s): p = total digits (precision), s = digits after the decimal (scale).
| Use Case | Type | Max Value |
|---|---|---|
| Standard currency | DECIMAL(12, 2) | 9,999,999,999.99 |
| High-value / enterprise | DECIMAL(19, 4) | Large + 4 dp precision |
| Crypto / fractional units | DECIMAL(30, 18) | 18 decimal places |
The integer-cents alternative: Some systems store money as an integer number of the smallest unit (e.g., paise/cents) in a BIGINT — so 100.50 is stored as 10050. This is also exact and avoids decimals entirely; you divide by 100 only at display time. Both DECIMAL and integer-cents are valid; FLOAT is not.
-- DECIMAL rounds predictably; always ROUND to the currency's scale
SELECT ROUND(amount * 1.18, 2) AS with_tax -- 18% GST, rounded to paise
FROM invoices;
-- Avoid dividing then multiplying with FLOAT (introduces drift)
-- Do the math in DECIMAL context
SELECT CAST(total AS DECIMAL(12,2)) / 3 AS split_share FROM orders;Interviewer follow-up: "When is FLOAT/DOUBLE actually the right choice?" → For scientific or statistical data where approximate values and huge ranges are fine — sensor readings, measurements, ML features, geo-coordinates. The rule is simple: exact values (money, counts) -> DECIMAL/INTEGER; approximate values (measurements) -> FLOAT/DOUBLE.
These 40 advanced questions (81-120) go beyond definitions into what real project interviews test: predicting tricky output, debugging slow production queries, writing complex analytics SQL, taming transactions and locks, designing schemas that scale, and dodging silent data-integrity disasters. The pattern across every answer is the same: understand WHY the database behaves as it does, name the trade-off, and back it with a concrete fix. That reasoning is what marks a senior engineer.