Salesforce SOQL Cheatsheet, Scenario Based Interview Question.

If you are Revising for a Salesforce Interview and want a Quick look to SOQL. Then you are at Right Place.

In this Blog, You will get Salesforce SOQL cheatsheet, Salesforce SOQL Interview Questions and Scenario based Questions asked by top MNCs to increase your knowledge on Salesforce

NOTE: Don’t forget to check the scenario based questions. Similar Questions are commonly asked in top MNCs such as TCS / Infosys / Wipro / Accenture / IBM / Cognizant / Deloitte.

SOQL CHEATSHEET

SEQUENCE OF KEYWORDS IN SOQL

Knowing Sequence is the first step towards writing perfect SOQL Queries

SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT → OFFSET

SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT → OFFSET

BASIC STRUCTURE

Basic structure of SOQL with Sequence

// Full SOQL syntax 
List<Account> accs = [ 
SELECT Id, Name, Industry, AnnualRevenue 
FROM Account 
WHERE Industry = 'Technology' 
AND AnnualRevenue > 1000000 
ORDER BY Name ASC 
LIMIT 50 
OFFSET 10  // skip first 10 records 
];


// Full SOQL syntax 
List<Account> accs = [ 
SELECT Id, Name, Industry, AnnualRevenue 
FROM Account 
WHERE Industry = 'Technology' 
AND AnnualRevenue > 1000000 
ORDER BY Name ASC 
LIMIT 50 
OFFSET 10  // skip first 10 records 
];

COMPARISON OPERATORS IN SOQL

Below is the comparision operators widely used in SOQL.

WHERE Amount = 50000           // equal        
WHERE Amount != 50000          // not equal        
WHERE Amount > 50000           // greater than        
WHERE Amount >= 50000          // greater than or equal       
WHERE Name LIKE 'Sales%'       // starts with Sales (% = wildcard) 
WHERE Name LIKE '%force'       // ends with force 
WHERE Name LIKE '%cloud%'      // contains cloud 
WHERE Industry IN ('Tech', 'Finance')       // in a list 
WHERE Industry NOT IN ('Tech', 'Finance')   // not in list 
WHERE AnnualRevenue = NULL     // is null 
WHERE AnnualRevenue != NULL    // is not null


WHERE Amount = 50000           // equal        
WHERE Amount != 50000          // not equal        
WHERE Amount > 50000           // greater than        
WHERE Amount >= 50000          // greater than or equal       
WHERE Name LIKE 'Sales%'       // starts with Sales (% = wildcard) 
WHERE Name LIKE '%force'       // ends with force 
WHERE Name LIKE '%cloud%'      // contains cloud 
WHERE Industry IN ('Tech', 'Finance')       // in a list 
WHERE Industry NOT IN ('Tech', 'Finance')   // not in list 
WHERE AnnualRevenue = NULL     // is null 
WHERE AnnualRevenue != NULL    // is not null

DATE LITERALS

It is also one of the important concept in SOQL often asked in interviews.

// Very Important

WHERE CloseDate = TODAY 
WHERE CloseDate = YESTERDAY 
WHERE CloseDate = TOMORROW 
WHERE CloseDate = THIS_WEEK 
WHERE CloseDate = LAST_WEEK 
WHERE CloseDate = THIS_MONTH 
WHERE CloseDate = LAST_MONTH 
WHERE CloseDate = THIS_YEAR 
WHERE CloseDate = LAST_N_DAYS:30    // last 30 days 
WHERE CloseDate = NEXT_N_DAYS:7     // next 7 days 
WHERE CreatedDate = LAST_N_MONTHS:3 // last 3 months


// Very Important

WHERE CloseDate = TODAY 
WHERE CloseDate = YESTERDAY 
WHERE CloseDate = TOMORROW 
WHERE CloseDate = THIS_WEEK 
WHERE CloseDate = LAST_WEEK 
WHERE CloseDate = THIS_MONTH 
WHERE CloseDate = LAST_MONTH 
WHERE CloseDate = THIS_YEAR 
WHERE CloseDate = LAST_N_DAYS:30    // last 30 days 
WHERE CloseDate = NEXT_N_DAYS:7     // next 7 days 
WHERE CreatedDate = LAST_N_MONTHS:3 // last 3 months

NOTE : Date Literal is generally Asked in the Interviews. Don’t forget to SKIP this as this is also mostly used in Real-world scenarios and Use Cases

RELATIONSHIP QUERIES

Two types

Child to parent using Dot Notation.
parent to child using subquery.

CHILD TO PARENT (dot notation):

We can get the parent, grandparent (records upto 5 levels up) in SOQL using dot notation.

// Access parent fields using dot notation 
List<Contact> cons = [ 
    SELECT Id, Name, 
           Account.Name,           // parent Account's Name 
           Account.Industry,       // parent Account's Industry 
           Account.Owner.Name      // grandparent — Owner of the Account 
    FROM Contact 
    WHERE Account.Industry = 'Technology' 
];

