Medium & Hard SOQL & SOSL Practice Problems

Problem Statement

Write a SOQL query to find the Nth highest Salary__c from a custom object Employee__c. Return the full employee record at that salary rank.

Handle ties by counting distinct salary values.

Example

Salaries: [90, 85, 85, 70, 60]
N = 2 → employees with salary 85
N = 3 → employees with salary 70

Problem Statement

Build a reusable utility method that constructs and executes a dynamic SOQL query from parameters, following all Salesforce security best practices.

Method Signature

public static List<SObject> buildAndRun(
    String objectType,
    List<String> fields,
    String whereClause,
    Integer limitCount
)

Security Requirements

• Use String.escapeSingleQuotes() on the where clause to prevent SOQL injection
• Always include a LIMIT clause (default to 200 if null)
• Use String.join() to concatenate field names
• Call Database.query() to execute

Problem Statement

Write a static method that returns all Account records along with their related Contact records in a single SOQL query using a child relationship subquery in the SELECT clause.

Method Signature

public static List<Account> getAccountsWithContacts()

Key Concept — Child Subquery

A child relationship subquery sits inside the outer SELECT clause in parentheses. You use the relationship name — for Contact on Account, the relationship name is Contacts (plural).

SELECT Id, Name, (SELECT Id, FirstName, LastName FROM Contacts)
FROM Account

Constraints

• Use a single SOQL query — no loops, no separate Contact query
• Include at least Id and LastName in the subquery SELECT
• Order results by Account.Name ASC
• No ORDER BY or LIMIT inside the child subquery (not allowed)

Problem Statement

Write a method that returns all Contact records whose parent Account has at least one Closed Won Opportunity — using a SOQL semi-join (IN with a subquery).

Method Signature

public static List<Contact> getContactsAtWinningAccounts()

Key Concept — Semi-Join

A semi-join filters the outer query by checking whether a relationship field appears in the result of a subquery:

WHERE AccountId IN (SELECT AccountId FROM Opportunity WHERE StageName = 'Closed Won')

The subquery must select a single field that matches the type of the outer WHERE field.

Constraints

• Use a single SOQL statement — no Apex loops to compare records
• The subquery must query Opportunity and filter on StageName
• Return at least Id, FirstName, LastName, AccountId

Problem Statement

Write a method that returns all Account records that have no open Cases — using a SOQL anti-join (NOT IN with a subquery).

Method Signature

public static List<Account> getAccountsWithNoOpenCases()

Key Concept — Anti-Join

An anti-join excludes records whose Id appears in a subquery result:

WHERE Id NOT IN (SELECT AccountId FROM Case WHERE Status != 'Closed')

Constraints

• Use NOT IN with a subquery — not a loop-based approach
• Subquery must filter for open (non-Closed) Cases
• Return at least Id and Name
• Order results by Name ASC

Problem Statement

Write a method that uses a SOQL aggregate query to find all Account IDs that have more than 2 related Contacts.

Method Signature

public static List<AggregateResult> getHighContactAccounts()

Key Concept — HAVING

HAVING filters after aggregation — it is the equivalent of WHERE for aggregate results. You cannot use WHERE to filter on a COUNT() result.

SELECT AccountId, COUNT(Id) cnt
FROM Contact
GROUP BY AccountId
HAVING COUNT(Id) > 2

Constraints

• Use GROUP BY AccountId — do not loop over contacts in Apex
• Use HAVING COUNT(Id) > 2 to filter at the database level
• Order by COUNT(Id) DESC to show most-contacted accounts first
• Access results with: (Integer) result.get('cnt')

Problem Statement

Write a SOQL aggregate query to find the total number of Opportunities for each Account, returning the highest-count accounts first.

Requirements

• SELECT AccountId, COUNT(Id) oppCount
• FROM Opportunity
• GROUP BY AccountId
• ORDER BY COUNT(Id) DESC
• Return type: List<AggregateResult>

Access Results

for (AggregateResult ar : results) {
    Id accId     = (Id)      ar.get('AccountId');
    Integer cnt  = (Integer) ar.get('oppCount');
}

Problem Statement

Retrieve the names and emails of all Contacts whose Account has an Opportunity matching a given opportunity name.

