SQL Quiz: Parameterized Reporting Queries
10 questions · exam conditions
0:00
Parameterized Reporting QueriesQuestion 1 of 10

A grouped report returns one row per salesperson. It accepts optional parameter :minimum_total; when supplied, only salespeople whose summed sale amount reaches that threshold should appear. When the parameter is NULL, all salesperson groups should appear.

Which clause correctly implements the parameterized group filter?

WHERE :minimum_total IS NULL OR SUM(amount) >= :minimum_total before GROUP BY salesperson_id
HAVING :minimum_total IS NOT NULL AND SUM(amount) >= :minimum_total after GROUP BY salesperson_id
WHERE :minimum_total IS NULL OR amount >= :minimum_total before GROUP BY salesperson_id
HAVING :minimum_total IS NULL OR SUM(amount) >= :minimum_total after GROUP BY salesperson_id
← Back to quizzes

SQL Quiz

SQL Quiz: Parameterized Reporting Queries

Practice Parameterized Reporting Queries 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 Parameterized Reporting Queries, 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 grouped report returns one row per salesperson. It accepts optional parameter :minimum_total; when supplied, only salespeople whose summed sale amount reaches that threshold should appear. When the parameter is NULL, all salesperson groups should appear.

Which clause correctly implements the parameterized group filter?

  1. WHERE :minimum_total IS NULL OR SUM(amount) >= :minimum_total before GROUP BY salesperson_id
  2. HAVING :minimum_total IS NOT NULL AND SUM(amount) >= :minimum_total after GROUP BY salesperson_id
  3. WHERE :minimum_total IS NULL OR amount >= :minimum_total before GROUP BY salesperson_id
  4. HAVING :minimum_total IS NULL OR SUM(amount) >= :minimum_total after GROUP BY salesperson_id (correct answer)
Explanation: Whenever you see a question mixing aggregation with optional parameters, ask yourself two things: where does the filter belong (WHERE vs. HAVING), and what logic handles the "no filter" case? The critical rule is that WHERE filters individual rows before grouping, while HAVING filters groups after aggregation. Because this report filters based on each salesperson's total (a SUM), the filter must go in a HAVING clause — it simply doesn't exist yet at the WHERE stage. Option D — HAVING :minimum_total IS NULL OR SUM(amount) >= :minimum_total — is correct because it does both things right. When :minimum_total is NULL, the first condition short-circuits to TRUE, so every group passes through. When a value is supplied, only groups whose summed amount meets the threshold are returned. That's exactly the optional-filter behavior the question describes. Option A fails on placement: you cannot reference SUM(amount) inside a WHERE clause, because aggregation hasn't happened yet. Even if the syntax were accepted, it would filter individual rows rather than salesperson totals. Option B gets the placement right (HAVING) but inverts the NULL logic. Using IS NOT NULL AND ... means the filter only activates when a parameter is provided — so far so good — but it excludes all groups when the parameter is NULL, rather than returning everyone. That's the opposite of the requirement. Option C compounds two mistakes: it's in WHERE (wrong stage) and it compares individual amount values rather than the aggregated SUM, so it would never correctly represent a salesperson's total sales. A handy rule of thumb: if the condition involves an aggregate function, it belongs in HAVING. If it needs to "turn off" when a parameter is NULL, use IS NULL OR <condition> as the short-circuit.

Question 2

A reporting service repeatedly runs a query filtered by invoices.customer_id, an indexed integer column. The application currently constructs SQL by inserting the supplied customer value as quoted text. The report must be safer and should avoid unnecessary type conversions that could interfere with index use.

Which revision best meets both goals?

  1. Bind the value as an integer parameter and compare customer_id = :customer_id without converting the column. (correct answer)
  2. Bind the value as a string parameter and compare CAST(customer_id AS VARCHAR) = :customer_id for consistency.
  3. Validate that the value contains digits, then concatenate it unquoted into the SQL statement before execution.
  4. Escape quotation marks in the value, concatenate it as text, and rely on the database's implicit conversion.
