SQL at Depth: Subqueries, Set Operations and Views · seed 1 · A4, ink-friendly. The answer key prints on its own page for grown-ups.

Asking about rows that are missing

Computing · Data & Databases · ages 18-20
Name ______________________   Date ____________

  1. What does a view store?

    • The query text, run fresh each use
    • A frozen copy of the rows
    • The passwords of its readers

  2. Which clause tests that no matching rows occur?

    • ORDER BY
    • NOT EXISTS
    • GROUP BY

  3. UNION stacks results that share the same shape.

    Circle one:   True   False

  4. What is the second way to ask the same question?

    • LEFT JOIN Orders and keep rows where the order side is NULL
    • DELETE all orders first
    • UNION customers with orders

  5. Which query lists customers who never ordered?

    • SELECT name FROM Customers WHERE id IN (SELECT customer_id FROM Orders)
    • SELECT name FROM Orders WHERE total > 0
    • SELECT name FROM Customers c WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.customer_id = c.id)

  6. Orders hold one NULL customer id. Which form still answers rightly?

    • NOT IN, since NULLs speed it up
    • NOT EXISTS, since it skips the NULL trap
    • UNION, since it deletes NULLs

  7. A view freezes its rows at creation time.

    Circle one:   True   False

  8. A join version returns one customer three times, the NOT EXISTS version once. Why?

    • NOT EXISTS hides paying customers
    • UNION ran in secret
    • The join multiplies rows on many matches
LightMySky · lightmysky.comW1-mt_SLKUlC-7or-s1

Answer key

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

Asking about rows that are missing W1-mt_SLKUlC-7or-s1

  1. The query text, run fresh each use · Views name logic, not data.
  2. NOT EXISTS · NOT EXISTS filters to rows with zero matches.
  3. True · Set words need matching shapes.
  4. LEFT JOIN Orders and keep rows where the order side is NULL · Unmatched join rows carry NULLs on the order side.
  5. SELECT name FROM Customers c WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.customer_id = c.id) · The absence test keeps only unmatched customers.
  6. NOT EXISTS, since it skips the NULL trap · NOT IN breaks on NULLs; NOT EXISTS stays steady.
  7. False · Each use runs the stored text against live data.
  8. The join multiplies rows on many matches · Many matches fan out join rows; the test just checks.
Worksheet · LightMySky