Chapter 9 — Quiz

13 questions. Answers at the bottom.

Multiple choice

Q1. A scalar subquery returns: - A) A whole table - B) Exactly one value (one row, one column) - C) One column of many rows - D) Nothing

Q2. Subqueries are possible because of which property? - A) Referential integrity - B) Closure (a query returns a relation, usable inside another query) - C) Three-valued logic - D) Indexing

Q3. A subquery in the FROM clause is called a: - A) Correlated subquery - B) Derived table (and it requires an alias) - C) Scalar subquery - D) View

Q4. A correlated subquery: - A) Runs once, before the outer query - B) References a column from the outer query and is evaluated per outer row - C) Always returns one value - D) Cannot use aggregates

Q5. EXISTS (subquery) returns true when: - A) The subquery returns at least one row - B) The subquery returns exactly one row - C) The subquery returns no rows - D) The subquery returns a non-NULL value

Q6. The NULL-safe way to express "X with no matching Y" is: - A) NOT IN - B) NOT EXISTS - C) != ANY - D) EXCEPT ALL

Q7. price > ALL (SELECT price FROM products WHERE category_id = 5) means: - A) Greater than at least one audio price - B) Greater than every audio price (the maximum) - C) Equal to some audio price - D) Less than all audio prices

Q8. IN (...) is equivalent to: - A) = ALL (...) - B) = ANY (...) - C) <> ANY (...) - D) EXISTS (...)

Q9. A scalar subquery used as an expression that returns 2 rows will: - A) Return the first row - B) Error - C) Return NULL - D) Return both

Q10. You need columns from both tables in the output. You should use: - A) An IN subquery - B) A JOIN - C) EXISTS - D) NOT IN

Q11. Which never causes row duplication (fan-out) when testing for a match? - A) JOIN to a one-to-many table - B) EXISTS - C) CROSS JOIN - D) FULL OUTER JOIN

True/False

Q12. WHERE x NOT IN (SELECT ...) is always safe even if the subquery returns NULLs. (True / False)

Q13. Many subqueries and joins are interchangeable, and the optimizer often rewrites one into the other. (True / False)

Short answer

Q14. Give two reasons to prefer NOT EXISTS over NOT IN for an anti-join.

---

Answer key

Q1 — B. Scalar = one value; usable anywhere a value is expected.

Q2 — B. Closure: a query's result is a relation, so it can be nested.

Q3 — B. A derived table; PostgreSQL requires it to be aliased.

Q4 — B. Correlated subqueries reference the outer row and (conceptually) run per row.

Q5 — A. EXISTS is true if at least one row comes back; it stops at the first.

Q6 — B. NOT EXISTS is the NULL-safe anti-join (also LEFT JOIN ... IS NULL).

Q7 — B. > ALL = greater than every element = greater than the maximum.

Q8 — B. IN is = ANY.

Q9 — B. A multi-row scalar subquery errors ("more than one row returned…").

Q10 — B. Only a join can return columns from the other table; subqueries in WHERE only filter.

Q11 — B. EXISTS tests existence without multiplying rows; a one-to-many join can fan out.

Q12 — False. A NULL in the subquery makes NOT IN return no rows (three-valued logic). Use NOT EXISTS.

Q13 — True. They're frequently equivalent, and optimizers transform between them.

Q14. (1) NOT EXISTS is NULL-safe — a NULL in the inner result doesn't wipe out the whole answer the way it does for NOT IN. (2) NOT EXISTS short-circuits at the first match and often optimizes to an efficient anti-join, whereas NOT IN may materialize and scan the full list. (Also: EXISTS never causes fan-out.)

Scoring: 11–13 you've mastered nesting; 8–10 re-drill EXISTS/NOT EXISTS; below 8, redo Exercises D.