Database 101

The practice problems below rely on this managed package being installed in the org. The description of the objects and fields is available here and reproduced below.

Student (apxio__Student__c)

Represents a student that can be enrolled in classes.

Field Type Additional Info
Name Text Standard Name field. Should contain student’s full name.
apxio__Phone__c Phone
apxio__Email__c Email
apxio__Registration_Number__c Autonumber Autonumber assigned after record is created.
apxio__Home_Planet__c Picklist Remember that we’re far off into the future. Valid values include Earth, Mars, Neptune, Saturn, Uranus.
apxio__Cumulative_GPA__c Number Allows one digit before and two digits after decimal point.

Course (apxio__Course__c)

Represents a course that may be offered as a class as part of a semester.

Field Type Additional Info
Name Text Standard Name field. Should contain a descriptive name of the course
apxio__Course_Details__c Rich Text Area Free-form description of the course.
apxio__Credits__c Picklist Valid values include 0, 1, 2, 3, and 4
apxio__Active__c Checkbox

Class (apxio__Class__c)

A class record is a course that is being offered for registration at a campus as part of a semester. Students can be enrolled in classes.

Field Type Additional Info
Name Text Standard Name field
apxio__Course__c Master-detail The course (apxio__Course__c) that is being offered as a class
apxio__Description__c Text Area Free-form description
apxio__Max_Enrollment__c Number Maximum number of students allowed to enroll in a class
apxio__Offered_Semester__c Lookup The offered semester (apxio__Offered_Semester__c) this class is being offered in
apxio__Primary_Teacher__c Lookup The primary teacher (apxio__Teacher__c) assigned to this course

Class Enrollment (apxio__Class_Enrollment__c)

An enrollment is a junction between Student and Class representing that the given student is/was enrolled in the class.

Field Type Additional Info
Name Auto Number Auto-generated. Does not need to filled out
apxio__Offered_Class__c Master-detail The class (apxio__Class__c) that the student is enrolled in
apxio__Student__c Master-detail The student (apxio__Student__c) enrolled in the class
apxio__Final_Grade__c Picklist Valid values include 4.00, 3.67, 3.33, 3.00, 2.67, 2.33, 2.00, 1.67, 1.33, 1.00, 0.67, 0.33, and 0.00
apxio__Grade_Change_Reason__c Text Area Text that should be filled out if the grade is changed
apxio__Pass_Fail__c Formula (Text) Reads Pass if the final grade is 2.00 or above, Fail if it is 0.67 or below, and blank if the final grade is blank
apxio__Tuition_Rate__c Picklist Valid values include In-planet and Out-of-planet. Remember that we’re in the future! Predatory price-gouging by educational institutions is no longer based on state and country lines, but on planetary boundaries!

126 - Insert Student

The method insertStudent takes as input strings name and email, and returns a record ID. Implement the method to insert an apxio__Student__c record with the Name and apxio__Email__c fields filled out, and return the Id of the new record.

You will be working with the following custom object and field names for this problem:

apxio__Student__c
apxio__Student__c.apxio__Email__c

public Id insertStudent(String name, String email) {
    apxio__Student__c student = new apxio__Student__c();

    student.name = name;
    student.apxio__Email__c = email;

    insert student;
    
    return student.Id;
}

132 - Insert Course

The method insertCourse takes as input strings name and details, an integer credits, and returns a record ID. Implement the method to insert a apxio__Course__c record with the Name and apxio__Course_Details__c and apxio__Credits__c fields filled out, and return the Id of the new record.

Note that apxio__Credits__c is a restricted picklist with valid values 1, 2, 3, and 4. If an invalid value is provided for this picklist, return null.

You will be working with the following custom object and field names for this problem:

apxio__Course__c
apxio__Course__c.apxio__Course_Details__c
apxio__Course__c.apxio__Credits__c

public Id insertCourse(String name, String details, Integer credits) {
    apxio__Course__c course = new apxio__Course__c();

    course.name = name;
    course.apxio__Course_Details__c = details;
    course.apxio__Credits__c = String.valueOf(credits);

    try {
        insert course;
    } catch(Exception e) {
        return null;
    }

    return course.Id;
}

127 - Register Student

