Sql server® 2012 t-sql fundamentals

98  

Microsoft SQL Server 2012 T-SQL Fundamentals

7

You can handle the conditional logic required by this exercise by using a CASE expression. Using the 

simple CASE expression form, you specify the titleofcourtesy attribute right after the CASE keyword; 

list each possible title of courtesy in a separate WHEN clause followed by the THEN clause and the 

gender; and in the ELSE clause, specify ‘Unknown’.

SELECT empid, firstname, lastname, titleofcourtesy, 

  CASE titleofcourtesy 

    WHEN 'Ms.'  THEN 'Female' 

    WHEN 'Mrs.' THEN 'Female' 

    WHEN 'Mr.'  THEN 'Male' 

    ELSE             'Unknown' 

  END AS gender 

FROM HR.Employees;

You can also use the searched CASE form with two predicates—one to handle all cases where the 

gender is female and one for all cases where the gender is male—and an ELSE clause with ‘Unknown’.

SELECT empid, firstname, lastname, titleofcourtesy, 

  CASE  

    WHEN titleofcourtesy IN('Ms.', 'Mrs.') THEN 'Female' 

    WHEN titleofcourtesy = 'Mr.'           THEN 'Male' 

    ELSE                                        'Unknown' 

  END AS gender 

FROM HR.Employees;

8

By default, SQL Server sorts NULL marks before non-NULL values. To get NULL marks to sort last, you 

can use a CASE expression that returns 1 when the region column is NULL and 0 when it is not NULL. 

Non-NULL marks get 0 back from the expression; therefore, they sort before NULL marks (which get 

1). This CASE expression is used as the first sort column. The region column should be specified as the 

second sort column. This way, non-NULL marks sort correctly among themselves. Here’s the complete 

solution query.

SELECT custid, region 

FROM Sales.Customers 

ORDER BY 

  CASE WHEN region IS NULL THEN 1 ELSE 0 END, region;

www.it-ebooks.info

---