Lesson 3 of 4 in this module

3/33 in track

Tables, rows, and columns

sql10 min

Overview

A table has columns (properties) and rows (records). Each column has a data type. Each row is one instance.

On this page8 sections

The idea

What does a table look like?

A database table is the spreadsheet grid you already know, with stricter rules. Labelled headers run across the top.

The orders table with five labelled columns and 3 rows. The order_total column is highlighted as 1 column, the ORD-002 row is highlighted as 1 row, and the value 42.50 is called out as one cell.
Anatomy of a table. The grey word under each column name is its data type.

The yellow stripe is a column, so every value in it is an order total. The blue stripe is a row, so it holds everything the table knows about one order. The cell where they cross holds one value.

What are columns, rows and cells?

Here are the first rows of the real orders table. We keep using them.

Column order_total, row ORD-0002, and the cell where they cross: 446.70.

order_iduser_idorder_statusorder_total (₹)
ORD-0001U004paid612.07
ORD-0002U073refunded446.70
ORD-0003U078paid1104.32

A cell holds one atomic value.

PieceDirectionHoldsIn the table above
ColumnVerticalOne property, with a unique nameorder_total
RowHorizontal1 recordeverything about ORD-0002
CellWhere they crossOne single value446.70

Can one cell hold 2 values?

No. A cell holds a single atomic value. Packing 2 values into one cell makes filtering and counting hard.

One value per cell keeps every query simple.

user_id cellCan SQL filter on U073 cleanly?
U073Yes: user_id = 'U073'
U004, U073No: it is one text, and neither id matches it

If an order really has two things, add another row or another column. Do not stuff them into one cell.

What do textbooks call them?

In interviews you may hear the formal words. A column is an attribute, a row is a tuple, and the whole table is a relation.

A grid of cells with 1 column highlighted in teal and labelled Attribute, 1 row highlighted in purple and labelled Tuple, and a brace under the whole grid labelled Relation.
The formal vocabulary for a table. Teal stripe: one attribute, which is a column. Purple stripe: one tuple, which is a row. The brace: the relation, which is the whole table.Source: Wikimedia Commons, 'Relational database terms.svg' by Booyabazooka · Public domain

Tuple is said 'tuh-pul' or 'too-pul'. Both are normal. In daily work, people just say column and row.

Where does the word relational come from?

Most beginners guess it means links between tables. It does not.

A relational database is a system built on relations, meaning tables.

Common guessActual origin
Relational means orders link to customersRelation is the mathematical word for a table of rows and columns
Foreign keys make it relationalEdgar F. Codd built it on set theory in 1970

A relation is a table. Relational means built on tables.

Words you will see in this lesson

Six words. The rest of the lesson uses only these.

WordWhat it means
TableA grid of data about one kind of thing
ColumnOne field in the table, such as city
RowOne record in the table, such as one order
CellOne value where a row and a column meet
Data typeWhat kind of value a column holds, such as number or text
RelationalTables linked to each other by shared values

Why this exists

Why does a spreadsheet sort 100 before 9?

Put 9, 20 and 100 in a text-formatted column and sort. The sheet gives 100, 20, 9.

The same digits as a number and as text
Number columnText columnvs9, 20, 100Sorted: 9, 20, 1009 + 20 = 29'9', '20', '100'Sorted: '100', '20', '9''9' + '20' is not addition

Whether 100 is bigger than 20 depends on the type of the column it lives in.

Text sorts character by character, and '1' comes before '2' and '9'. The sheet did what you asked, but the order is useless for numbers.

What is a data type?

A data type is a contract for a column. It says what kind of value fits, how much space it takes, and what you may do with it.

The names vary a little between databases, but these five cover most columns you will see.

Data typeHoldsExample valuesTypical use
VARCHAR (text)Letters, digits, symbols'ORD-0001', 'paid'Names, ids, statuses
INTEGERWhole numbers4, 1000, 0Quantities, counts
DECIMALExact numbers with decimals19.50, 0.08Prices, money
TIMESTAMPA date and a time2026-08-01 10:15:00When something happened
BOOLEANTrue or falsetrue, falseYes or no flags