The method registerStudent takes as input strings name, phone and email, and returns a string. Implement the method to insert an apxio__Student__c record with the Name, apxio__Phone__c and apxio__Email__c fields filled out, and return the autogenerated apxio__Registration_Number__c of the new record.

You will be working with the following custom object and field names for this problem:

apxio__Student__c
apxio__Student__c.apxio__Email__c
apxio__Student__c.apxio__Phone__c
apxio__Student__c.apxio__Registration_Number__c

public String registerStudent(String name, String phone, String email) {
    apxio__Student__c student = new apxio__Student__c();

    student.name = name;
    student.apxio__Phone__c = phone;
    student.apxio__Email__c = email;

    insert student;

    // When records are inserted, the Id is available immediately available on the record, but 
    // other fields that were changed or populated as a result of the insert are not.
    // Note use of bind variable (:) below
    apxio__Student__c insertedStudent = [SELECT apxio__Registration_Number__c
                                           FROM apxio__Student__c
                                          WHERE apxio__Student__c.Id = :student.Id];
    
    return insertedStudent.apxio__Registration_Number__c;
}

128 - Active Students

Implement the method selectActiveStudents that returns a list of all apxio__Student__c records with apxio__Active__c field checked. Make sure the students have a value in the Id and Name fields.

You will be working with the following custom object and field names for this problem:

apxio__Student__c
apxio__Student__c.apxio__Active__c

public List<apxio__Student__c> selectActiveStudents() {
    List<apxio__Student__c> students = [SELECT Id, Name
                                          FROM apxio__Student__c
                                         WHERE apxio__Active__c=True];
    
    return students;
}

130 - Unreachable Students

Implement the method selectUnreachableStudents that queries for and returns a list of all active apxio__Student__c records that are unreachable because they are missing both an email and a phone number. Make sure to include the Id and Name fields in the result. The returned list should be sorted A-Z on Name.

For example, given the following list of students in the database:

Student Name Phone Email Active
Brian Crumley (791)232-997 [email protected] true
Paulina Smith true
Azeem Khan [email protected] true

The method should return a list with a single record for Paulina Smith

You will be working with the following custom object and field names for this problem:

apxio__Student__c
apxio__Student__c.apxio__Active__c
apxio__Student__c.apxio__Email__c
apxio__Student__c.apxio__Phone__c
apxio__Student__c.apxio__Registration_Number__c

public List<apxio__Student__c> selectUnreachableStudents() {
    List<apxio__Student__c> unreachable = [SELECT Id, Name
                                             FROM apxio__Student__c
                                            WHERE apxio__Active__c=True
                                              AND apxio__Phone__c=null
                                              AND apxio__Email__c=null
                                            ORDER BY Name];

    return unreachable;
}

129 - Students Missing Info

Implement the method selectStudentsWithoutContactInfo that queries for and returns a list of all active apxio__Student__c records that are missing an email, phone, or both. Make sure to include the Id and Name fields in the result. The returned list should be sorted A-Z on Name.

For example, given the following list of students in the database:

Student Name Phone Email Active
Brian Crumley (791)232-997 [email protected] true
Paulina Smith true
Azeem Khan [email protected] true

The method should return a list containing Azeem Khan at index 0 and Paulina Smith at index 1

You will be working with the following custom object and field names for this problem:

apxio__Student__c
apxio__Student__c.apxio__Active__c
apxio__Student__c.apxio__Email__c
apxio__Student__c.apxio__Phone__c
apxio__Student__c.apxio__Registration_Number__c

Discuss this problem on the ApexSandbox.io Trailblazer Community Group

public List<apxio__Student__c> selectStudentsWithoutContactInfo() {
    List<apxio__Student__c> noContactInfo = [SELECT Id, Name
                                               FROM apxio__Student__c
                                              WHERE apxio__Active__c=True
                                                AND (apxio__Phone__c=null 
                                                 OR  apxio__Email__c=null)
                                              ORDER BY Name];

    return noContactInfo;
}

131 - Course and Class

The method createCourseAndClass takes as input string parameters courseName and description, and returns void. Provide an implementation of the method that first inserts a apxio__Course__c record with the provided name and description (if provided) copied into the Name and apxio__Course_Details__c fields, and then inserts a child apxio__Class__c record with the same name and description copied into the Name and apxio__Description__c fields.

