Explain the use of CASE statements in SQL.

The CASE statement in SQL is used to add conditional logic to your queries — kind of like an IF-ELSE or SWITCH in programming languages.

✅ Basic Syntax of CASE:

SELECT 
  column_name,
  CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
  END AS alias_name
FROM table_name;

• It goes through each WHEN condition in order.
• Returns the result of the first condition that is true.
• If no condition is true, it returns the ELSE result.
• The ELSE part is optional.

🔹 Example 1: Grading System Based on Marks

SELECT student_name, marks,
  CASE
    WHEN marks >= 90 THEN 'A'
    WHEN marks >= 75 THEN 'B'
    WHEN marks >= 60 THEN 'C'
    ELSE 'Fail'
  END AS grade
FROM students;

✅ This assigns grades based on each student’s marks.

🔹 Example 2: Categorize Salary

SELECT name, salary,
  CASE
    WHEN salary >= 100000 THEN 'High'
    WHEN salary >= 50000 THEN 'Medium'
    ELSE 'Low'
  END AS salary_level
FROM employees;

🔹 Example 3: Using CASE in ORDER BY

SELECT * FROM products
ORDER BY 
  CASE category
    WHEN 'Electronics' THEN 1
    WHEN 'Clothing' THEN 2
    ELSE 3
  END;

✅ This sorts products with Electronics first, Clothing second, and all others last.

🔹 Example 4: Use CASE in an UPDATE

UPDATE employees
SET bonus = 
  CASE
    WHEN salary >= 100000 THEN 10000
    WHEN salary >= 70000 THEN 5000
    ELSE 2000
  END;

✅ This sets the bonus amount based on salary brackets.

🔹 Simple CASE vs Searched CASE

Simple CASE Example:

SELECT name, gender,
  CASE gender
    WHEN 'M' THEN 'Male'
    WHEN 'F' THEN 'Female'
    ELSE 'Other'
  END AS gender_full
FROM employees;

🔸 Use Cases:

• Conditional output in SELECT
• Dynamic ORDER BY
• Conditional UPDATE or SET
• Building logic in views or stored procedures

Let me know if you want to try a custom CASE based on your own table structure!