SQL Quiz: Set Based Thinking
10 questions · exam conditions
0:00
Set Based ThinkingQuestion 1 of 10

A pricing rule says that a purchase receives a discount when the immediately preceding purchase by the same customer exceeded a threshold. Existing procedural code sorts purchases by timestamp and remembers the previous amount. Timestamps are not unique.

What must be addressed before the procedure can be replaced reliably with a set-based LAG expression?

The threshold must be converted into an aggregate so that one row remains for each customer.
The purchases must be physically stored on disk in the same sequence used by the procedure.
A deterministic tie-breaker must define which purchase is immediately preceding when timestamps are equal.
The previous purchase amount must be copied permanently into every purchase row before querying.
← Back to quizzes

SQL Quiz

SQL Quiz: Set Based Thinking

Practice Set Based Thinking in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Set Based Thinking, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

A pricing rule says that a purchase receives a discount when the immediately preceding purchase by the same customer exceeded a threshold. Existing procedural code sorts purchases by timestamp and remembers the previous amount. Timestamps are not unique.

What must be addressed before the procedure can be replaced reliably with a set-based LAG expression?

  1. The threshold must be converted into an aggregate so that one row remains for each customer.
  2. The purchases must be physically stored on disk in the same sequence used by the procedure.
  3. A deterministic tie-breaker must define which purchase is immediately preceding when timestamps are equal. (correct answer)
  4. The previous purchase amount must be copied permanently into every purchase row before querying.
Explanation: When replacing procedural logic with a window function like LAG, your first instinct should be to ask: does the window function produce the same deterministic result as the procedure? The procedure works sequentially — it sorts rows and remembers the previous value step by step. LAG replicates this only if the ordering inside the OVER clause produces a single, unambiguous sequence. That's exactly why C is correct. When two purchases share the same timestamp, SQL has no inherent rule about which one comes "immediately before" the other. The procedure likely handled ties implicitly — perhaps by insertion order or memory address — but LAG requires an explicit ORDER BY that produces a strict ranking. Without a tie-breaker (like a unique purchase ID added as a secondary sort key), the engine may process tied rows in any order, giving you non-deterministic results that silently differ from the procedure's output. A is wrong because converting a threshold into an aggregate is a completely unrelated operation — discounts are row-level calculations, not aggregations, and collapsing rows per customer would destroy the data you need. B is wrong because SQL's relational model intentionally decouples logical query results from physical storage order. LAG is governed by its ORDER BY clause, not by how rows sit on disk — physical sequence provides no guarantee. D is wrong because LAG is designed precisely to avoid permanently copying prior-row values. Materializing the previous amount into every row would be redundant and defeats the purpose of using a window function. Your study tip: whenever you use LAG or LEAD, always ask whether your ORDER BY is truly deterministic — if any tied rows exist, add a unique column as a tiebreaker or your results cannot be trusted.

Question 2

A join intentionally returns one result row for every matching product-tag association. Because a product can have several matching tags, the same product identifier may appear more than once in the result.

A reviewer says the query cannot be set-based unless DISTINCT is added. Which response is most accurate?

  1. The query can still be set-based because SQL commonly preserves duplicates unless the requested semantics remove them. (correct answer)
  2. The reviewer is correct because every set-based query must return each selected identifier exactly one time.
  3. The reviewer is correct because duplicate identifiers prove that the join was evaluated one row at a time.
  4. The query can be set-based only when duplicate identifiers originate from a single base table rather than a join.
