SQL: Joining Tables and Summarising Groups · seed 1 · A4, ink-friendly. The answer key prints on its own page for grown-ups.
En anglais

Joining tables and counting the groups

Computing · Computer Systems · ages 17-18
Name ______________________   Date ____________

  1. One group contains three loan rows with fine values 2, 5 and 5. What do COUNT(*) and SUM(fine) return for this group?

    • COUNT(*) = 12; SUM(fine) = 3
    • COUNT(*) = 3; SUM(fine) = 12
    • COUNT(*) = 3; SUM(fine) = 10

  2. A Members row has member_id 8. A Loans row has member_id 8 and loan_id 92. For ON Members.member_id = Loans.member_id, why do these rows match?

    • Their member_id values are equal
    • Their table names are identical
    • Their loan_id values are identical

  3. An inner join on equal member IDs includes a member even when no loan has that member ID.

    Circle one:   True   False

  4. A query groups joined loans by member_id. Member ID 21, named Sam, has loans 71, 72 and 73. Its result shows member_id 21 and COUNT(*) 3. What does this row mean?

    • Three member IDs with one loan apiece
    • One loan ID with three matching members
    • One member ID with three matching loans

  5. Members has IDs 4 and 5. Two Loans rows have member_id values 4 and NULL. An inner join tests equal member_id values. Which source rows have no match?

    • Member 5 and the loan with a NULL member_id
    • Member 4 and the loan whose member_id is 4
    • Both members and both of the listed loan rows

  6. Members has 2 rows and Loans has 3 rows. A CROSS JOIN with no WHERE filter returns all 6 possible pairs.

    Circle one:   True   False

  7. A join of two 500-row tables returns 250,000 rows. You cannot see the query or the compared values. Which conclusion is justified by this count alone?

    • A matching condition was definitely omitted from the query
    • A written matching condition was definitely ignored by the engine
    • The count does not establish whether a condition was omitted

  8. The librarian needs a loan count for every member, including zero. loan_id is never NULL in Loans. Which plan gives that result?

    • Members LEFT JOIN Loans; COUNT(*) per member ID
    • Members LEFT JOIN Loans; COUNT(Loans.loan_id) per member ID
    • Members INNER JOIN Loans; COUNT(Loans.loan_id) per member ID
LightMySky · lightmysky.comW1-mt_0QPZDE2KuS-s1

Answer key

For grown-ups. Fold this page away before handing over the rest.

Joining tables and counting the groups W1-mt_0QPZDE2KuS-s1

  1. COUNT(*) = 3; SUM(fine) = 12 · COUNT(*) counts the three rows. SUM(fine) adds their values: 2 + 5 + 5 = 12.
  2. Their member_id values are equal · The ON condition compares the member_id values. Both are 8, so this pair passes.
  3. False · Without a matching loan, no member-loan pair passes the equality condition. The inner join omits that member.
  4. One member ID with three matching loans · The group contains three loan rows for member ID 21. Its summary row represents that member ID and the number of matching loans.
  5. Member 5 and the loan with a NULL member_id · Member 4 matches the loan with member_id 4. Member 5 has no matching loan. NULL does not pass equality with either member ID.
  6. True · Each of the two member rows pairs with each of the three loan rows, giving 2 × 3 = 6 result rows.
  7. The count does not establish whether a condition was omitted · The count equals the full product, but that does not identify the query. An unfiltered CROSS JOIN or a written condition true for every pair could produce it. Inspect the query and data.
  8. Members LEFT JOIN Loans; COUNT(Loans.loan_id) per member ID · LEFT JOIN preserves unmatched members. COUNT(Loans.loan_id) ignores their NULL loan field and returns zero. COUNT(*) would count the unmatched joined row.
Worksheet · LightMySky