Best Practices for Query Optimization

I'd like to take note of few tips from Pinal Dave. Certainly good reminders when creating queries.