📊

SOQL Fundamentals

SOQL (Salesforce Object Query Language) is used to search your Salesforce data. Understanding relationships is key to powerful queries!

🎮 Interactive Object Relationship Map

📊 Account
Parent Object

→

👤 Contact
Child Object

💰 Opportunity
Child Object

Click on any object above to see its details and sample data!

🧠 Quick Quiz: What does SOQL stand for?

A) Salesforce Object Quick Language

B) Salesforce Object Query Language

C) Salesforce Online Query Language

D) Salesforce Object Quality Language

🌳

Parent-to-Child Queries (Subqueries)

Parent-to-Child queries use subqueries to fetch related child records. Perfect for getting all contacts for an account!

One-to-Many Relationship

🎮 Interactive Query Builder

Drag components to build your Parent-to-Child query:

SELECT

Id, Name

(SELECT Name, Email FROM Contacts)

FROM Account

WHERE Industry = ‘Technology’

Drop SELECT here

Drop parent fields here

Drop subquery here

Drop FROM here

Drop WHERE here (optional)

📋 Example: Account with Contacts

Parent-to-Child Query

SELECT Id, Name, Industry,
    (SELECT Id, Name, Email, Phone 
     FROM Contacts 
     WHERE Email != null)
FROM Account
WHERE Industry = ‘Technology’
LIMIT 5

💰 Example: Opportunity with Line Items

Opportunity → Line Items

SELECT Id, Name, Amount, CloseDate,
    (SELECT Id, Product2.Name, Quantity, UnitPrice 
     FROM OpportunityLineItems
     ORDER BY UnitPrice DESC)
FROM Opportunity
WHERE StageName = ‘Closed Won’
LIMIT 3

✏️ Practice: Build Your Own Parent-to-Child Query

Your Practice Query

🔗

Child-to-Parent Queries (Dot Notation)

Child-to-Parent queries use dot notation to access parent object fields. Navigate up the relationship hierarchy!

Many-to-One Relationship

🎮 Interactive Relationship Navigator

👤 Contact
Click to navigate to Account

💰 Opportunity
Click to navigate to Account

📋 Case
Click to navigate to Contact

👤 Example: Contact with Account Info

Child-to-Parent Query

SELECT Id, Name, Email, Phone,
    Account.Name,
    Account.Industry,
    Account.BillingCity,
    Account.Type
FROM Contact
WHERE Account.Industry = ‘Healthcare’
AND Email != null
LIMIT 5

📋 Example: Case with Contact & Account

Multiple Level Navigation

SELECT Id, CaseNumber, Subject, Status,
    Contact.Name,
    Contact.Email,
    Contact.Account.Name,
    Contact.Account.Industry
FROM Case
WHERE Status = ‘Open’
AND Contact.Account.Type = ‘Customer’
LIMIT 5

🧩 Drag & Drop: Build Dot Notation

Drag components to build correct dot notation:

Contact

.

Account

.

Name

Build: Contact.Account.Name

🔧

Complex Relationship Queries

Combine both techniques for powerful multi-level queries that traverse multiple relationships!

Lookup Relationship

Lookup relationships are more flexible – child records can exist without parents.

🔍 Example: Lead with Campaign Info

Lookup Relationship Query

SELECT Id,
    Campaign.Name,
    Campaign.Type,
    Campaign.Status,
    Campaign.StartDate
FROM Lead
WHERE Campaign.Type = ‘Email’
AND CreatedDate = THIS_MONTH

Master-Detail Relationship

Master-Detail relationships are tighter – detail records cannot exist without master records.

🏗️ Example: Opportunity Line Item with Product

Master-Detail Query

SELECT Id, Quantity, UnitPrice, TotalPrice,
    Product2.Name,
    Product2.ProductCode,
    Product2.Family,
    Opportunity.Name,
    Opportunity.Amount,
    Opportunity.Account.Name
FROM OpportunityLineItem
WHERE Product2.IsActive = true
AND Opportunity.StageName = ‘Negotiation’

