Lesson 1 of 20
In progress
What a relational database does
Tables, rows, columns and keys explained, plus why SQL still runs the systems you already use every day.
Every bank statement, booking system and school record you have used is probably backed by a relational database. SQL is the language for asking it questions, and the good news is that a small subset answers most of them.
What you will learn
- Picture data as tables with rows and columns.
- Understand keys and why they matter.
- Run the environment for this track and read your first query.
Tables are the whole model
A relational database stores data in tables. A table has named columns, each with a type, and any number of rows. A table of students might look like this:
Editable. Runs in your browser.
INSERT INTO students (id, name, city, joined_on) VALUES
(1, 'Ana', 'Pune', '2024-06-01'),
(2, 'Ravi', 'Delhi', '2024-07-14'),
(3, 'Meera', 'Pune', '2025-01-09');
SELECT id, name, city FROM students;
Press Run to see the output.
Three rows, four columns. Every row describes one student, and every column describes one attribute of a student. That regularity is the reason SQL is compact: the shape is declared once, and queries work from the shape.
The type of a column constrains what can go in it. INTEGER holds whole numbers, TEXT holds strings, REAL holds approximate decimals. A database that stores a price in a TEXT column will happily accept "twelve" and fail you later.
Rows are unordered
A common misunderstanding: rows have no natural order. The database returns them in whatever order is cheapest unless you ask for a specific one with ORDER BY. If you run the same query twice and get different orders, nothing is broken.
That is why an id column is useful. It gives each row a stable identity that does not depend on position or on the values changing.
Keys
A primary key is a column, or set of columns, that uniquely identifies a row. No duplicates, no nulls. In the example above, id is the primary key.
A foreign key is a column that points at a primary key in another table. If an orders table has a student_id column, a foreign key can guarantee that every order belongs to a student that exists.
Those two ideas are most of relational design. Tables hold one kind of thing, keys connect them, and the database refuses data that would break the connection.
Why not a spreadsheet
A spreadsheet can hold the same rows, and for a one-off calculation it is the right tool. A database adds:
- Constraints. Types, uniqueness and foreign keys reject bad rows at write time.
- Concurrency. Many users read and write without overwriting each other.
- Indexes. Fast lookup in millions of rows, not just thousands.
- Transactions. A group of changes either all happen or none do.
The last one matters more than beginners expect: a payment that debits one account must credit another, and a crash in between must not leave half the work done.
The SQL family
SQL is a standard, and every database implements roughly the same core with its own additions. PostgreSQL, MySQL, SQLite and SQL Server all accept the queries in this track. Differences appear in types, functions and advanced features, and they are worth checking in the documentation of whichever you use.
This track uses SQLite in the browser, which means no install and no server. The syntax you learn transfers; the handful of differences are called out where they matter.
Reading a query out loud
SELECT name, city
FROM students
WHERE city = 'Pune'
ORDER BY name;
Read as: "select the name and city columns, from the students table, where the city is Pune, ordered by name". Every clause has one job, and the order you write them in is fixed even though the database may evaluate them differently.
That fixed order is worth memorising early, because most beginner errors are a clause in the wrong place:
SELECT columns
FROM table
WHERE row filter
GROUP BY grouping
HAVING group filter
ORDER BY sorting
LIMIT row count
Assuming the order of rows in a result is meaningful. Without ORDER BY, "the first row" is not a defined thing, and code that relies on it will eventually show the wrong record.
Create a table of three books with an id, a title and a year, insert the rows, and select only the titles published after 2015. Order them newest first.
Show solution
Editable. Runs in your browser.
INSERT INTO books (id, title, published) VALUES
(1, 'The Pragmatic Programmer', 1999),
(2, 'Designing Data-Intensive Applications', 2017),
(3, 'Refactoring', 2018);
SELECT title, published
FROM books
WHERE published > 2015
ORDER BY published DESC;
Press Run to see the output.
The filter runs on rows, ORDER BY ... DESC puts the newest first, and only the two requested columns are returned. Selecting * would also work; naming the columns makes the query’s contract explicit.
Recap
- A table is a fixed set of typed columns holding rows of one kind of thing.
- Primary keys identify rows; foreign keys connect tables and reject orphans.
- Rows have no inherent order, so any ordering you need must be requested.
Next, you will write your first real SELECT and learn what the asterisk hides.
Do I need to install a database?
Is SQL case sensitive?
Progress is saved in this browser only. No account, no email.