There is, however, a difference between the course details and description fields on the two objects. While the apxio__Course__c.apxio__Course_Details__c has type Rich Text capable of storing thousands of characters, apxio__Class__c.apxio__Description__c can only store a maximum of 255 characters. Make sure to truncate the description to 255 characters before adding it to your apxio__Class__c record. You can assume that the provided description will never be too large for the rich text field.

You will be working with the following custom object and field names for this problem:

apxio__Course__c
apxio__Course__c.apxio__Course_Details__c
apxio__Class__c
apxio__Class__c.apxio__Course__c
apxio__Class__c.apxio__Description__c

public void createCourseAndClass(String name, String description) {
    apxio__Course__c course = new apxio__Course__c();
    
    course.Name = name;
    course.apxio__Course_Details__c = description;

    insert course;

    apxio__Class__c cls = new apxio__Class__c();

    cls.Name = name;
    cls.apxio__Description__c = description?.left(255);
    cls.apxio__Course__c = course.Id;

    insert cls;
}

133 - Student List

The method insertStudents takes as input two lists of strings studentNames and studentEmails. The two lists will always have the same size, with studentNames[i] and studentEmails[i] (for any in-range value of i) representing a student’s name and email.

Write an implementation of the method that creates apxio__Student__c records for each entry in the lists with the Name and apxio__Email__c fields filled out.

You will be working with the following custom object and field names for this problem:

apxio__Student__c
apxio__Student__c.apxio__Email__c

public void insertStudents(List<String> studentNames, List<String> studentEmails) {
    List<apxio__Student__c> students = new List<apxio__Student__c>();
    
    for (Integer i = 0; i < studentNames.size(); i++) {
        apxio__Student__c student = new apxio__Student__c();

        student.name = studentNames[i];
        student.apxio__Email__c = studentEmails[i];

        students.add(student);
    }

    insert students;
}

134 - Class from Course

Implement the method classFromCourse that takes as input a string courseName, creates an apxio__Class__c record associated with the course named courseName, and returns the Id of the new record. The new class should have the same Name as the course.

You should not create a new apxio__Course__c record. The new class should be linked to the course that already exists in the database. In case no course with the given name is found, do not create any class record and return null.

You will be working with the following custom object and field names for this problem:

apxio__Course__c
apxio__Class__c
apxio__Class__c.apxio__Course__c

public Id classFromCourse(String courseName) {
    List<apxio__Course__c> course = [SELECT Id, Name
                                       FROM apxio__Course__c
                                      WHERE apxio__Course__c.Name =: courseName];

    if (course.size() == 0) {
        return null;
    }

    apxio__Class__c cls = new apxio__Class__c();
    cls.Name = course[0].Name;
    cls.apxio__Course__c = course[0].Id;

    insert cls;

    return cls.Id;
}

135 - Enroll Students

Implement the method enrollStudents that takes as input a list of strings emails and a string className and returns a boolean. The method should enroll all students with the provided emails into a class with the given name by creating apxio__Class_Enrollment__c records.

Note that apxio__Student__c and apxio__Class__c records already exist in the database.

You will be working with the following custom object and field names for this problem:

apxio__Student__c
apxio__Student__c.apxio__Email__c
apxio__Class__c
apxio__Class_Enrollment__c
apxio__Class_Enrollment__c.apxio__Student__c
apxio__Class_Enrollment__c.apxio__Offered_Class__c

public void enrollStudents(List<String> emails, String className) {
    List<apxio__Class_Enrollment__c> enrollments = new List<apxio__Class_Enrollment__c>();
    List<apxio__Student__c> students = [SELECT Id, apxio__Email__c 
                                          FROM apxio__Student__c
                                         WHERE apxio__Email__c IN: emails ];
    List<apxio__Class__c> cls = [SELECT Id, Name 
                                   FROM apxio__Class__c
                                  WHERE Name =: className];

    for(Integer i=0; i<students.size(); i++){
        apxio__Class_Enrollment__c enrollment = new apxio__Class_Enrollment__c();

        enrollment.apxio__Student__c = students[i].Id;
        enrollment.apxio__Offered_Class__c = cls[0].Id;

        enrollments.add(enrollment);
    }

    if(!enrollments.isEmpty()){
        insert enrollments;
    }
}