Explanation: When a question combines SQL injection prevention with index efficiency, you need to evaluate two criteria simultaneously: security and performance. The safest and fastest approach uses parameterized queries with the correct data type. Option A is the right choice because binding customer_id as an integer parameter does both jobs at once. Parameterized queries separate SQL code from user-supplied data, eliminating injection risk entirely — the database engine never interprets the value as SQL syntax. Equally important, comparing an integer column to an integer parameter means the database can use the index on customer_id directly, with no implicit or explicit type conversion needed. Option B is tempting because it also uses a parameter, but casting customer_id AS VARCHAR is a critical flaw. Applying a function to an indexed column typically disables index usage — the database must evaluate the expression for every row rather than scanning the index efficiently. You've traded a performance win for unnecessary work. Option C avoids quotes but still builds SQL through string concatenation, just with a digit-only validation check. This is a common trap: validation can be bypassed or incomplete, and concatenation — even "safe" concatenation — is fundamentally the wrong pattern. Parameterized queries are the standard for a reason. Option D compounds the original problem. Escaping quotes and concatenating text still leaves you vulnerable to edge cases and encoding tricks, and still risks implicit type conversion costing you index performance. The key study tip: always match the parameter type to the column type. Using the right data type in a parameterized query is both the security best practice and the performance best practice.

Question 3

A report accepts a parameter :search_text that users intend as literal text. For example, the value 50%_off must match names containing the literal characters 50%_off; % and _ in the parameter must not act as SQL wildcards.

Which implementation is most appropriate?

  1. Bind '%' || :search_text || '%' directly to LIKE, allowing any wildcard characters already in the parameter.
  2. Escape %, _, and the escape character within the parameter, then use LIKE with an explicit ESCAPE character. (correct answer)
  3. Replace % and _ with empty strings in the parameter, then surround the remaining value with % wildcards.
  4. Use equality against the bound parameter after converting both the column and parameter to the same character case.
Explanation: When using LIKE for user-supplied search terms, your central concern is wildcard injection: characters like % and _ carry special meaning in SQL pattern matching, so if a user's literal text contains them, you must neutralize them before the database interprets the pattern. The correct approach — B — is to escape the wildcard characters inside the parameter value before passing it to LIKE. SQL provides the ESCAPE clause precisely for this purpose. You choose an escape character (commonly \ or !), then replace every % with \%, every _ with _, and every literal \ with \\ inside the parameter. Then your query reads column LIKE '%' || :escaped_text || '%' ESCAPE '\'. This way, 50%_off becomes 50\%_off, and the database treats % and _ as plain characters rather than wildcards. The result is an accurate substring search with no unintended pattern behavior. A is the classic wildcard injection trap. Concatenating % around the raw parameter and passing it straight to LIKE allows any % or _ already in the user's input to act as wildcards, producing incorrect or unpredictable matches. C strips the problematic characters entirely, which destroys the user's intended search string. A search for 50%_off would become a search for 50off, which is a completely different value — data is lost before the query even runs. D sidesteps LIKE in favor of equality (=), which handles case normalization but performs an exact match, not a substring search. This fails to find 50%_off when it appears inside a longer string. As a study habit, whenever you see user input flowing into a LIKE clause, immediately ask: "Have the wildcards been escaped?" If not, the query is almost certainly flawed.

Question 4

A paginated audit report accepts bound parameters :page_size and :offset. Many rows share the same event_time. Users expect repeated executions over unchanged data to assign each row to the same page.

Which query design best provides repeatable pagination?

  1. Order by event_time alone, then apply the parameterized limit and offset because timestamps establish chronological order.
  2. Omit ORDER BY and depend on the audit table's insertion sequence before applying limit and offset.
  3. Apply the parameterized limit and offset first, then sort the selected rows by event_time, event_id.
  4. Order by event_time, event_id, where event_id is unique, then apply the parameterized limit and offset. (correct answer)
Explanation: Whenever you see a question about pagination in SQL, the key concept to focus on is determinism — will the same query return the same rows in the same order every time? LIMIT/OFFSET pagination only works reliably when the ORDER BY produces a strict, unambiguous sequence across all rows. The winning approach is D because it sorts by event_time, event_id, where event_id is unique. Even when hundreds of rows share the same timestamp, the tiebreaker event_id guarantees every row lands in exactly one position in the result set. Applying your parameterized limit and offset on top of that stable order means page 3 today will contain the same rows as page 3 tomorrow — true repeatable pagination. A fails because sorting by event_time alone doesn't break ties. When multiple rows share a timestamp, the database is free to return them in any order it finds convenient (index scan direction, parallel execution, etc.). You'll get non-deterministic page boundaries. B is even more dangerous — omitting ORDER BY entirely means the database has no obligation whatsoever to return rows in any consistent order. Insertion sequence is not guaranteed; query planners routinely reorder rows for performance. C gets the operation order backwards. Sorting after applying limit and offset only sorts the rows already selected for that page — it doesn't control which rows land on which page. The selection step still has no stable ordering. Study tip: When reviewing pagination queries, always ask: "Is my ORDER BY unique for every row?" If not, add a unique column as a tiebreaker before applying any offset.

