Salesforce SOQL Query

Using the Force.com query tool of your choice (e.g., the Developer Console Query Editor,Workbench), you can execute SOQL query to get raw data from Salesforce which some of them not visible in report.

Simple query
SELECT Id, Name FROM Account WHERE Name LIKE 'A%'
SELECT Id, Name FROM Contact WHERE Name LIKE 'A%' AND MailingCity='California'
SELECT Id, Name FROM Account WHERE CurrencyIsoCode = 'USD' OR CurrencyIsoCode = 'SGD'

Query with LIKE and NOT
SELECT a.Id, a.Name FROM Account a WHERE NOT a.Name LIKE 'A%'
*** NOT should be put before field name

Query filter on DateTime
SELECT Name FROM Account WHERE CreatedDate > 2011-04-26T10:00:00-08:00
SELECT Name FROM Account WHERE CreatedDate > 2011-04-26T10:00:00Z

Query with Date function
SELECT Amount FROM Opportunity WHERE CALENDAR_YEAR(CreatedDate) = 2011

Query filter on null 
SELECT AccountId FROM Event WHERE ActivityDate != null

Query Multi-Select Picklists
SELECT Id, MSP1__c from CustObj__c WHERE MSP1__c includes ('AAA;BBB','CCC')
this will return record with MSP__1 example: 'AAA;BBB;DDD' ; 'CCC;EEE'

Semi-Join Query
SELECT Id, Name FROM Account WHERE Id IN ( SELECT AccountId FROM Opportunity WHERE StageName = 'Closed Lost')

Reference Field Semi-Join Query
SELECT Id FROM Task WHERE WhoId IN ( SELECT Id FROM Contact WHERE MailingCity = 'Twin Falls' )

Anti-Join Query
SELECT Id FROM Account WHERE Id NOT IN ( SELECT AccountId FROM Opportunity WHERE IsClosed = false )

Reference Field Anti-Join Query
SELECT Id FROM Opportunity WHERE AccountId NOT IN ( SELECT AccountId FROM Contact WHERE LeadSource = 'Web' )

Multiple Semi-Joins Query
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 )

Relationship Query: parent to child
SELECT Id, (SELECT Id from OpportunityLineItems) FROM Opportunity

Relationship Query: child to parent 
SELECT Id, Name, Account.Name FROM Contact

Relationship Query: Polymorphic 
A polymorphic relationship field in object being queried that can reference multiple object types. For example, the What relationship field of an Event could be an Account, or a Campaign, or an Opportunity.
SELECT Id FROM Event WHERE What.TYPE IN ('Account', 'Opportunity')

With OFFSET
Use OFFSET to specify the starting row offset into the result set returned by your query.
SELECT Id, Name FROM Opportunity ORDER BY Name OFFSET 5

With GROUP BY
From API version 18.0 and later, you can use GROUP BY with aggregate functions, such as COUNT(), SUM() or MAX()
SELECT Stagename, COUNT(Id) FROM Opportunity GROUP BY Stagename

SELECT LeadSource, COUNT(Name) FROM Lead GROUP BY LeadSource
SELECT Stagename, SUM(amount) FROM Opportunity GROUP BY Stagename
SELECT CALENDAR_YEAR(CloseDate), COUNT(Id) FROM Opportunity GROUP BY CALENDAR_YEAR(CloseDate) ORDER BY CALENDAR_YEAR(CloseDate)

With GROUP BY ROLLUP
Same with GROUP BY, with additional ROLLUP, it add subtotal for aggregated data in the last row
SELECT Stagename, COUNT(Id) FROM Opportunity GROUP BY ROLLUP(Stagename)

With GROUP BY ROLLUP with 2 fields
SELECT Status, LeadSource, COUNTId) FROM Lead GROUP BY ROLLUP(Status, LeadSource)

HAVING in GROUP BY
You can use a HAVING clause with a GROUP BY clause to filter the results returned by aggregate functions, same with WHERE with normal query
SELECT LeadSource, COUNT(Id) FROM Lead GROUP BY LeadSource HAVING COUNT(Id) > 2

Querying Currency Fields in Multi-currency Organizations
SELECT Id, Name FROM Opportunity WHERE Amount > JPY5000
without currency code it will use organization's default currency