Subqueries in SQL Server

Subqueries and derived tables can add great versatility to SQL statements, cut down complexity, but can occasionally be a curse...