Question 5

Event timestamps are stored in events.occurred_at_utc as UTC. A report accepts a local calendar date and an IANA time-zone parameter. It must return events occurring during that local day, including on daylight-saving transition dates, while retaining the ability to use an index on the UTC timestamp column.

Which filtering strategy is most reliable?

  1. Convert the UTC timestamp column to the requested local date for every row, then compare it with the date parameter.
  2. Treat local midnight as UTC, add exactly 24 hours, and compare the UTC column with those two values.
  3. Convert the local start midnight and next local midnight separately to UTC, then use a half-open range on the UTC column. (correct answer)
  4. Subtract the zone's current UTC offset from both local boundaries, then use BETWEEN on the UTC timestamp column.
Explanation: Whenever you see a question about filtering time-zone-aware data, think about two competing goals: correctness across DST transitions and index sargability (the ability to use an index efficiently). The right strategy must satisfy both. The reliable approach — answer C — works by converting the boundaries to UTC rather than converting every stored value. You take local midnight of the requested date and the next local midnight, convert each independently to UTC, and then filter with a half-open range (>= start_utc AND < end_utc) on the raw UTC column. Because you're comparing the unmodified indexed column against two precomputed constants, the database can perform a range scan. Critically, converting both midnights separately handles DST correctly: a "spring forward" day is only 23 hours long, and a "fall back" day is 25 hours — converting each boundary independently captures that exactly. A is wrong because wrapping the column in a function (e.g., CONVERT_TZ(occurred_at_utc, 'UTC', tz)) defeats index usage, forcing a full table scan on every query execution. B assumes every local day is exactly 24 hours, which breaks on DST transition days — you'll either miss an hour or double-count one. D uses the current UTC offset, but DST means the offset isn't constant. Subtracting a single fixed offset from both boundaries produces the wrong window whenever the local offset at the start of the day differs from the offset at the end. A useful rule of thumb: move the math to the parameters, not to the column. Convert boundaries once, keep the indexed column untouched, and always convert each boundary independently when time zones are involved.

Question 6

A report has an optional :department_code filter. The user interface submits an empty string when the field is left blank, but department code '' is not a meaningful stored value. The SQL predicate currently uses :department_code IS NULL OR department_code = :department_code, causing a blank submission to return no rows.

Which change most clearly establishes the intended parameter contract?

  1. Normalize an empty string input to NULL before binding it, then retain the existing optional-filter predicate so the filter is disabled consistently whenever the field is blank. (correct answer)
  2. Change the predicate to department_code = COALESCE(:department_code, department_code) and bind the empty string unchanged so the database handles the omission implicitly.
  3. Add OR department_code IS NULL to the predicate so that a blank submission also returns departments that have no assigned code.
  4. Replace the empty string with % before binding and compare the department code using LIKE :department_code to treat omission as a match-all wildcard.
Explanation: When designing optional filters in SQL, the key principle is parameter contract clarity: the application and the database should agree on what "no filter" means. The cleanest convention is to use NULL as the sentinel value for "omitted," because SQL's NULL semantics are well-defined and the existing predicate :department_code IS NULL OR department_code = :department_code already relies on exactly that contract. The bug here is a mismatch at the boundary: the UI sends an empty string, but the predicate expects NULL to disable the filter. The fix isn't to rewrite the predicate — it's to honor the contract by converting '' to NULL before binding. That's precisely what A does: normalize the empty string to NULL in application code, then bind it. The existing predicate works correctly, the contract is explicit, and no rows are silently excluded. B is tempting but flawed. COALESCE(:department_code, department_code) returns department_code when the parameter is NULL, making every row match — but it still fails when an empty string is bound, because COALESCE('', department_code) returns '', not department_code. The empty string problem is never actually solved. C adds OR department_code IS NULL, which changes the semantics of the query — now blank submissions also return rows where no department is assigned, which is logically unrelated to "no filter selected." D replaces the empty string with % and switches to LIKE, which is a clever hack but introduces a hidden convention (% means "all") that's fragile, undocumented, and surprising to future maintainers. The strategy to remember: fix bad input at the boundary, not inside the SQL. Predicates should encode business logic, not compensate for upstream encoding inconsistencies.