An INTEGER column sorts numerically and refuses the text 'pending'. The database is the gatekeeper at the door.

A column has one type, and the type decides what you can do with it.

How do you pick a type for a new column?

Ask these questions in order. The first yes wins.

  1. Is it money or a price? Use DECIMAL, so the cents stay exact.
  2. Will you count it or add it up, like a quantity? Use INTEGER.
  3. Is it a moment in time, like when an order was placed? Use TIMESTAMP.
  4. Is it only yes or no? Use BOOLEAN.
  5. Otherwise it is a label or a name. Use VARCHAR.

A good type also saves space on disk, because the engine knows exactly what each column holds.

A common mistake is storing a price as a plain decimal-looking number that is really floating point. Adding 0.1 and 0.2 in floating point gives 0.30000000000000004. DECIMAL keeps the cents exact, so totals match your invoices to the paisa.

What can you do with each type?

The type decides which operations make sense. You can add two numbers, compare two dates, and join two texts. Some mixes are nonsense.

SQLA number you can multiply, a timestamp you can compare
SELECT order_id,
       order_total * 2 AS doubled,
       created_at > TIMESTAMP '2026-08-05' AS after_aug_5
FROM orders
LIMIT 3;
order_id  doubled  after_aug_5
ORD-0001  1224.14  false
ORD-0002  893.4    false
ORD-0003  2208.64  true

Multiplying the total works, and comparing a date works. Now try the nonsense mix: multiply a timestamp.

SQLMultiplying a timestamp
SELECT created_at * 2
FROM orders
LIMIT 3;
Binder Error: No function matches the given name and argument types '*(TIMESTAMP, INTEGER_LITERAL)'. You might need to add explicit type casts.
...

The engine refuses before it touches a single row. Text plus text fails the same way.

Why not store money as a float?

DOUBLE stores fractions as approximations in base 2. Tiny errors appear.

SQLThe same sum in two types
SELECT 0.1::DOUBLE + 0.2::DOUBLE AS as_double,
       CAST(0.1::DECIMAL(3, 1) + 0.2::DECIMAL(3, 1) AS VARCHAR) AS as_decimal;
as_double            as_decimal
0.30000000000000004  0.3

Across millions of rupees these crumbs can add up until the finance team's totals no longer match. Use DECIMAL for money. The practice orders table uses DOUBLE for order_total, which is fine for learning.

Picture this

What does 1 row stand for?

Before any SQL, ask this about a new table. The answer is its grain.

Same database, four different grains.

Table1 row is...A column might be...
ordersone orderorder_total
paymentsone paymentorder_id
ecommerce_eventsone click or page viewthe kind of event
telemetryone sensor readingreading

Say the grain out loud before you write any SQL. Every count, sum and join depends on it.

What goes wrong if you forget the grain?

Suppose a manager asks how many orders were placed. You count the wrong table.

SQL2 tables, two grains
SELECT (SELECT COUNT(*) FROM orders)   AS order_rows,
       (SELECT COUNT(*) FROM payments) AS payment_rows;
order_rows  payment_rows
180         150

Counting payments says 150 orders. The 30 cancelled orders never got a payment row. In other shops, one order paid in two instalments would show up twice.

Count the table whose grain matches your question.

A small example

What columns does orders have?

Never guess. DESCRIBE lists each column and its type without showing any rows.

SQLSee column names and types
DESCRIBE orders;
column_name   column_type  null  key   default  extra
order_id      VARCHAR      YES   NULL  NULL     NULL
user_id       VARCHAR      YES   NULL  NULL     NULL
order_status  VARCHAR      YES   NULL  NULL     NULL
order_total   DOUBLE       YES   NULL  NULL     NULL
currency      VARCHAR      YES   NULL  NULL     NULL
created_at    TIMESTAMP    YES   NULL  NULL     NULL
updated_at    TIMESTAMP    YES   NULL  NULL     NULL

