Exam 8: CASE, CTEs & COALESCE
Difficulty: Advanced
Topics covered: CASE WHEN, WITH (CTEs), combining multiple techniques
Each question shows the target output. Write a SQL query that produces exactly those columns and rows.
Question 1
Add a price_tier column to every product using CASE WHEN: 'Budget' if price < 50, 'Mid-range' if price is between 50 and 500, 'Premium' if price > 500. Return name, price, and price_tier.
Question 2
Using CASE WHEN and SUM, pivot the order status counts into three columns in a single row: completed_count, pending_count, cancelled_count.
Question 3
Add a value_label column to each order: 'High' if total_amount > 500, 'Low' otherwise. Return order_id, total_amount, and value_label.
Question 4
Write a CTE called high_value_orders that contains all orders with total_amount > 500. Then join it with customers to return customer_name, order_id, total_amount, and status.
Question 5
Write a CTE called order_stats that computes customer_id, order_count (number of orders), and total_qty (sum of quantity) per customer. Join it with customers to return customer_name, order_count, and total_qty.