// Access parent fields using dot notation 
List<Contact> cons = [ 
    SELECT Id, Name, 
           Account.Name,           // parent Account's Name 
           Account.Industry,       // parent Account's Industry 
           Account.Owner.Name      // grandparent — Owner of the Account 
    FROM Contact 
    WHERE Account.Industry = 'Technology' 
];

PARENT TO CHILD (subquery):

We can write a subquery for each object to get the child object data.

NOTE : Subquery uses the Plural child Relationship Name

// Subquery uses the CHILD RELATIONSHIP NAME (plural) 
List<Account> accs = [ 
    SELECT Id, Name, 
          (SELECT Id, LastName, Email FROM Contacts),      // standard child 
          (SELECT Id, Subject FROM Cases),                  // another child 
          (SELECT Id, StageName FROM Opportunities) 
    FROM Account 
    WHERE Name = 'Salesforce' 
];

// Subquery uses the CHILD RELATIONSHIP NAME (plural) 
List<Account> accs = [ 
    SELECT Id, Name, 
          (SELECT Id, LastName, Email FROM Contacts),      // standard child 
          (SELECT Id, Subject FROM Cases),                  // another child 
          (SELECT Id, StageName FROM Opportunities) 
    FROM Account 
    WHERE Name = 'Salesforce' 
];

Accessing the SubQuery result

// Accessing subquery results 
for (Account acc : accs) { 
    List<Contact> relatedContacts = acc.Contacts; // access child list 
    for (Contact c : relatedContacts) { 
        System.debug(c.LastName); 
    } 
}
//  Child Relationship Name: Standard objects have predefined names. Custom objects use __r suffix.

// Accessing subquery results 
for (Account acc : accs) { 
    List<Contact> relatedContacts = acc.Contacts; // access child list 
    for (Contact c : relatedContacts) { 
        System.debug(c.LastName); 
    } 
}
//  Child Relationship Name: Standard objects have predefined names. Custom objects use __r suffix.

AGGREGATE FUNCTIONS

Aggregate Functions are used to summarize data using Count, Sum, Min, Max, Avg functions and are mostly combined with the GROUP BY Clause based on scenarios.

It can be used for counting records, such as counting the total number of open opportunities. It can be used for summarizing data, such as calculating the total revenue generated by each account, and it can also be used for finding Extremes using MIN and MAX functions, such as finding the minimum and maximum opportunity amount for each account.

// COUNT, SUM, AVG, MIN, MAX
List<AggregateResult> results = [ 
    SELECT COUNT(Id) totalCount, 
           SUM(AnnualRevenue) totalRevenue, 
           AVG(AnnualRevenue) avgRevenue, 
           MAX(AnnualRevenue) maxRevenue, 
           MIN(AnnualRevenue) minRevenue, 
           Industry 
    FROM Account 
    WHERE Industry != NULL 
    GROUP BY Industry 
    HAVING COUNT(Id) > 5  // filter on aggregated results (like SQL HAVING) 
];

// COUNT, SUM, AVG, MIN, MAX
List<AggregateResult> results = [ 
    SELECT COUNT(Id) totalCount, 
           SUM(AnnualRevenue) totalRevenue, 
           AVG(AnnualRevenue) avgRevenue, 
           MAX(AnnualRevenue) maxRevenue, 
           MIN(AnnualRevenue) minRevenue, 
           Industry 
    FROM Account 
    WHERE Industry != NULL 
    GROUP BY Industry 
    HAVING COUNT(Id) > 5  // filter on aggregated results (like SQL HAVING) 
];

READING AGGREGATERESULT

Sometime Interviewer may ask you a follow up question to write the code for Reading Query using Aggregate function.

for (AggregateResult ar : results) { 
    Integer total   = (Integer) ar.get('totalCount'); 
    Decimal revenue = (Decimal) ar.get('totalRevenue'); 
    String industry = (String)  ar.get('Industry'); 
} 
// You cannot use WHERE on aggregate results - use HAVING instead.

for (AggregateResult ar : results) { 
    Integer total   = (Integer) ar.get('totalCount'); 
    Decimal revenue = (Decimal) ar.get('totalRevenue'); 
    String industry = (String)  ar.get('Industry'); 
} 
// You cannot use WHERE on aggregate results - use HAVING instead.

FOR UPDATE

It is used to lock record for the current transaction so that it can prevent updates and no other user or processes can update this record until your transaction is completed.

// Locks records during processing to prevent concurrent edits 
List<Account> accs = [SELECT Id, Name FROM Account 
                      WHERE Id IN :accountIds 
                      FOR UPDATE];

// Locks records during processing to prevent concurrent edits 
List<Account> accs = [SELECT Id, Name FROM Account 
                      WHERE Id IN :accountIds 
                      FOR UPDATE];

FORMULA IN SOQL

Formula in SOQL is introduced in Salesforce Summer ’26 Release. Now we can directly write Formula in SOQL without creating unnecessary formula field in an object and then using that field in SOQL like before.

