pbPassingBI
/
sql/quizzes/sql-quiz-joins
12 questions

Combining data — quiz

12 questions covering this module. Every join type, multi-table joins, and set operations.

  1. Joining orders to line_items inflates SUM(total). Why?

    1. The join is wrong type
    2. Each order repeats once per line item
    3. Nulls are counted
    4. Missing index

    Answer: Each order repeats once per line item — The one-to-many relationship duplicates the order row.

  2. The reliable fix is to:

    1. Add DISTINCT
    2. Pre-aggregate line_items before joining
    3. Use RIGHT JOIN
    4. Add an index

    Answer: Pre-aggregate line_items before joining — DISTINCT would not fix an already-inflated SUM.

  3. An INNER JOIN between orders and customers excludes:

    1. Nothing
    2. Customers with no orders, and orders with no matching customer
    3. Only null rows
    4. Duplicate rows

    Answer: Customers with no orders, and orders with no matching customer — Anything without a match on both sides is dropped.

  4. JOIN with no keyword prefix means:

    1. LEFT JOIN
    2. INNER JOIN
    3. FULL JOIN
    4. CROSS JOIN

    Answer: INNER JOIN — INNER is the default.

  5. LEFT JOIN with WHERE right.status = 'x' behaves like:

    1. LEFT JOIN
    2. INNER JOIN
    3. CROSS JOIN
    4. FULL OUTER JOIN

    Answer: INNER JOIN — The NULL-extended rows fail the WHERE and are discarded.

  6. After a LEFT JOIN, a customer with no orders and COUNT(*) shows:

    1. 0
    2. 1
    3. NULL
    4. An error

    Answer: 1 — COUNT(*) counts the null-extended row. Use COUNT(o.order_id) for 0.

  7. Which join keeps unmatched rows from both tables?

    1. INNER
    2. LEFT
    3. FULL OUTER
    4. CROSS

    Answer: FULL OUTER — FULL OUTER preserves both sides.

  8. A query unexpectedly returns millions of rows. Check first for:

    1. A missing index
    2. A missing join condition
    3. A wrong data type
    4. Too many columns

    Answer: A missing join condition — A missing ON produces a Cartesian product.

  9. An INNER JOIN placed after a LEFT JOIN can:

    1. Speed up the query
    2. Eliminate the rows the LEFT JOIN preserved
    3. Change column order
    4. Have no effect

    Answer: Eliminate the rows the LEFT JOIN preserved — The inner join requires a match, discarding null-extended rows.

  10. To find which join causes extra rows:

    1. Add DISTINCT
    2. Add joins one at a time and count after each
    3. Use EXPLAIN only
    4. Reorder the SELECT

    Answer: Add joins one at a time and count after each — Incremental counting isolates the offending join.

  11. Which is faster and why?

    1. UNION, it does less work
    2. UNION ALL, it skips deduplication
    3. They are identical
    4. Depends on indexes

    Answer: UNION ALL, it skips deduplication — Deduplication needs a sort or hash across the whole result.

  12. Which returns rows in A but not in B?

    1. UNION
    2. INTERSECT
    3. EXCEPT
    4. UNION ALL

    Answer: EXCEPT — EXCEPT (MINUS in Oracle) is the difference operator.