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

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:

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