// Without FORMULA(): business rule lives in Apex after a wider query

 List rows = [SELECT Id, Name, Revenue__c, Cost__c FROM Order__c];
  List highMargin = new List();
  for (Order__c o : rows) {
      if (o.Revenue__c != null && o.Cost__c != null
          && (o.Revenue__c - o.Cost__c) > 150) {
          highMargin.add(o);
      }
  }

// Without FORMULA(): business rule lives in Apex after a wider query

 List rows = [SELECT Id, Name, Revenue__c, Cost__c FROM Order__c];
  List highMargin = new List();
  for (Order__c o : rows) {
      if (o.Revenue__c != null && o.Cost__c != null
          && (o.Revenue__c - o.Cost__c) > 150) {
          highMargin.add(o);
      }
  }

// With FORMULA(): same rule expressed in the WHERE clause 

SELECT Id, Name FROM Order__c WHERE FORMULA('Revenue__c - Cost__c') > 150

// With FORMULA(): same rule expressed in the WHERE clause 

SELECT Id, Name FROM Order__c WHERE FORMULA('Revenue__c - Cost__c') > 150

With this enhancement, we can do the

Dynamic calculations in queries
Direct comparisons of multiple field values
Reduction of unnecessary formula fields
Cleaner and more efficient Apex logic

BIND VARIABLES

Bind variable is a placeholder inside a soql query which is denoted by a colon ( : ). It help us to prevent SOQL Injection attack. It help us to write dynamic, secure and reusable SOQL queries.

// CORRECT — bind variable, safe from SOQL injection 
String industryName = 'Technology'; 
List<Account> accs = [SELECT Id FROM Account WHERE Industry = :industryName]; 
 
// WRONG — string concatenation, vulnerable to SOQL injection 
String query = 'SELECT Id FROM Account WHERE Industry = \'' + industryName + '\'';

// CORRECT — bind variable, safe from SOQL injection 
String industryName = 'Technology'; 
List<Account> accs = [SELECT Id FROM Account WHERE Industry = :industryName]; 
 
// WRONG — string concatenation, vulnerable to SOQL injection 
String query = 'SELECT Id FROM Account WHERE Industry = \'' + industryName + '\'';

DYNAMIC SOQL

It is used when the query structure is not known. it helps us to create more flexible applications.

// Used when query structure is not known at compile time 
String objectName = 'Account'; 
String fieldName = 'Name'; 
String filterValue = 'Salesforce'; 
 
String query = 'SELECT Id, ' + fieldName + 
               ' FROM ' + objectName + 
               ' WHERE Name = :filterValue'; // bind variable still works 
 
List<SObject> results = Database.query(query); 
Exam trap: In dynamic SOQL, bind variables still work but must be in scope. String concatenation in 
dynamic SOQL is still a SOQL injection risk.

// Used when query structure is not known at compile time 
String objectName = 'Account'; 
String fieldName = 'Name'; 
String filterValue = 'Salesforce'; 
 
String query = 'SELECT Id, ' + fieldName + 
               ' FROM ' + objectName + 
               ' WHERE Name = :filterValue'; // bind variable still works 
 
List<SObject> results = Database.query(query); 
Exam trap: In dynamic SOQL, bind variables still work but must be in scope. String concatenation in 
dynamic SOQL is still a SOQL injection risk.

ENFORCING SECURITY USING SOQL

By Default, Apex runs in System mode means apex code will ignore/bypass the user’s permission, sharing rules, field level security (FLS) etc.

For example, If a user doesn’t have access to AnnualRevenue Field in Account object, but If they trigger an Apex class (for example, by clicking a custom button), the Apex code can query and read the AnnualRevenue field because it runs in System Mode.

NOTE – While Apex does run in System Mode by default, the Developer Console is an exception to the above example. When you run a raw SOQL query directly in the Developer Console’s “Query Editor” tab, it actually executes in User Mode (respecting FLS and CRUD). So, the user wouldn’t see the AnnualRevenue field there if they don’t have access.

When querying data in Salesforce, enforcing security ensures that the running user’s field-level security (FLS) and object-level security (CRUD) permissions are respected. If a user doesn’t have access to a field or object, the query will block them or skip them.

Below is the ways to enforce security in SOQL using the below Keywords and method

WITH USER_MODE
WITH SYSTEM_MODE
WITH SECURITY_ENFORCED
Security.stripInaccessible()

WITH USER_MODE

WITH USER_MODE – It enforces both Object-Level (CRUD) and Field-Level Security (FLS), as well as sharing rules.

List<Account> acc = [SELECT Id,Price__c FROM Item__c WITH USER_MODE];

List<Account> acc = [SELECT Id,Price__c FROM Item__c WITH USER_MODE];

If the User Doesn’t have access to Price__c or Item__c, System.QueryException is thrown at runtime with User_Mode.

WITH SYSTEM_MODE

WITH SYSTEM_MODE Here you are purposely bypassing user permissions.

List<Account> acc = [SELECT Id,Price__c FROM Item__c WITH SYSTEM_MODE];

List<Account> acc = [SELECT Id,Price__c FROM Item__c WITH SYSTEM_MODE];

If the User Doesn’t have access to Price__c or Item__c, then also User will be able to see the specific field value.

WITH SECURITY_ENFORCED

