SOQL Scenarios in Apex

Writing SOQL query to retrieve records is one of the most common scenario while writing apex logic. Therefore strong hold on SOQL is very important. Below are some of the SOQL scenarios to practice

Parent to child query

Get name of accounts and related contacts

Select name,(SELECT name FROM contacts) from Account

Note: For custom relationships you use the API name of the relationship field, but with __r rather than __c.

Select name, (Select name from camping__r) from Account

Child to parent query

Use Dot Notation when looking for parent object record and querying on child object.

childObjectName.parentObjectRelationshipName.fieldName

Get account and contact name from contact

Select account.name,name from Contact

Get all the contact names in descending order

SELECT name FROM contact ORDER BY name desc

Count Individual balance from Contact group by name having count of Individual balance greater than 1000

SELECT count(IndividualBalance__c),name FROM Contact GROUP by Name HAVING count(IndividualBalance__c) > 1000

Count number of unique stage names from opportunities

SELECT Count_Distinct(StageName) FROM Opportunity

Get the name of the single record for a given id

String name1 = [SELECT Id, name FROM Account WHERE id = '0010b00002Kpi6b' LIMIT 1].name;
system.debug(name1);

Get name and createdDate of Accounts which are created before last month

SELECT name, id, createdDate FROM account WHERE createdDate < LAST_MONTH

Get contact on asc order of account creation date

select id, name, account.CreatedDate from contact order by account.CreatedDate asc

Get the names and created date of all the Account records which are created in last 30 days

SELECT name, id, createdDate FROM account WHERE createdDate = LAST_N_DAYS:30

Get name from contact where name is either Sarah Smith or Jessi Jones

Note: The IN keyword allows to define a list of values by placing them inside parentheses and separated with commas.

SELECT name, id FROM CONTACT where name IN ('Sarah Smith','Jessi Jones')

Note: “_” inside the single quotes, replaces it with any single character

SELECT name, id FROM CONTACT where name like '_arah S_ith'

Get 10 names from account in ascending order starting from 4th name

select id,name from account order by name asc limit 10 offset 3

Retrieve all the contact records where name contains ‘s’

SELECT name, id FROM CONTACT where name like '%S%'

Get maximum amount from opportunity grouped by stageName

SELECT MAX(Amount),StageName FROM Opportunity GROUP BY StageName LIMIT 1

Get the 5th highest amount from opportunity

SELECT Amount FROM Opportunity ORDER BY Amount DESC NULLS LAST limit 1 offset 4

Get Sum, Minimum, Maximum, Average amount from opportunity

Way 1:

Aggregateresult[] result = 
[select sum(Amount) total, Avg(Amount) avg, Min(Amount) minm, Max(Amount) maxm from Opportunity];
  object totalAmount = result[0].get('total');  
  object avgAmount = result[0].get('avg');    
  object minAmount = result[0].get('minm');  
  object maxAmount = result[0].get('maxm');    

  system.debug('Total Amount: ' + totalAmount);
  system.debug('Average Amount: ' + avgAmount);
  system.debug('Minimum Amount: ' + minAmount);
  system.debug('Maximum Amount: ' + maxAmount);
system.debug('result '+result);

Explanation:

1.Here I am storing value in the array [] because I will be getting multiple values i.e. sum, average, min, max

Aggregateresult[] result

2. I am passing index value in the result because I am storing value in an array so I need to put from which index of the array I want the value.

result[0].get('maxm')

Output

Way 2: Using expressions

AggregateResult[] groupedResults
  = [select sum(Amount), Avg(Amount), Min(Amount), Max(Amount) from Opportunity];


for (AggregateResult ar : groupedResults)  {
     System.debug('Sum amount' + ar.get('expr0'));
    System.debug('Avg amount' + ar.get('expr1'));
    System.debug('Min amount' + ar.get('expr2'));
    System.debug('Max amount' + ar.get('expr3'));
}

Explanation:

In Any aggregated field in a SELECT list that does not have an alias automatically gets an implied alias with a format expri.

The value of i starts at 0 and increments for every aggregated field with no explicit alias. Here expr0 denotes sum, expr1 denotes avg, expr2 denotes min, expr3 denotes max.

Output

Practice SOQL and get more confidence in it. For more of these kind of articles please subscribe to the blog. Happy Coding!

Reference

Working with SOQL and SOSL Query Results