CASE (Advanced Patterns)

Overview

CASE — Create Conditional Logic in SQL

CASE lets you add if–then logic inside a SQL query.

It creates new categories, labels, or computed values based on conditions.

It’s perfect for questions like:

• “If a student has 0 enrollments, label them No Courses.”
• “If a course has 3 or more credits, call it Full Credit.”
• “If a grade is 90 or above, label it A.”

CASE works like simple decision-making inside SQL.

---

The Basic Pattern

CASE
    WHEN condition THEN result
    WHEN condition THEN result
    ELSE result
END
  

SQL reads the CASE from top to bottom and stops at the first true condition.

---

Examples

Example 1: Label courses based on credit load

SELECT course_name,
       credits,
       CASE
           WHEN credits >= 4 THEN 'High Credit'
           WHEN credits = 3 THEN 'Medium Credit'
           ELSE 'Low Credit'
       END AS CreditLevel
FROM courses;
  

Explanation

• If credits >= 4 → High Credit
• If credits = 3 → Medium Credit
• Otherwise → Low Credit

---

Example 2: Label students based on number of enrollments

SELECT s.first_name,
       s.last_name,
       (SELECT COUNT(*)
        FROM enrollments e
        WHERE e.student_id = s.student_id) AS TotalEnrollments,
       CASE
           WHEN (SELECT COUNT(*)
                 FROM enrollments e
                 WHERE e.student_id = s.student_id) = 0
                THEN 'No Courses'
           WHEN (SELECT COUNT(*)
                 FROM enrollments e
                 WHERE e.student_id = s.student_id) = 1
                THEN 'One Course'
           ELSE 'Multiple Courses'
       END AS EnrollmentStatus
FROM students s;
  

Explanation

• 0 enrollments → No Courses
• 1 enrollment → One Course
• More than 1 → Multiple Courses

---

Example 3: Simple grade categories

(Assuming grades are stored as numeric values.)

SELECT student_id,
       course_id,
       grade,
       CASE
           WHEN grade >= 90 THEN 'A'
           WHEN grade >= 80 THEN 'B'
           WHEN grade >= 70 THEN 'C'
           ELSE 'D or Below'
       END AS GradeCategory
FROM enrollments;
  

A straightforward way to convert numeric grades into letters.

---

Example 4: Label instructors by department type

SELECT first_name,
       last_name,
       department,
       CASE
           WHEN department = 'Math' THEN 'STEM'
           WHEN department = 'Science' THEN 'STEM'
           ELSE 'Non STEM'
       END AS DeptType
FROM instructors;
  

Explanation

• Math or Science → STEM
• Everything else → Non STEM

---

Why CASE Is Useful

• Adds categories and labels
• Makes query results easier to read
• Helps with reporting and dashboards
• Works in SELECT, ORDER BY, and GROUP BY

---

CASE Summary

Concept	Meaning
CASE	Adds if-then logic to SQL
WHEN	Condition to check
THEN	Result when condition is true
ELSE	Default result

---

Practice Tasks (Your Turn!)

Task 1

Label courses as High, Medium, or Low credit.

Task 2

Label students as No Courses, One Course, or Multiple Courses.

Task 3

Label instructors as STEM or Non STEM.