Published lesson · 30 minutes

Relate tables with keys

Objective: Join two small tables on a matching key and select only the fields needed for a report.

Understand the idea

Relate tables with keys works best when the learner can explain the reason behind each step, not just copy a result. A primary key identifies a record; a foreign key stores the matching identifier in another table so related information can be joined.

A practice inventory stores item details separately from stock movements, then joins them to show item name and quantity change. Treat the names and details as invented practice information; the example is for learning a method and does not describe a live service.

A dependable sequence is: Identify the key in each table, join on those columns, qualify ambiguous names and select a small readable result. Write down the important decision before you begin so another person can follow the reasoning.

Compare row counts before and after the join. A missing or duplicated key can produce lost or repeated report rows. Compare the result with the objective, then change one thing at a time if the first attempt does not work.

Draw the two table keys and explain which record is returned by one example join. Explain one assumption that would need checking before anyone used a similar result outside the classroom.

Keep a short record of the choices you made and the evidence you used to check them. A useful practice result is understandable by another learner, stays within the stated scope and makes its limitations visible before anyone relies on it.

Worked examples

Relate tables with keys: practice example

Explore how relate tables with keys supports the lesson objective with a small synthetic example.

inventory.sql

CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE stock_moves (item_id INTEGER, quantity INTEGER);
INSERT INTO items VALUES (1, "Seeds"); INSERT INTO stock_moves VALUES (1, 5);
SELECT items.name, stock_moves.quantity FROM items JOIN stock_moves ON items.id = stock_moves.item_id;

Practice tasks

  1. Explore relate tables with keys

    Use the worked example for “Relate tables with keys”. Change one small detail to suit a different synthetic community situation, then explain what you expected and how you checked whether the change still met the lesson objective.

  2. Build a SQL practice example

    Create a small practice result that demonstrates this objective: Join two small tables on a matching key and select only the fields needed for a report. Use only synthetic information, keep the first version focused, and write down one limitation that a later version should address.

  3. Review and explain the result

    Review your “Relate tables with keys” result as if another learner will use it without your explanation. Identify one strength, one exact point that may be unclear, and a revision that would make the result easier to understand or use.

Relate tables with keys becomes easier when you identify the key in each table, check the result and explain the choice to another learner.

Course overview