Before USER_MODE, WITH SECURITY_ENFORCED was the standard way to enforce FLS and CRUD in SOQL.

List<Account> acc = [SELECT Id,Price__c FROM Item__c WITH SECURITY_ENFORCED];

List<Account> acc = [SELECT Id,Price__c FROM Item__c WITH SECURITY_ENFORCED];

Throws a System.QueryException if permissions are misising.

Feature	WITH USER_MODE	WITH SECURITY_ENFORCED	Default SOQL (No keyword)
Object-Level (CRUD)	Enforced	Enforced	Ignored (Runs as System)
Field-Level (FLS)	Enforced	Enforced	Ignored (Runs as System)
Where/Order By Clause FLS	Enforced	Ignored	Ignored
Sharing Rules	Enforced	Inherits Class Context	Inherits Class Context

Security.StripInaccessible()

If the user doesn’t have permissions, This method will skip the fields which doesn’t have permissions and retruns the remaining accessible fields.

You can prevent user to edit the fields in which they doesn’t have access, preventing malicious or accidental edits.

SObjectAccessDecision securityDecision = 
         Security.stripInaccessible(AccessType.READABLE,
                 [SELECT Name, BudgetedCost, ActualCost FROM Campaign]                 );

SObjectAccessDecision securityDecision = 
         Security.stripInaccessible(AccessType.READABLE,
                 [SELECT Name, BudgetedCost, ActualCost FROM Campaign]                 );

If the User doesn’t have access to ActualCost field, The System will not Block the execution by throwing a System.QueryException Instead it will remove the ActualCost field from the result.

stripInaccessible is Perfect for usecases like general UIs or data exports where a missing field shouldn’t break the whole page.

GOVERNOR LIMITS – Must Memorise

Below are the governor limits as per Salesforce official documentation (Summer ’26) specific to SOQL (Salesforce object Query Language):

LIMIT	VALUE
SOQL queries per transaction	100
Records returned per query	50,000
Records returned by Database.getQueryLocator	50,000,000 (Batch Apex)
Subquery rows per subquery	1,000
Characters in SOQL query string	20,000

SOQL INTERVIEW QUESTION

Question 1

Write a SOQL query to calculate the sum of marks scored by all students.

Solution

In the Question, It is given there is a Student Object and let it API name be Student__c and there is a Marks Field and let it API name be Marks__c. We will use the SUM function in Query.

SELECT SUM(Marks__c) FROM Student__c

SELECT SUM(Marks__c) FROM Student__c

Question 2

How would you optimize a slow SOQL query?

Solution

Limit the Number of field to be returned
Use the Query Plan Tool (try to get the cost below 1.0)
Use Indexes (Use where Clause with Indexed fields like Id,Name,Email,Owner )
Limit the Number of Records to be returned ( Use LIMIT Keyword )
Use Filtering (Use where Clause)
Use SOQL inside for loop
Use aggregate function like SUM,MAX,MIN if required
Use Bind variables (‘ :’ operator while writing SOQL Queries )

SOQL SCENARIO BASED QUESTION

Question 1 (Basic filter + ORDER BY)

Write a SOQL query to get all Opportunities where Stage is ‘Closed Won’ and Amount is greater than 50,000. Order by Amount descending. Limit to 20 records.

// SOLUTION
SELECT Id, Name, StageName, Amount, CloseDate 
FROM Opportunity 
WHERE StageName = 'Closed Won' AND Amount > 50000 
ORDER BY Amount DESC 
LIMIT 20

// SOLUTION
SELECT Id, Name, StageName, Amount, CloseDate 
FROM Opportunity 
WHERE StageName = 'Closed Won' AND Amount > 50000 
ORDER BY Amount DESC 
LIMIT 20

Question 2 (Child to Parent)

Write a SOQL query to get all Contacts along with their Account Name and Account Industry. Filter only Contacts whose Account Industry is ‘Finance’.

// SOLUTION
SELECT Id, FirstName, LastName, Account.Name, Account.Industry 
FROM Contact 
WHERE Account.Industry = 'Finance'

// SOLUTION
SELECT Id, FirstName, LastName, Account.Name, Account.Industry 
FROM Contact 
WHERE Account.Industry = 'Finance'

Question 3 (Parent to Child subquery)

Write a SOQL query to get all Accounts along with their related Contacts (Id, LastName, Email) and related Opportunities (Id, Name, StageName). Only fetch Accounts in the ‘Healthcare’ industry.

// SOLUTION
SELECT Name, Industry, 
    (SELECT Id, LastName, Email FROM Contacts), 
    (SELECT Id, Name, StageName FROM Opportunities)
FROM Account
WHERE Industry = 'Healthcare'

// SOLUTION
SELECT Name, Industry, 
    (SELECT Id, LastName, Email FROM Contacts), 
    (SELECT Id, Name, StageName FROM Opportunities)
FROM Account
WHERE Industry = 'Healthcare'

Question 4 (Aggregate)

Write a SOQL query to find the total number of Opportunities and total Amount grouped by StageName. Only show stages that have more than 3 Opportunities.