Use a semi-join to find relevant AccountIds from the Opportunity object, then return Contacts for those accounts.

Requirements

• Outer: SELECT Id, Name, Email FROM Contact
• Semi-join: WHERE AccountId IN (SELECT AccountId FROM Opportunity WHERE Name = :opportunityName)
• Method parameter: String opportunityName

Problem Statement

Retrieve Account names that have at least one associated Opportunity with an Amount greater than $50,000.

Use a semi-join to avoid duplicate Account rows.

Requirements

• Outer: SELECT Id, Name, Industry FROM Account
• Semi-join: WHERE Id IN (SELECT AccountId FROM Opportunity WHERE Amount > 50000)
• on outer query

Problem Statement

Retrieve the names and email addresses of all Contacts whose Account has at least one Opportunity in the "Proposal/Price Quote" stage.

Approach

Use a semi-join: filter Contacts by AccountIds that have a Proposal-stage Opportunity.

Requirements

• Outer: SELECT Id, Name, Email FROM Contact
• Semi-join: WHERE AccountId IN (SELECT AccountId FROM Opportunity WHERE StageName = 'Proposal/Price Quote')

Problem Statement

Find and return the AccountId of the Account that has the highest number of associated Opportunities.

Requirements

• SELECT AccountId, COUNT(Id) cnt
• GROUP BY AccountId → ORDER BY COUNT(Id) DESC → LIMIT 1
• Return the AccountId from the first (and only) result row
• Return null if no Opportunities exist

Problem Statement

Retrieve all Accounts that have both at least one Opportunity and at least one Case, using two semi-joins combined with AND.

Requirements

• WHERE Id IN (SELECT AccountId FROM Opportunity)
• AND Id IN (SELECT AccountId FROM Case)
• Single SOQL statement — no Apex loops

Problem Statement

Identify Opportunities that have not been modified in the last 30 days.

Key Concept

LastModifiedDate < LAST_N_DAYS:30 means the record was modified more than 30 days ago — i.e., it sits outside (before) the rolling window.

Requirements

• WHERE LastModifiedDate < LAST_N_DAYS:30
• SELECT Id, Name, StageName, LastModifiedDate
• ORDER BY LastModifiedDate ASC (oldest first)

Problem Statement

Find Contacts whose MailingState is not equal to their parent Account's BillingState — these Contacts are located in a different state than their company's billing address.

Key Concept — Cross-object Field Comparison

Use dot-notation to traverse the Account relationship from Contact: Account.BillingState in both SELECT and WHERE.

Requirements

• SELECT Id, Name, MailingState, Account.BillingState
• WHERE MailingState != Account.BillingState
• Null guards: AND MailingState != null AND Account.BillingState != null

Problem Statement

Retrieve each Account's total Opportunity Amount — the sum of all Opportunity Amounts grouped by Account, highest total first.

Requirements

• SELECT AccountId, SUM(Amount) totalAmount
• FROM Opportunity
• GROUP BY AccountId
• ORDER BY SUM(Amount) DESC

Access

Decimal total = (Decimal) ar.get('totalAmount');

Problem Statement

Retrieve the names and emails of all Contacts whose Account has at least one Opportunity with an Amount greater than the average Opportunity Amount.

3-Step Approach

1. Compute average: SELECT AVG(Amount) avgAmt FROM Opportunity
2. Collect AccountIds: SELECT AccountId FROM Opportunity WHERE Amount > :avgAmt
3. Fetch Contacts: SELECT Name, Email FROM Contact WHERE AccountId IN :accountIds

Governor Rules

• No SOQL inside loops — collect IDs first, then query
• Maximum 3 SOQL queries total

Problem Statement

Retrieve the top 3 Opportunities by Amount for each Account, returned as a Map<Id, List<Opportunity>> keyed by AccountId.

Why Apex?

SOQL does not support LIMIT n PER GROUP, so post-query Apex logic is needed to cap each account's list at 3.

Requirements

• One SOQL query: ORDER BY Amount DESC
• Iterate and stop adding once a group reaches 3 entries
• Return Map<Id, List<Opportunity>>

Problem Statement

Identify Opportunities where the custom field Approval_Status__c is set to "Pending".

Custom Field Convention

