Forcing sort order not directly supported by the data Eg: Retrieve only colored products and show red products first before other products.
Solution: The solution converts the value ‘Red’ into a single space, which sorts before all the color names. The CASE expression specifies LOWER(Color) to ensure ‘Red’, ‘RED’, ‘red’, and so forth are all treated the same. Other color values are forced to lowercase to prevent any case-sensitivity problems in the sort.

SELECT ProductID, Name, Color FROM Products
WHERE Color IS NOT NULL
ORDER BY CASE LOWER(Color)
WHEN 'red' THEN ' '
ELSE LOWER(Color)
END;

Present a result set to an application user N rows at a time. Eg: Skip the first ten rows and fetch the next five rows.
Solution: Make use of the query paging feature in SQL Server 2012. Use OFFSET to specify how many rows to skip from the beginning of the possible result set and FETCH to set the number of rows to return. You must specify an ORDER BY clause.

SELECT ProductID, Name FROM Product
ORDER BY Name
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

Query to find Nth highest salary without using SubQuery Eg: write a query to get the 5th highest salary.
Solution: Make use of the Common table expression to get the salaries along with a row number. Then select from the common table express to get the 5th highest salary.

WITH Salaries AS(SELECT Salary, ROW_NUMBER() OVER(ORDER BY Salary DESC) AS 'RowNum' FROM Employee)
SELECT  Salary fROM  Salaries 
WHERE RowNum = 5

Differences between common table expression and temporary tables(or table variable):
A common table expression creates the table being used in memory, but is only valid for the specific query following it. Common table expression will need to be recreated every time it is needed.
A Temporary tables(or table variable) is also in-memory only, but can be used multiple times without needing to be recreated every time. Also, if you need to persist a few records now, add a few more records after the next select, add a few more records after another op, then return just those handful of records, then this is a handy in-memory structure.