Data types: numbers, text, dates and true/false

Step 1 Understand it

Why you need it. 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.

Watch it happen

Picture it

flowchart TD
  V["A value"] --> N["Number<br/>INTEGER, DECIMAL, REAL"]
  V --> T["Text<br/>TEXT, VARCHAR"]
  V --> D["Date and time<br/>DATE, TIMESTAMP"]
  V --> B["True or false<br/>BOOLEAN"]
  N --> N1["add, divide, SUM, AVG"]
  T --> T1["join, cut, LIKE"]
  D --> D1["compare, count days"]
  B --> B1["WHERE conditions"]

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.

The shape of it

CAST(value AS INTEGER)
CAST(value AS REAL)
CAST(value AS TEXT)

Words in lower case are the parts you fill in.

How it works, step by step

1. An integer divided by an integer gives an integer: 7 / 2 is 3. The part after the point is thrown away.
2. Make one side a decimal and you get the exact answer: 7 / 2.0 or CAST(7 AS REAL) / 2 is 3.5.
3. Text compares character by character, so '10' < '9' as text. Store numbers in number columns.
4. Dates written as YYYY-MM-DD text sort correctly, because the biggest unit (year) comes first.
5. A boolean expression such as salary > 60000 is true or false — or unknown when salary is NULL.

Analogy

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.

Where the analogy breaks

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.

Worked examples

SELECT 7 / 2 AS whole_division,
       7 / 2.0 AS exact_division,
       CAST('42' AS INTEGER) + 1 AS text_to_number;

SELECT name, hire_date
FROM employees
WHERE hire_date >= '2022-01-01'
ORDER BY hire_date;

Must-know 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.

In other databases

• MySQL is the odd one out: 7 / 2 returns 3.5000, and DIV does whole division.
• SQLite has no real DATE or BOOLEAN type: dates are text and true/false are 1 and 0. PostgreSQL has DATE, TIMESTAMP and BOOLEAN types.
• VARCHAR(50) caps text at 50 characters in PostgreSQL, MySQL and SQL Server; SQLite ignores the limit.

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Read

  SQLite docs — Datatypes in SQLite

  official documentation, free

• Read

  PostgreSQL docs — Data Types

  official documentation, free

• Read

  W3Schools — SQL Data Types

  short page with a try-it editor, free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain why 7 / 2 gives 3, and two ways to get 3.5.

   Show answer

   7 and 2 are both whole numbers (integers), and integer divided by integer gives an integer: the part after the point is thrown away, so 7 / 2 is 3. It is not rounded; it is cut off.

   Two ways to get 3.5:
   • Make one side a decimal: 7 / 2.0 or 7 * 1.0 / 2.
   • Convert it: CAST(7 AS REAL) / 2.

   The same trap hits averages and percentages built by dividing two integer columns.

2. Writefrom a blank page, then run it

   Write a query that returns each employee's name and their salary divided by 12, as an exact decimal monthly figure.

   Show answer

   Divide by 12.0, not 12. One decimal side is enough to make the whole division exact. (To show pounds and pence, wrap it in ROUND(salary / 12.0, 2), from the built-in functions lesson.)

   sqlCopy

   SELECT name, salary, salary / 12.0 AS monthly
   FROM employees
   ORDER BY emp_id;

   Try it

   8 rows

   name	salary	monthly
   Asha	95000	7916.67
   Ben	70000	5833.33
   Chen	70000	5833.33
   Dara	60000	5000
   Eli	45000	3750
   Fatima	52000	4333.33
   Gus	38000	3166.67
   Hana	80000	6666.67

3. Predictsay it before you run it

   What does SELECT 1 / 3, 1.0 / 3, '5' < '40'; return? Say all three before running it.

   Show answer

   0, then 0.333…, then 0 (false).

   • 1 / 3: two integers, so the fraction is cut off: 0.
   • 1.0 / 3: one decimal, so the answer is exact: 0.3333333333333333.
   • '5' < '40': these are text, so they compare letter by letter, like words in a dictionary. The first characters are 5 and 4, and 5 comes after 4, so '5' is bigger. The answer is false, which SQLite shows as 0.

   sqlCopy

   SELECT 1 / 3, 1.0 / 3, '5' < '40';

   Try it

   1 row

   1 / 3	1.0 / 3	'5' < '40'
   0	0.33	0

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   A column stores prices as text: '9', '10', '100'. What does ORDER BY on it return, and how do you fix it?

   Show answer

   Sorting text compares character by character, so you get '10', '100', '9'. The 1s come before the 9.

   The quick fix is to sort by the number: ORDER BY CAST(price AS INTEGER). The real fix is to store prices in a number column (INTEGER, or DECIMAL for pence), so they sort, compare and add up correctly everywhere.

   Below, WITH just makes a tiny three-row table to test on (you meet it in Phase 4). Change the ORDER BY to plain price to see the wrong order.

   sqlCopy

   WITH prices(price) AS (VALUES ('9'), ('10'), ('100'))
   SELECT price
   FROM prices
   ORDER BY CAST(price AS INTEGER);

   Try it

   3 rows

   price
   9
   10
   100

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

Data types decide how values compare and calculate. The classic traps are integer division — 7 / 2 is 3 in most databases, so for percentages I multiply by 100.0 first — and 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.

Follow-up questions

Tick each check when you can do it without notes