SOQL & DML in Salesforce

In this episode we will learn about retrieving and manipulating data stored within Salesforce programmatically using SOQL & DML in Salesforce. We will learn how to use SOQL in Salesforce. SOQL (Salesforce object Query Language) used to retrieves the records from the database. We will also learn about basic of SOSL in Salesforce.

What is SOQL in Salesforce?

SOQL Stands for Salesforce Object Query Language. Similar to SQL, but is designed specifically for Salesforce data. Uses the same structure and keywords from SQL. You can query data from Salesforce using SOQL in.

  1. Apex Code
  2. Developer Console
  3. Salesforce REST and SOAP APIs
  4. Salesforce CLI

Basics of SOQL

SOQL in Salesforce
Basics of SOQL

Conditional Expressions

Conditional Operator Description
= Equals
!= Not Equals
< , <= Less Than, Less Than or Equal To
> , >= Greater Than, Greater Than or Equal To
INCLUDES, EXCLUDES Includes or Excludes Values. Applies only to Multi-Select Picklists.
LIKE Returns the records where the field values match the pattern specified after the LIKE operator
IN Selects only the records where the value of the field matches any of the values specified after IN keyword
NOT IN Selects only the records where the value of the DOES NOT MATCH any of the values specified after IN keyword

Conditional Expressions

  • Each conditional operator mentioned in the above table returns a TRUE/FALSE value
  • Build your own logic by joining multiple conditional operators using logical operators –
    • AND
    • OR
    • NOT
  • Use Apex variables for comparison in your SOQL query by adding a colon (:) before the variable name

Aggregate Queries

  • Use aggregate queries to perform the following aggregate functions on the result set –
    • COUNT
    • SUM
    • AVERAGE
    • MIN
    • MAX
  • Specify the field name to perform aggregation against that field in the result set

GROUP BY and HAVING Clauses

  • Use the GROUP BY clause to group the result set based on the field specified
  • HAVING is an optional clause used to filter the results that an aggregated function returns
  • Difference between HAVING and WHERE clause is that you can use aggregated function in HAVING, but not in WHERE.

Writing SOQL in Apex

  • SOQL query always returns a List. In Apex, create a List for the object specified in the query
  • Always store the query result in a List and not a single instance
  • Use for…loop for iterating over the results of a SOQL query
  • Can directly write query in a for…loop for better performance
Writing SOQL in Apex

Writing SOQL in Apex

DML Operations in Salesforce

Apex allows DML operations on both List<SObject> and single SObject to update data in the database

DML Operation Usage
insert [SObject | List<SObject>] Insert a new record or a list of records
update [SObject | List<SObject>] Update existing records with the new data specified. Existing records are matched using the ID
delete [SObject | List<SObject>] Delete records with the matching ID from the database
upsert [SObject | List<SObject>] Performs both insert/update on the specified records. If record ID matches, then it updates the record. Otherwise it inserts a new record
merge SObject1 SObject2 Merges up to three records of the same SObject into a single record
undelete [SObject | List<SObject>] Retrieves a record or list of records previously deleted from recycle bin

Database Methods

DML Operation Equivalent Database Method
insert [SObject | List<SObject>] Database.insert([SObject|List<SObject>], [Boolean allOrNone]);
update [SObject | List<SObject>] Database.update([SObject|List<SObject>], [Boolean allOrNone]);
delete [SObject | List<SObject>] Database.delete([SObject|List<SObject>], [Boolean allOrNone]);
upsert [SObject | List<SObject>] Database.upsert([SObject|List<SObject>], [Boolean allOrNone]);
merge SObject1 SObject2 Database.merge([SObject|List<SObject>], [SObject|List<SObject>], [Boolean allOrNone]);
undelete [SObject | List<SObject>] Database.undelete([SObject|List<SObject>], [Boolean allOrNone]);

Recording

Basic of SOQL and SOSL in Salesforce | Query Plan| DAY 4 Part 

  • What is SOQL
  • Basics of SOQL
  • Relationship Queries
  • SOQL in Apex, Querying Records in Batches by Using SOQL for Loops
  • Introduction to SOSL
  • Using the right tool for the job SOQL Vs SOSL
YouTube video

Data Manipulation and Error Handling in Salesforce | DML | DAY 4 Part 2

  • Understanding DML Operations & Methods
  • Writing optimal queries – Query Plan
YouTube video

Further Learning

  • Apex Basics & Database
  • Search Solution Basics

Assignment

Complete below assignment to win $1000 Salesforce VoucherClick here for rule.

1.Write a method to insert an Account record. Also insert five related contacts to that same account.

2.Write a SOQL query to fetch the contact records that were inserted in previous question. (Hint: Use the CreatedDate field to add filter)

3.Write a query on Contact object and fetch the Account details. Use child to parent relationship query.

4.Write a query on Account object and fetch the related contacts. Use the parent to child relationship query.

