Specifying ranges in SQL predicates: can you establish a range using comparison operators like < and > (and <=, >=), or must a special operator be used?

Difficulty: Easy

Correct Answer: Correct

Explanation:


Introduction / Context:
Filtering by ranges is common: date windows, price bands, score thresholds. This question checks whether standard comparison operators suffice, or a special operator is mandatory.



Given Data / Assumptions:

  • We can use operators <, <=, >, >=, BETWEEN.
  • Data types include numbers, dates/timestamps, and text (for lexicographic ranges).
  • SQL predicate logic supports conjunctions with AND.


Concept / Approach:
Ranges can be expressed directly: WHERE amount >= 100 AND amount < 200 or using BETWEEN (inclusive on both ends): WHERE amount BETWEEN 100 AND 200. Both approaches are valid; the choice depends on inclusivity needs and readability. These operators also work for temporal types (e.g., order_date >= DATE '2025-01-01' AND order_date < DATE '2025-02-01').



Step-by-Step Solution:

Determine boundary inclusivity requirements.Use <, <=, >, >= combined with AND to form the range.Alternatively, use BETWEEN when inclusive bounds are desired.Test edge values to confirm correct inclusion/exclusion.


Verification / Alternative check:
Compare results of BETWEEN with explicit comparisons for off-by-one or end-of-day timestamp concerns. For timestamps, half-open intervals (>= start AND < next_start) are often safest.



Why Other Options Are Wrong:

  • Saying only BETWEEN can define ranges is false; comparison operators suffice.
  • Window functions handle analytic partitions, not simple filtering ranges.
  • Ranges work for numerics, dates, and even text using collation order.


Common Pitfalls:
Mishandling inclusivity, especially with timestamps—use half-open intervals to avoid missing late-night rows.



Final Answer:
Correct

Discussion & Comments

No comments yet. Be the first to comment!
Join Discussion