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?

    • Query text, fresh each use
    • A frozen copy of the rows
    • A copy of the rows, refreshed once a day

  2. Which clause tests that no matching rows occur?

    • ORDER BY
    • NOT EXISTS
    • GROUP BY

  3. Two lists each contain 1 and 2. UNION of the two lists returns 1 and 2, once each.

    Circle one:   True   False

  4. What is the second way to list customers who never ordered?

    • SELECT name FROM Customers c LEFT JOIN Orders o ON o.customer_id = c.id WHERE o.customer_id IS NULL
    • SELECT name FROM Customers c INNER JOIN Orders o ON o.customer_id = c.id
    • 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. List customers who never ordered, matching orders by customer id. Orders hold one NULL customer id. Which form still answers correctly?

    • NOT IN, since it ignores NULL customer ids
    • NOT EXISTS, NULL never matches
    • NOT IN, since it treats NULL as a match

  7. CREATE VIEW NeverShipped AS SELECT name FROM Suppliers s WHERE NOT EXISTS (SELECT 1 FROM Shipments h WHERE h.supplier_id = s.id) defines a view that later queries can use as a table.

    Circle one:   True   False

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

    • EXISTS returns one row for every match
    • The join removes duplicate customers
    • Join fans out rows
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. Query text, fresh each use · Views give logic a name, not data.
  2. NOT EXISTS · NOT EXISTS filters to rows with zero matches.
  3. True · UNION removes duplicate rows, so the shared values appear once each. UNION ALL would return each value twice.
  4. SELECT name FROM Customers c LEFT JOIN Orders o ON o.customer_id = c.id WHERE o.customer_id IS NULL · The join keeps every customer, and the IS NULL test keeps only customers with no matching order.
  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, NULL never matches · For a nonmatching customer, NOT IN gives unknown when one order row's customer id, Orders.customer_id, is NULL, so WHERE leaves that customer out. NOT EXISTS finds no match, so it still answers correctly.
  7. True · CREATE VIEW, followed by the view name, AS, and the query, defines a view that later queries can treat as a table.
  8. Join fans out rows · Many matches fan out join rows; the test just checks.
Worksheet · LightMySky