Learn SQL: Tables

What is a table?

Rosa puts a steaming cup of coffee in front of you. "Before you write a single query: let me introduce you to our data."

What is a database?

Welcome to Bonen & Co! Everything the shop knows about customers, coffee and orders lives in a database.

Think of it as a digital filing cabinet: everything neatly sorted, so you quickly find what you are looking for.

Tables

A database is made up of tables. Each table is about one kind of thing, just like a tab in a spreadsheet.

Bonen & Co has four: customers, products, orders and order_lines. Here you see a piece of the customers table.

SELECT * FROM customers LIMIT 5

Result
id	name	city	email	member_since
1	Lotte Peeters	Gent	lotte@example.be	2023-02-14
2	Jonas Claes	Antwerpen	jonas.claes@example.be	2022-11-03
3	Amira El Idrissi	Brussel	NULL	2024-01-20
4	Sander de Vries	Utrecht	sander@example.nl	2023-06-08
5	Fien Maes	Gent	fien.maes@example.be	2024-03-02

Rows

Each row is one customer.

The highlighted row is Lotte Peeters from Gent. Everything we know about Lotte sits on that one line.

SELECT * FROM customers LIMIT 5

Result
id	name	city	email	member_since
1	Lotte Peeters	Gent	lotte@example.be	2023-02-14
2	Jonas Claes	Antwerpen	jonas.claes@example.be	2022-11-03
3	Amira El Idrissi	Brussel	NULL	2024-01-20
4	Sander de Vries	Utrecht	sander@example.nl	2023-06-08
5	Fien Maes	Gent	fien.maes@example.be	2024-03-02

Columns

Each column is one property, for all customers at once. The city column holds where everyone lives.

At the top of each column is its name. You will need those names in your queries soon.

SELECT * FROM customers LIMIT 5

Result
id	name	city	email	member_since
1	Lotte Peeters	Gent	lotte@example.be	2023-02-14
2	Jonas Claes	Antwerpen	jonas.claes@example.be	2022-11-03
3	Amira El Idrissi	Brussel	NULL	2024-01-20
4	Sander de Vries	Utrecht	sander@example.nl	2023-06-08
5	Fien Maes	Gent	fien.maes@example.be	2024-03-02

The key

The id column gives every customer a unique number. Such a column is called the key: no two customers have the same id.

That way you always know exactly who you mean, even if two customers are ever called Lotte Peeters.

SELECT * FROM customers LIMIT 5

Result
id	name	city	email	member_since
1	Lotte Peeters	Gent	lotte@example.be	2023-02-14
2	Jonas Claes	Antwerpen	jonas.claes@example.be	2022-11-03
3	Amira El Idrissi	Brussel	NULL	2024-01-20
4	Sander de Vries	Utrecht	sander@example.nl	2023-06-08
5	Fien Maes	Gent	fien.maes@example.be	2024-03-02

Tables work together

Tables point to each other with those keys. The orders table holds no name, but a customer_id.

That is the id of the customer who placed the order. So you only need to store a customer's details once.

SELECT * FROM orders LIMIT 4

orders
id	customer_id	order_date	status
1	1	2024-09-02	delivered
2	2	2024-09-03	delivered
3	4	2024-09-05	delivered
4	1	2024-09-12	delivered

SELECT id, name, city FROM customers LIMIT 5

customers
id	name	city
1	Lotte Peeters	Gent
2	Jonas Claes	Antwerpen
3	Amira El Idrissi	Brussel
4	Sander de Vries	Utrecht
5	Fien Maes	Gent

And then, SQL

To get something out of the database, you ask a question in SQL. Such a question is called a query.

This query means: give me all columns (*) from (FROM) the customers table. The result is shown below it.

SELECT * FROM customers

Result
id	name	city	email	member_since
1	Lotte Peeters	Gent	lotte@example.be	2023-02-14
2	Jonas Claes	Antwerpen	jonas.claes@example.be	2022-11-03
3	Amira El Idrissi	Brussel	NULL	2024-01-20
4	Sander de Vries	Utrecht	sander@example.nl	2023-06-08
5	Fien Maes	Gent	fien.maes@example.be	2024-03-02
6	Noah Janssens	Leuven	NULL	2022-09-17
7	Emma Bakker	Amsterdam	emma@example.nl	2023-10-30
8	Wout Verhoeven	Brugge	wout@example.be	2024-05-11

Try it yourself

Your turn. Show the whole products table.

Ready for more?

The full lesson has 7 exercises, from easy to challenging, with hints, points and a streak you can build.

Start the lesson