Question 7

A reusable sales report accepts an optional parameter :region. When :region is non-NULL, the report must return only rows having that region. When :region is NULL, it must return all rows, including rows whose region value is NULL.

Which predicate implements the required behavior?

  1. WHERE region = COALESCE(:region, region)
  2. WHERE :region IS NULL OR region = :region (correct answer)
  3. WHERE region = :region OR region IS NULL
  4. WHERE COALESCE(region, :region) = :region
Explanation: Whenever you see a conditional filter that depends on whether a parameter is NULL, think through two separate scenarios: what happens when the parameter has a value, and what happens when it's NULL. The predicate must handle both cases correctly — including the tricky edge case of NULL data in the column itself. Option B, WHERE :region IS NULL OR region = :region, handles both scenarios cleanly. When :region is NULL, the first condition IS NULL evaluates to TRUE, making the entire OR expression TRUE for every row — so all rows are returned, even those where region is NULL. When :region has a value, the first condition is FALSE, so SQL evaluates the second condition and returns only rows where region matches. Option A, WHERE region = COALESCE(:region, region), looks clever but fails when :region is NULL and region is also NULL. COALESCE(:region, region) becomes COALESCE(NULL, NULL), which is NULL, and NULL = NULL evaluates to UNKNOWN (not TRUE) in SQL — so rows with a NULL region are silently dropped. Option C, WHERE region = :region OR region IS NULL, seems to address NULLs but does the opposite of what's needed. When :region has a value (say, 'West'), this returns matching rows plus every row where region is NULL — incorrect. Option D, WHERE COALESCE(region, :region) = :region, has the same flaw as A in reverse: when :region is NULL, the expression becomes COALESCE(region, NULL) = NULL, and any = comparison with NULL returns UNKNOWN. The key study tip: never use = to compare with something that might be NULL. Use IS NULL / IS NOT NULL for NULL checks, and structure OR logic to short-circuit cleanly.

Question 8

A customer report must always return every customer. It left-joins orders, and an optional :minimum_date parameter should restrict which orders contribute to each customer. Customers with no qualifying orders must still appear with an order count of zero.

Which query structure preserves the required customer rows?

  1. LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE :minimum_date IS NULL OR o.order_date >= :minimum_date
  2. LEFT JOIN orders o ON o.customer_id = c.customer_id AND (:minimum_date IS NULL OR o.order_date >= :minimum_date) (correct answer)
  3. INNER JOIN orders o ON o.customer_id = c.customer_id AND (:minimum_date IS NULL OR o.order_date >= :minimum_date)
  4. LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE o.order_date >= COALESCE(:minimum_date, o.order_date)
Explanation: Whenever you see a LEFT JOIN paired with an optional filter, ask yourself: where does the filter live? This distinction is the heart of the question. In a LEFT JOIN, conditions placed in the ON clause are evaluated before rows are joined — they determine which right-table rows match, but they never eliminate left-table rows. Conditions placed in the WHERE clause, however, are evaluated after the join, filtering the full result set and effectively converting your LEFT JOIN into an INNER JOIN for any row where the right side is NULL. B is correct because placing (:minimum_date IS NULL OR o.order_date >= :minimum_date) directly in the ON clause means only qualifying orders attach to each customer. When no orders qualify, the customer still appears with NULLs from the orders side — which your COUNT or aggregation then correctly treats as zero. A looks reasonable at first, but the WHERE clause runs after the join. If a customer has no orders meeting the date condition, o.order_date is NULL, the WHERE predicate fails, and that customer row is dropped entirely — defeating the entire purpose of the LEFT JOIN. C uses an INNER JOIN, which immediately excludes any customer with no matching orders, guaranteed to drop customers with no qualifying history. D applies COALESCE(:minimum_date, o.order_date) in the WHERE clause. When o.order_date is NULL (no orders), the comparison fails and the customer is again silently eliminated. Study tip: Always ask, "Does my filter belong in ON or WHERE?" For LEFT JOINs, any condition on the optional (right) table must live in the ON clause to preserve outer rows.

