Lesson 6.4

Encoding Business Rules in SQL

Real reports turn requirements into logic. Combine CASE, null checks, and comparisons into one readable rule.

Most reporting work starts as a sentence from someone on the team: "Which orders still need attention?" Your job is to translate that into logic. Usually it's a CASE that stitches together the conditions you've already learned.

Here the rule is: an order needs a follow-up when it hasn't shipped and hasn't been cancelled. "Hasn't shipped" means shipped_date is null. Keep using IS NULL, never = NULL.

Pattern

select id,
       case
         when balance > 0 and due_date < date '2024-06-30' then 'overdue'
         else 'ok'
       end as billing_state
from invoices

Syntax reference

Need syntax details? See the SQL CASE reference

Schema · Garden Shop Table · orders 6 columns · 24 rows

Table · orders

One row per order. Unshipped orders have a null shipped_date.

6 columns · 24 rows

order_id int customer_id int order_date date shipped_date date status text coupon_code text

Your task

Build a follow-up worklist. For every order, add an action column that reads 'follow up' when the order hasn't shipped (no shipped_date) and its status isn't 'cancelled', and 'none' otherwise. Sort by action, then order_id, so the follow-ups group at the top.

Garden Shop · SQL engine loading · Ctrl/⌘ + Enter to run·

Run your query to inspect the result.

Expected answer

Columns: order_id, status, shipped_date, action.
Rows: 24 orders.
Orders with no shipped_date that aren't cancelled get 'follow up'; everything else is 'none'.

← Previous · 6.3 Filling gaps with COALESCE

End of Chapter 6

That's the last lesson in “CASE Logic and Derived Columns.”

The next chapter builds on what this one covered.

Start Chapter 7: Dates, Strings, and Nulls in Practice → Begins with 7.1 Filtering by date range

Reference

SQL CASE Expressions

Searched and simple CASE patterns.

Cheatsheet

SQL CASE Expressions Cheatsheet

Searched and simple CASE, plus conditional aggregation.

Interview