The idea
If you've used a spreadsheet, you already understand most of a database. A database is an organised collection of data that a program can store, search and update safely, even when many people use it at once. A relational database stores that data in tables, and lets tables point at each other. PostgreSQL, the database this track teaches, is one of the most popular relational databases in the world, and it's free.
So why not just use spreadsheets? Three reasons you'll feel quickly at work:
- Size. A spreadsheet starts to struggle around a few hundred thousand rows. Databases handle millions without blinking.
- Rules. A database can refuse bad data, such as an order for a customer who doesn't exist, or a price that's empty.
- Questions. You ask a database questions in SQL (Structured Query Language), and get exact answers back in seconds, no copy-pasting or dragging formulas.
A spreadsheet is a notebook you write in by hand. A database is a well-run library: every book has a fixed place, a catalogue card, and a librarian (the database engine) who finds exactly what you ask for and stops anyone from shelving books in the wrong place.
Tables, rows and columns
A table holds one kind of thing. Here are the first rows of the customers table in our practice shop, Toko Kita, an online store selling coffee, kitchen gear and desk accessories:
| customer_id | name | city | signup_date | segment | |
|---|---|---|---|---|---|
| 1 | Ayu Lestari | [email protected] | Jakarta | 2023-01-14 | Consumer |
| 2 | Budi Santoso | [email protected] | Bandung | 2023-02-03 | Business |
| 3 | Citra Dewi | [email protected] | Surabaya | 2023-03-22 | Consumer |
| 4 | Dimas Pratama | NULL | Jakarta | 2023-04-09 | Consumer |
- A row (also called a record) is one thing: one customer.
- A column (also called a field) is one fact about every thing: everyone's city, everyone's email.
- Each column has a data type, so every value in it is the same kind:
nameis text,customer_idis a whole number,signup_dateis a date. The database refuses a value of the wrong type, which keeps the data clean. NULLmeans "no value". Dimas never gave an email address. NULL isn't zero and it isn't an empty string, it's "unknown". You'll learn to handle it properly in a later module.
Keys: how tables point at each other
Look at the first column, customer_id. Every customer has a different number, and it never changes. That's a primary key: a column whose value uniquely identifies each row. Names can repeat (two customers called Ayu is perfectly possible), but a primary key never does.
Now look at the orders table:
| order_id | customer_id | order_date | status | channel |
|---|---|---|---|---|
| 1 | 11 | 2024-01-03 | completed | app |
| 2 | 3 | 2024-01-09 | completed | web |
| 3 | 5 | 2024-01-17 | completed | app |
| 4 | 3 | 2024-01-18 | completed | app |
The order doesn't repeat the customer's name, email and city. It only stores customer_id. Order 2 and order 4 both belong to customer 3, Citra Dewi. A column that holds another table's primary key is called a foreign key, and it's the "relational" in relational database.
Store each fact once, in the table where it belongs, and use keys to connect tables. If Citra changes her email, you update one row in customers, and every order still points to the right person.
Here's the whole Toko Kita database. You'll use it all through this track:
| Table | One row is… | Key columns |
|---|---|---|
customers | a customer | customer_id (primary key) |
products | a product we sell | product_id (primary key) |
orders | one checkout by a customer | order_id (primary key), customer_id (foreign key) |
order_items | one product line inside an order | order_id + product_id together, both foreign keys |
employees | a staff member | employee_id (primary key), manager_id (points to another employee) |
Why is there an order_items table at all? Because one order can contain several products, and one product appears in many orders. Rather than cramming "Mug, Coffee, Pen" into a single cell, each product in an order gets its own row in order_items. That design is what lets you later ask questions like "how many mugs did we sell in March?" in one line.
Your first query
SQL reads almost like English. To see every column of every row in a table, you write:
SELECT * FROM products;SELECTsays what you want to see. The*means "every column".FROM productssays which table to look in.- The semicolon
;ends the statement. With one statement it's optional, but it's a good habit.
Press Try it above to run it. You'll see the 15 products the shop sells.
product_id | name | category | price | cost | stock
------------+----------------------+-------------+--------+--------+-------
1 | French Press 600ml | Kitchen | 185000 | 110000 | 24
2 | Pour-over Kettle | Kitchen | 320000 | 210000 | 12
3 | Arabica Gayo 250g | Coffee | 95000 | 52000 | 80
...
(15 rows)SQL keywords like SELECT and FROM aren't case-sensitive, so select * from products works too. The convention is to write keywords in capitals and table or column names in lowercase, so you can see the structure of a query at a glance.
Every SQL question on this site has a Tables tab under the editor. It shows the tables you're working with, their columns, and their first rows. Check it before you write a query, the same way you'd glance at a spreadsheet before writing a formula.
Why this matters for clean code
The habit you just learned, store each fact once and connect with keys, is called normalisation, and it's the foundation of good data design. Messy databases repeat data everywhere, and repeated data eventually disagrees with itself. Clean ones are boring and predictable, and that's exactly what you want when a report goes to your manager.
Recap
- A relational database stores data in tables that connect through keys.
- A row is one thing, a column is one fact about every thing, and every column has a data type.
- A primary key uniquely identifies a row. A foreign key points to a row in another table.
NULLmeans "no value", not zero or blank text.SELECT * FROM table;shows every column and row of a table.