lesson 17 Joins & sets · beginner

Databases, schemas, tables & keys

Understand tables, keys and how related data is organized.

the idea

SQL is the language; the database engine executes it. Tables contain rows and typed columns. A primary key identifies one row; a foreign key points to another table — for example, orders.customer_id points at customers.id. To read columns from both tables in one result, match those keys with JOIN: SELECT o.id AS order_id, c.name AS customer_name FROM orders o JOIN customers c ON c.id = o.customer_id. The next lesson practices joins in depth; here you use the pattern once so the key relationship is concrete. SQLite catalogs differ from SQL Server schemas. In Runtime, each run starts with the original sample data.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

customers4 columns · 8 rows

idinteger · key	nametext	citytext	joined_attext
1	Alice	Chicago	2024-01-10
2	Ben	Austin	2024-02-12
3	Cara	Seattle	2024-03-05
4	Dev	Chicago	2024-04-20
5	Elena	NULL	2024-05-01

showing 5 of 8 rows

products4 columns · 6 rows

idinteger · key	nametext	categorytext	pricereal
1	Notebook	Stationery	12
2	Desk lamp	Home	45
3	Keyboard	Tech	80
4	Pen set	Stationery	8
5	Headphones	Tech	120

showing 5 of 6 rows

orders6 columns · 12 rows

idinteger · key	customer_idinteger	product_idinteger	quantityinteger	statustext	ordered_attext
101	1	1	2	paid	2025-01-01
102	2	3	1	paid	2025-01-02
103	1	2	1	paid	2025-01-05
104	3	5	2	pending	2025-01-08
105	4	4	5	paid	2025-02-01

showing 5 of 12 rows

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

example.sql

SELECT name,type
FROM sqlite_schema
WHERE type='table' AND name IN('customers','products','orders');

Watch for: A matching-looking name is not a reliable join key; use the defined identifiers.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1
Find the tables and their keys. sqlite_schema lists table names. In this shop database, customers.id is a primary key and orders.customer_id is the foreign key that points to it.
step-1.sql
sql›
```
SELECT name, type
FROM sqlite_schema
WHERE type = 'table' AND name IN ('customers', 'products', 'orders');
```
2
Match related rows with JOIN. JOIN pairs each order with the customer whose id equals orders.customer_id. That is how a foreign key becomes a readable result. Challenge 2 asks for this shape.
step-2.sql
sql›
```
SELECT o.id AS order_id, c.name AS customer_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
LIMIT 8;
```