Explanation: When you see a question mixing the terms "set-based" and "duplicates," you need to recall what set-based actually means in SQL. Set-based processing means the database engine operates on entire collections of rows at once — as opposed to procedural, row-by-row cursor logic. It says nothing about whether duplicates exist in the result. SQL works with multisets (also called bags), not strict mathematical sets. A multiset allows duplicate values, and SQL preserves them by default. So a query can be entirely set-based — processed in bulk by the optimizer — and still return repeated product identifiers because one product matched multiple tags. That's exactly why A is correct: set-based processing and duplicate results are independent concepts, and SQL's standard behavior is to keep duplicates unless you explicitly request otherwise with DISTINCT or GROUP BY. B reflects a common misconception that "set-based" maps directly to mathematical set theory, where each element appears exactly once. SQL deliberately departs from that strict definition by using multisets, making this wrong. C is a logical non-sequitur — duplicate identifiers in a result are produced by the join condition matching multiple rows, not by row-at-a-time processing. You can get duplicates from a fully parallel, set-based execution plan. D invents a false rule; duplicates from joins are just as normal and valid as duplicates from a single table scan. As a study tip, remember: set-based = bulk processing, not uniqueness. On SQL exams, watch for questions that conflate these two ideas — they're testing whether you know that DISTINCT controls output, not execution style.

Question 3

After identifying customers who meet a set of SQL conditions, an application must send one personalized message to each customer through an external service that accepts individual requests. Sending a request is not a transactional database operation.

Which design best applies set-based thinking without ignoring the external side effect?

  1. Call the external service from a database cursor while the customer-selection transaction remains open.
  2. Select recipients and enqueue message records set-wise, then let workers perform the individual external calls. (correct answer)
  3. Combine all recipients into one database row and send one identical external request for the entire group.
  4. Select recipients one at a time because any downstream individual action requires row-by-row SQL selection.
Explanation: When a problem mixes database work with external side effects, the core challenge is preserving set-based SQL efficiency without forcing a non-transactional service into a place it doesn't belong. The guiding principle: do the SQL work in bulk, then hand off individual tasks appropriately. Option B is the right design because it honors both constraints. You select all qualifying recipients in a single set-based query — that's efficient, transactional, and idiomatic SQL. Then you enqueue message records (also a set-wise database operation), decoupling the selection from the delivery. Background workers then make individual external calls at their own pace, outside any database transaction. This pattern is called the transactional outbox or queue-based approach, and it's a standard solution for coordinating databases with external services. Option A is dangerous: keeping a transaction open while calling an external service ties up database locks for an unpredictable duration and couples a non-transactional operation to a transactional one. If the external call is slow or fails, you've held resources hostage. Option C is a category error — combining all recipients into one row destroys the relational model and sending one identical message defeats the entire purpose of personalization. Option D reflects a common misconception: that individual actions downstream require individual SQL selections upstream. SQL's power is retrieving sets; what happens after retrieval is a separate concern. Study tip: On SQL design questions, watch for answers that confuse how data is selected with how results are consumed. Set-based selection and row-by-row processing can coexist — the key is where the boundary sits.

Question 4

A maintenance job must delete millions of expired rows. To limit transaction-log growth and lock duration, it repeatedly deletes bounded groups of qualifying rows using indexed key ranges. Each statement deletes many rows, and the job records the last completed key range.

How should this design be classified?

  1. It is row-by-row because executing any SQL statement repeatedly is equivalent to using a cursor.
  2. It is set-based only if all expired rows are deleted by one statement and one transaction.
  3. It is row-by-row because recording a key boundary introduces procedural state between statements.
  4. It is batched set-based processing because each statement operates on a qualifying subset rather than one row. (correct answer)
Explanation: When evaluating batch deletion strategies, the key distinction to understand is the difference between row-by-row processing and set-based processing — and recognizing that set-based thinking exists on a spectrum that includes batching. Set-based processing means each SQL statement operates on a set of rows simultaneously, letting the database engine optimize the operation internally. A statement that deletes thousands of rows matching an indexed key range is fundamentally set-based — the engine processes qualifying rows as a group, not one at a time. Wrapping multiple such statements in a loop to manage transaction size doesn't change the nature of each individual statement. This is classic batched set-based processing, making D the correct answer: each statement targets a meaningful qualifying subset, and the loop structure exists purely for operational control (log growth, lock duration), not to simulate row-level logic. A is wrong because repeating a SQL statement in a loop is not equivalent to cursor-based row-by-row processing. Cursors fetch and process one row per iteration; here each iteration handles thousands of rows as a set. Repetition alone doesn't define row-by-row behavior — granularity does. B imposes a false requirement. Set-based processing doesn't demand a single statement or single transaction. That definition would make virtually all large-scale batch jobs "row-by-row," which is both impractical and conceptually incorrect. C misidentifies what "procedural state" means in this context. Recording a key boundary is bookkeeping for fault tolerance and progress tracking — it has no bearing on whether each SQL statement processes rows as a set. Study tip: Always ask "what does one iteration of this process operate on?" If it's a set of rows, it's set-based — regardless of how many iterations occur.

