Simple CASE:

-- Example #1
SELECT RatingStars = 
       CASE Rating
           WHEN 1 THEN '*'
           WHEN 2 THEN '**'
           WHEN 3 THEN '***'
           ELSE 'Not Rated'
       END
FROM Book
 
-- Example #2
-- Mapping CHAR to INT
SELECT BookTypeCode = 
       CASE BookType
           WHEN 'P' THEN 1
           WHEN 'A' THEN 2
           WHEN 'U' THEN 3
           WHEN 'R' THEN 4
       END
FROM Book
 
-- Example #3
DECLARE @TYPE INT, @ObjectID INT
...
SELECT @TYPE = CASE WHEN SubType = 1 THEN 
    ISNULL((SELECT ObjectType FROM Object WHERE ID=@ObjectID), 100) ELSE SubType END
FROM MessageType
 
-- Example #4
DECLARE @Name VARCHAR(100), @TYPE INT, @Price MONEY
...
SET @Name = @Name + 
    CASE WHEN @TYPE=0 THEN '-' ELSE '+' END +
    CASE WHEN @Price IS NULL THEN '' 
         WHEN @Price=0 THEN '($0.00) '
         ELSE '($' + CONVERT(VARCHAR, CAST(@Price AS MONEY), 1) + ') ' END

Search CASE:

-- Example #1
SELECT Title,
    CASE WHEN Rating >= 2 AND BookType='P' THEN 'Good programming book'
         WHEN Rating < 2 AND BookType='A' THEN 'Bad admin book'
         ELSE 'No opinion'
    END AS Opinion
FROM Book
 
-- Example #2
-- CASE with UPDATE...FROM and EXISTS
UPDATE Book SET RatingCode = CASE
    WHEN EXISTS (SELECT BookId FROM Book WHERE BookType='P' AND tmp.Rating>3) THEN 200
    WHEN EXISTS (SELECT BookId FROM Book WHERE BookType='A' AND tmp.Rating>4) THEN 300
    ELSE 100 END
FROM Book tmp

if..then..else:

-- Example #1
SELECT IsProgrammingBook = CASE WHEN BookType='P' THEN 1 ELSE 0 END
FROM Book
 
-- Example #2
SELECT AdminBookExists = CASE WHEN EXISTS (SELECT BookID FROM Book WHERE BookType='A') THEN 1 ELSE 0 END
FROM Book
 
-- Example #3
SELECT @Url = CASE WHEN IsActive=1 THEN PageUrl ELSE '' END
FROM WebPage
WHERE PageID=@PageID