14.1.case_when

My complete SQL Masterclass Notes in Tanglish is now live! Covers 30+ topics with syntax, examples, and step-by-step explanations. 
Perfect for both beginners and professionals to master SQL easily. Check it out here 👉 https://topmate.io/dataengineering/1697791

====================================================================================



-- Case When Statement

CREATE TABLE CustomerData (
    id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    email VARCHAR(100),
    phone_number VARCHAR(15),
    address VARCHAR(200),
    amount DECIMAL(10, 2)
);

INSERT INTO CustomerData VALUES
(1, 'Ravi', 'ravi@example.com', '98765', 'Chennai', 5000.00),
(2, 'Priya', NULL, '98765', 'Bangalore', NULL),
(3, 'Arjun', 'arjun@example.com', NULL, 'Hyderabad', 1500.00),
(4, 'Meena', NULL, NULL, 'Mumbai', 2500.00),
(5, 'Karthik', 'karthik@example.com', '98765', NULL, 3000.00);

Example 1
  --------

SELECT 
    customer_name, 
    amount,
    CASE 
        WHEN amount > 4000 THEN 'High Spender'
        WHEN amount BETWEEN 2000 AND 4000 THEN 'Medium Spender'
        WHEN amount <= 2000 THEN 'Low Spender'
        ELSE 'No Data'
    END AS spending_category
FROM 
    CustomerData;

Example 2
---------


select customer_name,
amount,
case 
    when email is null and phone_number is null then '9999'  -- ordering matters
    when email is null  then phone_number
    else email
end  as contact 

from CustomerData