// SOLUTION
SELECT StageName, COUNT(Id), SUM(Amount)
FROM Opportunity
GROUP BY StageName
HAVING COUNT(Id) > 3

// SOLUTION
SELECT StageName, COUNT(Id), SUM(Amount)
FROM Opportunity
GROUP BY StageName
HAVING COUNT(Id) > 3

Question 5 (Bind variable + Security)

Write a method that accepts an Industry name as a String parameter and returns all Accounts in that industry. Use a bind variable. Enforce FLS using WITH SECURITY_ENFORCED.

// SOLUTION
public static List<Account> getAccountsByIndustry(String industryName) {
    // Check for null or blank input to avoid unnecessary querying
    if (String.isBlank(industryName)) {
        return new List<Account>();
    }

    try {
        // The :industryName syntax binds the parameter directly into the query safely avoiding SQL injection Risk
        return [SELECT Id, Name, Industry, AnnualRevenue 
                FROM Account 
                WHERE Industry = :industryName 
                WITH SECURITY_ENFORCED];
                
    } catch (System.QueryException qe) {
        System.debug('Security exception: User does not have access to the Account fields. ' + qe.getMessage());
        // Return an empty list or handle the exception based on your application's requirements
        return new List<Account>();
    }
}

// SOLUTION
public static List<Account> getAccountsByIndustry(String industryName) {
    // Check for null or blank input to avoid unnecessary querying
    if (String.isBlank(industryName)) {
        return new List<Account>();
    }

    try {
        // The :industryName syntax binds the parameter directly into the query safely avoiding SQL injection Risk
        return [SELECT Id, Name, Industry, AnnualRevenue 
                FROM Account 
                WHERE Industry = :industryName 
                WITH SECURITY_ENFORCED];
                
    } catch (System.QueryException qe) {
        System.debug('Security exception: User does not have access to the Account fields. ' + qe.getMessage());
        // Return an empty list or handle the exception based on your application's requirements
        return new List<Account>();
    }
}

Question 6 (Dynamic SOQL)

Write a method that accepts an object name, a field name, and a filter value as Strings. Build and execute a dynamic SOQL query to return matching records.

// SOLUTION
public static List<SObject> getRecords(String objectName, String fieldName, String filterValue) {
    // 1. Sanitize the inputs to prevent SOQL Injection
    String cleanObject = String.escapeSingleQuotes(objectName);
    String cleanField = String.escapeSingleQuotes(fieldName);
    
    // 2. Construct the dynamic query string using Bind variable
    String queryString = 'SELECT Id, Name, ' + cleanField + 
                         ' FROM ' + cleanObject + 
                         ' WHERE ' + cleanField + ' = :filterValue' + 
                         ' WITH USER_MODE';
    
    try {
        // 3. Execute the dynamic query
        return Database.query(queryString);
        
    } catch (System.QueryException qe) {
        System.debug('Error executing dynamic SOQL: ' + qe.getMessage());
        return new List<SObject>();
    }
}

// SOLUTION
public static List<SObject> getRecords(String objectName, String fieldName, String filterValue) {
    // 1. Sanitize the inputs to prevent SOQL Injection
    String cleanObject = String.escapeSingleQuotes(objectName);
    String cleanField = String.escapeSingleQuotes(fieldName);
    
    // 2. Construct the dynamic query string using Bind variable
    String queryString = 'SELECT Id, Name, ' + cleanField + 
                         ' FROM ' + cleanObject + 
                         ' WHERE ' + cleanField + ' = :filterValue' + 
                         ' WITH USER_MODE';
    
    try {
        // 3. Execute the dynamic query
        return Database.query(queryString);
        
    } catch (System.QueryException qe) {
        System.debug('Error executing dynamic SOQL: ' + qe.getMessage());
        return new List<SObject>();
    }
}

Question 7 (Date Literal)

Write a SOQL query to get all Cases created in the last 7 days where Status is not ‘Closed’. Order by CreatedDate ascending.

// SOLUTION
SELECT Id, CaseNumber, Status, Subject, CreatedDate 
FROM Case 
WHERE Status != 'Closed' AND CreatedDate = LAST_N_DAYS:7 
ORDER BY CreatedDate ASC

// SOLUTION
SELECT Id, CaseNumber, Status, Subject, CreatedDate 
FROM Case 
WHERE Status != 'Closed' AND CreatedDate = LAST_N_DAYS:7 
ORDER BY CreatedDate ASC

ADVANCED SOQL PRACTISE QUESTION

Question 1 (Aggregate + HAVING)

Write a full Apex method that returns a Map where the key is StageName and the value is the count of Opportunities in that stage. Only include stages that have more than 5 Opportunities.

