SQL Quiz: Where Filtering
10 questions · exam conditions
0:00
Where FilteringQuestion 1 of 10

A parameterized order query uses WHERE (:region IS NULL OR region = :region) AND (:minimum IS NULL OR amount >= :minimum). The parameter :region is null, and :minimum is 100. Order O1 is East with amount 120; O2 is West with amount 150; O3 is East with amount 80; and O4 is West with a null amount.

Which order IDs are returned under standard SQL null semantics?

O1 and O2 only
O1, O2, and O4
O1 and O3 only
O1, O2, and O3
← Back to quizzes

SQL Quiz

SQL Quiz: Where Filtering

Practice Where Filtering 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 Where Filtering, 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 parameterized order query uses WHERE (:region IS NULL OR region = :region) AND (:minimum IS NULL OR amount >= :minimum). The parameter :region is null, and :minimum is 100. Order O1 is East with amount 120; O2 is West with amount 150; O3 is East with amount 80; and O4 is West with a null amount.

Which order IDs are returned under standard SQL null semantics?

  1. O1 and O2 only (correct answer)
  2. O1, O2, and O4
  3. O1 and O3 only
  4. O1, O2, and O3
Explanation: Whenever you see a parameterized SQL query with optional filters using the (:param IS NULL OR column = :param) pattern, your job is to evaluate each condition independently for every row using SQL's three-valued logic (TRUE, FALSE, or UNKNOWN). Here, :region is NULL and :minimum is 100. For the region condition, since :region IS NULL evaluates to TRUE, the entire OR expression is TRUE for every row — meaning region is not filtered at all. For the minimum condition, :minimum IS NULL is FALSE (100 is not null), so the filter falls through to amount >= 100, which must be TRUE for a row to pass. Now evaluate each order against amount >= 100: O1 has amount 120 → TRUE ✓; O2 has amount 150 → TRUE ✓; O3 has amount 80 → FALSE ✗; O4 has a null amount → NULL >= 100 evaluates to UNKNOWN, which SQL treats as non-TRUE, so the row is excluded ✗. Only O1 and O2 pass, confirming A is correct. Choice B is wrong because it includes O4 — a common trap. Students assume a null amount means "no minimum restriction applies," but the null is in the data, not the parameter. The comparison NULL >= 100 yields UNKNOWN, not TRUE. Choice C is wrong because it includes O3 (amount 80 < 100) and excludes O2, reversing the correct result. Choice D incorrectly includes O3 for the same reason — 80 does not satisfy >= 100. Remember: null in a parameter disables a filter; null in data causes comparisons to return UNKNOWN, which always fails a WHERE clause.

Question 2

A support team wants to return tickets that are open and whose priority is either high or urgent.

Which WHERE clause implements the requirement without including closed tickets or lower-priority open tickets?

  1. WHERE priority = 'high' OR (priority = 'urgent' AND state = 'open')
  2. WHERE (priority = 'high' OR priority = 'urgent') AND state = 'open' (correct answer)
  3. WHERE priority IN ('high', 'urgent') OR state = 'open'
  4. WHERE priority = 'high' AND priority = 'urgent' AND state = 'open'
Explanation: When filtering rows using multiple conditions in SQL, operator precedence and grouping with parentheses are critical. The key rule: AND is evaluated before OR unless you use parentheses to override that order — just like multiplication before addition in math. The requirement has two distinct conditions that must both be true simultaneously: the ticket's state must be 'open', and the priority must be either 'high' or 'urgent'. Option B — WHERE (priority = 'high' OR priority = 'urgent') AND state = 'open' — correctly groups the two priority values with parentheses first, then requires state = 'open' for the entire result. Every row returned must satisfy both constraints, which is exactly what the business rule demands. Option A is the classic precedence trap. Without parentheses, SQL evaluates it as priority = 'high' OR (priority = 'urgent' AND state = 'open'). This means any ticket with priority = 'high' passes — even if it's closed — because the state = 'open' check only applies to the urgent branch. Option C uses OR between the priority filter and the state filter, which means any open ticket (regardless of priority) or any high/urgent ticket (regardless of state) would be returned — far too broad. Option D uses AND between both priority values, requiring a single row to be simultaneously 'high' and 'urgent', which is logically impossible. No rows would ever be returned. As a study habit, whenever you write a WHERE clause mixing OR and AND, ask yourself: "Does every returned row satisfy all required conditions?" If not, add parentheses until it does.

Question 3

Three products have discount values as follows: product P1 has discount 5, product P2 has a null discount, and product P3 has discount 0.

Under standard SQL null semantics, which products are returned by WHERE discount <> 0?

  1. P1 and P2 only
  2. P1 and P3 only
  3. P1 only (correct answer)
  4. P1, P2, and P3