The most powerful queries combine both parent-to-child AND child-to-parent techniques!

🌟 Ultimate Example: Account Hierarchy

Mixed Relationship Query

SELECT Id, Name, Industry, BillingCity,
    Parent.Name,
    Parent.Industry,
    (SELECT Name, Title, Email FROM Contacts
     WHERE Email != null
     ORDER BY Title),
    (SELECT Name, Amount, CloseDate, StageName
     FROM Opportunities
     WHERE Amount > 10000
     ORDER BY CloseDate DESC)
FROM Account
WHERE Parent.Industry = ‘Technology’
AND Industry = ‘Software’
LIMIT 3

🏗️

Interactive Query Builder Challenge

🎯 Challenge: Build a Complete Business Report Query

Create a query that shows accounts with their opportunities and the primary contact for each opportunity.

Your Challenge Query

🧠

Interactive SOQL Quiz

Test your SOQL relationships knowledge with this comprehensive quiz! Track your progress and get personalized feedback.

🎯 SOQL Mastery Quiz

Score: 0/0

📋

Sample Queries Library

Ready-to-use SOQL queries for common business scenarios. Click any query to copy it!

👥 All Contacts for Each Account

Basic Parent-to-Child

SELECT Id, Name, Type, Industry,
    (SELECT Id, Name, Title, Email, Phone 
     FROM Contacts 
     WHERE Email != null
     ORDER BY Name)
FROM Account
WHERE Type = ‘Customer’
ORDER BY Name
LIMIT 10

Use Case: Get customer accounts with all their contacts who have email addresses

📞 Contact Details with Account Info

Basic Child-to-Parent

SELECT Id, Name, Title, Email, Phone,
    Account.Name,
    Account.Type,
    Account.Industry,
    Account.BillingCity,
    Account.BillingState
FROM Contact
WHERE Account.Type = ‘Customer’
AND Email != null
ORDER BY Account.Name, Name

Use Case: Contact directory with company information for customer accounts

💰 Opportunities with Products

Opportunity Line Items

SELECT Id, Name, Amount, StageName, CloseDate,
    Account.Name,
    (SELECT Id, Product2.Name, Product2.ProductCode,
            Quantity, UnitPrice, TotalPrice
     FROM OpportunityLineItems
     ORDER BY TotalPrice DESC)
FROM Opportunity
WHERE StageName IN (‘Proposal’, ‘Negotiation’, ‘Closed Won’)
AND Amount > 10000
ORDER BY CloseDate DESC

Use Case: Sales pipeline analysis with product breakdown

📋 Support Cases with Full Context

Multi-level Child-to-Parent

SELECT Id, CaseNumber, Subject, Status, Priority,
    CreatedDate, ClosedDate,
    Contact.Name,
    Contact.Email,
    Contact.Phone,
    Contact.Account.Name,
    Contact.Account.Type,
    Contact.Account.Industry,
    Owner.Name
FROM Case
WHERE Status IN (‘New’, ‘Working’, ‘Escalated’)
AND Contact.Account.Type = ‘Customer’
ORDER BY Priority DESC, CreatedDate ASC

Use Case: Support queue with customer and contact details

📊 Sales Team Performance Report

Sales Analytics (Fixed – No User Subqueries)

— CORRECT: Query FROM Opportunity TO User (child-to-parent)
SELECT Id, Name, Amount, StageName, CloseDate,
    Account.Name, 
    Account.Industry,
    Owner.Name,
    Owner.Email,
    Owner.Profile.Name,
    Owner.Manager.Name
FROM Opportunity
WHERE StageName = ‘Closed Won’
AND CloseDate = THIS_QUARTER
AND Owner.Profile.Name = ‘Sales User’
AND Owner.IsActive = true
ORDER BY Owner.Name, Amount DESC

— Alternative: Get User details separately
— SELECT Id, Name, Email, Profile.Name, Manager.Name
— FROM User  
— WHERE Profile.Name = ‘Sales User’ AND IsActive = true

