Study guide: Validate, Join and Summarize Records
Use stable keys, reject duplicate identifiers, preserve missing matches and aggregate related rows.
Ideas to explain in your own words
- Use identifiers rather than row position to combine two tables.
- Find duplicate identifiers and stop an ambiguous lookup.
- Match every left-table row by key and explicitly preserve missing matches.
- Aggregate matched records and retain objects with zero related rows.
Watch your reasoning
- The shared key remains P2, which still identifies Bea. Row order does not change the stored key or the relationship.
- K1 occurs twice. The uniqueness requirement concerns the key field; other non-key values can legitimately repeat.
- Each of the four loan rows yields one output because people keys are unique. The unmatched loan is still retained.
- The new loan supplies one non-NULL loan_id in P3’s group, so its loan count becomes 1.
A worked review example
Match records with a shared key: L1’s person_id is P2, so the atlas loan belongs to fictional Bea. L2 uses P1, so the ruler loan belongs to fictional Ari. The first loan does not match the first people row simply because both are first. Check Key Uniqueness Before Joining: Validation sees O1 new, O2 new, O2 repeated: duplicate O2. After the exercise repair to O3 = globe, seen is {O1,O2,O3}; request O2 unambiguously returns compass. A real data repair would require confirming the intended identifiers, not guessing. Keep Unmatched Rows in a Left Join: Left-join rows: (L1,P2,atlas,Emi), (L2,P2,globe,Emi), (L3,P9,ruler,NULL). Left-join count = 3; inner-join count = 2. P2 appears twice because two different loan rows reference it. Count Related Records by Key: Output counts: P1 → 1; P2 → 2; P3 → 0. The counts sum to 3 actual loans. For P3, COUNT(loan_id) = 0 but COUNT(*) = 1 because its left-join placeholder row still exists.
Make a practice plan
- Try the supplied lesson problems without looking at the answers.
- Compare your method with the worked examples and explain any correction.
- Use the new review cases, then complete the transfer task on paper or in your own document.