Question 5

A transaction report must display a running balance for each account. Transactions are ordered by posted_at, and transaction_id uniquely breaks ties when multiple transactions have the same timestamp.

Which approach is the strongest example of set-based thinking for this report?

  1. Use a windowed sum partitioned by account and ordered by posted_at, transaction_id. (correct answer)
  2. Open one cursor per account and add each transaction amount to a balance variable.
  3. Use a grouped sum by account and repeat the final account total on every transaction.
  4. Use a windowed sum partitioned by account but omit ordering within each account partition.
Explanation: When a question asks about "set-based thinking" in SQL, you should immediately think about solving problems by describing what data you want rather than how to loop through it row by row. Relational databases are optimized to process entire sets of data at once, and window functions are the premier tool for calculations that need both per-row detail and aggregate context simultaneously. A running balance is the perfect use case for a windowed SUM(). By partitioning on account, you keep each account's balance independent. By ordering on posted_at, transaction_id, you establish the precise, deterministic sequence of transactions — including correct tie-breaking — and SQL computes the cumulative sum across that ordered frame automatically. Option A describes this exactly, making it the strongest set-based solution. Option B, using a cursor per account, is the opposite of set-based thinking. Cursors iterate row by row, shifting logic from the database engine to procedural code. This is slower, harder to maintain, and exactly what SQL was designed to replace for this kind of task. Option C uses a grouped SUM(), which collapses rows into one total per account. Repeating that final total on every transaction row gives you the ending balance everywhere — not a running balance — so it fundamentally misrepresents the data. Option D is tempting but critically flawed: omitting the ORDER BY inside the window partition means the database has no defined sequence, so the "running" sum is non-deterministic. You'd get unpredictable results that look like a running balance but aren't reliable. A useful study rule: any time you see "running," "cumulative," or "rolling" in a report requirement, think window function with both PARTITION BY and ORDER BY.

Question 6

A query returns customers whose total order amount exceeds a threshold. It uses a correlated subquery that references the current customer's identifier when summing orders. A reviewer describes the query as row-by-row solely because the subquery is written in correlated form.

Which conclusion best reflects set-based thinking?

  1. Every correlated subquery is procedural because its text references an outer row, forcing literal row-by-row evaluation.
  2. The query is set-based only if the database documents that it uses a hash aggregate internally.
  3. Correlation alone does not make the query procedural; the optimizer can transform it into set-oriented join and aggregate operations. (correct answer)
  4. Correlation is set-based only when the correlated subquery references a single base table with no joins.
Explanation: When SQL questions contrast "correlated subquery" with "set-based processing," you need to separate syntax from execution strategy. A correlated subquery looks like it references one outer row at a time in its text, but that is a matter of how you write the query — not necessarily how the database engine runs it. Modern query optimizers are specifically designed to recognize correlated subqueries and transform them into equivalent join-and-aggregate operations, which are processed over entire sets simultaneously. The optimizer doesn't care that you wrote a correlation; it cares about producing the correct result efficiently. This is exactly why C is correct — correlation in the SQL text does not force literal row-by-row evaluation. The optimizer can "decorrelate" the subquery and execute it as a grouped aggregation joined back to the outer table, a fully set-based operation. A is wrong because it conflates syntax with execution. Saying a correlated subquery forces procedural, row-by-row evaluation is the core misconception this question is testing. The optimizer routinely eliminates this behavior. B is wrong because set-based thinking is a logical property of how you express data retrieval, not something that depends on which internal algorithm (like a hash aggregate) the engine happens to choose. D is wrong because it invents a fictional restriction — correlation has nothing to do with whether the subquery touches one table or many, and that condition has no bearing on whether processing is set-based. Study tip: On SQL exam questions, always distinguish between how a query is written and how the optimizer executes it — these are frequently two very different things, and conflating them is the most common trap.