// SOLUTION
public static Map<String, Integer> getOpportunityCountByStage() {
    // Initialize the map to store our final results
    Map<String, Integer> stageCountMap = new Map<String, Integer>();

    try {
        // 1. Run the aggregate query with security enforced
        AggregateResult[] groupedResults = [
            SELECT StageName, COUNT(Id) oppCount
            FROM Opportunity
            GROUP BY StageName
            HAVING COUNT(Id) > 5
            WITH USER_MODE
        ];

        // 2. Loop through the results and populate the map
        for (AggregateResult ar : groupedResults) {
            // Extract the stage name (cast as String)
            String stageName = (String) ar.get('StageName');
            
            // Extract the count (cast as Integer)
            Integer count = (Integer) ar.get('oppCount');
            
            // Add the key-value pair to our map
            stageCountMap.put(stageName, count);
        }

    } catch (System.QueryException qe) {
        System.debug('Security or query exception occurred: ' + qe.getMessage());
        // Handle gracefully based on your requirements (e.g., rethrow or return empty map)
    }

    return stageCountMap;
}

// SOLUTION
public static Map<String, Integer> getOpportunityCountByStage() {
    // Initialize the map to store our final results
    Map<String, Integer> stageCountMap = new Map<String, Integer>();

    try {
        // 1. Run the aggregate query with security enforced
        AggregateResult[] groupedResults = [
            SELECT StageName, COUNT(Id) oppCount
            FROM Opportunity
            GROUP BY StageName
            HAVING COUNT(Id) > 5
            WITH USER_MODE
        ];

        // 2. Loop through the results and populate the map
        for (AggregateResult ar : groupedResults) {
            // Extract the stage name (cast as String)
            String stageName = (String) ar.get('StageName');
            
            // Extract the count (cast as Integer)
            Integer count = (Integer) ar.get('oppCount');
            
            // Add the key-value pair to our map
            stageCountMap.put(stageName, count);
        }

    } catch (System.QueryException qe) {
        System.debug('Security or query exception occurred: ' + qe.getMessage());
        // Handle gracefully based on your requirements (e.g., rethrow or return empty map)
    }

    return stageCountMap;
}

Question 2 (Bulkified Trigger pattern)

Write a trigger on Contact that fires after insert. For every new Contact, query their related Account and store the Account’s Industry into a custom field Contact_Industry__c on the Contact. Write it in a bulkified way — no SOQL inside loops.

// SOLUTION
trigger ContactTrigger on Contact (after insert) {
    if (Trigger.isAfter && Trigger.isInsert) {
        ContactTriggerHandler.afterInsert(Trigger.new);
    }
}

// SOLUTION
trigger ContactTrigger on Contact (after insert) {
    if (Trigger.isAfter && Trigger.isInsert) {
        ContactTriggerHandler.afterInsert(Trigger.new);
    }
}

public with sharing class ContactTriggerHandler {
    
    public static void afterInsert(List<Contact> newContacts) {
        Set<Id> accountIds = new Set<Id>();
        List<Contact> contactsToUpdate = new List<Contact>();

        // Step 1: Collect all unique Account IDs from the new Contacts
        for (Contact con : newContacts) {
            if (con.AccountId != null) {
                accountIds.add(con.AccountId);
            }
        }

        // If no contacts are linked to an account, stop early to save processing/SOQL
        if (accountIds.isEmpty()) {
            return;
        }

        // Step 2: Query all related Accounts in a single, bulkified SOQL statement
        Map<Id, Account> accountMap = new Map<Id, Account>([
            SELECT Id, Industry 
            FROM Account 
            WHERE Id IN :accountIds
            WITH USER_MODE
        ]);

        // Step 3: Loop through the contacts again to map the data
        for (Contact con : newContacts) {
            // Check if the Contact is linked to an Account and if that Account has an Industry
            if (con.AccountId != null && accountMap.containsKey(con.AccountId)) {
                String industry = accountMap.get(con.AccountId).Industry;
                
                // Since this is an AFTER insert trigger, records are read-only in Trigger.new.
                // We must instantiate a new instance with the Contact ID to update it.
                Contact updatedContact = new Contact(
                    Id = con.Id,
                    Contact_Industry__c = industry
                );
                contactsToUpdate.add(updatedContact);
            }
        }

        // Step 4: Perform the DML update on the modified contacts
        if (!contactsToUpdate.isEmpty()) {
            update as user contactsToUpdate;
        }
    }
}

public with sharing class ContactTriggerHandler {
    
    public static void afterInsert(List<Contact> newContacts) {
        Set<Id> accountIds = new Set<Id>();
        List<Contact> contactsToUpdate = new List<Contact>();

        // Step 1: Collect all unique Account IDs from the new Contacts
        for (Contact con : newContacts) {
            if (con.AccountId != null) {
                accountIds.add(con.AccountId);
            }
        }

        // If no contacts are linked to an account, stop early to save processing/SOQL
        if (accountIds.isEmpty()) {
            return;
        }

        // Step 2: Query all related Accounts in a single, bulkified SOQL statement
        Map<Id, Account> accountMap = new Map<Id, Account>([
            SELECT Id, Industry 
            FROM Account 
            WHERE Id IN :accountIds
            WITH USER_MODE
        ]);

        // Step 3: Loop through the contacts again to map the data
        for (Contact con : newContacts) {
            // Check if the Contact is linked to an Account and if that Account has an Industry
            if (con.AccountId != null && accountMap.containsKey(con.AccountId)) {
                String industry = accountMap.get(con.AccountId).Industry;
                
                // Since this is an AFTER insert trigger, records are read-only in Trigger.new.
                // We must instantiate a new instance with the Contact ID to update it.
                Contact updatedContact = new Contact(
                    Id = con.Id,
                    Contact_Industry__c = industry
                );
                contactsToUpdate.add(updatedContact);
            }
        }

        // Step 4: Perform the DML update on the modified contacts
        if (!contactsToUpdate.isEmpty()) {
            update as user contactsToUpdate;
        }
    }
}

