The overview put employees.salary in the numeric category and left it there. This lesson is the real reason it's DECIMAL(10,2) specifically, not INT and not FLOAT, and what every other number-shaped column in this course could have used instead.
Learning Objectives
- Name each database's whole-number (integer) types, and explain why Oracle and SQLite don't really have a family of them at all.
- Explain the real difference between an exact type (DECIMAL/NUMERIC) and an approximate one (FLOAT/DOUBLE).
- Explain why money, salary, and prices should always use an exact type.
Numeric Value
42, or 1299.99
Whole, Exact, or Approximate?
Integer vs decimal vs float
Matching Type
INT, DECIMAL, or FLOAT
Stored & Compared Correctly
No silent rounding error
Exact Whole Numbers: the Integer Family
| Database | Integer Types |
|---|---|
| MySQL / MariaDB | TINYINT (-128 to 127), SMALLINT, MEDIUMINT, INT, BIGINT |
| PostgreSQL | SMALLINT, INTEGER, BIGINT (no TINYINT or MEDIUMINT) |
| SQL Server | TINYINT (0 to 255, unsigned only), SMALLINT, INT, BIGINT |
| Oracle | No dedicated integer types at all, every one is really NUMBER underneath |
| SQLite | INTEGER only, one type for every size, 1 to 8 bytes chosen automatically |
Oracle's row deserves a closer look: INT, INTEGER, and SMALLINT exist only as ANSI-compatibility aliases, every single one of them maps to the exact same NUMBER(38), with no real size distinction between them at all, and there's no BIGINT alias to reach for either, that needs NUMBER(19) written out directly. SQLite's single INTEGER type, by contrast, genuinely does vary its storage, 1, 2, 3, 4, 6, or 8 bytes depending on how large the actual value is, chosen automatically per row.
Exact Decimals: DECIMAL and NUMERIC
DECIMAL and NUMERIC are the same type with two names, every database here accepts both interchangeably. The (10,2) means up to 10 total digits, 2 of them after the decimal point, so 99999999.99 fits, 100000000.00 doesn't. This is the real reason salary is DECIMAL(10,2): it stores exactly 1299.99, no more and no less, and arithmetic on it (salary * 1.1) stays exact.
salary DECIMAL(10,2) NOT NULL
SQLite's DECIMAL Is Only a Suggestion
| Database | DECIMAL(p,s) / NUMERIC(p,s) Enforcement |
|---|---|
| MySQL / MariaDB / PostgreSQL / SQL Server / Oracle | Enforced exactly: precision and scale are real, write-time checked limits |
| SQLite | Accepted syntactically, enforced not at all |
SQLite's type system accepts any declared type name, including DECIMAL(10,2), but SQLite has no fixed-point storage class to actually back it: non-integer numeric values are stored as an 8-byte floating-point REAL internally, regardless of what precision and scale were written in parentheses. For most everyday values this is invisible, but it means SQLite can't give the same exactness guarantee for money-shaped columns that the other five databases do.
Why Not FLOAT for Money
FLOAT and DOUBLE store numbers in binary floating point, which can't represent every decimal fraction exactly, the classic example is 0.1 + 0.2 producing 0.30000000000000004 instead of a clean 0.3 on practically every language and database that uses binary floats. For a quantity like salary, where 1299.99 has to stay exactly 1299.99 after a raise is calculated, that tiny, unpredictable error is unacceptable. DECIMAL/NUMERIC exist specifically to avoid it, by storing the exact decimal digits instead of a binary approximation.
Approximate Numbers: FLOAT, DOUBLE, and REAL
| Database | Approximate (Floating-Point) Types |
|---|---|
| MySQL / MariaDB | FLOAT (4 bytes) / DOUBLE (8 bytes) |
| PostgreSQL | REAL (4 bytes) / DOUBLE PRECISION (8 bytes) |
| SQL Server | REAL (4 bytes) / FLOAT(n) (4 or 8 bytes depending on n) |
| Oracle | BINARY_FLOAT / BINARY_DOUBLE (true binary floats); legacy FLOAT(p) is actually a decimal NUMBER subtype |
| SQLite | REAL only, always an 8-byte double, no separate single-precision type |
Oracle's legacy FLOAT(p) is the genuine surprise here: despite the name, it's documented as a subtype of NUMBER, meaning it's decimal-based internally, not true IEEE binary floating point the way BINARY_FLOAT and BINARY_DOUBLE are. These are appropriate for measurements, scientific data, or anything where a tiny rounding error genuinely doesn't matter, never for money.
Try It Yourself
Add a column unit_price to a new products table, that needs to store exact prices to 2 decimal places, on PostgreSQL.
ALTER TABLE products ADD COLUMN unit_price NUMERIC(10,2) NOT NULL;
Frequently Asked Questions
Is there any real difference between DECIMAL and NUMERIC?
No, in every database in this course they're the same type under two different names, fully interchangeable. DECIMAL(10,2) and NUMERIC(10,2) store and behave identically.
If SQLite doesn't really enforce DECIMAL, why does it accept the syntax at all?
SQLite's permissive type system never rejects a CREATE TABLE for an unrecognized type name, it maps whatever's declared onto one of a small number of internal storage classes using affinity rules, and DECIMAL/NUMERIC both map to the same affinity as a plain number. The precision and scale in parentheses are accepted, parsed, and then completely ignored.
Common Beginner Mistakes
Using FLOAT or DOUBLE for money, salary, or prices
Binary floating point can't exactly represent most decimal fractions, so values like 19.99 accumulate tiny rounding errors across repeated calculations. DECIMAL/NUMERIC store the exact decimal digits instead and never have this problem, always prefer them for anything money-shaped.
Assuming SQLite's DECIMAL(10,2) enforces precision and scale the same way MySQL's does
It doesn't, SQLite accepts the syntax but stores the value as an ordinary floating-point REAL regardless. Anyone relying on SQLite to reject a too-precise value the way the other five databases would is relying on something that silently isn't happening.
Best Practices
- Use DECIMAL/NUMERIC for anything involving money or any value that must stay exact.
- Use FLOAT/DOUBLE/REAL only for genuinely approximate scientific or measurement values, where a tiny rounding error is acceptable.
- Pick the smallest integer type that comfortably fits the real range of values, on the databases that actually offer a size choice.
Interview Questions
Why is DECIMAL preferred over FLOAT for storing money? Give a concrete example.
Binary floating point can't exactly represent most decimal fractions. 0.1 + 0.2 computed as FLOAT or DOUBLE on practically any system produces 0.30000000000000004, not a clean 0.3, because both 0.1 and 0.2 are repeating fractions in binary. DECIMAL(4,2) stores the exact digits 0.10 and 0.20 and adds them to exactly 0.30, with no approximation at any step, which is exactly why salary, prices, and any other money-shaped column should always be DECIMAL/NUMERIC, never FLOAT/DOUBLE.
Does SQLite have a TINYINT or a BIGINT?
No, SQLite collapses the entire integer family into one single INTEGER type, and chooses the actual storage size automatically per value, anywhere from 1 to 8 bytes depending on the value's magnitude. There's no separate TINYINT/SMALLINT/BIGINT keyword to choose between the way there is in MySQL or SQL Server.
Summary
Whole numbers, exact decimals, and approximate floating point are three genuinely different storage mechanisms, not interchangeable stylistic choices. DECIMAL/NUMERIC store exact decimal digits and belong on any money-shaped column; FLOAT/DOUBLE/REAL trade that exactness for range and speed, appropriate only when a small rounding error is acceptable. SQLite is the one database in this course that accepts DECIMAL's syntax without actually backing its exactness guarantee.
What's Next?
String Data Types is next: CHAR vs VARCHAR, each database's large-text type, and why "LONGVARCHAR" isn't a real keyword in any of them.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.