In this post (tutorial), we will see basic SOQL queries which we need for database operations in Salesforce.
1. SELECT
Syntax:
SELECT Field 1, Field 2,....Field n FROM Object
e.g.
SELECT Id, Name, Roll_Number__c, Dept__c FROM Student__c
SELECT Id, LastName, FirstName, Name, Title, Company, Street, State, City, PostalCode, Country FROM Lead
2. WHERE
Syntax:
SELECT Field 1, Field 2,....Field n FROM Object
WHERE Field=Value
e.g.
SELECT Id, Name, Roll_Number__c, Dept__c, Contact__c, College__c FROM Student__c
WHERE Dept__c='CE'
SELECT Id, Name, Roll_Number__c, Dept__c, Contact__c, College__c FROM Student__c
WHERE Roll_Number__c=37
3. AND
Syntax:
SELECT Field 1, Field 2,....Field n FROM Object
WHERE Field1=Value1 AND Field2=Value2
e.g.
SELECT Id, Name, Roll_Number__c, Dept__c, Contact__c, College__c FROM Student__c
WHERE Roll_Number__c=32 AND Dept__c='CE'
4. OR
Syntax:
SELECT Field 1, Field 2,....Field n FROM Object
WHERE Field1=Value1 OR Field2=Value2
e.g.
SELECT Id, Name, Roll_Number__c, Dept__c, Contact__c, College__c FROM Student__c
WHERE Roll_Number__c=32 OR Roll_Number__c=37
SELECT Id, Name, Roll_Number__c, Dept__c, Contact__c, College__c FROM Student__c
WHERE Roll_Number__c=32 OR Dept__c='ENTC'
SELECT Id, Name, Roll_Number__c, Dept__c, Contact__c, College__c FROM Student__c
WHERE Roll_Number__c=32 OR Dept__c='ENTC' OR Dept__c='IT'
Combination of AND, OR
e.g.
SELECT Id, Name, Roll_Number__c, Dept__c, Contact__c, College__c FROM Student__c
WHERE Roll_Number__c=32 AND (Dept__c='CE' OR Dept__c='IT')
5. IN
Syntax:
SELECT Field 1, Field 2,....Field n FROM Object
WHERE Field IN (Value1, Value2,......, Value n)
e.g.
SELECT Id, Name, Roll_Number__c, Dept__c, Contact__c, College__c FROM Student__c
WHERE Roll_Number__c IN (32,37)
SELECT Id, Name, Roll_Number__c, Dept__c, Contact__c, College__c FROM Student__c
WHERE Dept__c IN ('CE','IT')
6. LIKE
LIKE keyword provides a mechanism for matching partial text strings.
It supports two wildcards % and _.
i. % stands for occurrence of zero or more characters.
ii. _ stands for occurance of exactly one character.
Syntax:
SELECT Field 1, Field 2,....Field n FROM Object
WHERE Field LIKE 'String%'
SELECT Field 1, Field 2,....Field n FROM Object
WHERE Field LIKE '%String'
SELECT Field 1, Field 2,....Field n FROM Object
WHERE Field LIKE '%String%'
e.g.
SELECT Id, FirstName, Name, Company, Status FROM Lead
WHERE Status LIKE 'Working%'
SELECT Id, FirstName, Name, Company, Status FROM Lead
WHERE Status LIKE '%Contacted'
SELECT Id, FirstName, Name, Company, Status FROM Lead
WHERE Status LIKE '%Not%'
7. ORDER By
ORDER BY keyword shows the records in an order. By default, records are in ascending order.
We can also show records in descending order by keyword DESC.
Keyword ASC shows records in ascending order.
Syntax:
SELECT Field 1, Field 2,....Field n FROM Object
ORDER BY field_name;
SELECT Field 1, Field 2,....Field n FROM Object
ORDER BY field_name DESC;
SELECT Field 1, Field 2,....Field n FROM Object
ORDER BY field_name ASC;
e.g.
SELECT Id, FirstName, Name, Company, Status FROM Lead
ORDER BY Name
SELECT Id, FirstName, Name, Company, Status FROM Lead
ORDER BY Name DESC
SELECT Id, FirstName, Name, Company, Status FROM Lead
ORDER BY Name ASC
DML (Data Manipulation Language)
1. Insert
2. Update
3. Delete
4. Undelete
1. Insert
e.g.
//College__c record=new College__c(Name='WCE', College_ID__c=5000, City__c='WCE');
//insert record;
College__c record1=new College__c(Name='WCE1', College_ID__c=5000, City__c='WCE');
College__c record2=new College__c(Name='WCE2', College_ID__c=5000, City__c='WCE');
College__c record3=new College__c(Name='WCE3', College_ID__c=5000, City__c='WCE');
List<College__c> records=new List<College__c>();
records.add(record1);
records.add(record2);
records.add(record3);
//insert records; //partial success is not allowed
Database.insert(records); //partial success is not allowed
Database.insert(records, true); //partial success is not allowed
Database.insert(records, false); //partial success is allowed
2. Update
e.g.
List<College__c> records=[SELECT Name, College_ID__c, City__c FROM College__c WHERE Name LIKE 'WCE%'];
for(College__c record : records)
{
record.City__c='Sangli';
}
//update records; //partial success is not allowed
Database.update(records, false); //partial success is allowed
3. Delete
e.g.
List<College__c> records= [SELECT Name, College_ID__c,City__c FROM College__c WHERE Name='WCE'];
//delete records; //partial success is not allowed
Database.delete(records, false); //partial success is allowed
4. Undelete
e.g.
List<College__c> deletedRecords= [SELECT Id, Name, College_ID__c,City__c FROM College__c WHERE isDeleted=true ALL ROWS];
undelete deletedRecords; //partial success is not allowed
//Database.undelete(deletedRecords, false); //partial success is allowed
Aggregate Functions:
Aggregate functions in SOQL allow to roll up and summarize the data in a query.
AVG() – Returns the average value of a numeric field
MIN() – Returns the minimum value of a field
MAX() – Returns the maximum value of a field
SUM() – Returns the total sum of a numeric field
COUNT() – Returns the number of rows matching the query criteria
GROUP BY clause helps aggregate functions to work on group of similar records instead of individual records.
We can use a HAVING clause with a GROUP BY clause to filter the results returned by aggregate functions.
The HAVING clause is similar to a WHERE clause. The difference is that you can include aggregate functions in a HAVING clause, but not in a WHERE clause.
1. AVG()
Returns the average value of a numeric field.
e.g. 1
SELECT AVG(Amount) FROM Opportunity
e.g. 2
SELECT LeadSource, AVG(Amount) FROM Opportunity GROUP BY LeadSource
e.g. 3
SELECT LeadSource, AVG(Amount) FROM Opportunity GROUP BY LeadSource HAVING AVG(Amount)>=100000
2. SUM()
Returns the total sum of a numeric field.
e.g. 1
SELECT SUM(Amount) FROM Opportunity
e.g. 2
SELECT LeadSource, SUM(Amount) FROM Opportunity GROUP BY LeadSource
e.g. 3
SELECT LeadSource, SUM(Amount) FROM Opportunity GROUP BY LeadSource HAVING SUM(Amount)>=100000
3. MIN()
Returns the minimum value of a field.
e.g. 1
SELECT MIN(Amount) FROM Opportunity
e.g. 2
SELECT LeadSource, MIN(Amount) FROM Opportunity GROUP BY LeadSource
e.g. 3
SELECT LeadSource, MIN(Amount) FROM Opportunity GROUP BY LeadSource HAVING MIN(Amount)>=100000
4. MAX()
Returns the maximum value of a field.
e.g.
e.g. 1
SELECT MAX(Amount) FROM Opportunity
e.g. 2
SELECT LeadSource, MAX(Amount) FROM Opportunity GROUP BY LeadSource
e.g. 3
SELECT LeadSource, MAX(Amount) FROM Opportunity GROUP BY LeadSource HAVING MAX(Amount)>=100000
6. COUNT()
Returns the number of rows matching the query criteria.
e.g. 1
SELECT COUNT() FROM Opportunity
e.g. 2
SELECT COUNT() FROM Opportunity WHERE LeadSource='Word of mouth'
e.g. 3
SELECT COUNT() FROM Account WHERE Name LIKE 'p%'
7. COUNT(fieldName)
Returns the number of rows matching the query criteria.
e.g. 1
SELECT COUNT(LeadSource) FROM Opportunity
e.g. 2
SELECT LeadSource, COUNT(Amount) FROM Opportunity GROUP BY LeadSource
e.g. 3
SELECT LeadSource, COUNT(Amount) FROM Opportunity GROUP BY LeadSource HAVING COUNT(Amount)>=100000
8. COUNT_DISTINCT()
e.g. 2
SELECT COUNT_DISTINCT(LeadSource) FROM Opportunity
e.g. 3
SELECT COUNT_DISTINCT(LeadSource) FROM Opportunity WHERE LeadSource='External Referral'
No comments:
Post a Comment