Tables, rows, and columns
A database keeps its data in tables. A table has a name, a fixed set of named columns, and any number of rows. Each column holds the same kind of value in every row: a price column holds prices, never a name in some rows and a date in others. Each row holds 1 record, such as one product in a products table, with a value under every column. The table below is the one this lesson reads again and again, so look at it closely before moving on.
| product_id | product_name | category | price | in_stock |
|---|---|---|---|---|
| 1 | Desk Lamp | lighting | 24.0 | 1 |
| 2 | Standing Desk | furniture | 349.0 | 1 |
| 3 | Monitor Arm | accessories | 89.5 | 0 |
| 4 | Office Chair | furniture | 199.0 | 1 |
| 5 | USB Hub | accessories | 18.75 | 1 |
This table is named products. It has 5 columns and 5 rows, which makes 25 cells. Reading across the row where product_id is 3 gives everything the store knows about the Monitor Arm: its category is accessories, its price is 89.5, and in_stock is 0, the store's way of recording that it is sold out. Reading down the price column gives every product's price in 1 pass. A cell is the point where 1 row meets 1 column, and every query in this lesson is a way of choosing which columns to read from every row. Choosing which rows to read is the next topic in the track.
Rows have no built-in order
The rows above run from product_id 1 to 5 because that is the order they were typed in, and the database makes no promise to keep them that way. A table is a collection of rows, and when a query reads it without asking for an order, the database may hand the rows over in any sequence, often the order they happen to sit in storage. A later lesson covers asking for a specific order. Until then, 2 results with the same rows in a different sequence count as the same result, and a query that returns the Office Chair first has done nothing wrong.
Finding your way around several tables
- products: product_id, product_name, category, price, in_stock
- orders: order_id, product_id, quantity, order_date
- suppliers: supplier_id, supplier_name, country, lead_days
A real database holds dozens or hundreds of tables, and the first job in any of them is to learn each table's name and its exact column names, because a query can only ask for columns that exist. The 3 cards above describe a small store. The column product_id appears in both products and orders: an orders row records which product was sold by carrying its product_id, and reading the 2 tables together is a later topic. The habit to build now is checking the card before typing anything. A column called product_name exists; a column called name does not, and that difference decides whether a query runs or stops with an error.
- A row
- 1 record: 1 product, 1 order, 1 supplier
- Read across to see every fact about that record
- The count grows every time data is added
- Holds no fixed position in the table
- A column
- 1 attribute: price, category, country
- Read down to see 1 fact about every record
- The set is fixed when the table is designed
- Has a name and holds 1 kind of value
In the products table, what does the cell at the row where product_id is 5 and the column price hold?
- USB Hub, because a row's name is repeated in every cell of that row
- 18.75, because a cell holds the value where that row meets that column
- 5, because the price column copies the value found in product_id
- The whole row, because a cell and a row describe the same thing
About this lesson
This section is part of the Query Structure: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.