⚠️ Important: User object doesn’t support subqueries! Query from Opportunity to User instead.

🎯 Marketing Campaign Effectiveness

Campaign ROI Analysis

SELECT Id, Name, Type, Status, StartDate, EndDate,
    ActualCost, ExpectedRevenue,
    (SELECT Id, Name, Email, Status,
            CreatedDate, ConvertedDate
     FROM CampaignMembers
     WHERE Status IN (‘Responded’, ‘Sent’)
     ORDER BY CreatedDate DESC)
FROM Campaign
WHERE Type IN (‘Email’, ‘Webinar’, ‘Trade Show’)
AND StartDate = THIS_YEAR
ORDER BY ExpectedRevenue DESC

Use Case: Campaign performance with member responses and conversions

🏢 Account Hierarchy Management

Parent-Child Account Structure

SELECT Id, Name, Type, Industry, AnnualRevenue,
    Parent.Name,
    Parent.Type,
    Parent.Industry,
    (SELECT Id, Name, Type, Industry, AnnualRevenue
     FROM ChildAccounts
     WHERE Type = ‘Customer’
     ORDER BY AnnualRevenue DESC)
FROM Account
WHERE Parent.Type = ‘Customer’
AND Type IN (‘Customer’, ‘Partner’)
ORDER BY AnnualRevenue DESC

Use Case: Enterprise account structure with subsidiaries and pipeline

🎓 Student Enrollment System

Education Management

— Assuming custom objects: Course__c, Enrollment__c, Student__c
SELECT Id, Name, Course_Code__c, Credits__c, Department__c,
    Instructor__r.Name,
    Instructor__r.Email,
    (SELECT Id, Student__r.Name,
            Student__r.Email,
            Student__r.Student_ID__c,
            Grade__c, Status__c
     FROM Enrollments__r
     WHERE Status__c = ‘Active’
     ORDER BY Student__r.Name)
FROM Course__c
WHERE Semester__c = ‘Fall 2024’
AND Department__c = ‘Computer Science’
ORDER BY Course_Code__c

Use Case: Course management with enrolled students (custom objects)

🔄 Complex Multi-Object Query

Full Customer 360 View

SELECT Id, Name, Type, Industry, AnnualRevenue,
    BillingAddress, ShippingAddress,
    Parent.Name,
    Owner.Name,
    Owner.Email,
    (SELECT Id, Name, Title, Email, Phone, Department
     FROM Contacts
     WHERE Email != null
     ORDER BY CreatedDate DESC
     LIMIT 5),
    (SELECT Id, Name, Amount, StageName, CloseDate,
            Probability, Owner.Name
     FROM Opportunities
     WHERE StageName NOT IN (‘Closed Lost’)
     ORDER BY CloseDate ASC
     LIMIT 3),
    (SELECT Id, CaseNumber, Subject, Status, Priority,
            CreatedDate, Owner.Name
     FROM Cases
     WHERE Status != ‘Closed’
     ORDER BY Priority DESC, CreatedDate DESC
     LIMIT 3)
FROM Account
WHERE Type = ‘Customer’
AND AnnualRevenue > 1000000
ORDER BY AnnualRevenue DESC
LIMIT 5

Use Case: Complete customer overview with contacts, opportunities, and support cases

📈 Territory Management Query

Sales Territory Analysis (Fixed)

— CORRECT: Get Account data with User info (child-to-parent)
SELECT Id, Name, Type, Industry, BillingState, AnnualRevenue,
    Owner.Name,
    Owner.Email,
    Owner.Title,
    Owner.Department,
    Owner.UserRole.Name,
    Owner.Manager.Name,
    Owner.Profile.Name
FROM Account
WHERE Type = ‘Customer’
AND BillingCountry = ‘United States’
AND Owner.Profile.Name IN (‘Sales Manager’, ‘Account Executive’)
AND Owner.IsActive = true
AND Owner.UserRole.Name LIKE ‘%Sales%’
ORDER BY Owner.UserRole.Name, Owner.Name, AnnualRevenue DESC