Question 7

An Accounts table stores one row per account, including the current balance. A Transactions table contains several new transactions per account. Accounts without new transactions must remain unchanged, and each affected account must have the sum of all its new transactions added exactly once.

Which design most directly applies set-based thinking while preserving the required result?

  1. Fetch each transaction, locate its account, and increment the balance once for every fetched transaction.
  2. Aggregate transactions by account, then update accounts by joining to the single grouped result for each account. (correct answer)
  3. Join accounts directly to ungrouped transactions, then issue one update that references each matching transaction amount.
  4. Aggregate transactions by account, then replace every account balance with the corresponding grouped transaction total.
Explanation: When working with bulk updates in SQL, the key question to ask yourself is: "Am I processing one row at a time, or am I letting the database engine operate on entire sets simultaneously?" Set-based thinking means preparing your data as a complete, pre-aggregated result before touching the target table — this is both more efficient and more correct. Option B is the right approach because it first groups the Transactions table by account, producing exactly one summary row per account with the correct total. That aggregated result is then joined to Accounts, and each account receives its balance increment in a single, clean update. Every affected account is touched once, unaffected accounts are left alone, and the logic mirrors how relational databases are designed to work. Option A is the classic row-by-row anti-pattern — looping through individual transactions and updating the account for each one. Even if the final numbers were correct, this is procedural thinking, not set-based, and it risks incremental intermediate states if anything fails partway through. Option C joins directly to ungrouped transactions, meaning an account with three transactions would match three rows. The update would apply multiple times per account, likely overwriting itself or producing wrong totals depending on update semantics. Option D looks similar to B but contains a critical flaw: it replaces the balance with the transaction total rather than adding to the existing balance. Accounts lose their prior balance entirely, which violates the requirement that the new transactions be added to the current balance. As a study tip, watch for options that correctly aggregate but then use assignment (=) instead of addition (+= / = balance + total) — that subtle swap is a common trap in update-join questions.

Question 8

An annual process must increase salaries for all qualifying employees and write an audit record containing each affected employee's old and new salary. Either both changes must succeed for all qualifying employees or neither change may remain.

Which design most directly combines set-based processing with the required consistency?

  1. Run one salary update, commit it, and then select qualifying employees to create audit rows.
  2. Update one employee, insert that employee's audit row, and commit before processing the next employee.
  3. Use a cursor for both actions inside one transaction because atomicity requires processing employees individually.
  4. Use set-oriented update and audit capture operations within one transaction covering the complete logical change. (correct answer)
Explanation: When a question asks about combining set-based processing with consistency, you need to think about two core SQL principles simultaneously: transactions (which enforce all-or-nothing atomicity) and set-based operations (which act on entire result sets at once rather than row by row). The passage demands exactly this combination — every qualifying employee must be updated and audited together, or nothing sticks. Answer D delivers precisely that: a single transaction wrapping both a set-based UPDATE across all qualifying employees and a corresponding set-based INSERT into the audit table. One transaction means atomicity is guaranteed across the complete logical change, and set-based operations mean you're processing all rows efficiently in one sweep rather than looping. Answer A breaks atomicity by committing the salary update before inserting audit rows — if something fails mid-audit, you're left with updated salaries and no audit trail, violating the business rule entirely. Answer B commits after each individual employee, which means a failure halfway through leaves some employees updated and audited while others are skipped, again destroying the all-or-nothing guarantee. Answer C is the sneakiest distractor: it correctly keeps everything in one transaction, but uses a cursor to process employees one at a time. This is the classic row-by-row anti-pattern — you get atomicity but sacrifice the set-based efficiency the question explicitly requires, and cursor logic is far more complex than necessary here. D is correct because it's the only option that satisfies both requirements simultaneously. A helpful study rule: whenever a question mentions "either all or none" plus "set-based," look for the single-transaction, set-oriented solution — that combination is almost always the right answer.