All custom fields in Salesforce end with __c. Reference them exactly as defined — casing and underscores matter.

Requirements

• WHERE Approval_Status__c = 'Pending'
• SELECT includes Approval_Status__c
• ORDER BY Amount DESC

Problem Statement

Find all Accounts that have not been modified in the last 60 days. These are candidates for re-engagement or data cleanup.

Key Concept

LastModifiedDate < LAST_N_DAYS:60 returns records whose last modification is before the 60-day rolling window (older than 60 days).

Requirements

• WHERE LastModifiedDate < LAST_N_DAYS:60
• ORDER BY LastModifiedDate ASC (oldest first)

Problem Statement

Retrieve all Closed Won Opportunities whose CloseDate falls within the last calendar quarter.

Key Concept

LAST_QUARTER covers the complete previous fiscal quarter (Q1, Q2, Q3, or Q4) relative to today.

Requirements

• WHERE StageName = 'Closed Won' AND CloseDate = LAST_QUARTER
• ORDER BY Amount DESC

Problem Statement

Write a SOQL aggregate query that returns each Account's total Contact count, ordered from most contacts to fewest.

Requirements

• SELECT AccountId, COUNT(Id) contactCount
• FROM Contact — not Account
• GROUP BY AccountId
• ORDER BY COUNT(Id) DESC

Problem Statement

Retrieve Opportunities where the custom field Priority__c is "High" and the Amount exceeds $50,000.

Requirements

• WHERE Priority__c = 'High' AND Amount > 50000
• SELECT includes Priority__c
• ORDER BY Amount DESC

Problem Statement

Retrieve the names of all Accounts that have at least one Opportunity in the "Prospecting" stage, using a semi-join to avoid duplicate rows.

Requirements

• Outer: SELECT Id, Name, Industry FROM Account
• Semi-join: WHERE Id IN (SELECT AccountId FROM Opportunity WHERE StageName = 'Prospecting')
• ORDER BY Name ASC

Problem Statement

Write a SOQL query that traverses two levels of parent relationships: Opportunity → Account → Owner.

Requirements

• SELECT: Id, Name, StageName, Amount, Account.Name, Account.Owner.Name
• FROM Opportunity WHERE IsClosed = false
• ORDER BY Account.Name ASC, LIMIT 20

Key Concept

SOQL supports up to 5 levels of parent traversal using chained dot notation: Account.Owner.Name goes Opportunity → Account → User (Owner).

Problem Statement

Write a child-to-parent query that filters Contacts by their parent Account's Type field.

Requirements

• SELECT: Id, FirstName, LastName, Email, Phone, Account.Name, Account.Type
• FROM Contact WHERE Account.Type = 'Customer'
• ORDER BY LastName ASC, LIMIT 50

Problem Statement

Write a SOQL query that retrieves Cases with fields from two different parent objects: Contact and Account.

Requirements

• SELECT: Id, CaseNumber, Subject, Status, Contact.FirstName, Contact.LastName, Contact.Email, Account.Name, Account.Phone
• FROM Case WHERE Status != 'Closed'
• ORDER BY CaseNumber ASC, LIMIT 20

Problem Statement

Write a query that returns open Opportunities where the parent Account's AnnualRevenue meets a minimum threshold.

Requirements

• SELECT: Id, Name, StageName, Amount, CloseDate, Account.Name, Account.AnnualRevenue
• FROM Opportunity WHERE Account.AnnualRevenue >= 1000000 AND IsClosed = false
• ORDER BY Account.AnnualRevenue DESC, LIMIT 20

Problem Statement

Build a contact directory query that retrieves multiple Account fields alongside contact details for a richer data set.

Requirements

• SELECT: Id, FirstName, LastName, Email, Phone, Title, Account.Name, Account.Type, Account.BillingCity, Account.Website
• FROM Contact WHERE Account.Name != null
• ORDER BY Account.Name ASC, LastName ASC, LIMIT 50

Problem Statement

Write a query with two-level parent traversal: Contact → Account → Owner (User), retrieving the account owner's name and email.

Requirements

• SELECT: Id, FirstName, LastName, Email, Account.Name, Account.Owner.Name, Account.Owner.Email
• FROM Contact WHERE Account.Owner.Name != null
• ORDER BY Account.Owner.Name ASC, LastName ASC, LIMIT 30