— For opportunity metrics, use separate queries:
— SELECT Owner.Name, COUNT(Id), SUM(Amount)
— FROM Opportunity 
— WHERE StageName IN (‘Qualification’, ‘Proposal’, ‘Negotiation’)
— AND CloseDate >= THIS_QUARTER
— GROUP BY Owner.Name

⚠️ Fixed: User object limitations – query from Account to User, not User with subqueries

🏭 Manufacturing Supply Chain

Product & Inventory Management

SELECT Id, Name, ProductCode, Family, IsActive,
    Description, UnitPrice,
    (SELECT Id, Opportunity.Name,
            Opportunity.Account.Name,
            Opportunity.StageName,
            Quantity, UnitPrice, TotalPrice,
            CreatedDate
     FROM OpportunityLineItems
     WHERE Opportunity.StageName IN (‘Proposal’, ‘Negotiation’)
     AND CreatedDate = THIS_MONTH
     ORDER BY TotalPrice DESC),
    (SELECT Id, Product_Family__c, Supplier__r.Name,
            Cost__c, Lead_Time_Days__c, Stock_Level__c
     FROM Product_Details__r
     WHERE Stock_Level__c < Reorder_Point__c)
FROM Product2
WHERE IsActive = true
AND Family IN ('Hardware', 'Software', 'Services')
ORDER BY Family, ProductCode

Use Case: Product catalog with current opportunities and inventory levels

🏥 Healthcare Patient Management

Patient Care Coordination

— Assuming healthcare custom objects
SELECT Id, Name, Email, Phone, Birthdate,
    Primary_Physician__r.Name,
    Primary_Physician__r.Department__c,
    Insurance_Provider__r.Name,
    (SELECT Id, Name, Appointment_Date__c, Status__c,
            Provider__r.Name, Provider__r.Department__c,
            Diagnosis__c, Treatment_Plan__c
     FROM Medical_Appointments__r
     WHERE Appointment_Date__c >= TODAY
     ORDER BY Appointment_Date__c ASC),
    (SELECT Id, Medication_Name__c, Dosage__c,
            Prescribed_Date__c, Prescribing_Doctor__r.Name,
            Refills_Remaining__c, Active__c
     FROM Prescriptions__r
     WHERE Active__c = true
     ORDER BY Prescribed_Date__c DESC),
    (SELECT Id, Test_Type__c, Test_Date__c, Results__c,
            Ordering_Doctor__r.Name, Status__c
     FROM Lab_Results__r
     WHERE Test_Date__c = LAST_N_DAYS:30
     ORDER BY Test_Date__c DESC)
FROM Patient__c
WHERE Primary_Physician__r.Department__c = ‘Cardiology’
AND Active__c = true
ORDER BY Name

Use Case: Comprehensive patient record with appointments, prescriptions, and lab results

⚡ Optimized Large Data Query

Performance Best Practices

— Optimized query with selective filters and indexed fields
SELECT Id, Name, Type, Industry, CreatedDate,
    Owner.Name,
    (SELECT Id, Name, Email, Title
     FROM Contacts
     WHERE Email != null
     AND CreatedDate = THIS_YEAR
     ORDER BY CreatedDate DESC
     LIMIT 5)
FROM Account
WHERE Type = ‘Customer’
AND Industry IN (‘Technology’, ‘Healthcare’, ‘Financial Services’)
AND CreatedDate = THIS_YEAR
AND Owner.IsActive = true
ORDER BY CreatedDate DESC
LIMIT 200

— Performance Tips:
— 1. Use indexed fields in WHERE clauses (Id, Name, CreatedDate, Owner)
— 2. Limit subquery results to reduce data transfer
— 3. Use specific date ranges instead of open-ended queries
— 4. Filter on both parent and child levels
— 5. Always use LIMIT to prevent timeouts

Performance Tips: Uses indexed fields, specific filters, and result limits for optimal performance

