Conditional expressions using the CASE statement in SQL empower analytics engineers to add context-aware categorization directly into query results. This powerful construct allows for dynamic labeling and categorization based on specified conditions, enhancing readability and interpretability of data.
In this tutorial, “SQL Exercises: Conditional Expressions (CASE),” you’ll explore how to implement CASE statements to classify and segment data clearly and effectively. From categorizing orders by value and labeling employee salaries relative to averages, to segmenting customers by their loyalty duration, CASE expressions are instrumental in providing actionable insights and streamlined data reporting. Mastering CASE statements significantly enhances your analytical capabilities, making your SQL queries more versatile and insightful.
🚀 Jump Right to Exercise Tasks: SQL Exercises – Conditional Expressions (CASE)
Categorizing Data with CASE
Imagine you’re categorizing customer feedback scores into satisfaction levels. You can use a CASE statement to dynamically label each feedback score as ‘Excellent’, ‘Good’, or ‘Needs Improvement’.
Practical Example
SELECT customer_name, feedback_score,
CASE
WHEN feedback_score >= 90 THEN 'Excellent'
WHEN feedback_score BETWEEN 75 AND 89 THEN 'Good'
ELSE 'Needs Improvement'
END AS satisfaction_level
FROM customer_feedback;Example Solution Explained:
This query labels each feedback entry based on score thresholds, instantly clarifying customer satisfaction levels for further analysis.
Example Output:
customer_name | feedback_score | satisfaction_level
--------------|----------------|-------------------
Alice Johnson | 92 | Excellent
Mark Smith | 80 | Good
Tom Brown | 65 | Needs ImprovementKey Takeaways:
- CASE categorizes data clearly and dynamically.
- Ideal for enhancing query interpretability.
Conditional Segmentation of Customers
Suppose you’re evaluating customer loyalty. You can use a CASE expression to segment customers based on their membership duration, creating actionable groups for targeted marketing campaigns.
Practical Example
SELECT customer_name, join_date,
CASE
WHEN join_date > '2022-01-01' THEN 'New'
WHEN join_date BETWEEN '2020-01-01' AND '2021-12-31' THEN 'Established'
ELSE 'Veteran'
END AS customer_segment
FROM customers;Example Solution Explained:
This query categorizes customers by how long they’ve been associated, facilitating customized marketing strategies based on their loyalty.
Example Output:
customer_name | join_date | customer_segment
--------------|------------|-----------------
Lisa Green | 2022-06-15 | New
David Gray | 2021-07-22 | Established
Susan White | 2018-03-11 | VeteranKey Takeaways:
- Easily segment data into meaningful groups.
- Great for targeted marketing and customer insights.
Formatting Data Conditionally
Let’s say you’re preparing an inventory report. A CASE statement can help you label products as ‘Available’, ‘Out of Stock’, or ‘Discontinued’, clearly indicating product availability status.
Practical Example
SELECT product_name, stock_quantity,
CASE
WHEN stock_quantity > 0 THEN 'Available'
WHEN stock_quantity = 0 THEN 'Out of Stock'
ELSE 'Discontinued'
END AS availability
FROM inventory;Example Solution Explained:
This CASE statement dynamically labels inventory items based on their stock status, enhancing report clarity for stakeholders.
Example Output:
product_name | stock_quantity | availability
-------------|----------------|-------------
Laptop | 20 | Available
Printer | 0 | Out of Stock
Old Model TV | -5 | DiscontinuedKey Takeaways:
- CASE statements add valuable context to raw data.
- Improves data presentation and readability.
What You’ll Gain from Completing This Exercise
Mastering CASE expressions enables you to create insightful, dynamic categorizations within your data queries, greatly enhancing your reporting and analytical capabilities.
Earn XP, Unlock Rewards, and Track Progress!
Log in to earn XP, unlock exciting rewards, and automatically track your progress as you master conditional expressions and advanced SQL analytics!
