Categorizing Products with a CASE Statement

This challenge focuses on utilizing the CASE statement in SQL to categorize products based on their price range. Understanding conditional logic within SQL queries is crucial for data analysis, reporting, and creating dynamic views of your data. You'll be writing a SQL query that assigns a category label to each product based on its price.

Problem Description

You are given a table named Products with the following columns:

• ProductID (INT): Unique identifier for each product.
• ProductName (VARCHAR): Name of the product.
• Price (DECIMAL): Price of the product.

Your task is to write a SQL query that selects the ProductName and Price from the Products table, and adds a new column called PriceCategory. The PriceCategory should be determined using a CASE statement based on the following price ranges:

• "Low": If Price is less than 50.
• "Medium": If Price is between 50 (inclusive) and 100 (inclusive).
• "High": If Price is greater than 100.

The query should return all products with their corresponding price category.

Key Requirements:

• Use a CASE statement to implement the conditional logic.
• Handle all possible price ranges correctly.
• Return the ProductName, Price, and the newly calculated PriceCategory.

Expected Behavior:

The query should produce a result set with three columns: ProductName, Price, and PriceCategory. Each row should represent a product and its assigned category based on its price.

Edge Cases to Consider:

• Products with a price of exactly 50 or 100 should be categorized as "Medium".
• The table might be empty. The query should still execute without errors and return an empty result set.
• The Price column might contain NULL values. Consider how you want to handle these (e.g., assign a default category or exclude them). For this challenge, treat NULL prices as "Low".

Examples

Example 1:

Input:
Products Table:
ProductID | ProductName | Price
----------|-------------|-------
1         | Widget A    | 25
2         | Widget B    | 75
3         | Widget C    | 120
4         | Widget D    | 50
5         | Widget E    | 100
6         | Widget F    | 30
7         | Widget G    | 150
8         | Widget H    | NULL

Output:
ProductName | Price | PriceCategory
------------|-------|---------------
Widget A    | 25    | Low
Widget B    | 75    | Medium
Widget C    | 120   | High
Widget D    | 50    | Medium
Widget E    | 100   | Medium
Widget F    | 30    | Low
Widget G    | 150   | High
Widget H    | NULL  | Low

Explanation:
The query iterates through each product, checks its price against the defined ranges, and assigns the appropriate category label.  NULL prices are treated as "Low".

Example 2:

Input:
Products Table:
ProductID | ProductName | Price
----------|-------------|-------
1         | Widget A    | 150
2         | Widget B    | 25

Output:
ProductName | Price | PriceCategory
------------|-------|---------------
Widget A    | 150   | High
Widget B    | 25    | Low

Explanation:
Simple case demonstrating the categorization based on price.

Constraints

• The Price column is of type DECIMAL.
• The Products table always exists.
• The query should be efficient and avoid unnecessary computations.
• The SQL dialect should be compatible with most standard SQL databases (e.g., MySQL, PostgreSQL, SQL Server).

Notes

• Consider using COALESCE or similar functions to handle NULL values in the Price column gracefully.
• The CASE statement can be nested for more complex conditional logic, but this problem requires a simple, flat structure.
• Focus on clarity and readability of your SQL query. Proper indentation and comments can significantly improve maintainability.
• Think about how to handle edge cases where the price falls exactly on the boundary of a price range. The problem specifies inclusive boundaries for the "Medium" category.