Subquery in SOQL

Sample Query:

SELECT Id, Name,
(SELECT FirstName, LastName, Title, Email, Phone FROM Contacts),
(SELECT Name, StageName, Amount, CloseDate FROM Opportunities),
(SELECT CaseNumber, ContactId, Subject, Priority, Status FROM Cases) FROM Account LIMIT 10

For Custom object, ChildObject__r is the Child Relationship name which can be found in child object's parent field definition.

SOQL statements cannot query aggregate relationships more than 1 level away from the root entity object.