SQL Server Searched CASE With Sub Queries Very Powerful

CASE_SUB_QUERIES.sql

SELECT au_lname, au_fname, title, Category =
        CASE
         WHEN (SELECT AVG(royaltyper) FROM titleauthor ta
                           WHERE t.title_id = ta.title_id) > 65
                 THEN 'Very High'
         WHEN (SELECT AVG(royaltyper) FROM titleauthor ta
                           WHERE t.title_id = ta.title_id)
                                     BETWEEN 55 and 64
                 THEN 'High'
         WHEN (SELECT AVG(royaltyper) FROM titleauthor ta
                           WHERE t.title_id = ta.title_id)
                                     BETWEEN 41 and 54
                 THEN 'Moderate'
         ELSE 'Low'
       END
FROM authors a,
     titles t,
     titleauthor ta
WHERE a.au_id = ta.au_id
AND   ta.title_id = t.title_id
ORDER BY Category, au_lname, au_fname

Case_UPDATE.sql

-- This is an example of using a Search CASE within an Update Statement
UPDATE titles
       SET price =
                 CASE
                   WHEN (price < 5.0 AND ytd_sales > 999.99)
                                   THEN price * 1.25
                   WHEN (price < 5.0 AND ytd_sales < 1000.00)
                                   THEN price * 1.15
                   WHEN (price > 4.99 AND ytd_sales > 999.99)
                                   THEN price * 1.2
                   ELSE price
                 END

http://www.mullinsconsulting.com/ssu_0899.htm