Wednesday, 8 February 2023

Basic SOQL Queries

          

               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














No comments:

Post a Comment