7 columns. column_name is the attribute and column_type is its contract.

Read DESCRIBE as the contract for every column.

ColumnTypeWhy that type fits
order_id, user_idVARCHARThey are labels, not quantities
order_status, currencyVARCHARShort words like paid and INR
order_totalDOUBLEA number you can add up
created_at, updated_atTIMESTAMPPoints in time you can compare

Spot the type before you write the filter. Text values need quotes. Numbers and timestamps follow their own rules.

How do you ask for just 2 columns?

Listing columns after SELECT is called projection. Predict the 5 rows.

SQL2 columns, 5 rows
SELECT order_id, order_total
FROM orders
LIMIT 5;
order_id  order_total
ORD-0001  612.07
ORD-0002  446.7
ORD-0003  1104.32
ORD-0004  339.9
ORD-0005  261.73

Projection trims columns vertically, the way WHERE trims rows horizontally.

How do you design a table?

To see types from the other side, create a small table for refund requests. Each column gets a name and a type.

SQLDeclaring a table: a name and a type for each column
CREATE OR REPLACE TABLE refund_requests (
  refund_id     VARCHAR,
  order_id      VARCHAR,
  refund_amount DECIMAL(8, 2),
  requested_at  TIMESTAMP,
  is_approved   BOOLEAN
);

DECIMAL(8, 2) means up to 8 digits, 2 after the point. That suits money, and this kind of statement is called DDL. Each column answers one question from the checklist above.

What does a valid row look like?

ORD-0002 was refunded for 446.70, so we record that refund. Predict what comes back.

SQLInsert one refund and read it back
INSERT INTO refund_requests
VALUES ('REF-001', 'ORD-0002', 446.70, '2026-08-04 09:10:00', true);

SELECT refund_id, order_id, requested_at, is_approved
FROM refund_requests;
refund_id  order_id  requested_at         is_approved
REF-001    ORD-0002  2026-08-04 09:10:00  true

The 446.70 went into the DECIMAL(8, 2) column without a complaint.

What happens when the type does not match?

Now a buggy app tries to save the word hello as a refund amount.

SQLA text value in a decimal column
INSERT INTO refund_requests
VALUES ('REF-002', 'ORD-0004', 'hello', '2026-08-04 09:20:00', false);
Conversion Error: Could not convert string "hello" to DECIMAL(8,2)

LINE 1: INSERT INTO refund_requests VALUES ('REF-002', 'ORD-0004', 'hello', '2026-08-04 09:20:00', false)
                                                                   ^

The bad row never gets in. The table still holds 1 row.

Types are a data-quality gate: the bad row is refused at the door.

What if you mistype a column name?

Typos are common. Predict what the engine says about total_amount.

SQLA column that does not exist
SELECT order_id, total_amount
FROM orders
LIMIT 5;
Binder Error: Referenced column "total_amount" not found in FROM clause!
Candidate bindings: "created_at", "updated_at", "order_total", "order_status"

LINE 1: SELECT order_id, total_amount FROM orders LIMIT 5
                         ^

The binder checks every column name against the table. The candidate list points at order_total.

Which ids should be text, not numbers?

Ask one question: would adding two of these values ever make sense?

If maths on the value is pointless, use text.

ValueStore asWhy
Postcode 02138VARCHARAn INTEGER would drop the leading zero and give 2138
Phone 0987654321VARCHARNobody averages phone numbers
Badge 00412VARCHARThe zeros carry meaning
Quantity 4INTEGERYou do add quantities
Watch outIds that look like numbers are still often text

A phone number, a postcode or an id like 00123 should usually be stored as text. Stored as a number it would lose the leading zeros and you could do pointless maths on it. A good test: would adding two of these values ever make sense?

What is a blank cell?

Some cells hold no recorded value at all. SQL calls that NULL, and it is none of the other empty-looking things.

A missing price does not mean the product is free.