Problem Statement

Retrieve Accounts that have open Cases, using a parent-to-child subquery for the cases and a semi-join to filter only accounts with open cases.

Requirements

• Subquery: (SELECT Id, CaseNumber, Subject, Status, Priority FROM Cases WHERE Status != 'Closed' ORDER BY Priority ASC)
• Outer WHERE: Id IN (SELECT AccountId FROM Case WHERE Status != 'Closed')
• ORDER BY Name ASC, LIMIT 10

Problem Statement

Write a parent-to-child query that retrieves Accounts with only the Contact Id in the subquery — the minimal useful subquery pattern.

Requirements

• Subquery: (SELECT Id FROM Contacts)
• Outer WHERE: Id IN (SELECT AccountId FROM Contact)
• ORDER BY Name ASC, LIMIT 20

Problem Statement

Retrieve Accounts with a subquery for Contacts that have an email address. Filter and sort within the subquery.

Requirements

• Subquery: (SELECT Id, FirstName, LastName, Email FROM Contacts WHERE Email != null ORDER BY LastName ASC)
• Outer: FROM Account, ORDER BY Name ASC, LIMIT 15

Problem Statement

Retrieve high-revenue Accounts with their Opportunities sorted by close date. Combines outer-query filtering with inner-subquery sorting.

Requirements

• Subquery: (SELECT Id, Name, StageName, Amount, CloseDate FROM Opportunities ORDER BY CloseDate ASC)
• Outer: FROM Account WHERE AnnualRevenue > 0, ORDER BY AnnualRevenue DESC, LIMIT 10

Problem Statement

Retrieve Accounts with a Contacts subquery that sorts contacts by LastName using the NULLS LAST clause to handle contacts with no last name gracefully.

Requirements

• Subquery: (SELECT Id, FirstName, LastName, Title FROM Contacts ORDER BY LastName ASC NULLS LAST)
• Outer: FROM Account, ORDER BY Name ASC, LIMIT 15

Problem Statement

Write a query with two parent-to-child subqueries in a single SELECT, retrieving both Contacts and Opportunities for each Account.

Requirements

• First subquery: (SELECT Id, FirstName, LastName, Email FROM Contacts ORDER BY LastName ASC)
• Second subquery: (SELECT Id, Name, StageName, Amount FROM Opportunities WHERE IsClosed = false)
• Outer: FROM Account, ORDER BY Name ASC, LIMIT 10

Problem Statement

Write a three-level nested query: Account → Cases → CaseComments, using two levels of parent-to-child subqueries.

Requirements

• Inner subquery: (SELECT Id, CommentBody, CreatedDate FROM CaseComments ORDER BY CreatedDate DESC)
• Outer subquery: (SELECT Id, CaseNumber, Subject, Status, <inner> FROM Cases WHERE Status != 'Closed' ORDER BY CaseNumber ASC)
• Outer: FROM Account, ORDER BY Name ASC, LIMIT 5

Problem Statement

Write two SOQL queries in the same editor — one using child-to-parent, one using parent-to-child — demonstrating both relationship directions.

Query 1 — Child-to-Parent

• SELECT Account.Name, Account.Owner.Name FROM Opportunity WHERE IsClosed = false, ORDER BY Amount DESC, LIMIT 20

Query 2 — Parent-to-Child

• SELECT (SELECT Id, FirstName, LastName, Email, Phone FROM Contacts ORDER BY LastName ASC) FROM Account, LIMIT 10

Problem Statement

Write a SOQL aggregate query that returns the AccountId and the number of Contacts (COUNT(Id)) for every Account that has 5 or more Contacts.

Requirements

• Query the Contact object.
• Filter out Contacts with no Account: WHERE AccountId != null.
• Group by AccountId.
• Apply HAVING COUNT(Id) >= 5.
• Order by count descending.
• Limit to 20 results.

Key Concepts

• GROUP BY — collapses rows by a field value.
• HAVING — filters after grouping (unlike WHERE which filters before).
• Aggregate functions: COUNT(Id), SUM(), AVG(), MAX(), MIN().

Problem Statement

