Salesforce Object Query Language (SOQL)

SELECT fieldList FROM objectType
[WHERE conditionExpression]
[ORDER BY fieldName ASC | DESC ? NULLS FIRST | LAST ?]
LIMIT ?

Examples:

1. SELECT Id, Name, BillingCity FROM Account
2. SELECT count() FROM Contact
3. SELECT Contact.Firstname, Contact.Account.Name FROM Contact
4. SELECT Account.Name, (SELECT Contact.LastName FROM Account.Contacts)
FROM Account
5. SELECT Name FROM Account WHERE Name like 'A%'
6. SELECT Id FROM Contact WHERE Name like 'A%' AND
MailingCity='California'
7. SELECT Id FROM Case WHERE Contact.Lastname = null
8. SELECT AccountId, FirstName, lastname FROM Contact WHERE lastname LIKE 'appl_%'
9. SELECT Name FROM ACCOUNT WHERE BillingState IN ('California', 'New York')
10. SELECT Name FROM ACCOUNT WHERE BillingState NOT IN ('California', 'New York')
11. SELECT Id, Name FROM Account
WHERE Id IN (SELECT AccountId FROM Opportunity WHERE StageName = 'Closed Lost')
12. SELECT Id
FROM Account
WHERE Id NOT IN (SELECT AccountId FROM Opportunity WHERE IsClosed = false)
13. SELECT Id, Name
FROM Account
WHERE Id IN (SELECT AccountId FROM Contact WHERE LastName LIKE 'apple%')
 AND Id IN (SELECT AccountId FROM Opportunity WHERE isClosed = false)
14. SELECT Id, (SELECT Id from OpportunityLineItems)
FROM Opportunity
WHERE Id IN
 (SELECT OpportunityId FROM OpportunityLineItem WHERE totalPrice > 10000)
15. etc.

Escape sequence:

Sequence	Meaning
\n	New line
\r	Carriage return
\t	Tab
\b	Bell
\f	Form feed
\"	One double-quote character
\'	One single-quote character
\\	Backslash

Example with escape sequence:
SELECT Id FROM Account WHERE Name LIKE 'Bob\'s BBQ'

More information about query with SOQL, read here

Note that NULL operator is: '= null' or '!= null', NOT 'IS NULL' or 'IS NOT NULL'

Source: salesforce.com