ValueWhat it says
0A known number, zero
'' (empty text)A known text of length zero
NULLMissing, unknown or not applicable
Watch outA blank cell is not the same as zero

Some cells have no value recorded at all. SQL calls that NULL, and it behaves differently from 0 or an empty text. A missing price does not mean the product is free. You will handle this carefully in the NULL lesson.

How do you meet a new table?

3 steps, 30 seconds, and most wrong counts never happen.

Meeting the orders table
  1. Run DESCRIBE orders: 7 columns, each with a type.

  2. Peek with SELECT order_id, order_total FROM orders LIMIT 5: 5 rows, 2 columns.

  3. Say the grain: 1 row is one order. Count orders here, not payments.

  4. Check types before maths: order_total is a number, order_id is text.

RememberBefore you query a new table

Run DESCRIBE, peek at a few rows with LIMIT, and say what 1 row stands for. It takes a minute and saves you from most wrong counts.

Interviewers like this one. Table grain prevents join fan-out bugs, where a join repeats rows and inflates totals. Precise types prevent rounding errors and bad sorts.

  1. A column is a property, a row is a record, a cell is one value.
  2. Every column has one type, and the type decides what operations work.
  3. State the grain before you count or join anything.
  4. DESCRIBE a table and LIMIT a peek before you query it.
  5. Money goes in DECIMAL. Ids with leading zeros go in text. Missing is NULL.

Common beginner questions

Can a column hold more than one kind of value?

No. Each column has exactly one type, so all its values are numbers, or all are text, and so on. That uniformity is what lets the database sort and add up the column reliably.

What happens if I do not know what a column means?

Read its name, look at a few values with a small LIMIT, and check its type with DESCRIBE. In a real job you would also ask whoever owns the table, since names like status can hide surprising meanings.

Why does the order of columns not matter?

Because a query names the columns it wants. SELECT order_total, order_id gives you the same 2 columns in the order you asked for. Rows have no guaranteed order unless you sort them, which you will see shortly.

What comes next

A table tells you what 1 row stands for, but it does not yet tell you how to pick out a single row, or how a payment finds its order. The next lesson covers primary keys and foreign keys, which answer both.

Practice

In the editor, run DESCRIBE on the orders table and read the names and types of the columns. Then write a query that returns only two of them, order_id and order_total, and keep the result to 5 rows.

Going further: state the grain, then test it

A table has 1 row per something. Saying what that something is (1 row per order, per click, per customer per day) is the first thing to know about any table, and you can test your belief with a query. If the grain is 1 row per order_id, no order_id should appear twice.

A table that is supposed to have 1 row per order.

order_idcustomer_idstatus
1100paid
2101paid
2101refunded
3102paid

Predict which order breaks the rule.

SQLTest the grain
CREATE TABLE orders (order_id INTEGER, customer_id INTEGER, status VARCHAR);
INSERT INTO orders VALUES (1,100,'paid'),(2,101,'paid'),(2,101,'refunded'),(3,102,'paid');
SELECT order_id, COUNT(*) AS rows_for_this_id
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1
order_id  rows_for_this_id
2         2

Order 2 appears twice with different statuses. Either the table is really 1 row per order status change (a history), or a refund was appended instead of updating the order. Both are legitimate designs, but a report that sums amounts per order would double count order 2 until you know which one it is.

Try a new scenario

A table is meant to have 1 row per customer per day. Write the test that proves it, and say what a non-empty result means.

Work it out, then open the answer

GROUP BY customer_id, day and keep groups with HAVING COUNT(*) > 1. A non-empty result means the table is finer than 1 row per customer per day, so any query that treats it as daily will overcount. Fix the table, or aggregate it to 1 row per customer per day before using it.

Does a table remember the order of its rows?

No. Without ORDER BY a database may return rows in any order, and the order can change after an update or on another engine. Never rely on the order you inserted the rows.

Close the lesson and explain it

Without scrolling up, explain how you would prove that a table really has 1 row per key.

Finished this lesson?

Mark it as read when you could explain it to someone else.

Rate:
Was this useful?