Write two SOQL queries demonstrating semi-join and anti-join patterns.

Query 1 — Semi-Join (IN subquery)

• Select Contacts whose Account is in the Technology industry.
• WHERE AccountId IN (SELECT Id FROM Account WHERE Industry = 'Technology')
• ORDER BY LastName ASC, LIMIT 50.

Query 2 — Anti-Join (NOT IN subquery)

• Select Accounts that have no Contacts.
• WHERE Id NOT IN (SELECT AccountId FROM Contact WHERE AccountId != null)
• ORDER BY Name ASC, LIMIT 25.

Key Rules

• The subquery in an anti-join must filter out nulls: WHERE AccountId != null — otherwise the NOT IN returns no rows.
• Semi-join / anti-join subqueries can only return one field.

Problem Statement

Write AccountSearchService with a static method searchByName(String searchTerm) that performs a dynamic SOQL search on Account using LIKE.

Requirements

• Return an empty list if searchTerm is blank.
• Sanitise input: String.escapeSingleQuotes(searchTerm.trim()).
• Wrap with wildcards: '%' + safeTerm + '%'.
• Build the SOQL string using string concatenation with a bind variable (:safeTerm).
• Execute with Database.query(soql).
• Wrap in try-catch(QueryException e) — return empty list on error.
• SELECT: Id, Name, Industry, AnnualRevenue, Phone; LIMIT 50.

Best Practices

• String.escapeSingleQuotes() is mandatory for dynamic SOQL — prevents SOQL injection attacks.
• Catch QueryException specifically, not generic Exception, for SOQL errors.
• Return an empty list (not null) on error — callers should never receive null.

Problem Statement

Write two advanced aggregate SOQL queries using date functions.

Query 1 — Monthly Revenue Breakdown

• SELECT CALENDAR_MONTH(CloseDate), SUM(Amount), COUNT(Id) FROM Opportunity.
• Filter: IsWon = true AND FISCAL_YEAR(CloseDate) = THIS_FISCAL_YEAR.
• GROUP BY CALENDAR_MONTH(CloseDate), ORDER BY month ASC.

Query 2 — Deal Size Stats by Stage

• SELECT StageName, AVG(Amount), MAX(Amount), MIN(Amount) FROM Opportunity.
• Filter: CloseDate >= LAST_N_DAYS:90 AND IsClosed = true.
• GROUP BY StageName, ORDER BY AVG(Amount) DESC.

Key Concepts

• CALENDAR_MONTH(), FISCAL_YEAR() — SOQL date functions usable in SELECT and GROUP BY.
• THIS_FISCAL_YEAR, LAST_N_DAYS:N — date literals.
• AVG(), MAX(), MIN() — aggregate functions.

Problem Statement

Implement getTotalOpportunityAmount() that returns the sum of the Amount field across all Opportunities.

Requirements

• Use SUM(Amount) with alias totalAmount
• FROM Opportunity
• Access the result with results[0].get('totalAmount')
• Cast and return as Decimal

Problem Statement

Implement getAverageOpportunityAmount() that returns the average of the Amount field across all Opportunities.

Requirements

• Use AVG(Amount) with alias avgAmount
• FROM Opportunity
• Access via results[0].get('avgAmount')
• Return type: Decimal

Problem Statement

Implement getMaxOpportunityAmount() that returns the largest Amount value across all Opportunities.

Requirements

• Use MAX(Amount) with alias maxAmount
• FROM Opportunity
• Access via results[0].get('maxAmount')
• Return type: Decimal

Problem Statement

Implement getMinOpportunityAmount() that returns the smallest Amount value across all Opportunities.

Requirements

• Use MIN(Amount) with alias minAmount
• FROM Opportunity
• Access via results[0].get('minAmount')
• Return type: Decimal

Problem Statement

Implement getOpportunityStageCount() that returns a count of Opportunities grouped by StageName.

Requirements

• SELECT StageName, COUNT(Id) totalCount
• FROM Opportunity
• GROUP BY StageName
• Return type: List<AggregateResult>

Accessing Results

for (AggregateResult ar : results) {
    String stage = (String)  ar.get('StageName');
    Integer cnt  = (Integer) ar.get('totalCount');
}

Problem Statement

