Creating fields with CASE WHEN

The CASE WHEN statement is a straightforward technique for creating new fields using conditional logic. It allows you to specify multiple conditions and define actions or outcomes for each condition. The CASE WHEN statement is often used to transform data, create calculated columns, or perform conditional aggregations. The syntax of the CASE WHEN statement is as follows:

CASE WHEN
condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE else_result
END As alias;

Here is an example where we create a new field that will detail if a student passed or failed, based on their scores:

SELECT student_id, student_name, exam_score,
CASE WHEN exam_score >= 60 THEN 'Pass'
ELSE 'Fail'
END AS result
FROM students;

This query creates a new field called result, and populates it with "Pass" when the student scored at least 60 on their exa; otherwise, it populates it "Fail". The results...