Explanation: Whenever you see a SQL filtering question involving NULL, your first instinct should be: NULL values behave differently from every other value in a WHERE clause. In SQL, NULL means "unknown." When you write WHERE discount <> 0, the database evaluates this condition for each row. For P1 (discount = 5), the condition 5 <> 0 is TRUE — P1 is returned. For P3 (discount = 0), the condition 0 <> 0 is FALSE — P3 is excluded. For P2 (discount = NULL), the condition NULL <> 0 evaluates to UNKNOWN, not TRUE or FALSE. SQL's WHERE clause only returns rows where the condition is TRUE, so UNKNOWN rows are silently dropped, just like FALSE rows. That leaves only P1 — confirming C is correct. Choice A is wrong because it includes P2. Since NULL <> 0 is UNKNOWN (not TRUE), P2 fails the filter and is never returned. Choice B is wrong because it includes P3. The condition 0 <> 0 is explicitly FALSE, so P3 is excluded — exactly as you'd expect. Choice D is wrong for both reasons above: neither P2 nor P3 satisfies the WHERE condition. A useful memory trick: think of NULL as a "mystery value." You can never confirm that a mystery value is not equal to 0, so SQL refuses to include it. To explicitly catch NULLs, you'd need WHERE discount <> 0 OR discount IS NULL. On any SQL exam, watch for NULL in filter conditions — it's one of the most commonly tested traps.

Question 4

An employees table contains E1 in Sales, E2 in HR, and E3 with a null department. Assume standard SQL three-valued logic.

Which employees are returned by WHERE department NOT IN ('HR', NULL)?

  1. E1 only
  2. E1 and E3 only
  3. E3 only
  4. No employees (correct answer)
Explanation: Whenever you see NOT IN with a list containing NULL, you need to think carefully about SQL's three-valued logic (TRUE, FALSE, UNKNOWN). Here's the key mechanism: NOT IN ('HR', NULL) expands into a series of <> comparisons joined by AND. For any value x, it evaluates as x <> 'HR' AND x <> NULL. The critical rule is that any comparison with NULL produces UNKNOWN, not TRUE or FALSE. Since UNKNOWN AND anything can never guarantee TRUE, the entire expression returns UNKNOWN for every row — and SQL's WHERE clause only passes rows where the condition is TRUE. Walk through each employee: E1 (Sales) evaluates 'Sales' <> 'HR' (TRUE) AND 'Sales' <> NULL (UNKNOWN) → result is UNKNOWN, so E1 is excluded. E2 (HR) evaluates 'HR' <> 'HR' (FALSE) AND ... → FALSE, excluded. E3 (NULL department) evaluates NULL <> 'HR' (UNKNOWN) → UNKNOWN, excluded. No rows pass, making D correct. Choice A is wrong because it assumes E1's non-HR department is sufficient to pass — it ignores that the NULL in the list poisons the entire comparison. Choice B compounds this error by also including E3, whose NULL department makes it doubly prone to UNKNOWN results. Choice C is wrong for the same reason; a NULL department compared against anything yields UNKNOWN, never TRUE. The study tip here: a single NULL inside an IN or NOT IN list will silently eliminate all rows from NOT IN. Always use IS NOT NULL guards or rewrite as NOT EXISTS when NULLs may be involved.

Question 5

Four shipments have status values as follows: S1 is cancelled, S2 is returned, S3 is shipped, and S4 has a null status. Assume standard SQL three-valued logic.

Which shipments are returned by WHERE status <> 'cancelled' OR status <> 'returned'?

  1. S3 only
  2. S1, S2, and S3 (correct answer)
  3. S3 and S4 only
  4. S1, S2, S3, and S4
Explanation: When evaluating WHERE clauses with OR, remember that SQL returns a row whenever at least one condition evaluates to TRUE — and with NULL values, SQL uses three-valued logic (TRUE, FALSE, or UNKNOWN). Let's trace each shipment through status <> 'cancelled' OR status <> 'returned':
  • S1 (cancelled): 'cancelled' <> 'cancelled' = FALSE, 'cancelled' <> 'returned' = TRUE → FALSE OR TRUE = TRUE ✓
  • S2 (returned): 'returned' <> 'cancelled' = TRUE, 'returned' <> 'returned' = FALSE → TRUE OR FALSE = TRUE ✓
  • S3 (shipped): Both conditions are TRUE → TRUE OR TRUE = TRUE ✓
  • S4 (null): NULL <> 'cancelled' = UNKNOWN, NULL <> 'returned' = UNKNOWN → UNKNOWN OR UNKNOWN = UNKNOWN ✗
So S1, S2, and S3 pass — making B correct. The key insight is that this OR condition is a tautology for any non-null value: every concrete status is either not 'cancelled' or not 'returned' (it can't equal both simultaneously), so at least one side is always TRUE. Only NULL breaks this, since comparisons with NULL yield UNKNOWN rather than TRUE or FALSE. A is wrong because it excludes S1 and S2 — a common mistake when students assume the OR means "must satisfy both exclusions." C is wrong because S4 (null) evaluates to UNKNOWN, not TRUE, so it's excluded. D is wrong for the same NULL reason — S4 does not pass. Your study tip: whenever you see <> 'x' OR <> 'y', test whether the condition is effectively always TRUE for non-null values — and always trace NULL separately using three-valued logic.

Question 6

Assessment records have these scores: S1 has 59, S2 has 60, S3 has 80, and S4 has 81.

Which records are returned by WHERE score NOT BETWEEN 60 AND 80?

  1. S1 and S4 only (correct answer)
  2. S1, S2, and S4
  3. S1, S3, and S4
  4. S2 and S3 only
Explanation: When working with BETWEEN in SQL, the most important thing to remember is that it is inclusive — meaning BETWEEN 60 AND 80 includes both 60 and 80 themselves. So NOT BETWEEN 60 AND 80 excludes those boundary values and returns only records where the score is strictly less than 60 or strictly greater than 80. Apply that logic to each record: S1 has 59 (less than 60 ✓), S2 has 60 (equal to 60, so it falls within the range and is excluded), S3 has 80 (equal to 80, so it also falls within the range and is excluded), and S4 has 81 (greater than 80 ✓). Only S1 and S4 pass the NOT BETWEEN filter, making A the correct answer. Choice B incorrectly includes S2. Because BETWEEN is inclusive, a score of exactly 60 satisfies BETWEEN 60 AND 80, so it does not satisfy NOT BETWEEN — S2 is filtered out. Choice C incorrectly includes S3 for the same reason: a score of exactly 80 is inside the boundary, not outside it. Choice D returns only S2 and S3, which are precisely the records that do satisfy BETWEEN 60 AND 80 — this answer confuses the NOT BETWEEN condition with plain BETWEEN. A reliable study tip: whenever you see BETWEEN in SQL, mentally replace it with >= lower AND <= upper. That makes the inclusive boundaries explicit and helps you quickly evaluate NOT BETWEEN as < lower OR > upper without second-guessing.

Question 7

A customers table contains these rows: customer 1 is in East, is active, and has balance 50; customer 2 is in West, is active, and has balance 200; customer 3 is in East, is inactive, and has balance 200; customer 4 is in West, is inactive, and has balance 50.

Which customer IDs are returned by WHERE region = 'East' OR status = 'active' AND balance >= 100?

  1. Customers 1, 2, and 3 (correct answer)
  2. Customers 2 and 3 only
  3. Customers 1 and 3 only
  4. Customers 2, 3, and 4
Explanation: Whenever you see a WHERE clause mixing AND and OR, your first instinct should be to think about operator precedence. In SQL, AND is evaluated before OR — just like multiplication before addition in math. This means you must mentally add parentheses before reading the logic. The query WHERE region = 'East' OR status = 'active' AND balance >= 100 is actually evaluated as WHERE region = 'East' OR (status = 'active' AND balance >= 100). Now check each customer against this rule:
  • Customer 1 (East, active, 50): region = 'East' ✓ → included
  • Customer 2 (West, active, 200): region ≠ East, but active AND balance ≥ 100 ✓ → included
  • Customer 3 (East, inactive, 200): region = 'East' ✓ → included
  • Customer 4 (West, inactive, 50): region ≠ East, and inactive → excluded
That gives you customers 1, 2, and 3 — making A the correct answer. B (customers 2 and 3 only) is wrong because it drops customer 1, likely from misreading balance < 100 as a disqualifier — but customer 1 qualifies solely through the OR (East) branch. C (customers 1 and 3 only) ignores customer 2, as if AND were evaluated before OR correctly but then the West region incorrectly excluded customer 2 from the active+balance check. D (customers 2, 3, and 4) incorrectly includes customer 4, treating OR as applying equally to all conditions rather than respecting AND's higher precedence. Study tip: Always rewrite complex WHERE clauses with explicit parentheses before evaluating them — AND binds tighter than OR every time.

Question 8

A requests table has four rows: R1 is open with high priority; R2 is closed with high priority; R3 is open with low priority; and R4 is open with null priority.

Which request IDs are returned by WHERE NOT (status = 'closed' OR priority = 'low')?

  1. R1 and R4 only
  2. R1 and R3 only
  3. R1 and R2 only
  4. R1 only (correct answer)
Explanation: When SQL evaluates a WHERE NOT (...) clause, you must work through two layers of logic: first resolve what's inside the parentheses, then flip it. This question tests your understanding of De Morgan's Law and how NULL values behave in boolean logic — two concepts that trip up many students together. Start inside the parentheses: status = 'closed' OR priority = 'low'. This is TRUE if either condition holds. The outer NOT then flips the result, so you're keeping rows where status is not closed and priority is not low. Now walk through each row:
  • R1 (open, high): NOT closed ✓, NOT low ✓ → returned
  • R2 (closed, high): status = 'closed' makes the OR true, so NOT flips it to FALSE → excluded
  • R3 (open, low): priority = 'low' makes the OR true, so NOT flips it to FALSE → excluded
  • R4 (open, null): null = 'low' evaluates to NULL (unknown), not FALSE. status = 'closed' is FALSE. FALSE OR NULL = NULL. NOT NULL = NULL. NULL is not TRUE, so the row is excluded.
That leaves only R1, making D correct. Choice A is wrong because R4's NULL priority causes it to fail — NULL is not treated as "not low." Choice B incorrectly includes R3, whose low priority gets it excluded. Choice C includes R2, which is explicitly closed. The key study tip: NULL is never equal or not-equal to anything — comparisons with NULL produce NULL, which SQL treats as "unknown" and excludes from results.

Question 9

Column created_at stores timestamps. A report must include every timestamp in January 2025 and exclude every timestamp outside that month. Assume the date literals are interpreted as midnight at the start of the stated date.

Which WHERE clause most reliably implements the required timestamp range?

  1. WHERE created_at >= '2025-01-01' AND created_at <= '2025-01-31'
  2. WHERE created_at > '2025-01-01' AND created_at < '2025-02-01'
  3. WHERE created_at >= '2025-01-01' AND created_at < '2025-02-01' (correct answer)
  4. WHERE created_at BETWEEN '2025-01-01' AND '2025-02-01'
Explanation: When filtering timestamps in SQL, the critical concept is boundary inclusivity. Timestamps rarely land exactly at midnight — a value like 2025-01-31 23:59:59 is still in January, and your filter must capture it. The most reliable approach is a half-open interval: include the start date with >= and exclude the first moment of the next month with <. That's exactly what option C does — created_at >= '2025-01-01' AND created_at < '2025-02-01'. This captures every possible timestamp from the very first moment of January through 2025-01-31 23:59:59.999..., without accidentally pulling in any February data. Option A fails because <= '2025-01-31' compares against 2025-01-31 00:00:00 (midnight). Any timestamp later on January 31st — like 2025-01-31 14:30:00 — would be excluded, dropping valid data from your report. Option B uses > '2025-01-01', which cuts out 2025-01-01 00:00:00 itself (midnight on New Year's Day). Records created at exactly midnight on January 1st would be missed. Option D uses BETWEEN, which is fully inclusive on both ends. Since BETWEEN '2025-01-01' AND '2025-02-01' includes 2025-02-01 00:00:00, any record timestamped at exactly midnight on February 1st would be incorrectly included in a January report. The key strategy here: whenever you filter a date range on a datetime/timestamp column, always use >= start_date AND < first_day_of_next_month. This half-open interval pattern is the safest and most portable approach across SQL dialects.

