Forum

Forums
SQL
Interview Questions...
"How to Use CASE St...
 
Share:
Notifications
Clear all

"How to Use CASE Statements in SQL for Conditional Logic"

 
Interview Questions & Answers
Last Post by SQL Admin 1 year ago
1 Posts
1 Users
0 Reactions
670 Views
RSS
Posts: 134
 SQL Admin
Admin
Topic starter
Jan 12, 2025 3:33 pm
(@sql-admin)
Reputable Member
Joined: 6 years ago

Introduction
The CASE statement in SQL is a powerful tool that allows you to perform conditional logic directly within your queries. Whether you're creating dynamic columns, handling complex business rules, or categorizing data, the CASE statement offers flexibility and efficiency. In this post, we'll explore various use cases of the CASE statement with practical examples.

Understanding the Syntax of CASE

The basic syntax of a CASE statement is as follows:

sql
 
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result_default
END
  • WHEN: Specifies a condition to evaluate.
  • THEN: Defines the result if the condition is true.
  • ELSE: Provides a default result if none of the conditions are met.
  • END: Marks the end of the CASE statement.

Example Table: Employees

EmployeeIDNameSalaryDepartment
1Alice7000HR
2Bob9000IT
3Charlie4000Finance
4Diana8000IT
5Edward3000HR

1. Categorizing Salaries into Ranges

You can use a CASE statement to classify employees based on their salary range:

sql
 
SELECT Name, Salary,
CASE
WHEN Salary >= 8000 THEN 'High'
WHEN Salary BETWEEN 5000 AND 7999 THEN 'Medium'
ELSE 'Low'
END AS SalaryCategory
FROM Employees;

Output:

NameSalarySalaryCategory
Alice7000Medium
Bob9000High
Charlie4000Low
Diana8000High
Edward3000Low

2. Creating Dynamic Columns

You can display dynamic column values based on a condition. For example, calculate a bonus percentage based on the department:

sql
 
SELECT Name, Department,
CASE Department
WHEN 'HR' THEN 10
WHEN 'IT' THEN 15
WHEN 'Finance' THEN 20
ELSE 5
END AS BonusPercentage
FROM Employees;

Output:

NameDepartmentBonusPercentage
AliceHR10
BobIT15
CharlieFinance20
DianaIT15
EdwardHR10

3. Conditional Aggregation with CASE

The CASE statement can be used inside aggregate functions to compute conditional sums or counts.

For example, calculate the total salary for each department:

sql
 
SELECT Department,
SUM(CASE WHEN Salary >= 5000 THEN Salary ELSE 0 END) AS TotalHighSalaries,
SUM(CASE WHEN Salary < 5000 THEN Salary ELSE 0 END) AS TotalLowSalaries
FROM Employees
GROUP BY Department;

4. Combining CASE with ORDER BY

You can use CASE in the ORDER BY clause to sort data dynamically.

For example, sort employees by custom salary categories:

sql
 
SELECT Name, Salary
FROM Employees
ORDER BY
CASE
WHEN Salary >= 8000 THEN 1
WHEN Salary BETWEEN 5000 AND 7999 THEN 2
ELSE 3
END;

Applications of CASE in SQL

  1. Data Transformation: Categorize data on the fly.
  2. Custom Reporting: Generate dynamic reports based on business rules.
  3. Complex Queries: Simplify logic without creating multiple queries or views.
  4. Conditional Aggregation: Perform conditional counts, sums, or averages.

Conclusion
The CASE statement is a versatile tool in SQL, enabling you to handle complex conditions and dynamic logic seamlessly. By mastering its syntax and applications, you can significantly enhance your data queries and reporting capabilities. For more SQL insights and advanced techniques, visit our SQL forum.


Topic Tags
SQL CASE statement examp conditional logic in SQL SQL dynamic columns SQL conditional aggregat advanced SQL techniques

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Related Topics
Topic Tags:  SQL CASE statement examp (1) , conditional logic in SQL (2) , SQL dynamic columns (1) , SQL conditional aggregat (1) , advanced SQL techniques (3) ,
Share: