50 Questions on CASE WHEN
Basic CASE WHEN Usage
1️.Classify employees based on salary brackets
SELECT Name, Salary,
CASE
WHEN Salary < 60000 THEN 'Low'
WHEN Salary BETWEEN 60000 AND 1️00000 THEN 'Medium'
ELSE 'High'
END AS SalaryCategory
FROM Employees;
2️.Check if employees are experienced or not
SELECT Name, Experience,
CASE
WHEN Experience >= 1️0 THEN 'Senior'
WHEN Experience BETWEEN 5 AND 9 THEN 'Mid-Level'
ELSE 'Junior'
END AS ExperienceLevel
FROM Employees;
Using CASE WHEN with Aggregations
3️.Count employees in each salary category
SELECT
CASE
WHEN Salary < 60000 THEN 'Low'
WHEN Salary BETWEEN 60000 AND 1️00000 THEN 'Medium'
ELSE 'High'
END AS SalaryCategory,
COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY
CASE
WHEN Salary < 60000 THEN 'Low'
WHEN Salary BETWEEN 60000 AND 1️00000 THEN 'Medium'
ELSE 'High'
END;
4️.Total salary per department with categorization
SELECT Department,
SUM(Salary) AS TotalSalary,
CASE
WHEN SUM(Salary) > 2️00000 THEN 'High Budget'
ELSE 'Low Budget'
END AS BudgetCategory
FROM Employees
GROUP BY Department;
CASE WHEN with Joins
5.Classify employees with department details (using a join)
CREATE TABLE Departments (
DeptID INT PRIMARY KEY,
DeptName VARCHAR(50),
Budget DECIMAL(10,2)
);
INSERT INTO Departments (DeptID, DeptName, Budget) VALUES
(1, 'HR', 200000),
(2, 'IT', 500000),
(3, 'Finance', 300000);
SELECT e.Name, e.Department, d.Budget,
CASE
WHEN d.Budget > 4️00000 THEN 'Rich Department'
WHEN d.Budget BETWEEN 2️00000 AND 4️00000 THEN 'Moderate'
ELSE 'Low Budget'
END AS BudgetCategory
FROM Employees e
JOIN Departments d ON e.Department = d.DeptName;
CASE WHEN with Date Functions
6.Check if employees are recent joiners
SELECT Name, JoiningDate,
CASE
WHEN JoiningDate >= '2️02️2️-01️-01️' THEN 'New Joiner'
ELSE 'Experienced'
END AS JoiningCategory
FROM Employees;
7️.Assign financial quarter based on joining date
SELECT Name, JoiningDate,
CASE
WHEN MONTH(JoiningDate) IN (1️,2️,3️) THEN 'Q1️'
WHEN MONTH(JoiningDate) IN (4️,5,6) THEN 'Q2️'
WHEN MONTH(JoiningDate) IN (7️,8,9) THEN 'Q3️'
ELSE 'Q4️'
END AS FinancialQuarter
FROM Employees;
CASE WHEN with NULL Handling
8.Handle NULL values in salary
SELECT Name,
CASE
WHEN Salary IS NULL THEN 'Salary Not Available'
ELSE 'Salary Available'
END AS SalaryStatus
FROM Employees;
9.Replace NULL experience with 'Unknown'
SELECT Name,
COALESCE(CAST(Experience AS VARCHAR), 'Unknown') AS ExperienceDetails
FROM Employees;
Nested CASE WHEN
🔟 Classify employees based on age and experience
SELECT Name, Age, Experience,
CASE
WHEN Age < 3️0 THEN
CASE
WHEN Experience < 5 THEN 'Young & Inexperienced'
ELSE 'Young & Experienced'
END
WHEN Age BETWEEN 3️0 AND 4️5 THEN
CASE
WHEN Experience > 1️0 THEN 'Mid-aged & Expert'
ELSE 'Mid-aged & Developing'
END
ELSE 'Senior'
END AS EmployeeCategory
FROM Employees;
More Advanced Scenarios
1️1️.Determine leave balance category
SELECT Name,
CASE
WHEN Age < 3️0 THEN 'Eligible for Extra Leave'
ELSE 'Standard Leave'
END AS LeaveCategory
FROM Employees;
1️2️.Performance rating based on salary and experience
SELECT Name, Salary, Experience,
CASE
WHEN Salary > 1️00000 AND Experience > 1️0 THEN 'Excellent'
WHEN Salary BETWEEN 7️0000 AND 1️00000 AND Experience BETWEEN 5 AND 1️0
THEN 'Good'
ELSE 'Average'
END AS PerformanceRating
FROM Employees;
More CASE WHEN Scenarios
CASE WHEN with Different Operators
1️3️.Check if employees are in IT or Finance department
SELECT Name, Department,
CASE
WHEN Department IN ('IT', 'Finance') THEN 'Technical'
ELSE 'Non-Technical'
END AS DepartmentType
FROM Employees;
1️4️.Assign bonus based on salary slab
SELECT Name, Salary,
CASE
WHEN Salary > 1️00000 THEN Salary * 0.2️
WHEN Salary BETWEEN 7️0000 AND 1️00000 THEN Salary * 0.1️5
ELSE Salary * 0.1️
END AS Bonus
FROM Employees;
1️5.Check if employee is eligible for overtime pay
SELECT Name,
CASE
WHEN Salary < 60000 THEN 'Eligible for Overtime'
ELSE 'Not Eligible'
END AS OvertimeEligibility
FROM Employees;
CASE WHEN with Multiple Columns
1️6.Determine highest-paid employee in each department
SELECT Name, Department, Salary,
CASE
WHEN Salary = (SELECT MAX(Salary) FROM Employees e2️ WHERE e2️.Department =
e1️.Department)
THEN 'Highest Paid'
ELSE 'Not Highest Paid'
END AS Status
FROM Employees e1️;
1️7️.Calculate tax category based on salary and experience
SELECT Name, Salary, Experience,
CASE
WHEN Salary > 1️00000 AND Experience > 1️0 THEN 'High Tax Bracket'
WHEN Salary BETWEEN 7️0000 AND 1️00000 AND Experience BETWEEN 5 AND 1️0
THEN 'Medium Tax Bracket'
ELSE 'Low Tax Bracket'
END AS TaxCategory
FROM Employees;
1️8.Determine relocation benefits based on experience and salary
SELECT Name, Experience, Salary,
CASE
WHEN Experience > 1️0 OR Salary > 1️2️0000 THEN 'Full Relocation Package'
WHEN Experience BETWEEN 5 AND 1️0 THEN 'Partial Relocation Package'
ELSE 'No Relocation Benefits'
END AS RelocationStatus
FROM Employees;
CASE WHEN with String Functions
1️9.Identify employees with a specific letter in their name
SELECT Name,
CASE
WHEN Name LIKE '%A%' THEN 'Contains A'
ELSE 'Does not contain A'
END AS NameCheck
FROM Employees;
2️0.Determine gender description
SELECT Name, Gender,
CASE
WHEN Gender = 'M' THEN 'Male'
WHEN Gender = 'F' THEN 'Female'
ELSE 'Other'
END AS GenderDescription
FROM Employees;
CASE WHEN with GROUP BY and HAVING
2️1️.Count employees in each experience category
SELECT
CASE
WHEN Experience >= 1️0 THEN 'Senior'
WHEN Experience BETWEEN 5 AND 9 THEN 'Mid-Level'
ELSE 'Junior'
END AS ExperienceCategory,
COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY
CASE
WHEN Experience >= 1️0 THEN 'Senior'
WHEN Experience BETWEEN 5 AND 9 THEN 'Mid-Level'
ELSE 'Junior'
END;
2️2️.Department-wise total salary categorization
SELECT Department, SUM(Salary) AS TotalSalary,
CASE
WHEN SUM(Salary) > 2️50000 THEN 'High Expense'
ELSE 'Low Expense'
END AS ExpenseCategory
FROM Employees
GROUP BY Department;
CASE WHEN with Joins (Advanced)
2️3️.Join employees and departments to classify based on budget
SELECT e.Name, e.Department, d.Budget,
CASE
WHEN d.Budget > 4️00000 THEN 'Rich Department'
WHEN d.Budget BETWEEN 2️00000 AND 4️00000 THEN 'Moderate'
ELSE 'Low Budget'
END AS BudgetCategory
FROM Employees e
JOIN Departments d ON e.Department = d.DeptName;
2️4️.Check if department has a high-paid employee
SELECT e.Department,
CASE
WHEN MAX(e.Salary) > 1️00000 THEN 'Has High Paid Employees'
ELSE 'No High Paid Employees'
END AS HighPaidStatus
FROM Employees e
GROUP BY e.Department;
CASE WHEN with Date Manipulations
2️5.Determine if employees are nearing retirement (age > 55)
SELECT Name, Age,
CASE
WHEN Age > 55 THEN 'Nearing Retirement'
ELSE 'Active'
END AS RetirementStatus
FROM Employees;
2️6.Calculate years of service category
SELECT Name, JoiningDate,
CASE
WHEN YEAR(CURRENT_DATE) - YEAR(JoiningDate) > 1️0 THEN 'Veteran'
WHEN YEAR(CURRENT_DATE) - YEAR(JoiningDate) BETWEEN 5 AND 1️0 THEN
'Experienced'
ELSE 'New'
END AS ServiceCategory
FROM Employees;
CASE WHEN with Subqueries
2️7️.Find employees who earn above department average
SELECT Name, Salary, Department,
CASE
WHEN Salary > (SELECT AVG(Salary) FROM Employees e2️ WHERE e2️.Department =
e1️.Department)
THEN 'Above Average'
ELSE 'Below Average'
END AS SalaryComparison
FROM Employees e1️;
2️8.Identify employees in top 1️0% salary
SELECT Name, Salary,
CASE
WHEN Salary > (SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY
Salary) FROM Employees)
THEN 'Top 1️0% Earner'
ELSE 'Below Top 1️0%'
END AS TopEarnerCategory
FROM Employees;
More Complex Scenarios
2️9.Classify employees into salary percentiles
SELECT Name, Salary,
CASE
WHEN Salary >= (SELECT PERCENTILE_CONT(0.7️5) WITHIN GROUP (ORDER BY
Salary) FROM Employees) THEN 'Top 2️5%'
WHEN Salary >= (SELECT PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY
Salary) FROM Employees) THEN 'Top 50%'
ELSE 'Bottom 50%'
END AS SalaryPercentile
FROM Employees;
More Use Cases
3️0.CASE WHEN with RANK() to find salary ranks
SELECT Name, Salary,
RANK() OVER (ORDER BY Salary DESC) AS SalaryRank,
CASE
WHEN RANK() OVER (ORDER BY Salary DESC) = 1️ THEN 'Top Earner'
ELSE 'Not Top Earner'
END AS RankCategory
FROM Employees;
3️1️.Determine Probation Period Status
SELECT Name, JoiningDate,
CASE
WHEN DATEDIFF(CURRENT_DATE, JoiningDate) < 1️80 THEN 'On Probation'
ELSE 'Permanent Employee'
END AS ProbationStatus
FROM Employees;
3️2️.CASE WHEN with DISTINCT Count
SELECT COUNT(DISTINCT Department) AS UniqueDepartments,
CASE
WHEN COUNT(DISTINCT Department) > 3️ THEN 'Diverse Company'
ELSE 'Limited Departments'
END AS DiversityCategory
FROM Employees;
3️3️.Categorize Employees Based on First Letter of Name
SELECT Name,
CASE
WHEN Name LIKE 'A%' THEN 'Starts with A'
WHEN Name LIKE 'B%' THEN 'Starts with B'
ELSE 'Other'
END AS NameCategory
FROM Employees;
3️4️.CASE WHEN with ORDER BY
SELECT Name, Salary, Experience,
CASE
WHEN Experience > 1️0 THEN 'Highly Experienced'
ELSE 'Less Experienced'
END AS ExperienceLevel
FROM Employees
ORDER BY
CASE
WHEN Experience > 1️0 THEN 1️
ELSE 2️
END, Salary DESC;
3️5.CASE WHEN to Group Employees by Even/Odd ID
SELECT Name, EmpID,
CASE
WHEN EmpID % 2️ = 0 THEN 'Even ID'
ELSE 'Odd ID'
END AS IDCategory
FROM Employees;
3️6.Assign Employee Shift Based on Age
SELECT Name, Age,
CASE
WHEN Age < 3️0 THEN 'Morning Shift'
WHEN Age BETWEEN 3️0 AND 4️5 THEN 'Afternoon Shift'
ELSE 'Night Shift'
END AS ShiftCategory
FROM Employees;
3️7️.Assign Work-from-Home Eligibility
SELECT Name, Department, Experience,
CASE
WHEN Department = 'IT' OR Experience > 1️0 THEN 'Eligible for Work from Home'
ELSE 'Office Work Required'
END AS WorkType
FROM Employees;
3️8.Assign Annual Leave Based on Experience
SELECT Name, Experience,
CASE
WHEN Experience >= 1️5 THEN '3️0 Days Leave'
WHEN Experience BETWEEN 1️0 AND 1️4️ THEN '2️5 Days Leave'
WHEN Experience BETWEEN 5 AND 9 THEN '2️0 Days Leave'
ELSE '1️5 Days Leave'
END AS LeaveCategory
FROM Employees;
3️9.Identify Employees Who Have Worked in Multiple Departments
SELECT Name, COUNT(DISTINCT Department) AS DepartmentCount,
CASE
WHEN COUNT(DISTINCT Department) > 1️ THEN 'Multi-Department Employee'
ELSE 'Single-Department Employee'
END AS EmployeeType
FROM Employees
GROUP BY Name;
4️0.Identify Salary Growth Over Time
SELECT e1️.Name, e1️.Salary AS CurrentSalary, e2️.Salary AS PreviousSalary,
CASE
WHEN e1️.Salary > e2️.Salary THEN 'Increased Salary'
WHEN e1️.Salary < e2️.Salary THEN 'Decreased Salary'
ELSE 'No Change'
END AS SalaryTrend
FROM Employees e1️
JOIN Employees e2️ ON e1️.EmpID = e2️.EmpID
WHERE e2️.JoiningDate < e1️.JoiningDate;
4️1️.Determine High-Performing Employees Based on Multiple Criteria
SELECT Name, Salary, Experience,
CASE
WHEN Salary > 90000 AND Experience > 8 THEN 'High Performer'
WHEN Salary > 60000 AND Experience BETWEEN 5 AND 8 THEN 'Medium
Performer'
ELSE 'Low Performer'
END AS PerformanceCategory
FROM Employees;
4️2️.CASE WHEN with UNION
SELECT Name, Salary, 'Above 80K' AS SalaryRange
FROM Employees WHERE Salary > 80000
UNION
SELECT Name, Salary, 'Below 80K'
FROM Employees WHERE Salary <= 80000;
4️3️.Categorize Employees Based on Joining Year
SELECT Name, JoiningDate,
CASE
WHEN YEAR(JoiningDate) >= 2️02️0 THEN 'Recent Joiner'
WHEN YEAR(JoiningDate) BETWEEN 2️01️0 AND 2️01️9 THEN 'Mid-Tenure'
ELSE 'Long-Term Employee'
END AS TenureCategory
FROM Employees;
4️4️.Identify Employees Close to Promotion
SELECT Name, Experience,
CASE
WHEN Experience >= 1️0 THEN 'Eligible for Promotion'
ELSE 'Needs More Experience'
END AS PromotionStatus
FROM Employees;
4️5.Employee Overtime Calculation
SELECT Name, Salary,
CASE
WHEN Salary < 60000 THEN Salary * 0.05
ELSE Salary * 0.02️
END AS OvertimePay
FROM Employees;
4️6.CASE WHEN with Nested IF Conditions
SELECT Name, Age, Salary,
CASE
WHEN Age < 3️0 THEN
CASE
WHEN Salary > 7️0000 THEN 'Young and High Earner'
ELSE 'Young and Average Earner'
END
WHEN Age >= 3️0 AND Age < 50 THEN
CASE
WHEN Salary > 90000 THEN 'Mid-Age and High Earner'
ELSE 'Mid-Age and Average Earner'
END
ELSE 'Senior Employee'
END AS EmployeeCategory
FROM Employees;
4️7️.Identify Employees Who Are Close to Retirement
SELECT Name, Age,
CASE
WHEN Age >= 58 THEN 'Retiring Soon'
ELSE 'Active Employee'
END AS RetirementCategory
FROM Employees;
4️8.Check Work Anniversary Status
SELECT Name, JoiningDate,
CASE
WHEN MONTH(JoiningDate) = MONTH(CURRENT_DATE) THEN 'Anniversary Month'
ELSE 'Regular Month'
END AS AnniversaryStatus
FROM Employees;
4️9.Determine If an Employee is Eligible for Training
SELECT Name, Experience,
CASE
WHEN Experience < 3️ THEN 'Needs Training'
ELSE 'No Training Required'
END AS TrainingStatus
FROM Employees;
50.Assign Performance Levels Using CASE WHEN
SELECT Name, Salary, Experience,
CASE
WHEN Salary > 1️00000 OR Experience > 1️5 THEN 'Top Performer'
WHEN Salary BETWEEN 7️0000 AND 1️00000 OR Experience BETWEEN 7️ AND 1️5
THEN 'Mid Performer'
ELSE 'Needs Improvement'
END AS PerformanceLevel
FROM Employees;
