DataslopeTables, Rows, and Columns
Learn how SQLite tables are built from rows, columns, cells, schemas, and data.
A SQLite database stores information in tables. A table looks a lot like a spreadsheet grid, but it has a stronger purpose: it stores facts about one kind of thing in a predictable structure.
If tables, rows, and columns make sense, the rest of SQL starts to feel much less mysterious.
The big picture
A table is a named grid. Each table has:
- Rows: individual records, running left to right.
- Columns: named attributes, running top to bottom.
- Cells: the values where a row and a column meet.
- A schema: the table's structure, such as column names and declared types.
- Data: the actual values stored in the rows.
A table is a grid of facts
Here is a small pets table:
Read one row as a sentence:
Pet
1is namedMochi, has speciescat, and is age3.
That sentence works because the table is organized around one kind of thing: pets.
Rows are records
A row is one complete record. In the pets table, one row means one pet.
Rows answer the question: which one?
- Which pet? Mochi.
- Which pet? Biscuit.
- Which pet? Sunny.
A row should describe one specific thing, event, or fact.
Columns are fields or attributes
A column is one attribute that every row can have. In the pets table, name is a column and age is a column.
Columns answer the question: what about it?
- What is the pet's name?
- What species is the pet?
- What age is the pet?
A column should hold one kind of value. Do not put ages in the name column or names in the age column.
In a table called books, what should one row most naturally represent?
A column heading like title.
One specific book, with values such as title, author, and page count.
Every book title joined into one long piece of text.
The database file itself.
Cells are single values
A cell is the value at one row-column crossing.
The cell shown above means: Biscuit's age is 5.
A cell should usually hold one value, not a list of values. For example, store cat, not cat, dog, bird in one cell.
Schema versus data
A table has both structure and contents.
The schema says what shape the table has. The data is what fills that shape.
For pets, the schema might say:
| Column | Declared type | Meaning |
|---|---|---|
id | INTEGER PRIMARY KEY | Unique identifier for each pet |
name | TEXT | Pet's name |
species | TEXT | Kind of animal |
age | INTEGER | Age in whole years |
The rows are the data:
| id | name | species | age |
|---|---|---|---|
| 1 | Mochi | cat | 3 |
| 2 | Biscuit | dog | 5 |
| 3 | Sunny | bird | 1 |
Row order is not guaranteed
A table is not a numbered list. SQLite may return rows in any order unless you ask for an order.
When order matters, use ORDER BY. Otherwise, do not assume the first row you see will always appear first.
See it in SQLite
Run this example and connect each result part to the words you learned: table, row, column, and cell.
Try changing the query to:
SELECT name, species
FROM pets;That asks SQLite for two columns instead of all four.
Check your understanding
Which statement best describes a table?
A single value, like 'Mochi'.
A named grid of rows and columns that stores facts about one kind of thing.
A command that always changes data.
A guarantee that rows are sorted alphabetically.
In the pets table, what is the column species?
One specific pet.
An attribute that can be recorded for every pet row.
The whole database.
The order in which rows are displayed.
What is the difference between a table's schema and its data?
The schema is the first row, and the data is every row after it.
The schema is the structure; the data is the actual stored values.
The schema is only for text values, and the data is only for numbers.
There is no difference in SQLite.
If you need rows returned from oldest pet to youngest pet, what should you do?
Trust SQLite to return rows in the order you inserted them.
Put the age inside the table name.
Use an ORDER BY clause on the age column.
Store all pets in one cell.