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

Joining tables and counting the groups

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

  1. How do COUNT and SUM differ?

    • COUNT sorts rows, SUM deletes them
    • COUNT tallies rows, SUM adds values
    • COUNT renames tables, SUM renames columns

  2. What links rows across two tables in a join?

    • The matching key value
    • The table colours
    • The number of columns

  3. An inner join keeps members who have no loans.

    Circle one:   True   False

  4. A result row shows Sam with a count of 3. What does that row represent?

    • Three separate members called Sam
    • One loan worth three points
    • One member and their loan count

  5. Members is joined to Loans with an inner join. Which rows does the join drop?

    • Members with no loans and loans with no member
    • The members who borrowed most
    • Nobody, because joins keep everything

  6. A join with no matching condition pairs every row with every row.

    Circle one:   True   False

  7. A join of two 500-row tables returns 250,000 rows. What went wrong?

    • The tables are too small to join
    • COUNT is broken and must be replaced
    • The join condition is missing, so every pair survived

  8. The librarian wants every member listed, including members with zero loans. What should the query use?

    • An inner join plus a bigger LIMIT
    • An outer join that keeps unmatched members
    • No join at all, just the Loans table
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 tallies rows, SUM adds values · COUNT answers how many rows, SUM answers what they add to.
  2. The matching key value · The join follows foreign keys pointing at primary keys.
  3. False · With no loan row to match, those members drop out.
  4. One member and their loan count · GROUP BY collapsed Sam's loans into one summary row.
  5. Members with no loans and loans with no member · Rows with no match on the other side cannot pair up.
  6. True · With no test to pass, all pairings survive.
  7. The join condition is missing, so every pair survived · 500 times 500 is exactly the every-with-every explosion.
  8. An outer join that keeps unmatched members · Only an outer join keeps rows that found no match.
Worksheet · LightMySky