Question 3 (Dynamic SOQL with injection risk)

Below code has a SOQL injection vulnerability. Identify it and rewrite the method securely:

// Identify the vulnerability and rewrite the correct code
public static List<Account> searchAccounts(String industryInput) { 
String query = 'SELECT Id, Name FROM Account WHERE Industry = \'' 
  + industryInput + '\''; 
  return Database.query(query); 
}

// Identify the vulnerability and rewrite the correct code
public static List<Account> searchAccounts(String industryInput) { 
String query = 'SELECT Id, Name FROM Account WHERE Industry = \'' 
  + industryInput + '\''; 
  return Database.query(query); 
}

Solution

The vulnerability in your code is string concatenation using raw user input (industryInput) to build a dynamic SOQL query. Because the string is concatenated directly, an attacker can input malicious text to break out of the intended query logic. For example, if a user passes the following string as industryInput

Apparel’ OR AnnualRevenue > 1000000 OR Name LIKE ‘%

The Resulting Query Will become

SELECT Id, Name FROM Account WHERE Industry = 'Apparel' 
       OR AnnualRevenue > 1000000 OR Name LIKE '%'

SELECT Id, Name FROM Account WHERE Industry = 'Apparel' 
       OR AnnualRevenue > 1000000 OR Name LIKE '%'

This completely alters your WHERE clause, bypassing your intended filter and exposing highly sensitive data that the user shouldn’t see.

FIX : USE BIND VARIABLE ( : )

public static List<Account> searchAccounts(String industryInput) { 
    // Check for invalid input early
    if (String.isBlank(industryInput)) {
        return new List<Account>();
    }

    // Secure: Using a bind variable (:industryInput) completely prevents SOQL injection.
    // Also appended USER_MODE to enforce Object/Field level security.
    String query = 'SELECT Id, Name, Industry FROM Account WHERE Industry = :industryInput WITH USER_MODE'; 
    
    try {
        return Database.query(query);
    } catch (System.QueryException qe) {
        System.debug('Query execution failed: ' + qe.getMessage());
        return new List<Account>();
    }
}

public static List<Account> searchAccounts(String industryInput) { 
    // Check for invalid input early
    if (String.isBlank(industryInput)) {
        return new List<Account>();
    }

    // Secure: Using a bind variable (:industryInput) completely prevents SOQL injection.
    // Also appended USER_MODE to enforce Object/Field level security.
    String query = 'SELECT Id, Name, Industry FROM Account WHERE Industry = :industryInput WITH USER_MODE'; 
    
    try {
        return Database.query(query);
    } catch (System.QueryException qe) {
        System.debug('Query execution failed: ' + qe.getMessage());
        return new List<Account>();
    }
}

Question 4 (Subquery + loop processing)

Write a full Apex method that queries all Accounts in the ‘Technology’ industry along with their related Contacts. For each Account, if it has more than 3 Contacts, add the Account Id to a List called highVolumeAccounts and return it.

// SOLUTION
public static List<Id> getHighVolumeTechAccounts() {
    // Initialize the list to store the matching Account IDs
    List<Id> highVolumeAccounts = new List<Id>();

    try {
        // Query Accounts and their related Contacts in a single relationship query
        List<Account> techAccounts = [
            SELECT Id, (SELECT Id FROM Contacts) 
            FROM Account 
            WHERE Industry = 'Technology'
            WITH USER_MODE
        ];

        // Loop through the queried accounts
        for (Account acc : techAccounts) {
            // acc.Contacts represents the list of child Contact records retrieved
            if (acc.Contacts.size() > 3) {
                highVolumeAccounts.add(acc.Id);
            }
        }
        
    } catch (System.QueryException qe) {
        System.debug('Security or query error: ' + qe.getMessage());
        // Handle gracefully based on your org's error logging strategy
    }

    return highVolumeAccounts;
}

// SOLUTION
public static List<Id> getHighVolumeTechAccounts() {
    // Initialize the list to store the matching Account IDs
    List<Id> highVolumeAccounts = new List<Id>();

    try {
        // Query Accounts and their related Contacts in a single relationship query
        List<Account> techAccounts = [
            SELECT Id, (SELECT Id FROM Contacts) 
            FROM Account 
            WHERE Industry = 'Technology'
            WITH USER_MODE
        ];

        // Loop through the queried accounts
        for (Account acc : techAccounts) {
            // acc.Contacts represents the list of child Contact records retrieved
            if (acc.Contacts.size() > 3) {
                highVolumeAccounts.add(acc.Id);
            }
        }
        
    } catch (System.QueryException qe) {
        System.debug('Security or query error: ' + qe.getMessage());
        // Handle gracefully based on your org's error logging strategy
    }

    return highVolumeAccounts;
}

