Examples:

-- SELECT ... FROM SELECT 
-- 'AS Temp' is important here
SELECT BookId, Title FROM (SELECT * FROM Books) AS Temp
 
-- Conditional ORDER BY
DECLARE @SortOrder INT
...
SELECT * FROM Book
ORDER BY CASE WHEN @SortOrder=0 THEN Title END ASC,
         CASE WHEN @SortOrder=1 THEN BookId END DESC
 
-- Show all tables in the current database
-- Columns: TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
SELECT *
FROM INFORMATION_SCHEMA.TABLES
ORDER BY TABLE_NAME
 
-- Show columns for a given table
SELECT TABLE_NAME, COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE LOWER(TABLE_NAME)='customer'
ORDER BY ORDINAL_POSITION

Examples with sample data

#items is a test table:

-- Create the test table. CustomerId is a foreign key to a parent table Customer.
CREATE TABLE #items 
(
    ItemId INT IDENTITY(1,1), 
    ItemType CHAR(1), 
    CustomerId INT, 
    Price MONEY, 
    PurchaseDate DATETIME
)
 
-- Insert some sample data.
INSERT INTO #items VALUES ('A', 1, 5.99,  '20120522')
INSERT INTO #items VALUES ('A', 1, 60.25, '20121008')
INSERT INTO #items VALUES ('B', 1, 12.0,  '20120317')
INSERT INTO #items VALUES ('B', 1, 3.99,  '20120317')
INSERT INTO #items VALUES ('C', 1, 23.80, '20120317')
INSERT INTO #items VALUES ('A', 2, 20.60, '20120905')
INSERT INTO #items VALUES ('C', 2, 9.99,  '20121106')
 
-- Example goes here:
-- ...
 
-- Drop the test table when we are done.
DROP TABLE #items
GO

Example #1: Select the following items:

• Items of type 'A' with prices higher than $10.
• Items of type 'B' with prices lower than $10

SELECT ItemId, ItemType, Price
FROM #items
WHERE ItemType=(CASE WHEN Price>10 THEN 'A' ELSE 'B' END)
 
-- Results:
ItemId      ItemType Price
----------- -------- ---------------------
2           A        60.25
4           B        3.99
6           A        20.60

Example #2: Select the last purchase date for each customer:

SELECT DISTINCT CustomerId, 
    (SELECT TOP 1 PurchaseDate 
     FROM #items rec2 
     WHERE rec1.CustomerId = rec2.CustomerId 
     ORDER BY PurchaseDate DESC) AS LatestPurchase
FROM #items rec1
 
-- Results:
CustomerId  LatestPurchase
----------- -----------------------
1           2012-10-08 00:00:00.000
2           2012-11-06 00:00:00.000

Example #3: Select ItemType of an item with the highest price (for a given customer):

SELECT ItemType = (SELECT TOP 1 ItemType
                   FROM #items
                   WHERE CustomerId=1
                   ORDER BY Price DESC)
-- Results:
ItemType
--------
A                   

Other examples:

-- Correlated queries
-- Select the first orders placed by each customer:
SELECT ord.CustomerID, ord.OrderID, ord.OrderDate
FROM [ORDER] ord
WHERE ord.OrderDate = (SELECT MIN(OrderDate)
                       FROM [ORDER]
                       WHERE CustomerID = ord.CustomerID)
ORDER BY CustomerID

Obtain the count using dynamic SQL:

DECLARE @SQL NVARCHAR(2000), @cnt INT
 
SET @SQL = 'SELECT @cnt = COUNT(*) FROM MyTable'
EXEC sp_executesql @query = @SQL, @params = N'@cnt INT OUTPUT', @cnt = @cnt OUTPUT