SPQuery Few important things ?

1. Always specify the RowLimit to get limited results.

oQuery.RowLimit = 100;

2. To Get the Search Items from the sub folders of the list using SPQuery you can do following. Check here

SPQuery.ViewAttributes = "Scope='Recursive'";
or
SPQuery.ViewAttributes = "Scope='RecursiveAll'";

3. Be sure to create a new SPQuery instance each time you use it. You cannot create one SPQuery instance and then reuse it multiple times in a loop where you alter the SPQuery's Query value.

 for (int i = 0; i < 10 ; i++)
{
SPQuery oQuery = new SPQuery();
oQuery.Query = "<Where><Eq><FieldRef Name='AssignedTo' /><Value Type='UserMulti'>"+i +"</Value></Eq></Where>";
SPListItemCollection collListItems = list.GetItems(oQuery);
}

4. SPQuery query object doesn't need to include tags

oQuery.Query = "Completed";

5. If you want to do CAML query which also consider the time in datetime fields then try this

<Where><Eq><FieldRef Name='Created'/><Value Type='DateTime' IncludeTimeValue='TRUE'><Today /></Value></Eq></Where>

6. SPQuery can't be used to search across site collection.One can use SPSiteDataQuery class to search across multiple Lists.

To query the lookup based value items

<where><eq><fieldref name="Employee" lookupid="TRUE"><value type="User">1</value></eq></where>  

7. To restrict the items to approved, use moderationtype attribute. more details here

SPQuery.ViewAttributes = "Scope='Recursive' ModerationType='HideUnapproved'";

8. If you want your query to return empty columns, you have to add Nullable='TRUE' to the viewfields.

query.ViewFields="<FieldRef Name='Description' Nullable='TRUE'/>";

To get the item related within past few days , you can use OffsetDays attribute along with Today.

<Where><Eq><FieldRef Name='Created'/><Value Type='DateTime' ><Today OffsetDays='-7' /></Value></Eq></Where>