Data types: numbers, text, dates and true/false
Every column has a data type, such as INTEGER, a decimal number, TEXT, a date or a boolean. The type decides what values the column can hold, and how they compare, sort and calculate.
Why it matters
The same value acts differently by type. The text '10' sorts before '9', because text compares letter by letter. Integer 7 divided by integer 2 gives 3, not 3.5. Knowing the types explains a whole class of "why is my answer wrong?" bugs.
The idea
Common types: INTEGER for whole numbers (counts, ids), DECIMAL or NUMERIC for exact money values, REAL or FLOAT for approximate numbers, TEXT or VARCHAR for words, DATE and TIMESTAMP for dates and times, and BOOLEAN for true or false.
You can convert between types with CAST. NULL is not a type: it is the marker for "no value", and any column can hold it unless it is declared NOT NULL.
Containers in a kitchen. A measuring jug (a number column) lets you add and divide; a label maker (text) lets you join and cut words; a calendar (dates) lets you count days. Put the right thing in the right container, or the tools stop working properly.
In a kitchen, a jug simply refuses a label. SQLite is relaxed and lets almost any value into any column, while PostgreSQL rejects the wrong type outright. Don't lean on SQLite's flexibility — write SQL as if every database were strict.
Syntax
CAST(value AS INTEGER)
CAST(value AS REAL)
CAST(value AS TEXT)How it works
- An integer divided by an integer gives an integer:
7 / 2is 3. The part after the point is thrown away. - Make one side a decimal and you get the exact answer:
7 / 2.0orCAST(7 AS REAL) / 2is 3.5. - Text compares character by character, so '10' < '9' as text. Store numbers in number columns.
- Dates written as YYYY-MM-DD text sort correctly, because the biggest unit (year) comes first.
- A boolean expression such as
salary > 60000is true or false — or unknown when salary is NULL.
Key points
- INTEGER ÷ INTEGER = INTEGER in SQLite, PostgreSQL and SQL Server. Use 100.0 or CAST to get decimals.
- Use DECIMAL or NUMERIC for money: REAL and FLOAT are approximate and can show 0.30000000000000004.
- Text values go in single quotes: 'London'. Double quotes are for names of tables or columns.
- Store dates in date columns (or YYYY-MM-DD text), never as '06/01/2024', which sorts wrongly.
- NULL means "no value" and can appear in any column that isn't NOT NULL.
Common mistakes
- Computing a ratio with two integers and getting 0.
- Storing numbers as text and getting '10' sorted before '9'.
- Using double quotes for text values. In PostgreSQL, "London" means a column called London.
Not true: Numbers stored as text sort in the same order as real numbers.
In fact: Text is compared character by character, so '10' comes before '9'. Store numbers in a number column.
In the interview
Data types decide how values compare and calculate. There are two classic traps. One is integer division: 7 / 2 is 3 in most databases, so for percentages I multiply by 100.0 first. The other is storing numbers or dates as text, which makes them sort wrongly. For money I use DECIMAL rather than FLOAT, because floating point can't represent most decimals exactly.
Part of Unit 0: Start here: databases, tables and SQL in the free SQL course.