Advanced SQL Statement 1.0

WITH
WITH x AS (...)
SELECT * FROM x

Write subqueries for use in a larger SELECT query.
a temporary table or inline view.
________________________________________________________________

ROW_NUMBER
SELECT ROW_NUMBER() OVER(ORDER BY Name DESC) AS Row

Returns the sequential number of a row
________________________________________________________________

CASE
CASE is used to provide if-then-else type of logic to SQL.

1) Simple CASE expression:  

SELECT  ProductNumber, Category =
      CASE ProductLine
         WHEN 'R' THEN 'Road'
         WHEN 'M' THEN 'Mountain'
         ELSE 'Not for sale'
      END,
   Name
FROM Production.Product

compare an expression to static values

2) Searched CASE expression:

SELECT ProductNumber, Name, "Price Range" = 
   CASE 
      WHEN ListPrice =  0 THEN 'not for resale'
      WHEN ListPrice < 50 THEN 'Under $50'
      WHEN ListPrice > 50 and ListPrice < 250 THEN 'Under $250'
      ELSE 'Over $1000'
      END
FROM Production.Product

compare an expression to one or more logical conditions.

Combination

WITH SortedCode AS (SELECT ROW_NUMBER() OVER 
(ORDER BY cm1.ServiceCode asc) AS RowNumber, p.PriceCodeID, 
(CASE p.Category 
 WHEN 0 THEN 'Content Fee'
 WHEN 1 THEN 'Subscription Fee' ELSE '' END) AS Description
 FROM PriceCodes p 
 INNER JOIN Countries c On c.CountryID = p.CountryID  
 WHERE p.Category != 2 ) 
SELECT * FROM SortedCode WHERE RowNumber BETWEEN 1 AND 25