Question 5 (Date literal + aggregate)

Write a SOQL query that returns the total Amount of all Opportunities created in the last 30 days, grouped by OwnerId. Only include Owners whose total Amount exceeds 100,000.

// SOLUTION
SELECT OwnerId, SUM(Amount) totalAmount
FROM Opportunity
WHERE CreatedDate = LAST_N_DAYS:30
GROUP BY OwnerId
HAVING SUM(Amount) > 100000

// SOLUTION
SELECT OwnerId, SUM(Amount) totalAmount
FROM Opportunity
WHERE CreatedDate = LAST_N_DAYS:30
GROUP BY OwnerId
HAVING SUM(Amount) > 100000

Question 6 (Mixed SOQL + DML)

Write a method that finds all Opportunities with Stage = ‘Prospecting’ that were created more than 90 days ago. Update their Stage to ‘Closed Lost’ and add a Description = ‘Auto-closed due to inactivity’. Write it in a bulkified way.

// SOLUTION
public static void autoCloseInactiveOpportunities() {
    try {
        // 1. Query all matching records in a single database sweep.
        // We use the date literal 'LAST_90_DAYS' with a negative operator (<) 
        // to find everything older than 90 days.
        List<Opportunity> inactiveOpps = [
            SELECT Id, StageName, Description 
            FROM Opportunity 
            WHERE StageName = 'Prospecting' 
              AND CreatedDate < LAST_90_DAYS
            WITH USER_MODE
            LIMIT 10000
        ];

        // If no records match the criteria, stop early to save a DML statement
        if (inactiveOpps.isEmpty()) {
            return;
        }

        // 2. Loop through the list to update the field values in memory
        for (Opportunity opp : inactiveOpps) {
            opp.StageName = 'Closed Lost';
            opp.Description = 'Auto-closed due to inactivity.';
        }

        // 3. Commit all updates to the database in a single, bulkified DML statement
        update as user inactiveOpps;

    } catch (System.QueryException qe) {
        System.debug('Security or Query error encountered: ' + qe.getMessage());
    } catch (System.DmlException de) {
        System.debug('DML update failed: ' + de.getMessage());
    }
}

// SOLUTION
public static void autoCloseInactiveOpportunities() {
    try {
        // 1. Query all matching records in a single database sweep.
        // We use the date literal 'LAST_90_DAYS' with a negative operator (<) 
        // to find everything older than 90 days.
        List<Opportunity> inactiveOpps = [
            SELECT Id, StageName, Description 
            FROM Opportunity 
            WHERE StageName = 'Prospecting' 
              AND CreatedDate < LAST_90_DAYS
            WITH USER_MODE
            LIMIT 10000
        ];

        // If no records match the criteria, stop early to save a DML statement
        if (inactiveOpps.isEmpty()) {
            return;
        }

        // 2. Loop through the list to update the field values in memory
        for (Opportunity opp : inactiveOpps) {
            opp.StageName = 'Closed Lost';
            opp.Description = 'Auto-closed due to inactivity.';
        }

        // 3. Commit all updates to the database in a single, bulkified DML statement
        update as user inactiveOpps;

    } catch (System.QueryException qe) {
        System.debug('Security or Query error encountered: ' + qe.getMessage());
    } catch (System.DmlException de) {
        System.debug('DML update failed: ' + de.getMessage());
    }
}

I would recommend that you practise and revise the above theory and questions by writing manually on paper. It will help you remember the facts for a longer time than usual.

Was this Article helpful or not?

What can be improved?

What is the toughest Salesforce question asked in the interview from you and what is the one toughest challenge you have faced during your work. Kindly let us know your thoughts in the comments and lets discuss.

Drop your answer in comments

Coming Next

Salesforce Security Cheatsheet and Interview Questions

Subscribe to us on these platforms.

Subscribe to us to not miss important updates and posts and to get the latest information.

Salesforce SOQL Cheatsheet, Scenario Based Interview Question.

SOQL CHEATSHEET

SEQUENCE OF KEYWORDS IN SOQL

BASIC STRUCTURE

COMPARISON OPERATORS IN SOQL

DATE LITERALS

RELATIONSHIP QUERIES

CHILD TO PARENT (dot notation):

PARENT TO CHILD (subquery):

AGGREGATE FUNCTIONS

READING AGGREGATERESULT

FOR UPDATE

FORMULA IN SOQL

BIND VARIABLES

DYNAMIC SOQL

ENFORCING SECURITY USING SOQL

WITH USER_MODE

WITH SYSTEM_MODE

WITH SECURITY_ENFORCED

Security.StripInaccessible()

GOVERNOR LIMITS – Must Memorise

SOQL INTERVIEW QUESTION

SOQL SCENARIO BASED QUESTION

ADVANCED SOQL PRACTISE QUESTION

One Comment

Leave a Reply Cancel reply