SQL CASE statement examples

It is quite difficult to write a stored procedure or a function if you do not know or understand the SQL conditional processing statements. From an interview perspective, you should familiarize yourself with the conditional processing as well as the flow control statements.

The CASE expression is used to evaluate several conditions and return a single value for each condition. For example, it allows an alternative value to be displayed depending on the value of a column. A common use of the CASE expression is to replace codes or abbreviations with more readable values. This is also known as a Simple CASE expression.

Question: For a given product and category abbreviation, show the full category name using a CASE expression.

SELECT   ProductNumber, Category =

      CASE ProductLine

         WHEN 'R' THEN 'Road'

         WHEN 'M' THEN 'Mountain'

         WHEN 'T' THEN 'Touring'

         WHEN 'S' THEN 'Other sale items'

         ELSE 'Not for sale'

      END,

   Name

FROM Product

ORDER BY ProductNumber;

Another use of CASE is to categorize data. 

Question: Based on an item list price, show the price range for the item. The example below shows a searched CASE expression which evaluates a set of Boolean expressions to determine the result. 

SELECT   ProductNumber, Name, 'Price Range' = 

      CASE 

         WHEN ListPrice =  0 THEN 'Mfg item - not for resale'

         WHEN ListPrice < 50 THEN 'Under $50'

         WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'

         WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'

         ELSE 'Over $1000'

      END

FROM Production.Product

ORDER BY ProductNumber ;

Read more: Programming Interviews Series

Posted via email from Jasper-net