Question 9

A developer writes one declarative query using joins, filtering, and grouping. The execution plan contains a nested-loops join, so another developer claims that the query is not truly set-based because the engine processes inner rows repeatedly.

Which assessment is most accurate?

  1. The claim is correct because only hash joins and merge joins can execute set-based SQL operations.
  2. The claim is incorrect because set-based formulation is logical; the optimizer may choose iterative physical operators. (correct answer)
  3. The claim is correct whenever an execution plan accesses the same index more than one time.
  4. The claim is incorrect only if the nested-loops join returns no duplicate rows in its result.
Explanation: When studying SQL execution, you must keep two layers clearly separate: the logical layer (how you write the query) and the physical layer (how the engine executes it). Set-based thinking belongs to the logical layer — it means you describe what data you want, not how to retrieve it row by row. The optimizer then independently decides which physical operators to use. This is exactly why B is correct. A developer who writes a single declarative SQL statement using joins, filters, and grouping has expressed a set-based operation regardless of what the execution plan looks like. The optimizer choosing a nested-loops join is a physical implementation detail — it's the engine's strategy, not a property of your query's logic. Nested loops are a perfectly valid way to execute a set-based query; the result is still the complete, correct set of rows described by your SQL. A is wrong because it confuses physical join algorithms with logical query style. Hash joins and merge joins are not prerequisites for set-based SQL — they're just alternative execution strategies the optimizer may prefer based on statistics and indexes. C is wrong because repeated index access is also a physical execution detail. An index being accessed multiple times says nothing about whether the query was formulated in a set-based manner. D is wrong because the correctness of set-based formulation has nothing to do with whether duplicates appear in the result. Introducing duplicates as a criterion is a red herring with no basis in how set-based processing is defined. As a study tip: whenever a question conflates how the optimizer executes a query with how the developer wrote it, remember that SQL's declarative nature means these two things are intentionally decoupled.

Question 10

A customer qualifies for archival only when the customer has at least one invoice and every invoice for that customer has status PAID. Invoice status is never null.

Which logical formulation best expresses the qualification as a set operation?

  1. Select customers for whom no invoice with status other than PAID exists.
  2. Select customers for whom at least one invoice with status PAID exists.
  3. Select customers whose number of paid invoices exceeds their number of unpaid invoices.
  4. Select customers having an invoice and for whom no invoice with status other than PAID exists. (correct answer)
Explanation: When translating a business rule into SQL logic, you need to capture every condition the rule states — missing even one condition produces a query that qualifies the wrong rows. The rule here has two distinct requirements: (1) the customer must have at least one invoice, and (2) every invoice must be PAID. This "universal" condition — all members of a set satisfying a property — is classically expressed in SQL using a double negative: instead of "every invoice is PAID," you write "no invoice exists that is NOT PAID." That's the core technique being tested. D is correct because it enforces both conditions explicitly. Requiring at least one invoice handles customers with no invoices at all, and requiring no invoice with status other than PAID enforces the universality condition. Together, these two constraints match the rule exactly. A fails because it only checks that no unpaid invoice exists — it says nothing about whether the customer has any invoices at all. A customer with zero invoices would pass this filter, incorrectly qualifying for archival. B fails because it only requires one paid invoice, completely ignoring whether other unpaid invoices also exist. A customer with one PAID and five UNPAID invoices would wrongly qualify. C fails because comparing counts of paid vs. unpaid invoices doesn't enforce universality. A customer with 3 paid and 2 unpaid invoices would pass even though they have unpaid invoices — violating the rule. Study tip: Whenever a rule says "every X must satisfy Y," translate it as "no X exists that does NOT satisfy Y," and always check whether an additional existence condition is also required.