Product Inventory Range Search

Imagine you are a developer for an e-commerce platform. You need to build a feature that allows users to filter products based on a price range. This involves querying a database to find all products whose prices fall within a specified minimum and maximum value. The BETWEEN operator in SQL is a highly efficient way to handle such range queries.

Problem Description

Your task is to write a SQL query that retrieves all products from an Products table that fall within a given price range. The query should be inclusive of the minimum and maximum prices.

Key Requirements:

• Select all columns for the matching products.
• Filter products based on a price range defined by a minimum and maximum price.
• The filtering must use the BETWEEN operator.

Expected Behavior:

The query should return all rows from the Products table where the Price column is greater than or equal to the specified minimum price AND less than or equal to the specified maximum price.

Edge Cases:

• Consider scenarios where the minimum and maximum prices are the same.
• Consider scenarios where no products fall within the specified range.

Examples

Example 1:

Input Table: Products
| ProductID | ProductName | Price |
|-----------|-------------|-------|
| 1         | Laptop      | 1200  |
| 2         | Mouse       | 25    |
| 3         | Keyboard    | 75    |
| 4         | Monitor     | 300   |
| 5         | Webcam      | 50    |

Query Parameters:
Minimum Price: 50
Maximum Price: 500

Output:
| ProductID | ProductName | Price |
|-----------|-------------|-------|
| 3         | Keyboard    | 75    |
| 4         | Monitor     | 300   |
| 5         | Webcam      | 50    |

Explanation: The query selects products with prices between 50 and 500 (inclusive). Products with prices 75, 300, and 50 match this criteria.

Example 2:

Input Table: Products
| ProductID | ProductName | Price |
|-----------|-------------|-------|
| 1         | Laptop      | 1200  |
| 2         | Mouse       | 25    |
| 3         | Keyboard    | 75    |
| 4         | Monitor     | 300   |
| 5         | Webcam      | 50    |

Query Parameters:
Minimum Price: 1000
Maximum Price: 1500

Output:
| ProductID | ProductName | Price |
|-----------|-------------|-------|
| 1         | Laptop      | 1200  |

Explanation: The query selects products with prices between 1000 and 1500 (inclusive). Only the Laptop at 1200 matches.

Example 3: (Edge Case - No Products in Range)

Input Table: Products
| ProductID | ProductName | Price |
|-----------|-------------|-------|
| 1         | Laptop      | 1200  |
| 2         | Mouse       | 25    |
| 3         | Keyboard    | 75    |
| 4         | Monitor     | 300   |
| 5         | Webcam      | 50    |

Query Parameters:
Minimum Price: 10
Maximum Price: 20

Output:
(Empty Table)

Explanation: No products have a price between 10 and 20 (inclusive), so the result set is empty.

Constraints

• The Products table will have at least one row.
• The Price column will be a numeric data type.
• The Minimum Price and Maximum Price parameters will be valid numeric values.
• The query should execute efficiently, with a time complexity that scales well with the size of the Products table.

Notes

• The BETWEEN operator in SQL is inclusive, meaning it includes the boundary values. For example, Price BETWEEN 50 AND 100 will include products priced at exactly 50 and exactly 100.
• Your solution should be a single SQL SELECT statement.
• Assume you have a table named Products with columns ProductID, ProductName, and Price.