The Life Starter
Sign up
Grade 11 · unit review

Review: Validate, Join and Summarize Records

Use stable keys, reject duplicate identifiers, preserve missing matches and aggregate related rows.

Download reviews and answer keys

Bring the unit skills together

Use these 4 new cases to check your reasoning. Choices are temporary and are not saved to an account, sent to staff or recorded as school grades. Download your current review choices before leaving or reloading.

Unit review questions
1. Fictional people R1 = Ivo, R2 = Jia; loan B1 references R2. People are reordered R2, R1. Who remains linked?
2. A contract requires unique item_id. Keys X1, X2, X3, X2 are supplied. What should validation report?
3. People keys are R1 and R2, unique. Four loans reference R1, R2, R2 and R9. How many rows remain in loans LEFT JOIN people?
4. A person has no loans. People LEFT JOIN loans creates one NULL loan_id placeholder. What is COUNT(loan_id) for that person?

The download contains your current choices and any checked feedback. Keep the separate transfer-task work in your own document; downloading its instructions does not mark it complete.

Use the skills in a new task

Use fictional people A1 = Kim, A2 = Lou, A3 = Mo, all unique. Loans: T1 → A2, T2 → A2, T3 → A9. Validate people keys, left join loans to names, then report loan counts for every listed person using non-NULL loan IDs. Retain unmatched references as a separate data-quality result. Explain what happens if an extra people row A2 = Nia appears.

Work on paper or in your own document. Explain the choices you made.

  • Confirm original people keys are unique; match T1 and T2 to Lou.
  • Preserve T3 with NULL name and flag A9 as unmatched.
  • Produce listed-person counts A1 = 0, A2 = 2, A3 = 0.
  • Reject duplicate A2 before joining instead of choosing Lou or Nia arbitrarily.
Compare with a model response

Original keys A1,A2,A3 are unique. Loans-left-join rows: T1,A2,Lou; T2,A2,Lou; T3,A9,NULL. Unmatched-reference report contains T3 → A9. People-left-join grouped by person_id with COUNT(loan_id) gives A1:0, A2:2, A3:0. These two counted loans plus one unmatched loan account for all three inputs. Adding A2=Nia violates uniqueness; stop and resolve that duplicate before joining.

A different response can also meet the criteria. Compare the evidence and reasoning, rather than copying the wording.