CASE expressions

Branch inside a query to label rows, bucket values and sort custom orders.

Beginner 12 min read Lesson 10 of 480 Challenge included

What you will be able to do

Write searched CASE expressions
Aggregate with conditional counts
Sort with a custom priority

CASE returns a value from the first matching branch. Use it to classify rows without changing the stored data.

case.sqlSQL

SELECT title, minutes,
       CASE
         WHEN minutes >= 15 THEN 'Long'
         WHEN minutes >= 12 THEN 'Medium'
         ELSE 'Short'
       END AS length_band
FROM lessons
ORDER BY minutes DESC;

SELECT course_slug,
       COUNT(*) AS total,
       SUM(CASE WHEN minutes >= 15 THEN 1 ELSE 0 END) AS long_lessons
FROM lessons
GROUP BY course_slug
ORDER BY long_lessons DESC;

The first matching WHEN wins, so order branches from most specific to least.
Without ELSE, an unmatched CASE returns NULL.
SUM(CASE WHEN ... THEN 1 ELSE 0 END) counts rows that meet a condition.
CASE also works in ORDER BY for a custom sort key.

Your turn

Band the courses

Medium

For each course_slug, return the rounded average minutes and a band: Deep at 15 or more, Standard at 12 or more, otherwise Fast.

SQL · starter

SELECT course_slug,
       AVG(minutes) AS avg_minutes
FROM lessons
GROUP BY course_slug

Check your understanding before moving on.

Knowledge checkQuestion 1

What does CASE return when no WHEN matches and there is no ELSE?

Select an answer to continue

Study tools

Make it yours.

Keep a private note for this lesson, then turn the key ideas into flashcards you can revisit.

Sign in to save notes and flashcards.Both are private to your account and stay with your progress.

Finished reading?

Lock in what you just learned.

Mark this lesson complete to save your place and move one step closer to the certificate.

Next: Subqueries and CTEs

Lesson 10 of 480 · 0% of course