Database Basics Flashcards
How are queries executed in the REST API?
The “Query” parameter, with a query string
How are queries executed in the SOAP API?
using the query() method
SOQL is used when you know which objects the data resides in and you want to:
Retrieve data from a single object or multiple objects that are related.
Count the number of records that meet a specified criteria.
Sort results as part of the query.
Retrieve data from number, date, or checkbox fields.
What is a SOQL semi-join?
a subquery on another object in an IN clause to restrict the records returned
What is a SOQL anti-join?
A subquery on another object in a NOT IN clause to restrict the records returned.
Explain parent-to-child semi-join.
A parent to child semi-join is when a parent filters for specific child records in the where clause.
ex: selecting opportunities related to an account based on Stage.
select id, name from account where id in (select accountid from opportunity where stagename= ‘closed lost’)
Explain parent-to-child anti-join.
A parent-to-child anti-join is when a parent filters for child records excluded from the where clause.
ex: selecting opportunities related to an account that don’t meet criteria.
select id, name from account where id not in (select accountid from opportunity where stagename = ‘closed lost’)
Explain child-to-child semi-join.
A c2c semi-join is when a result set is filtered by a reference fields of two child objects.
ex: retrieving opportunities for contacts who have a source of ‘web’.
select id from opportunity where accountid in (select accountid from contact where leadsource=’Web’)
Explain child-to-child anti-join.
A c2c semi-join is when a result set is filtered by the exclusion of reference fields of two child objects.
ex: retrieving opportunities for contacts who don’t have a source of ‘web’.
select id from opportunity where accountid not in (select accountid from contact where leadsource=’Web’)
True or false:
Semi-joins & Anti joins can evaluate relationship queries in a SELECT clause.
True
True/False:
Semi-joins/anti-joins can be combined in a single query.
True
What are the 2 basic limits of semi & anti joins?
No more than two IN or NOT IN clauses.
You cannot use NOT with a semi or anti-join
What are the restrictions of the main WHERE clause of a semi or anti join query?
The left operand must query a single ID (PK) or reference (FK).
The left operand can’t use relationships (ex: Account.id).
True/false
A subquery must query a field referencing the same object type as the main query
true
True/false
Standard SOQL limits apply to the number of records matched in a subquery .
False
True/False.
A query that has a subquery is subject to the standard SOQL limits on the main query.
true
True/False.
The selected column in a subquery must be a foreign key field.
true
True/False.
The selected column in a subquery can traverse relationships.
false
True/False.
You can query on the same object in a subquery as a main query.
false
True/False.
You cannot nest a semi or anti join statement in another semi or anti join statement.
True
True/False.
You can use semi/anti-joins in a subquery WHERE clase.
false
True/False.
Subqueries can be used with the OR clause
false
What four clauses are not supported in subqueries?
Count
For Update
Order By
Limit
Which objects are not supported in subqueries?
ActivityHistory Attachments Event EventAttendee Note OpenActivity Tags (AccountTag, ContactTag, etc.) Task
In a child-to-parent relationship, what is the relationship name to the parent?
Give an example
The foreign key.
Contact.Account
What property holds the reference to the parent object?
relationshipName
For parent-to-child relationships, the parent object has a name for the child relationship that is unique to the parent…what is it?
give an example.
the plural form of the child object.
Account.Contacts
What relationships can be queried using SOQL?
child-to-parent (n:1)
Parent-to-child (1:n)
parent-to-child as an FK in an aggregate query
True/False:
Any query, including subqueries, can include a WHERE clause, which applies to the object in the FROM clause in the current query.
True
True/False:
Relationship queries can’t be done on custom objects
false.
Why is the plural form of the relationship required on creation of a custom relationship?
To reference the relationship in a relationship query
In a child-to-parent relationship, how is the parent referenced?
using dot notation
ex: parent__r.fieldname
true/false:
Parent-to-Child relationship queries use dot notation.
false.
In Apex, how are parent to child relationship queries processed?
Perform query on parent object.
Iterate over parent QueryResult with queryMore().
for each parent, retrieve the child QueryResult.
Iterate over child recs using queryMore() on each QueryResult.
In a parent-to-child query, explain the result set.
The query returns a set of Parent records, which contains a query result set of child fields from the subquery
Give an example of a Parent-to-child relationship query
Select name, (select firstname, lastname from contacts) from account where name like ‘Acme%’
How does an outer join work in SOQL?
SOQL relationship queries return records even if the relevant foreign key field has a null value.
ex: Select id, casenumber, account.id, account.name from case; if account.id is not populated for a record, it is returned with this query.
Using the Enterprise WSDL, how can you determine if a parent-to-child relationship exists from the parent object?
In the WSDL, the parent object will contain an element with the plural name of the child relationship with a type of tns:QueryResult
Using the Enterprise WSDL, how can you determine if a child-to-parent relationship exists from the child object?
In the WSDL, the child object will contain two elements: one representing the ID of the parent object, the other representing the parent object.
ex: Account, type=ens: Account
AccountId, type=tns:ID
True/False:
All relationships are exposed in the API?
false
What is the most reliable way to identify relationships?
execute a describeSObjects() call.
How many child-to-parent relationships can be specified in a single query?
up to 35
How many relationships can a custom object have?
25
True/False:
All of the child-to-parent relationships can be referenced in a single query.
true
How many parent-to-child relationships can be specified in a single query?
up to 20
In a child-to-parent relationship, how many levels of relationships can be specified?
5
How many levels are represented in the following relationship?
Contact.Account.Owner.Firstname
3
How many levels of relationship can a query specify in a parent-to-child relationship?
1
ex: Account-to-opportunity, but not account to opportunity to OLI
True/False:
You can filter on the body of a Note via relationship query.
False
True/False:
You can filter on the body of an Attachment via relationship query
false
What field types cannot be filtered in a relationship query?
TextArea
Blobs
SControl objects
What is the max number of rows that can be fetched in a subquery that involves an external object?
1000
How many joins can a SOQL query have across external objects and other types of objects?
4
Why does a join to an external object take longer?
Because each join requires a separate round trip to the external system.
True/False:
External objects support the ORDER BY clause in a relationship query, except when using OData 2.0 for SF Connect?
False
True/false:
If the main object of a query is an external object, then queryMore() only supports the main object and not the subqueries?
true
What is the default batch size for a QueryResult Object?
query() or queryMore()
500
