SQL Tables, Rows, Columns & Relationships
How relational databases organize data into tables, and how foreign keys connect those tables to each other.
What you'll learn
- Describe what a table, row, and column represent in a relational database
- Explain what a primary key is and why every table needs one
- Explain how a foreign key models a relationship between two tables
Prerequisites
Explanation
A relational database organizes data into tables — think of each table as a labeled spreadsheet. Every table has columns (the fields, like title or price, each with a fixed data type) and rows (individual records, like one specific book). Throughout this track you'll work with a small bookstore database made of three connected tables: authors, books, and orders.
Every table needs a primary key — a column (or set of columns) guaranteed to uniquely identify each row, usually named id. No two rows in authors share the same id; that guarantee is what lets other tables refer back to a specific author reliably, no matter how many authors share the same name.
That's exactly what a foreign key is for. Look at the books table: alongside its own title, price, and so on, it has an author_id column. That column doesn't describe the book itself — it stores the id of the row in authors that wrote it. author_id is a foreign key: a pointer from one table into another table's primary key.
This is how relational databases model relationships without duplicating data. Instead of writing "Haruki Murakami, Japan" inside every single one of his book rows, each book just stores author_id = 3, and any question about that author is answered by looking up row 3 in authors once. The relationship between authors and books here is called one-to-many: one author can be linked to many books, but each book links back to exactly one author. The same pattern connects books to orders: each order references one book_id, while a single book can appear in many orders.
Keeping data this way — one fact stored in exactly one place, connected by keys instead of copied everywhere — is the core idea behind relational design. It avoids the mess of updating the same fact in ten different rows if it ever changes, and it's why SQL (Structured Query Language) is built around joining tables back together through these key relationships, which you'll practice hands-on starting in the next lesson.
Every SQL exercise in this track runs against the exact same bookstore schema, so the shapes of authors, books, and orders you learn here will keep showing up, lesson after lesson.
Example
Every row in `authors` has a unique `id` — its primary key. Reading the whole table shows all five authors and their `id` values.
SELECT id, name, country FROM authors;Try it yourself
The `books` table stores an `author_id` column pointing back to `authors.id`. Try adding a WHERE clause, like WHERE author_id = 3, then press Run.
Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.
Guided exercise
Guided exercise
Complete the query to look up the single author row for Haruki Murakami, whose id is 3.
Checks: Returns exactly the row for author id 3 (Haruki Murakami, Japan)
Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.
Stuck? Get a hint.
Independent exercise
Independent exercise
Write a query that returns the title, author_id, and price of every book written by author_id 4 (Chimamanda Ngozi Adichie), demonstrating how the foreign key connects books back to their author.
Checks: Returns all three books where author_id equals 4
Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.
Stuck? Get a hint.
Common mistakes
- Assuming a foreign key column (like author_id) stores a name or text — it stores a number pointing at another table's primary key.
- Forgetting that a primary key must be unique per row, and using a non-unique column (like a name, which two people could share) as if it were one.
- Thinking every relationship is one-to-one — most real relationships, like authors to books, are one-to-many.
- Expecting a table to 'know' related data from another table automatically, without writing a query that connects them through their keys.
Knowledge check
Takeaway
Tables store facts once, and foreign keys connect them by pointing at another table's primary key, instead of copying data everywhere.
Summary
Relational databases organize data into tables of rows and columns, each table anchored by a unique primary key. Foreign keys, like books.author_id, model relationships between tables by referencing another table's primary key, forming patterns like one-to-many.
References
Your notes
Notes save automatically.
Finished this lesson?
Mark it complete to track your progress and schedule a future review.
AI tutor
The optional AI tutor isn't enabled in this deployment. All lessons, exercises, quizzes, and search work fully without it.