🎯 Selective Query Pattern

Efficient Data Retrieval

— Use selective filters to reduce query scope
SELECT Id, Name, Amount, StageName, CloseDate,
    Account.Name,
    Account.Industry,
    Owner.Name
FROM Opportunity
WHERE StageName IN (‘Proposal’, ‘Negotiation’, ‘Closed Won’)
AND CloseDate >= LAST_N_DAYS:90
AND Amount > 50000
AND Account.Type = ‘Customer’
AND Account.Industry = ‘Technology’
ORDER BY CloseDate DESC, Amount DESC
LIMIT 100

— Why this is efficient:
— ✅ Filters on indexed StageName field
— ✅ Uses date range instead of open query  
— ✅ Includes amount threshold to reduce records
— ✅ Filters on related Account fields early
— ✅ Orders by indexed fields
— ✅ Includes reasonable LIMIT

Efficiency Focus: Multiple selective filters reduce query scope and improve performance

📊 Aggregate Query Examples

Summary & Analytics

— Aggregate queries for reporting and analytics
SELECT Account.Name, Account.Industry,
    COUNT(Id) NumOpportunities,
    SUM(Amount) TotalPipeline,
    AVG(Amount) AvgDealSize,
    MAX(Amount) LargestDeal,
    MIN(CloseDate) EarliestClose
FROM Opportunity
WHERE StageName NOT IN (‘Closed Lost’)
AND CloseDate >= THIS_QUARTER
AND Amount > 0
GROUP BY Account.Name, Account.Industry
HAVING COUNT(Id) > 1
ORDER BY SUM(Amount) DESC
LIMIT 50

— Additional aggregate examples:
— COUNT(), SUM(), AVG(), MAX(), MIN()
— GROUP BY with multiple fields
— HAVING clause for aggregate filtering
— ORDER BY aggregate functions

Analytics Power: Aggregate functions provide powerful reporting capabilities

🔍 Dynamic SOQL Patterns

Apex Dynamic SOQL

// Apex class with dynamic SOQL examples
public class DynamicSOQLExamples {
    
    // Dynamic field selection
    public static List<Account> getAccountsWithFields(Set<String> fields, String industry) {
        String fieldList = String.join(new List<String>(fields), ‘, ‘);
        String query = ‘SELECT ‘ + fieldList + ‘ FROM Account WHERE Industry = :industry LIMIT 100’;
        return Database.query(query);
    }
    
    // Dynamic subquery based on object type
    public static List<Account> getAccountsWithChildren(String childObjectType) {
        String subquery = ”;
        if (childObjectType == ‘Contact’) {
            subquery = ‘(SELECT Id, Name, Email FROM Contacts LIMIT 5)’;
        } else if (childObjectType == ‘Opportunity’) {
            subquery = ‘(SELECT Id, Name, Amount FROM Opportunities LIMIT 3)’;
        }
        
        String query = ‘SELECT Id, Name, ‘ + subquery + ‘ FROM Account LIMIT 10’;
        return Database.query(query);
    }
    
    // Security: Always use bind variables and validate inputs
    public static List<SObject> secureQuery(String objectType, String whereClause) {
        // Validate object type against schema
        if (!Schema.getGlobalDescribe().containsKey(objectType)) {
            throw new IllegalArgumentException(‘Invalid object type’);
        }
        
        String query = ‘SELECT Id, Name FROM ‘ + objectType + ‘ WHERE ‘ + whereClause;
        return Database.query(query);
    }
}

Dynamic SOQL: Build queries programmatically in Apex with proper security considerations

🎯 Quick Query Generator

📚

SOQL Relationships Quick Reference

🌳 Parent-to-Child (Subqueries)

Syntax: (SELECT fields FROM ChildObject)

Use Case: Get all related child records

Example:


                        SELECT Name, (SELECT Name FROM Contacts) FROM Account

🔗 Child-to-Parent (Dot Notation)

Syntax: ParentObject.Field

Use Case: Access parent object fields