Implement getStagesHavingMoreThanFiveOpps() that returns only Opportunity stages that have more than 5 records.

Requirements

• SELECT StageName, COUNT(Id) totalCount
• GROUP BY StageName
• HAVING COUNT(Id) > 5

Key Concept

HAVING filters after grouping (like a WHERE for aggregate results). It cannot be replaced by WHERE because WHERE filters individual rows, not groups.

Problem Statement

Implement getContactsWithAccountName() that retrieves all Contacts along with the parent Account's Name using a child-to-parent relationship traversal.

Requirements

• SELECT Id, FirstName, LastName, Account.Name
• FROM Contact
• Access the related field with dot notation: contact.Account.Name

Dot Notation

Traverse up to 5 levels of parent relationships using dots: Contact.Account.Owner.Profile.Name.

Problem Statement

Implement getOppsWithAccountName() that retrieves all Opportunities along with the parent Account's Name.

Requirements

• SELECT Id, Name, Account.Name
• FROM Opportunity
• Use dot notation to traverse the lookup to Account

Problem Statement

Implement getOpportunityOwnerProfile() that traverses two levels of parent relationships to retrieve the Opportunity owner's name and their Profile name.

Requirements

• SELECT Id, Name, Owner.Name, Owner.Profile.Name
• FROM Opportunity
• Traverse: Opportunity → User (Owner) → Profile

Relationship Chain

Opportunity.OwnerId → User.ProfileId → Profile.Name

Problem Statement

Implement getAccountsWithContacts() using a parent-to-child subquery to fetch each Account along with its related Contacts in a single query.

Requirements

• Outer: SELECT Id, Name FROM Account
• Subquery: (SELECT Id, FirstName, LastName FROM Contacts)
• Use the plural child relationship name Contacts

Key Concept

The child relationship name is the plural of the child object: Contacts, Opportunities, Cases, etc.

Problem Statement

Implement getAccountsWithOpportunities() using a parent-to-child subquery to retrieve each Account with its related Opportunities.

Requirements

• Outer: SELECT Id, Name FROM Account
• Subquery: (SELECT Id, Name, Amount FROM Opportunities)
• Note the plural child relationship name: Opportunities

Problem Statement

Implement getAccountsWithClosedWonOpps() that fetches every Account together with only its Closed Won Opportunities using a filtered parent-to-child subquery.

Requirements

• Outer: SELECT Id, Name FROM Account
• Subquery: (SELECT Id, Name, StageName FROM Opportunities WHERE StageName = 'Closed Won')

Problem Statement

Implement getAccountsHavingOpps() using a semi-join to return only Accounts that have at least one related Opportunity.

Requirements

• Outer: SELECT Id, Name FROM Account
• WHERE Id IN (SELECT AccountId FROM Opportunity)

Semi-Join vs Subquery

A semi-join uses IN (SELECT …) in the WHERE clause to filter the outer object based on existence in a related object — no JOIN keyword needed in SOQL.

Problem Statement

Implement getAccountsWithoutOpps() using an anti-join to return only Accounts that have no related Opportunities.

Requirements

• Outer: SELECT Id, Name FROM Account
• WHERE Id NOT IN (SELECT AccountId FROM Opportunity)

Anti-Join vs Semi-Join

• Semi-join: IN (subquery) — records that have a match
• Anti-join: NOT IN (subquery) — records that do NOT have a match

Problem Statement

Implement getOpportunityAmountByStage() that returns the total Opportunity amount for each stage.

Requirements

• SELECT StageName, SUM(Amount) totalAmount
• FROM Opportunity
• GROUP BY StageName
• Return type: List<AggregateResult>

Accessing Results

Decimal total = (Decimal) ar.get('totalAmount');
String stage  = (String)  ar.get('StageName');

Problem Statement

Implement getDynamicAccounts(String industryName) that builds a SOQL query dynamically at runtime and executes it with Database.query().

Requirements

• Build the query string: 'SELECT Id, Name, Industry FROM Account WHERE Industry = :industryName'
• Execute with Database.query(queryStr)
• Return type: List<Account>

Security Note

Using :variable bind syntax in dynamic SOQL is safe. Never concatenate user input directly into the query string — that creates SOQL injection vulnerabilities.