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 < current_date then 'overdue'
         else 'ok'
       end as billing_state
from invoices

Schema · Garden ShopTable · orders6 columns · 24 rows

Table · orders

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

6 columns · 24 rows

order_id intcustomer_id intorder_date dateshipped_date datestatus textcoupon_code text

Your task

Build a follow-up worklist. For every order, add anaction column that reads 'follow up' when the order has not shipped (no shipped_date) and its status is not'cancelled', and 'none' otherwise. Sort byaction, then order_id, so the follow-ups group at the top.

SQL Workbench

query.sqlgarden_shop · SQL engine loading

⌘↵ to run

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

✓ Chapter 6 complete

You finished “CASE Logic and Derived Columns.”

Nice work. Ready to start the next one?

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

Lesson

Calculated Columns in SQL

Create derived values in a SELECT list.

Lesson

SQL CASE WHEN: Labeling Rows

Add if/else logic to query results.

Lesson