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
No comments:
Post a Comment