Question 9

A reporting API accepts a collection parameter :selected_categories. The collection may contain repeated values. Each matching product must appear exactly once, and category values must remain bound parameters rather than being concatenated into SQL text.

Which query pattern best satisfies these requirements?

  1. Join products to the parameter collection on category and use DISTINCT in the select list to eliminate duplicate product rows.
  2. Use WHERE EXISTS against the parameter collection, comparing its category value to the product's category, so duplicates in the collection do not multiply product rows and values stay parameterized. (correct answer)
  3. Combine all collection values into one comma-separated scalar parameter and use WHERE category IN (:selected_categories) to match products.
  4. Concatenate quoted collection values directly inside an IN clause and execute the resulting SQL string.
Explanation: When a query must match rows against a parameterized collection while guaranteeing no duplicate output rows and no SQL injection risk, you need to think about three things simultaneously: deduplication logic, how duplicates in the input collection affect output, and whether values stay bound parameters. WHERE EXISTS (answer B) elegantly handles all three constraints at once. It checks whether at least one matching row exists in the parameter collection for a given product — so even if :selected_categories contains "Electronics" three times, each product row is evaluated as a boolean condition and appears at most once. Values remain bound parameters inside the subquery, never concatenated into SQL text. Answer A falls into a subtle trap: joining products to the collection on category creates one output row per matching collection entry, so a category repeated three times produces three duplicate product rows. Adding DISTINCT fixes the output, but it's an inefficient workaround that does extra work the database shouldn't need to do — and obscures intent. Answer C is technically appealing but practically broken. Most database drivers treat a parameter as a single scalar value. Passing "Books,Electronics" as one string won't expand into an IN list — the database sees it as a literal string, not two separate values, so the query returns nothing or wrong results. Answer D is the most dangerous choice: concatenating user-supplied values directly into SQL text opens a classic SQL injection vulnerability and violates the explicit requirement that values remain bound parameters. As a study tip, whenever a question mentions "parameterized" and "collection," EXISTS is usually the safest pattern — it naturally deduplicates and keeps values bound without any string manipulation.

Question 10

A report receives :start_date = '2026-02-01' and :end_date = '2026-02-28' as DATE parameters. The orders.created_at column is a timestamp. The report must include every order from both boundary dates, regardless of timestamp precision.

Which filter most reliably implements the requested inclusive date range?

  1. WHERE created_at BETWEEN :start_date AND :end_date
  2. WHERE created_at >= :start_date AND created_at < :end_date
  3. WHERE created_at >= :start_date AND created_at < :end_date + INTERVAL '1' DAY (correct answer)
  4. WHERE CAST(created_at AS DATE) > :start_date AND CAST(created_at AS DATE) <= :end_date
Explanation: Whenever you filter a timestamp column against date parameters, you need to think carefully about what values fall on the boundary. A date like '2026-02-28' implicitly means '2026-02-28 00:00:00' — so any order placed at, say, '2026-02-28 14:35:00' has a timestamp greater than that value. This is the core trap this question is testing. Option C — created_at >= :start_date AND created_at < :end_date + INTERVAL '1' DAY — is the correct approach. By adding one day to the end date, the upper bound becomes '2026-03-01 00:00:00'. Every timestamp on February 28th, no matter how late in the day, is strictly less than that boundary, so all orders are captured correctly without any ambiguity. Option A uses BETWEEN, which translates to >= start AND <= end. Since end_date resolves to '2026-02-28 00:00:00', any order after midnight on that day is excluded — making this not truly inclusive. Option B is actually worse than A in the opposite direction: < :end_date excludes all of February 28th entirely, since even 00:00:00 on that date fails the strict less-than check. Option D casts the timestamp to a date before comparing, which can work in some databases but is a performance anti-pattern — it prevents the database from using an index on created_at. It also uses > instead of >= for the start date, incorrectly excluding February 1st orders. Study tip: When filtering timestamps with date parameters, always extend the upper bound by one day and use strict less-than (<). This pattern is portable, index-friendly, and airtight.