SQL Case Statements

SQL Case statement can be used in 2 forms:

1. SQL Case statement with simple expression to compare and get results.
   
2. SQL Case statement with search or comparison expression to get results.

1. SQL Case statement with simple expression:
You can use default Northwind Database to test the functionality and syntax of SQL case statement.

Syntax:

USE NORTHWIND

SELECT
'Title' =
CASE TITLEOFCOURTESY
WHEN 'MS.' THEN 'MISSUS'
WHEN 'MRS.' THEN 'MISTRESS'
WHEN 'MR.' THEN 'MISTER'
WHEN 'DR.' THEN 'DOCTOR'
ELSE ''
END,
TITLEOFCOURTESY,
FIRSTNAME,
LASTNAME
FROM EMPLOYEES 

Output:

Title	TitleOfCourtsey	FirstName	LastName
Missus	Ms.	Nancy	Davolio
Doctor	Dr.	Andrew	Fuller
Missus	Ms.	Janet	Leverling
Mistress	Mrs.	Margaret	Peacock
Mister	Mr.	Steven	Buchanan
Mister	Mr.	Michael	Suyama
Mister	Mr.	Robert	King
Missus	Ms.	Laura	Callahan
Missus	Ms.	Anne	Dodsworth

2. SQL Case Statement with search comparison expressions:

Syntax:

USE NORTHWIND

SELECT
'COMMENT' =
CASE
WHEN  UNITPRICE IS NULL THEN 'NO PRICE DEFINED'
WHEN  UNITPRICE < 20 THEN 'LOW PRICE'
WHEN  UNITPRICE > 20 AND UNITPRICE < 30 THEN 'REASONABLE PRICE'
ELSE 'EXPENSIVE ITEM!!!'
END,
PRODUCTNAME,
UNITPRICE
FROM PRODUCTS

Output: 

Comment	ProductName	UnitPrice
Low Price	Chai	18.00
Low Price	Chang	19.00
Low Price	Aniseed Syrup	10.00
Reasonable Price	Chef Anton's Cajun Seasoning	22.00
Reasonable Price	Chef Anton's Gumbo Mix	21.35
Reasonable Price	Grandma's Boysenberry Spread	25.00
Expensive Item!!!	Uncle Bob's Organic Dried Pears	30.00
Expensive Item!!!	Northwoods Cranberry Sauce	40.00