DataslopeTables, Rows, and Columns
The three words that make up every relational database — table, row, column — and the mental model that turns SQL from cryptic to obvious.
We touched these words earlier. Now we make them rock-solid, because every idea in the rest of the course is built on this one picture. If tables, rows, and columns are crystal clear, SQL becomes mostly common sense.
The three words
- Table — a named grid that holds data about one kind of thing. This one holds students.
- Column — one attribute, running top to bottom.
nameis a column;gpais a column. Every column has a name and a type. - Row — one specific thing, running left to right. The second row is Ben, grade 10, gpa 3.4. A row is also called a record.
- Cell — the value where a row and column meet. The cell at
Cara's row and the
gpacolumn is2.9.
Read it as a sentence
A handy trick: read each row as a sentence using the column names. Row 1 becomes "Student 1 is named Ana, in grade 9, with a GPA of 3.8." If a row reads like a sensible sentence about one thing, your table is well-shaped.
Rows are "things," columns are "attributes"
This is worth slowing down on, because it is the source of good data instincts:
- A row answers "which one?" — which student, which order, which product.
- A column answers "what about it?" — what name, what price, what date.
| Table | Each row is one… | Typical columns (attributes) |
|---|---|---|
students | student | name, grade, gpa |
orders | order | date, amount, customer_id |
products | product | name, price, category |
cities | city | name, country, population |
When you design or read a table, always be able to finish the sentence "each row is one ____." If you cannot, the table is probably confused about what it holds.
Order does not matter (mostly)
A subtle but important point: in a relational table, rows have no inherent order. The database is free to store and return them in any order unless you explicitly ask for a sort. Ana being "first" in the picture is not a property of the data — it is just how we happened to print it.
This is why, later, we use ORDER BY whenever order matters. If you
do not ask, you must not assume.
Every value has a type
Notice that grade holds whole numbers and name holds text. Each
column has a declared type, and the database enforces it. We
devote the next pages to keys and types — for now, just register
that a column is not a free-for-all: it is a named slot of a
specific kind of value.
See it live
Run this and connect each part of the result to a word you just learned: the whole result is shaped like a table, each line is a row, each heading is a column, and each printed value is a cell.
Try changing SELECT * to SELECT name, gpa and run again. You
just asked for two columns instead of all of them — your first
deliberate slice of a table.
Check your understanding
In a table called orders, what does a single row most naturally represent?
The word "amount" that labels one of the columns.
One specific order, with a value in each of the table's columns.
The list of types each column can hold.
The total number of orders in the table.
Which statement about the order of rows in a relational table is correct?
Rows are always returned in the order they were inserted, automatically.
Rows are always sorted alphabetically by default.
Rows have no guaranteed order; if you need a specific order, you must ask for it with ORDER BY.
Row order is fixed forever once the table is created.
A column in a relational table corresponds to which concept?
One specific thing, such as one customer.
One attribute shared by every row, such as "name" or "price."
The grid as a whole.
A backup of the table.