From the SOQL reference guide.....

Alias Notation

You can use alias notation in SELECT queries: SELECT count() FROM Contact c, c.Account a WHERE a.name = 'MyriadPubs'

To establish the alias, first identify the object, in this example a contact, and then specify the alias, in this case “c.” For the rest of the SELECT statement, you can use the alias in place of the object or field name.

The following are SOQL keywords that can’t be used as alias names: AND, ASC, DESC, EXCLUDES, FIRST, FROM, GROUP, HAVING, IN, INCLUDES, LAST, LIKE, LIMIT, NOT, NULL, NULLS, OR, SELECT, WHERE, WITH

See page 2 of the reference guide here

You can use object aliasing in any SOQL query

From the SOQL reference guide.....

Alias Notation

You can use alias notation in SELECT queries: SELECT count() FROM Contact c, c.Account a WHERE a.name = 'MyriadPubs'

To establish the alias, first identify the object, in this example a contact, and then specify the alias, in this case “c.” For the rest of the SELECT statement, you can use the alias in place of the object or field name.

The following are SOQL keywords that can’t be used as alias names: AND, ASC, DESC, EXCLUDES, FIRST, FROM, GROUP, HAVING, IN, INCLUDES, LAST, LIKE, LIMIT, NOT, NULL, NULLS, OR, SELECT, WHERE, WITH

See page 2 of the reference guide here

You can only use field aliasing with aggregate expressions. For more info on aliasing with aggregates, take a look here

http://www.salesforce.com/us/developer/docs/dbcom_soql_sosl/Content/sforce_api_calls_soql_select_groupby_alias.htm