Tables, Rows, Columns, and Data Types

A relational database is built from one repeating unit: the table. Every user, every order, every product on the application you test lives in a table somewhere. This lesson zooms into a single table, names every part of it, and introduces the data types that decide what each column is allowed to hold. By the end you'll be able to read any schema and know exactly what each piece is doing — which is the foundation for every query in the rest of the course.

A table, end to end

A table has a name, a set of columns, and zero or more rows. Here's a sample users table:

| id | name          | email                | role    | is_active | created_at          |
|----|---------------|----------------------|---------|-----------|---------------------|
| 1  | Alice Khan    | alice@example.com    | admin   | TRUE      | 2026-04-12 09:14:00 |
| 2  | Bob Patel     | bob@example.com      | tester  | TRUE      | 2026-04-15 16:02:11 |
| 3  | Carol Singh   | carol@example.com    | tester  | FALSE     | 2026-04-22 11:48:53 |
| 4  | Dan Müller    | dan@example.com      | viewer  | TRUE      | 2026-05-01 08:30:27 |
| 5  | Esha Roy      | esha+qa@test.com     | tester  | TRUE      | 2026-05-04 14:55:09 |

The table name is users. By convention table names are lowercase and plural — users, orders, products.
The columns are id, name, email, role, is_active, created_at. Each column defines a piece of data every user will have, and each column has a data type that fixes what kind of value is allowed.
The rows are the records — five users in this example. Each row is one independent fact: "user 1 is Alice Khan, an admin, active, joined April 12."

That's the whole vocabulary: tables, columns, rows. Everything else builds on top.

Data types — what a column is allowed to hold

The data type of a column is the database's way of saying "this column holds numbers" or "this column holds text" or "this column holds dates." Picking the right type is partly a developer concern and partly a QA concern — wrong types cause real, testable bugs (we'll see one in the Common Mistakes section).

The handful of types you'll meet most often:

The data types testers see most often

	Type	Used for
Numbers	INTEGER / INT, DECIMAL(p,s), FLOAT	IDs, quantities, prices (DECIMAL for money), measurements
Text	VARCHAR(n), TEXT	names, emails, descriptions, statuses
Booleans	BOOLEAN	is_active, is_verified, in_stock — true/false flags
Dates & time	DATE, TIMESTAMP / DATETIME	created_at, birth_date, scheduled_for, deleted_at

A few details worth pinning down:

INTEGER holds whole numbers — 42, -7, 0. No decimals. Used for IDs, counts, quantities.
DECIMAL(10,2) holds exact decimals with a fixed number of digits and decimal places — 124.50, 9999999.99. Use this for money. Never use FLOAT for money: floats are approximate, and 0.1 + 0.2 famously isn't 0.3.
VARCHAR(255) holds variable-length text up to a max length (255 characters here). TEXT is similar but unlimited length. The difference rarely matters for testers — both store strings.
BOOLEAN is TRUE or FALSE. (SQLite doesn't have a real BOOLEAN type and stores them as 0/1 — same idea, different representation.)
DATE is a calendar date — 2026-05-06. TIMESTAMP (or DATETIME in MySQL) is a date and a time — 2026-05-06 14:32:11.

NULL — the value that means "no value"

A special value lives in every data type: NULL. It means "no value" or "unknown." It is not the same as zero, not the same as an empty string, not the same as FALSE. A user with email = NULL has no email recorded; a user with email = '' has an explicit empty string. Those are different facts, and different queries find them.

NULL trips up every SQL beginner because regular comparisons don't work on it — WHERE email = NULL returns nothing, even for rows where the email is NULL. The right way is WHERE email IS NULL or WHERE email IS NOT NULL. We'll meet this properly in Chapter 2.

The e-commerce database we'll use throughout this course

Every example, every practice task, and the final capstone uses a small e-commerce schema. Get familiar with these five tables now — you'll see them on every page of the course:

Table	Columns	What it holds
`users`	id, name, email, role, is_active, created_at	One row per registered user
`categories`	id, name	Top-level product groupings
`products`	id, name, price, category_id, in_stock, created_at	One row per product in the catalog
`orders`	id, user_id, total, status, created_at	One row per order placed
`order_items`	id, order_id, product_id, quantity, price	One row per line item inside an order

Read it once and let the relationships start to suggest themselves: each order belongs to one user (via user_id); each order has many order_items; each order_item references one product; each product belongs to one category. We'll formalise those relationships next lesson.

Reading a schema in the wild

When you join a real team, the schema you encounter will be similar in shape but bigger — more columns, more constraints, more tables. The skill you're practising here is the one that scales: identify the table name, scan the column list, note which columns are IDs, which are timestamps, which are flags, which hold business data. Once you can do that quickly, you can sit down with any schema and start writing useful verification queries.

⚠️ Common Mistakes

VARCHAR for things that aren't text. A birth_date VARCHAR(10) column will accept "01/05/2026", "2026-01-05", and "Jan 5 2026" indiscriminately. ORDER BY then sorts lexicographically (so "12/31/2025" sorts before "2026-01-01") and date math doesn't work. If a column is a date, it should be a DATE or TIMESTAMP.
FLOAT for money. Floating-point arithmetic is approximate. A test that expects 0.10 + 0.20 == 0.30 may fail. Money columns should always be DECIMAL(p, s) — exact, predictable, auditable.
Treating NULL as 0 or empty string. A total of 0 means "the order really cost zero"; a total of NULL means "we don't know what this order cost." Different bugs, different queries — keep them straight in your head.

🎯 Practice Task

20 minutes — practise the mental model before we start running queries.

On paper or in a doc, sketch a bugs table for your own bug-tracker — the columns you'd expect, with a data type for each. Aim for about 8 columns. (Hints: id, title, description, severity, status, reporter_id, assignee_id, created_at, …)
For each column, decide: should NULL be allowed? (e.g., assignee_id probably can be NULL for unassigned bugs; title probably cannot.)
Write down two pieces of test data — one valid bug, one invalid bug — that exercise the types you chose. Example: a bug where the title is a 600-character string (does VARCHAR(255) reject it?), or a bug where severity is an integer instead of 'P1'.
Stretch: look up the real types your team's bug tracker (Jira, Linear, GitHub Issues) uses for these fields. Notice how their model differs from yours — usually because they support extra features you didn't think of (labels, subscribers, parent issues).

Next lesson we connect the dots — how primary keys, foreign keys, and relationships turn isolated tables into a model of the real world.