Efficient Range Queries with SQL's BETWEEN Operator

Range queries are a fundamental operation in database systems, allowing you to retrieve data within a specific range of values. This challenge focuses on utilizing the BETWEEN operator in SQL to efficiently execute these range queries. Mastering BETWEEN improves query performance and readability when dealing with data that needs to be filtered based on value ranges.

Problem Description

You are tasked with writing SQL queries that retrieve data from a table based on specified ranges using the BETWEEN operator. The table contains information about products, including their product_id, product_name, and price. Your queries should accurately filter products based on price ranges provided as input.

What needs to be achieved:

Write SQL queries that select product information (specifically product_id and product_name) from a table named products where the price falls within a given range (inclusive of the boundaries).

Key Requirements:

The queries must use the BETWEEN operator for range filtering.
The queries should return only the product_id and product_name columns.
The queries must handle cases where the range is provided as two separate values (minimum and maximum price).
The queries must be efficient and avoid unnecessary computations.

Expected Behavior:

Given a minimum and maximum price, the query should return all rows from the products table where the price is greater than or equal to the minimum price and less than or equal to the maximum price. If no products fall within the specified range, the query should return an empty result set.

Edge Cases to Consider:

Minimum price greater than maximum price: The query should return an empty result set.
Zero or negative prices: The query should handle these values correctly within the specified range.
Large price ranges: The query should still perform efficiently.
Empty products table: The query should return an empty result set.

Examples

Example 1:

Input: Minimum Price = 10, Maximum Price = 50
Output:
product_id | product_name
----------|-------------
1         | Widget A
3         | Gadget C
4         | Thingamajig B
Explanation: The query should return products with prices between 10 and 50 (inclusive).  Assume the `products` table contains products with prices 5, 10, 25, 30, 50, 60.

Example 2:

Input: Minimum Price = 75, Maximum Price = 100
Output:
product_id | product_name
----------|-------------
6         | Super Gizmo
Explanation: The query should return products with prices between 75 and 100 (inclusive). Assume the `products` table contains products with prices 5, 10, 25, 30, 50, 60, 75, 100, 120.

Example 3:

Input: Minimum Price = 100, Maximum Price = 50
Output:
(empty result set)
Explanation: The minimum price is greater than the maximum price. The query should return an empty result set.

Constraints

The products table will always exist and have the columns product_id (INTEGER), product_name (VARCHAR), and price (DECIMAL).
The product_id will be unique and positive.
The price can be any decimal value, including zero and negative values.
The minimum and maximum prices will be provided as numerical values.
The query should execute within a reasonable time (e.g., less than 1 second) for tables with up to 10,000 rows.

Notes

The BETWEEN operator is inclusive, meaning it includes both the minimum and maximum values in the range.
Consider the order of the minimum and maximum values when constructing the query.
Focus on writing a clear and concise SQL query that effectively utilizes the BETWEEN operator.

Pseudocode for the general approach:

FUNCTION get_products_in_price_range(min_price, max_price):
  // Construct the SQL query string
  query = "SELECT product_id, product_name FROM products WHERE price BETWEEN min_price AND max_price;"

  // Execute the query against the database
  result = execute_sql_query(query)

  // Return the result set
  RETURN result
END FUNCTION

Remember to replace execute_sql_query with the appropriate database interaction method for your chosen SQL dialect.