lesson 17 Joins & sets · beginner

Databases, schemas, tables & keys

Understand tables, keys and how related data is organized.

the idea

A database holds tables; sqlite_schema lists them with their definitions. A primary key identifies each row of a table; a foreign key stores another table’s key, so orders.customer_id points at customers.customer_id. Joining along that relationship (FROM orders JOIN customers ON customers.customer_id = orders.customer_id) attaches customer attributes to each order.

Dataset: Retail

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   List tables in the database. Run this. sqlite_schema names every table and view the database defines.

   step-1.sql Run

   sql›

   SELECT name, type
   FROM sqlite_schema
   WHERE type IN ('table', 'view')
   ORDER BY name;

2. 2

   Read a table’s CREATE statement. Run this. The sql column shows columns, primary keys, and foreign keys for orders.the pattern

   example.sql Run

   sql›

   SELECT name, sql
   FROM sqlite_schema
   WHERE type = 'table' AND name = 'orders';

3. 3

   Attach customer columns with LEFT JOIN. Run this. Each order’s customer_id looks up customers; anonymous orders stay with NULL customer fields.

   step-3.sql Run

   sql›

   SELECT o.order_id, o.customer_id, c.country, c.region
   FROM orders o
   LEFT JOIN customers c ON c.customer_id = o.customer_id
   ORDER BY o.order_id
   LIMIT 10;

common mistake

A NULL foreign key (an anonymous order) matches no customer, so an inner join drops it.