How to Write SQL CTEs and Subqueries with AI Quiz

Answers and explanations appear as you take the quiz.

1. A SELECT CTE named customer_totals is defined with WITH. How long does that ordinary named result exist?
   • Until the database server next restarts
   • Within the larger statement that defines it
   • Until a separate DROP TABLE command removes it
   • Across every future query in the same application
2. A customer has three qualifying orders. How does an EXISTS condition affect that customer's outer row?
   • It automatically creates three output copies
   • It adds three to every customer total
   • It fails because more than one inner row exists
   • It tests true without multiplying the row because of those matches
3. A PostgreSQL scalar subquery unexpectedly returns two rows. Which response preserves a meaningful selection rule?
   • Clarify and implement which single value the query should return
   • Add an unordered LIMIT merely to suppress the error
   • Assume PostgreSQL will always choose the latest row
   • Change the output alias and leave the subquery unchanged
4. Why should a customer_totals CTE be tested before joining it to customer details?
   • The CTE name establishes that each customer key is unique
   • An aggregate inside a CTE cannot produce repeated customer keys
   • Unexpected repeated customer keys can multiply the later result
   • Checking only the final row count establishes each intermediate result's grain
5. An AI claims that replacing every subquery with a CTE always improves performance. How should this be assessed?
   • Accept it because named steps always execute once
   • Check the target engine's behavior and execution plan
   • Accept it because a shorter query always requires less work
   • Reject every CTE because it must always be materialized
6. Why might UNION fail to stop a recursive cycle when a depth column changes at each visit?
   • UNION preserves every exact duplicate by definition
   • Depth columns prevent any recursive query from running
   • Cycles can occur only in queries using UNION ALL
   • Repeated visits can remain distinct result rows because their depths differ
7. A correlated subquery refers to an outer value. Which performance claim would be premature without plan evidence?
   • It must physically rerun a full independent query for every outer row
   • Its result logically depends on an outer value
   • An optimizer may transform an eligible expression
   • Its behavior should be tested on representative data
8. Why should null-containing fixtures be used when rewriting a NOT IN condition?
   • Nulls always behave like ordinary equal values
   • NOT IN ignores every null in its comparison set
   • Nulls can change the condition's truth result and the rewrite's behavior
   • Adding a CTE automatically removes null-related differences