Question 10

Columns x and y are numeric and may contain null values. The database uses standard SQL three-valued logic.

Which condition is logically equivalent to NOT (x < 10 OR y > 20) for use in a WHERE clause?

  1. x >= 10 OR y <= 20
  2. x >= 10 AND y <= 20 (correct answer)
  3. x < 10 AND y > 20
  4. x <= 10 AND y >= 20
Explanation: When you see a negation applied to a compound logical expression, think De Morgan's Laws: NOT (A OR B) becomes NOT A AND NOT B, and NOT (A AND B) becomes NOT A OR NOT B. This is the core concept being tested here. Applying De Morgan's Law to NOT (x < 10 OR y > 20): distribute the NOT across both conditions and flip the OR to AND. Negating x < 10 gives x >= 10, and negating y > 20 gives y <= 20. The connector flips from OR to AND, giving you x >= 10 AND y <= 20 — which is answer B. As for the wrong answers: A gets the connector wrong. It correctly negates both individual conditions but keeps OR instead of flipping it to AND. This is the classic De Morgan trap — students often negate the operands but forget to flip the operator. C doesn't negate either condition; it simply removes the outer NOT while preserving the original internals, which is entirely wrong. D negates using the wrong boundary — x <= 10 and y >= 20 would be the negation of x > 10 and y < 20 respectively, not of the original conditions. One important nuance: SQL uses three-valued logic (TRUE, FALSE, NULL), but De Morgan's Laws still hold correctly for all three truth values, so no special adjustments are needed here. Study tip: Memorize De Morgan's Laws as a pair: flip OR↔AND and negate each operand. When you see NOT wrapping a compound condition on an exam, apply this transformation immediately.