TopicsCheat sheetLessons
Lurndo › Topics › Tables

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
idnamecityemailmember_since
1Lotte PeetersGentlotte@example.be2023-02-14
2Jonas ClaesAntwerpenjonas.claes@example.be2022-11-03
3Amira El IdrissiBrusselNULL2024-01-20
4Sander de VriesUtrechtsander@example.nl2023-06-08
5Fien MaesGentfien.maes@example.be2024-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
idnamecityemailmember_since
1Lotte PeetersGentlotte@example.be2023-02-14
2Jonas ClaesAntwerpenjonas.claes@example.be2022-11-03
3Amira El IdrissiBrusselNULL2024-01-20
4Sander de VriesUtrechtsander@example.nl2023-06-08
5Fien MaesGentfien.maes@example.be2024-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
idnamecityemailmember_since
1Lotte PeetersGentlotte@example.be2023-02-14
2Jonas ClaesAntwerpenjonas.claes@example.be2022-11-03
3Amira El IdrissiBrusselNULL2024-01-20
4Sander de VriesUtrechtsander@example.nl2023-06-08
5Fien MaesGentfien.maes@example.be2024-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
idnamecityemailmember_since
1Lotte PeetersGentlotte@example.be2023-02-14
2Jonas ClaesAntwerpenjonas.claes@example.be2022-11-03
3Amira El IdrissiBrusselNULL2024-01-20
4Sander de VriesUtrechtsander@example.nl2023-06-08
5Fien MaesGentfien.maes@example.be2024-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
idcustomer_idorder_datestatus
112024-09-02delivered
222024-09-03delivered
342024-09-05delivered
412024-09-12delivered
SELECT id, name, city FROM customers LIMIT 5
customers
idnamecity
1Lotte PeetersGent
2Jonas ClaesAntwerpen
3Amira El IdrissiBrussel
4Sander de VriesUtrecht
5Fien MaesGent

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
idnamecityemailmember_since
1Lotte PeetersGentlotte@example.be2023-02-14
2Jonas ClaesAntwerpenjonas.claes@example.be2022-11-03
3Amira El IdrissiBrusselNULL2024-01-20
4Sander de VriesUtrechtsander@example.nl2023-06-08
5Fien MaesGentfien.maes@example.be2024-03-02
6Noah JanssensLeuvenNULL2022-09-17
7Emma BakkerAmsterdamemma@example.nl2023-10-30
8Wout VerhoevenBruggewout@example.be2024-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
Next →Picking one column
SQL topicsCheat sheetExcelLeaderboardAccountPrivacyNederlands