Don’t forget to check our next session on Apex trigger. Check this post for all other session detail. All of session are recorded and posted on our YouTube channel. Make sure to subscribe our YouTube channel to get notification for video upload.

So, learn at your pace and free will and ace your journey to Salesforce!

Summary

I hope this session helped you to learn about what is SOQL in Salesforce and how to write SOQL in Salesforce. Let us know if this session helped you to learn SOQL.

Amit Chaudhary
Amit Chaudhary

Amit Chaudhary is Salesforce Application & System Architect and working on Salesforce Platform since 2010. He is Salesforce MVP since 2017 and have 17 Salesforce Certificates.

He is a active blogger and founder of Apex Hours.

Articles: 459

24 Comments

  1. Solution : All 4 steps in one method – >

    public class MyAccountActions {

    public MyAccountActions ( ) { } // Constructor

    public static void createAccount ( ) {

    Account acc = new Account ( Name = ‘ApexTestAccount ‘);
    insert acc;

    List listContact = new List();

    for ( integer i =1; i

    List result = [ SELECT Id, Name FROM Contact WHERE CreatedDate = TODAY ];
    System.debug(‘Result of query1:’+ result);
    for(Contact conlist : result){

    System.debug(conlist.Name);
    }

    // Query 2 –>

    List conList2 = [ SELECT Name,Account.Name FROM Contact];
    System.debug(‘Result of query2:’+ conList2);

    // Query 3 –>

    List accList = [ SELECT Name, ( SELECT ID, Name FROM Contacts ) from Account ];
    System.debug(‘Result of query3:’+ accList);
    }

    }

    • Please ignore above Solution, some lines got deleted by mistake. I am sorry about it.

      Solution: All 4 steps in one class

      public class MyAccountActions {

      public MyAccountActions () { } // Constructor

      public static void createAccount ( ) {

      Account acc = new Account(Name = ‘ApexTestAccount’);
      insert acc;

      List listContact = new List();

      for ( integer i =1; i<=5; i++ ) {

      Contact con = new Contact(FirstName = 'Apex', LastName = 'Contact' + i, AccountId = acc.Id);

      listContact.add(con);

      }

      insert listContact;

      system.debug('New account =' + acc);

      List result = [ SELECT Id, Name FROM Contact WHERE CreatedDate = TODAY ];
      System.debug(‘Result of query1:’+ result);

      for(Contact conlist : result){

      System.debug(conlist.Name);

      }

      List conList2 = [ SELECT Name,Account.Name FROM Contact];
      System.debug(‘Result of query2:’+ conList2);

      List accList = [ SELECT Name, ( SELECT ID, Name FROM Contacts ) from Account ];
      System.debug(‘Result of query3:’+ accList);
      }

      }

  2. Code for Assignment Day 4
    ——————————–
    public class ApexHourAssignmentDay4 {
    public void insertAccountAndContacts() {
    Account acc = new Account();
    acc.Name=’Apex Hour Day 4 Account’;
    insert acc;
    System.debug(‘Account Record has ben created:’ + acc.Name);
    List listContacts = new List();
    for(Integer i=1;i<=5;i++) {
    Contact contact = new Contact();
    contact.FirstName = 'Apex';
    contact.LastName = ' Contact' + i;
    contact.AccountId = acc.Id;
    listContacts.add(contact);
    }
    insert listContacts;
    System.debug('Contacts Records has been created:' + listContacts);
    }

    public void getRecentlyCreatedContacts(){
    List listContacts = new List();
    listContacts = [select Id,Name from Contact where createdDate = today];
    for(Contact contact : listContacts) {
    system.debug(‘Contact Name:’ + contact.name);
    }
    }

    public void getContactswithAccount(){
    List listContacts = new List();
    listContacts = [select Id,Name,Account.Name from Contact];
    for(Contact contact : listContacts) {
    system.debug(‘Account Name:’ + contact.Account.Name);
    system.debug(‘Contact Name:’ + contact.name);

    }
    }

    public void getAccountwithContacts(){
    List listAccounts = new List();
    listAccounts = [select Id,Name, (select Id,Name from Contacts) from Account];
    for(Account account : listAccounts){
    system.debug(‘Account Name:’ + account.Name);
    for(Contact contact : account.Contacts) {
    system.debug(‘Contact Name:’ + contact.Name);
    }
    }
    }
    }

    In Developer Console –> Open Execute Anonymous Window
    ————————————————————————
    ApexHourAssignmentDay4 day4 = new ApexHourAssignmentDay4();
    day4.insertAccountAndContacts();
    day4.getRecentlyCreatedContacts();
    day4.getContactswithAccount();
    day4.getAccountwithContacts();

  3. brother it’s showing this error

    Line: 2, Column: 6
    method does not exist or incorrect signature: void insertAccountAndContacts() from the type ApexHourAssignmentDay4

    I am not getting what’s going wrong here ?

Leave a Reply

Your email address will not be published. Required fields are marked *