SOQL Practice Problems Every Salesforce Developer Needs

TL;DR

SOQL is how Apex reads data. You need six things to be useful: filter rows, sort them, limit them, walk a relationship in both directions, group them, and stay inside the governor limits. This post works through all six. Each one links to a challenge where you write the query yourself.

Why SOQL Practice Feels Different

Most SOQL tutorials show you a query that already works. That is not the hard part. The hard part is going the other way. You have a business question, and you have to turn it into a query.

So this post is built around questions, not syntax. Read the question first. Try to write the query in your head. Then read the answer.

Every query here runs against standard objects. You can paste each one into the Developer Console and run it. Or you can skip straight to the SOQL challenges and write them for real.

Problem 1: Filter Rows

The question: get every account rated Hot.

The WHERE clause does the filtering. Text values go in single quotes.

List<Account> hotAccounts = [
    SELECT Id, Name, Rating
    FROM Account
    WHERE Rating = 'Hot'
];
System.debug(hotAccounts.size());

Two things trip people up here.

First, SELECT * does not exist in SOQL. You name every field you want. If you forget a field, you get a runtime error when you touch it, not a compile error.

Second, the query returns a list even when you expect one row. Asking for hotAccounts[0] on an empty list throws. Check the size first.

Two filters come up constantly once you are past equals. LIKE does partial matching, where % stands for any run of characters. Date literals such as THIS_MONTH or LAST_N_DAYS:30 go in without quotes, because they are keywords rather than text.

List<Account> matches = [
    SELECT Id, Name
    FROM Account
    WHERE Name LIKE 'Acme%'
    AND CreatedDate = LAST_N_DAYS:30
];
System.debug(matches.size());

Quoting a date literal is the mistake to watch for. CreatedDate = 'THIS_MONTH' does not filter by this month. It fails, because you handed a date field a string.

Problem 2: Sort and Limit

The question: get the five largest open opportunities.

List<Opportunity> topDeals = [
    SELECT Id, Name, Amount, StageName
    FROM Opportunity
    WHERE IsClosed = false
    ORDER BY Amount DESC NULLS LAST
    LIMIT 5
];
System.debug(topDeals.size());

ORDER BY sorts. DESC puts the biggest first. LIMIT cuts the list.

NULLS LAST matters more than it looks. Opportunities with no amount sort as null. By default Salesforce puts nulls first on a descending sort, so your "top five deals" can come back as five blank rows. Say NULLS LAST and they go to the bottom where they belong.

Practice this one on high value accounts, which combines a filter and a sort.

Problem 3: Parent to Child

The question: get each account with its contacts, in one query.

This is a subquery. The inner query uses the plural child relationship name.

List<Account> accountsWithContacts = [
    SELECT Id, Name, (SELECT Id, LastName FROM Contacts)
    FROM Account
    LIMIT 10
];
 
for (Account acct : accountsWithContacts) {
    System.debug(acct.Name + ' has ' + acct.Contacts.size() + ' contacts');
}

Note Contacts, not Contact. Standard objects use the plural. Custom objects use the relationship name with __r on the end, so a child object named Invoice__c is queried as Invoices__r.

The reason to care: this is one query instead of one query per account. That difference is what keeps you under the limits in Problem 6.

The parent to child challenge is exactly this shape.

Problem 4: Child to Parent

The question: get every contact with the name of the account it belongs to.

Going up is easier than going down. Use dots.

List<Contact> contacts = [
    SELECT Id, LastName, Account.Name, Account.Industry
    FROM Contact
    WHERE AccountId != null
    LIMIT 10
];
 
for (Contact con : contacts) {
    System.debug(con.LastName + ' works at ' + con.Account.Name);
}

You can walk up five levels this way. Account.Owner.Manager.Name is legal.

The WHERE AccountId != null guard is not decoration. A contact with no account returns null for Account, and con.Account.Name on that row throws a null pointer exception.

Problem 5: Group and Count

The question: how many opportunities are in each stage?

You could query everything and count in Apex. Do not. Let the database do it.

List<AggregateResult> byStage = [
    SELECT StageName, COUNT(Id) total
    FROM Opportunity
    GROUP BY StageName
];
 
for (AggregateResult row : byStage) {
    System.debug(row.get('StageName') + ': ' + row.get('total'));
}

An aggregate query returns AggregateResult, not your object. You pull values out with get() and a string key.

That is why COUNT(Id) total has an alias on the end. Without the alias the column comes back as expr0, and reading row.get('expr0') is a bug waiting to happen when someone adds a second aggregate.

You can filter the groups too. WHERE filters rows before grouping and HAVING filters the groups after, so "stages with more than five opportunities" is a HAVING question.

List<AggregateResult> busyStages = [
    SELECT StageName, COUNT(Id) total
    FROM Opportunity
    GROUP BY StageName
    HAVING COUNT(Id) > 5
];
System.debug(busyStages.size());

Aggregates count against your row limit far less than pulling every record, which leads to the last problem.

Problem 6: Stay Inside the Limits

The question: why does this code break at 201 records?

for (Account acct : accountsToProcess) {
    List<Contact> contacts = [SELECT Id FROM Contact WHERE AccountId = :acct.Id];
    // ...
}

The query is inside the loop. One hundred accounts means one hundred queries. A single Apex transaction gets 100 SOQL queries, so this throws a limit exception on the 101st.

The fix is to query once, outside the loop, and organize the results in a map.

Map<Id, Account> accountsById = new Map<Id, Account>(
    [SELECT Id, Name FROM Account LIMIT 100]
);
 
List<Contact> allContacts = [
    SELECT Id, LastName, AccountId
    FROM Contact
    WHERE AccountId IN :accountsById.keySet()
];
 
System.debug('One query returned ' + allContacts.size() + ' contacts');

That IN :accountsById.keySet() is a bind variable. The colon passes an Apex value into the query. It is how you filter by a collection you already have.

Two queries total, no matter how many accounts. This pattern is called bulkification, and it is the single most common reason production Apex fails review.

Try it on the governor limits challenge.

One More Thing: Respect The User

Every query above runs in system mode by default. It ignores the field and object permissions of the person using your code. That is fine in a batch job and wrong in a controller backing a page.

Add WITH USER_MODE and the query enforces the running user's permissions and field-level security for you.

List<Account> visibleAccounts = [
    SELECT Id, Name
    FROM Account
    WITH USER_MODE
    LIMIT 10
];
System.debug(visibleAccounts.size());

If a field is hidden from that user, the query throws instead of quietly handing back data they should not see. Reviewers look for this on anything a user can reach, and so does the security review for AppExchange packages.

What To Do Next

Reading queries is not the same as writing them. The six problems above map to challenges you can solve in the browser against a real org:

• Start with the Apex Fundamentals path if WHERE and ORDER BY are still new.
• Go to the SOQL challenges if you want to practice aggregates.
• Work the PD1 Prep path if you are studying for the certification, where SOQL is a large share of the questions.

Write the query before you read the answer. That is the part that sticks.