Code Example 1:

SELECT pub_name, 
  CASE WHEN state is NULL or state = ''
       THEN 'Not supplied'
       ELSE state
  END
FROM publishers
  
SELECT CASE WHEN datepart (dw, getdate()) In (1, 7)
            THEN 'Weekend overtime rates apply :-)'
            ELSE 'Standard rates apply'
END

Code Example 2:

SELECT CASE GROUPING(stor_name)
          WHEN 1 then '[State total]'
          ELSE stor_name
       End as store,
       state,
       sum(qty)
       FROM stores s
JOIN   sales sl on s.stor_id = sl.stor_id
GROUP  BY stor_name, state
WITH   CUBE
HAVING GROUPING(state) = 0
ORDER  BY state, GROUPING(stor_name)

Code Example 3:

declare @storeAvg int
select @storeAvg = avg(storeTotal)
from (
    select stor_id,
    sum(qty) as storeTotal
    from sales
    group by stor_id
) as derived

select  stor_name,
        storeTotal,
case when storeTotal > @storeAvg * 1.1
        then 'Above average'
     when storeTotal < @storeAvg * 0.9
        then 'Below average'
     else 'About average'
end as storePerformance
from (
          select  stor_id,
                  sum(qty) as storeTotal
                  from     sales
                  group    by stor_id
) as derivedStores
join stores
on stores.stor_id = derivedStores.stor_id

Code Example 4:

SELECT SUM (CASE when qty <= 5 then 1 else 0 end) 
              as '1_to_5',
       SUM (CASE when qty between 6 and 10 then 1 else 0 end) 
              as '6_to_10',
       SUM (CASE when qty between 11 and 20 then 1 else 0 end) 
              as '11_to_20',
       SUM (CASE when qty > 20 then 1 else 0 end) 
              as '20_plus'
  FROM sales