SOQL Demo: Use Workbench to create a SOQL

Workbench can be used to build SOQL commands.Use this link to sign in to Workbench. You will need to be signed into your Salesforce environment.

Salesforce Object Query Language is very similar to SQL but not as powerful.  It's an easy language to learn.

SOQL uses the SELECT statement combined with filtering statements to return sets of data, which may optionally be ordered:

SELECT one or more fields
FROM an object
WHERE filter statements and, optionally, results are ordered

For example, the following SOQL query returns the value of the Id and Name field for all Account records if the value of Name is Sandy:

SELECT Id, Name
FROM Account
WHERE Name = 'Sandy'

This process is very much like producing a salesforce report but is more time effective way to get the data you need.

Soql Guide

Watch this video:

Upwards traversal - from child to parent - via lookup or master-detail relationship
SELECT Id, Account.Name, Account.Industry, Account.Website
    FROM Contact

    WHERE Account.NumberOfEmployees >= 200

referencing account fields (parent) from contact (child) using dot notation

SELECT Account.Owner.Profile.CreatedBy.Name FROM Contact

You can traverse multiple levels upwards

SELECT Id, customlookupfield__r.customfield__c from contact

Here we’re traversing a custom lookup field customlookupfield__c on the contact object. Notice how the “__c” changes to a “__r” when traversing that field
Downwards traversal - from parent to child - via related list
SELECT Id, Name, Industry, AnnualRevenue,
    ( SELECT Name, Email, BirthDate FROM Contacts )

    FROM Account
nested soql is like another field, nested object uses the plural, (child relationship name found in object settings)

Simple query
SELECT Name FROM Account WHERE Name like 'A%'
SELECT Id FROM Contact WHERE Name LIKE 'A%' AND MailingCity='California'

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