Course challenge

1st grade

Question 1

Scenario 1, Questions 1-7

For the past six months, you have been working for a direct-mail marketing firm as a junior marketing analyst. Direct mail is advertising material sent to people through the mail. These people can be current or prospective customers, clients, or donors. Many charities depend on direct mail for financial support.

Your company, Directly Dynamic, creates direct-mail pieces with its in-house staff of graphic designers, expert mail list services, and on-site printing. Your team has just been hired by a local nonprofit, Food Justice Rock Springs. The mission of Food Justice Rock Springs is to eliminate food deserts by establishing local gardens, providing mobile pantries, educating residents, and more. Click below to read the email from Tayen Bell, vice president of marketing and outreach.

C5 Course Challenge, Email From Tayen Bell, Directly Dynamic.pdf

You begin by reviewing the dataset. To use the template for this dataset, click the link below and select “Use Template.”

Link to template: Dynamic Dataset

Or, if you don’t have a Google account, download the file directly from the attachment below.

Dynamic Dataset

The client has asked you to send two separate mailings: one to people within 50 miles of Rock Springs; the other to anyone outside that area. So, to research each donor’s distance from the city, you first need to find out where all of these people live.

You could scroll through 209 rows of data, but you know there is a more efficient way to organize the cities.

Which of the following procedures will enable you to sort your spreadsheet by city (Column K) in ascending order? Select all that apply.

• Use the SORT function syntax: =SORT(A2:R210, 11, TRUE)
• Use the SORT function syntax: =SORT(A2:R210, K, TRUE)
• Select A2-R210, then use the drop-down menu to Sort Range by Column K from A to Z
• Select A2-R210, then use the drop-down menu to Sort Sheet by Column K from A to Z

Explain: To sort your spreadsheet by city in ascending order, you can use one of two methods. You can use the SORT function syntax =SORT(A2:R210, 11, TRUE). You can also select A2-R210, then use the drop-down menu to Sort Sheet by Column K from A to Z.

Question 2

Scenario 1, continued

You notice that many cells in the city column, Column K, are missing a value. So, you use the zip codes to research the correct cities. Now, you want to add the cities to each donor’s row. However, you are concerned about making a mistake, such as a spelling error.

What spreadsheet tool can you use to add a drop-down list with predetermined options for each city name?

A. List

B. VLOOKUP

C. Find

D. Data validation

The correct answer is D. Data validation. Explain: You use data validation to add drop-down lists with predetermined options for each city name. This allows you to control what can and cannot be entered in your worksheet.

Question 3

Scenario 1, continued

Now, you decide to address Tayen’s request to include a handwritten note in the direct-mail piece for anyone who gave at least $100 last year.

To change how cells appear, you use conditional formatting. Then, to highlight cells with a value of at least 00, you choose to format cells if they have a value greater than or equal to 100.

A. True

B. False

Explain: To change how cells appear, you would use conditional formatting. Then, choose to format cells if they are greater than or equal to 100.

Question 4

Scenario 1, continued

At this point, you notice that the information about state and zip code is in the same cell. However, your company’s mailing list software requires states to be on a separate line from zip codes.

What function will enable you to move the 2-character state abbreviation in cell L2 into its own column?

A. =LEFT(L2,2)

B. =RIGHT(2,L2)

C. =RIGHT(L2,2)

D. =LEFT(2,L2)

The correct answer is A. =LEFT(L2,2). Explain: To move the 2-character state abbreviations in Column L into their own column, use the LEFT function: =LEFT(L2,2).

Question 5

Scenario 1, continued

Next, you duplicate your dataset twice using the Sheet Menu. You rename the first sheet Donation Form List, and you remove the cities that are further than 50 miles from Rock Springs. You rename the second sheet Postcard List, and you remove the cities that are within 50 miles of Rock Springs.

Then, you import these datasets into your company’s mailing list database. In a mailing list database, you create two tables: Donation_Form_List and Postcard_List. You decide to clean the Donation_Form_List first.

Your company’s mailing list software requires units to be on the same line as street addresses. However, they are currently in two separate columns (street_address and unit).

You use a SQL function to instruct the database to combine the two columns into a new column called “address.” The syntax is: JOIN(street_address, " to ", unit) as address.

A. True

B. False

Explain: To combine the two columns into a new column called “address,” the syntax is: CONCAT(street_address, " to ", unit) as address.

Question 6

Scenario 1, continued

Your database contains people who live in many areas of Wyoming. However, it’s important to align your in-house data with the data from Food Justice Rock Springs. You also need to separate your data into the two lists: Donation_Form_List and Postcard_List. They will be based on each city’s distance from Rock Springs.

What SQL function do you use to select all data from the Donation_Form_List organized by zip code?

A. ARRANGE BY

B. ORDER BY

C. SEQUENCE

D. ORGANIZE

The correct answer is B. ORDER BY. Explain: To select all data from the Donation_Form_List organized by zip code, you use the ORDER BY function. The ORDER BY function sorts results returned in a query.

Question 7

Scenario 1, continued

You finish cleaning your datasets, so you decide to review Tayen’s email one more time to make sure you completed the task fully. It’s a good thing you checked because you forgot to identify people who have served on the board of directors or board of trustees. She wants to write them a thank-you note, so you need to locate them in the database.

To retrieve only those records that include people who have served on the board of trustees or on the board of directors, you use the WHERE function. Which of the following SQL queries would return the needed information?

A.

SELECT *
FROM Donation_Form_List
WHERE Board_Member = 'True' AND Trustee = 'True'

B.

SELECT *
FROM Donation_Form_List
WHERE Board_Member = 'True' OR Trustee = "True"

C.

SELECT *
FROM Donation_Form_List
WHERE Board_Member != 'True' OR Trustee != 'True'

D.

SELECT *
FROM Donation_Form_List
WHERE Board_Member != 'True' AND Trustee != 'True'

The correct answer is B.

Question 8

Scenario 2, Questions 8-13

Your company’s direct-mail campaign was very successful, and Food Justice Rock Springs has continued partnering with Directly Dynamic. One thing you’ve been working on is assigning all donors identification numbers. This will enable you to clean and organize the lists more effectively.

Meanwhile, another team member has been creating a prospect list that contains data about people who have indicated interest in getting involved with Food Justice Rock Springs. These people are also assigned a unique ID. Now, you need to compare your donor list with the dataset in your database and collect certain data from both.

What SQL function will return all records from the left table and only the matching records from the right?

A. RIGHT JOIN

B. OUTER JOIN

C. INNER JOIN

D. LEFT JOIN

The correct answer is D. LEFT JOIN. Explain: A LEFT JOIN function will return all records from the left table and only the matching records from the right.

Question 9

Scenario 2, continued

Your next task is to identify the average contribution given by donors over the past two years. Tayen will use this information to set a donation minimum for inviting donors to an upcoming event.

You start with 2019. To return average contributions in 2019 (contributions_2019), you use the AVG function. What portion of your SQL statement will instruct the database to find this average and store it in the AvgLineTotal variable?

A. AVG(“contributions_2019”) IN AvgLineTotal

B. AVG(contributions_2019) AS AvgLineTotal

C. AVG(contributions_2019) = “AvgLineTotal”

D. AVG(“contributions_2019”) AS AvgLineTotal

The correct answer is B. AVG(contributions_2019) AS AvgLineTotal. Explain: To return average contributions in 2019, the correct portion of the SQL query is:

AVG(contributions_2019) AS AvgLineTotal

Question 10

Scenario 2, continued

Now that you provided her with the average donation amount, Tayen decides to invite 50 people to the grand opening of a new community garden. You return to your New Donor List spreadsheet to determine how much each donor gave in the past two years. You will use that information to identify the 50 top donors and invite them to the event.

What is the correct syntax to add the contribution amounts in cells O2 and P2?

A. =SUM(“O2,P2”)

B. =SUM(O2,P2)

C. =SUM(O2*P2)

D. =SUM(O2/P2)

The correct answer is B. =SUM(O2,P2). Explain: To add cells O2 and P2, use the function =SUM(O2,P2). You can also use the function =SUM(O2+P2) or the formula =O2+P2.

Question 11

Scenario 2, continued

Tayen informs you that she’s thinking about inviting anyone who donated at least $100 in 2018, as well. However, she only has five open spaces. She asks you to report how many people gave at least $100 so she can determine if they can also be invited to the event.

What is the correct syntax to count how many donations of 00 or great appear in Column O?

A. =COUNTIF(02:2010,”<=100”)

B. =SUMIF(O2:2010,">=100")

C. =SUMIF(02:2010,”>=100”)

D. =COUNTIF(O2:O210,">=100")

The correct answer is D. =COUNTIF(O2:O210,">=100").

Question 12

Scenario 2, continued

The community garden grand opening was a success. In addition to the 55 donors Food Justice Rock Springs invited, 20 other prospects attended the event. Now, Tayen wants to know more about the donations that came in from new prospects compared to the original donors.

Which SQL query can be used to calculate the percentage of contributions from prospects?

A.

B.

C.

D.

The correct answer is B. Explain: To identify the percentage of contributions from prospects, the correct query is:

SELECT
event_contributions,
Total_donors,
Total_prospects,
(Total_prospects / Total_donors * 100) AS Prospects_Percent
FROM contributions_data

Question 13

Scenario 2, continued

Your team creates a highly effective prospects list for Food Justice Rock Springs. After a few months, many of these prospects become donors. Now, Tayen wants to know the top three cities in which these new donors live. She will use that information to determine if it’s still true that people who live closer to Rock Springs are more likely to donate.

To retrieve the number of donors in each city, sorted high to low, you use the following query:

A. True

B. False

It is false statement. Explain: To retrieve the number of donors in each city, sorted high to low, DESC must be included. ASC will sort the donors low to high. The correct query is:

SELECT COUNT(DonorID), City
FROM new_donor_list
GROUP BY City
ORDER BY COUNT(DonorID) DESC

2nd grade

Question 1

Scenario 1, Questions 1-7

For the past six months, you have been working for a direct-mail marketing firm as a junior marketing analyst. Direct mail is advertising material sent to people through the mail. These people can be current or prospective customers, clients, or donors. Many charities depend on direct mail for financial support.

Your company, Directly Dynamic, creates direct-mail pieces with its in-house staff of graphic designers, expert mail list services, and on-site printing. Your team has just been hired by a local nonprofit, Food Justice Rock Springs. The mission of Food Justice Rock Springs is to eliminate food deserts by establishing local gardens, providing mobile pantries, educating residents, and more. Click below to read the email from Tayen Bell, vice president of marketing and outreach.

C5 Course Challenge, Email From Tayen Bell, Directly Dynamic.pdf

You begin by reviewing the dataset. To use the template for this dataset, click the link below and select “Use Template.”

Link to template: Dynamic Dataset

Or, if you don’t have a Google account, download the file directly from the attachment below.

Dynamic Dataset

The client has asked you to send two separate mailings: one to people within 50 miles of Rock Springs; the other to anyone outside that area. So, to research each donor’s distance from the city, you first need to find out where all of these people live.

You could scroll through 209 rows of data, but you know there is a more efficient way to organize the cities.

Which of the following procedures will enable you to sort your spreadsheet by city (Column K) in ascending order?

A. =SORT(A2:R210, 11, ASC)

B. =SORT(A2:R210, K, TRUE)

C. =SORT(A2:R210, K, ASC)

D. =SORT(A2:R210, 11, TRUE)

The correct answer is D. =SORT(A2:R210, 11, TRUE). Explain: To sort your spreadsheet by city in ascending order, use the SORT function syntax =SORT(A2:R210, 11, TRUE). You can also select A2-R210, then use the drop-down menu to Sort Sheet by Column K from A to Z.

Question 2

Scenario 1, continued

You notice that many cells in the city column, Column K, are missing a value. So, you use the zip codes to research the correct cities. Now, you want to add the cities to each donor’s row. However, you are concerned about making a mistake, such as a spelling typo.

Fill in the blank: To add drop-down lists to your worksheet with predetermined options for each city name, you decide to use _____.

A. VLOOKUP

B. data validation

C. the LIST function

D. the find tool

Explain: To add drop-down lists to your worksheet with predetermined options for each city name, you decide to use data validation.

Question 3

Scenario 1, continued

Now, you decide to address Tayen’s request to include a handwritten note in the direct-mail piece for anyone who gave at least $100 last year.

Which of the following procedures will enable you to change how cells in your spreadsheet appear if they contain a value of $100 or more?

A. Select Column M. Then, select Format > Conditional Formatting. Choose to format cells if text starts with 100.

B. Select Column M. Then, select Format > Conditional Formatting. Choose to format cells if they are greater than or equal to 100.

C. Select Column M. Then, select Format > Conditional Formatting. Choose to format cells if text contains 100.

D. Select Column M. Then, select Format > Conditional Formatting. Choose to format cells if they are greater than 100.

The correct answer is B. Select Column M. Then, select Format > Conditional Formatting. Choose to format cells if they are greater than or equal to 100. Explain: To change how cells appear if they contain a value of $100 or more, select Column M. Then, select Format > Conditional Formatting. Choose to format cells if they are greater than or equal to 100.

Question 4

Scenario 1, continued

At this point, you notice that the information about state and zip code is in the same cell. However, your company’s mailing list software requires states to be on a separate line from zip codes.

What function do you use to move the 5-digit zip code in cell L2 into its own column?

A. =LEFT(L2,5)

B. =RIGHT(5,L2)

C. =RIGHT(L2,5)

D. =LEFT(5,L2)

The correct answer is C. =RIGHT(L2,5). Explain: To move the 5-digit zip codes in Column L into their own column, use =RIGHT(L2,5).

Question 5

Scenario 1, continued

Next, you duplicate your dataset twice using the Sheet Menu. You rename the first sheet Donation Form List, and you remove the cities that are further than 50 miles from Rock Springs. You rename the second sheet Postcard List, and you remove the cities that are within 50 miles of Rock Springs.

Then, you import these datasets into your company’s mailing list database. In a mailing list database, you create two tables: Donation_Form_List and Postcard_List. You decide to clean the Donation_Form_List first.

Your company’s mailing list software requires units to be on the same line as street addresses. However, they are currently in two separate columns (street_address and unit).

You use a SQL function to instruct the database to combine the two columns into a new column called “address.” The syntax is: JOIN(street_address, " to ", unit) as address.

A. True

B. False

It is false statement. Explain: To combine the two columns into a new column called “address,” the syntax is: CONCAT(street_address, " to ", unit) as address.

Question 6

Scenario 1, continued

Your database contains people who live in many areas of Wyoming. However, it’s important to align your in-house data with the data from Food Justice Rock Springs. You also need to separate your data into the two lists: Donation_Form_List and Postcard_List. They will be based on each city’s distance from Rock Springs.

The zip codes are in a column called zip_code. What query do you use to select all data from the Donation_Form_List organized by zip code?

A.

B.

C.

D.

The correct answer is A. Explain: To organize your data by zip code, the correct query is:

SELECT *
FROM Donation_Form_List
ORDER BY zip_code

Question 7

Scenario 1, continued

You finish cleaning your datasets, so you decide to review Tayen’s email one more time to make sure you completed the task fully. It’s a good thing you checked because you forgot to identify people who have served on the board of directors or board of trustees. She wants to write them a thank-you note, so you need to locate them in the database.

To retrieve only those records that include people who have served on the board of trustees or on the board of directors, you use the WHERE function. Which of the following SQL queries would return the needed information?

A.

SELECT *
FROM Donation_Form_List
WHERE Board_Member = 'True' AND Trustee = 'True'

B.

SELECT *
FROM Donation_Form_List
WHERE Board_Member != 'True' AND Trustee != 'True'

C.

SELECT *
FROM Donation_Form_List
WHERE Board_Member = 'True' OR Trustee = "True"

D.

SELECT *
FROM Donation_Form_List
WHERE Board_Member != 'True' OR Trustee != 'True'

The correct answer is C.

Question 8

Scenario 2, Questions 8-13

Your company’s direct-mail campaign was very successful, and Food Justice Rock Springs has continued partnering with Directly Dynamic. One thing you’ve been working on is assigning all donors identification numbers. This will enable you to clean and organize the lists more effectively.

Meanwhile, another team member has been creating a prospect list that contains data about people who have indicated interest in getting involved with Food Justice Rock Springs. These people are also assigned a unique ID. Now, you need to compare your donor list with the dataset in your database and collect certain data from both.

What SQL function will combine RIGHT and LEFT JOIN to return all matching records and create additional null values in a table of non-matching records in both tables?

A. INNER JOIN

B. RIGHT JOIN

C. OUTER JOIN

D. LEFT JOIN

The correct answer is C. OUTER JOIN

Question 9

Scenario 2, continued

Your next task is to identify the average contribution given by donors over the past two years. Tayen will use this information to set a donation minimum for inviting donors to an upcoming event.

**You have performed the calculations for 2019, so now you move on to 2020. To return average contributions in 2020 (contributions_2020), you use the AVG function. You use the following section of a SQL query to find this average and store it in the AvgLineTotal variable: **

AVG(contributions_2020) AS AvgLineTotal

A. True

B. False

It is true statement. Explain: To return average contributions in 2020, the correct portion of the SQL query is:

AVG(contributions_2020) AS AvgLineTotal

Question 10

Scenario 2, continued

Now that you provided her with the average donation amount, Tayen decides to invite 50 people to the grand opening of a new community garden. You return to your New Donor List spreadsheet to determine how much each donor gave in the past two years. You will use that information to identify the 50 top donors and invite them to the event.

What is the correct syntax to add the contribution amounts in cells O2 and P2?

A. =SUM(O2*P2)

B. =SUM(“O2,P2”)

C. =SUM(O2/P2)

D. =SUM(O2,P2)

The correct answer is D. =SUM(O2,P2). Explain: To add cells O2 and P2, use the function =SUM(O2,P2). You can also use the formula =O2+P2.

Question 11

Scenario 2, continued

Tayen informs you that she’s thinking about inviting anyone who donated at least $100 in 2018, as well. However, she only has five open spaces. She asks you to report how many people gave at least $100 so she can determine if they can also be invited to the event.

What is the correct syntax to count how many donations of 00 or great appear in Column O?

A. =SUMIF(O2:2010,">=100")

B. =COUNTIF(02:2010,”<=100”)

C. =COUNTIF(O2:O210,">=100")

D. =SUMIF(02:2010,”>=100”)

The correct answer is C. =COUNTIF(O2:O210,">=100")

Question 12

Scenario 2, continued

The community garden grand opening was a success. In addition to the 55 donors Food Justice Rock Springs invited, 20 other prospects attended the event. Now, Tayen wants to know more about the donations that came in from new prospects compared to the original donors.

What is the section of a SQL query that will calculate the percentage of contributions from prospects?

A. (Total_prospects / Total_donors * 100) AS Prospects_Percent

B. (Total_prospects * Total_donors / 100) AS Prospects_Percent

C. (Total_prospects + Total_donors = 100) AS Prospects_Percent

D. (“Total_prospects” , “Total_donors” * 100) AS Prospects_Percent

The correct answer is A. (Total_prospects / Total_donors * 100) AS Prospects_Percent. Explain: To identify the percentage of contributions from prospects, the correct query is:

(Total_prospects / Total_donors * 100) AS Prospects_Percent

Question 13

Scenario 2, continued

Your team creates a highly effective prospects list for Food Justice Rock Springs. After a few months, many of these prospects become donors. Now, Tayen wants to know the top three cities in which these new donors live. She will use that information to determine if it’s still true that people who live closer to Rock Springs are more likely to donate.

Which SQL query will retrieve the number of donors in each city, sorted high to low?

A.

B.

C.

D.

The correct answer is A. Explain: To retrieve the number of donors in each city, sorted high to low, the correct SQL query is:

SELECT COUNT(DonorID), City
FROM new_donor_list
GROUP BY City
ORDER BY COUNT(DonorID) DESC

3rd grade

Passed 100%

Question 1

Scenario 1, Questions 1-7

For the past six months, you have been working for a direct-mail marketing firm as a junior marketing analyst. Direct mail is advertising material sent to people through the mail. These people can be current or prospective customers, clients, or donors. Many charities depend on direct mail for financial support.

Your company, Directly Dynamic, creates direct-mail pieces with its in-house staff of graphic designers, expert mail list services, and on-site printing. Your team has just been hired by a local nonprofit, Food Justice Rock Springs. The mission of Food Justice Rock Springs is to eliminate food deserts by establishing local gardens, providing mobile pantries, educating residents, and more. Click below to read the email from Tayen Bell, vice president of marketing and outreach.

C5 Course Challenge, Email From Tayen Bell, Directly Dynamic.pdf

You begin by reviewing the dataset. To use the template for this dataset, click the link below and select “Use Template.”

Link to template: Dynamic Dataset

Or, if you don’t have a Google account, download the file directly from the attachment below.

Dynamic Dataset

The client has asked you to send two separate mailings: one to people within 50 miles of Rock Springs; the other to anyone outside that area. So, to research each donor’s distance from the city, you first need to find out where all of these people live.

You could scroll through 209 rows of data, but you know there is a more efficient way to organize the cities.

Which of the following procedures will enable you to sort your spreadsheet by city (Column K) in ascending order?

A. Sort Range by Column K from Z to A

B. Sort Range by Column K from A to Z

C. Sort Sheet by Column K from A to Z

D. Sort Sheet by Column K from Z to A

The correct answer is C. Sort Sheet by Column K from A to Z. Explain: To sort your spreadsheet by city in ascending order, Sort Sheet by Column K from A to Z. You can also use the SORT function syntax =SORT(A2:R210, 11, TRUE).

Question 2

Scenario 1, continued

You notice that many cells in the city column, Column K, are missing a value. So, you use the zip codes to research the correct cities. Now, you want to add the cities to each donor’s row. However, you are concerned about making a mistake, such as a spelling typo.

Fill in the blank: To add drop-down lists to your worksheet with predetermined options for each city name, you decide to use _____.

A. the find tool

B. VLOOKUP

C. data validation

D. the LIST function

Explain: To add drop-down lists to your worksheet with predetermined options for each city name, you decide to use data validation.

Question 3

Scenario 1, continued

Now, you decide to address Tayen’s request to include a handwritten note in the direct-mail piece for anyone who gave at least $100 last year.

To change how cells appear, you use conditional formatting. Then, to highlight cells with a value of at least 00, you choose to format cells if they have a value greater than or equal to 100.

A. True

B. False

Explain: To change how cells appear, you would use conditional formatting. Then, choose to format cells if they are greater than or equal to 100.

Question 4

Scenario 1, continued

At this point, you notice that the information about state and zip code is in the same cell. However, your company’s mailing list software requires states to be on a separate line from zip codes.

What function will enable you to move the 2-character state abbreviation in cell L2 into its own column?

A. =RIGHT(L2,2)

B. =RIGHT(2,L2)

C. =LEFT(2,L2)

D. =LEFT(L2,2)

The correct answer is D. =LEFT(L2,2). Explain: To move the 2-character state abbreviations in Column L into their own column, use the LEFT function: =LEFT(L2,2).

Question 5

Scenario 1, continued

Next, you duplicate your dataset twice using the Sheet Menu. You rename the first sheet Donation Form List, and you remove the cities that are further than 50 miles from Rock Springs. You rename the second sheet Postcard List, and you remove the cities that are within 50 miles of Rock Springs.

Then, you import these datasets into your company’s mailing list database. In a mailing list database, you create two tables: Donation_Form_List and Postcard_List. You decide to clean the Donation_Form_List first.

Your company’s mailing list software requires units to be on the same line as street addresses. However, they are currently in two separate columns (street_address and unit).

What portion of your SQL statement will instruct the database to combine these two columns into a new column called “address”?

A. JOIN(street_address, " to ", unit) AS address

B. CONCAT(street_address to unit) AS address

C. JOIN(street_address to unit) AS address

D. CONCAT(street_address, " to ", unit) AS address

The correct answer is D. CONCAT(street_address, " to ", unit) AS address. Explain: The portion of your SQL statement used to instruct the database to combine these two columns into a new column called “address” is CONCAT(street_address, " to ", unit) AS address.

Question 6

Scenario 1, continued

Your database contains people who live in many areas of Wyoming. However, it’s important to align your in-house data with the data from Food Justice Rock Springs. You also need to separate your data into the two lists: Donation_Form_List and Postcard_List. They will be based on each city’s distance from Rock Springs.

What SQL function do you use to select all data from the Donation_Form_List organized by zip code?

A. ORDER BY

B. ORGANIZE

C. SEQUENCE

D. ARRANGE BY

The correct answer is A. ORDER BY. Explain: To select all data from the Donation_Form_List organized by zip code, you use the ORDER BY function. The ORDER BY function sorts results returned in a query.

Question 7

Scenario 1, continued

You finish cleaning your datasets, so you decide to review Tayen’s email one more time to make sure you completed the task fully. It’s a good thing you checked because you forgot to identify people who have served on the board of directors or board of trustees. She wants to write them a thank-you note, so you need to locate them in the database.

To retrieve only those records that include people who have served on the board of trustees or on the board of directors, you use the WHERE function. Which of the following SQL queries would return the needed information?

A.

SELECT *
FROM Donation_Form_List
WHERE Board_Member = 'True' AND Trustee = 'True'

B.

SELECT *
FROM Donation_Form_List
WHERE Board_Member != 'True' AND Trustee != 'True'

C.

SELECT *
FROM Donation_Form_List
WHERE Board_Member != 'True' OR Trustee != 'True'

D.

SELECT *
FROM Donation_Form_List
WHERE Board_Member = 'True' OR Trustee = "True"

The correct syntax is D.

Question 8

Scenario 2, Questions 8-13

Your company’s direct-mail campaign was very successful, and Food Justice Rock Springs has continued partnering with Directly Dynamic. One thing you’ve been working on is assigning all donors identification numbers. This will enable you to clean and organize the lists more effectively.

Meanwhile, another team member has been creating a prospect list that contains data about people who have indicated interest in getting involved with Food Justice Rock Springs. These people are also assigned a unique ID. Now, you need to compare your donor list with the dataset in your database and collect certain data from both.

What SQL function will combine RIGHT and LEFT JOIN to return all matching records and create additional null values in a table of non-matching records in both tables?

A. INNER JOIN

B. LEFT JOIN

C. RIGHT JOIN

D. OUTER JOIN

The correct answer is D. OUTER JOIN. Explain: An OUTER JOIN function will combine RIGHT and LEFT JOIN to return all matching records in both tables.

Question 9

Scenario 2, continued

Your next task is to identify the average contribution given by donors over the past two years. Tayen will use this information to set a donation minimum for inviting donors to an upcoming event.

You start with 2019. To return average contributions in 2019 (contributions_2019), you use the AVG function. What section of a SQL query will instruct the database to find this average and store it in the AvgLineTotal variable?

A. AVG(contributions_2019) = “AvgLineTotal”

B. AVG(contributions_2019) AS AvgLineTotal

C. AVG(“contributions_2019”) WHERE AvgLineTotal

D. AVG(“contributions_2019”) IN AvgLineTotal

The correct answer is B. AVG(contributions_2019) AS AvgLineTotal. Explain: To return average contributions in 2019, the correct portion of the SQL query is:

AVG(contributions_2019) AS AvgLineTotal

Question 10

Scenario 2, continued

Now that you provided her with the average donation amount, Tayen decides to invite 50 people to the grand opening of a new community garden. You return to your New Donor List spreadsheet to determine how much each donor gave in the past two years. You will use that information to identify the 50 top donors and invite them to the event.

What syntax adds the contribution amounts in cells O2 and P2? Select all that apply.

• =O2,P2
• =O2+P2
• =SUM(O2,P2)
• =(O2/P2)

Explain: To add cells O2 and P2, use the function =SUM(O2,P2). You can also use the formula =O2+P2.

Question 11

Scenario 2, continued

Tayen informs you that she’s thinking about inviting anyone who donated at least $100 in 2018, as well. However, she only has five open spaces. She asks you to report how many people gave at least $100 so she can determine if they can also be invited to the event.

What is the correct syntax to count how many donations of 00 or greater appear in Column O (Contributions 2018)?

A. =COUNTIF(O2:O210>=100)

B. =COUNTIF(O2:O210,">=100")

C. =COUNTIF(O2:O210,>=100)

D. =COUNTIF(O2:O210">=100")

The correct answer is B. =COUNTIF(O2:O210,">=100"). Explain: To count how many donations of $100 or greater appear in Column Q, the correct syntax is =COUNTIF(O2:O210,">=100").

Question 12

Scenario 2, continued

The community garden grand opening was a success. In addition to the 55 donors Food Justice Rock Springs invited, 20 other prospects attended the event. Now, Tayen wants to know more about the donations that came in from new prospects compared to the original donors.

What is the section of a SQL query that will calculate the percentage of contributions from prospects?

A. (Total_prospects + Total_donors = 100) AS Prospects_Percent

B. (“Total_prospects” , “Total_donors” * 100) AS Prospects_Percent

C. (Total_prospects * Total_donors / 100) AS Prospects_Percent

D. (Total_prospects / Total_donors * 100) AS Prospects_Percent

The correct answer is D. (Total_prospects / Total_donors * 100) AS Prospects_Percent. Explain: To identify the percentage of contributions from prospects, the correct query is:

(Total_prospects / Total_donors * 100) AS Prospects_Percent

Question 13

Scenario 2, continued

Your team creates a highly effective prospects list for Food Justice Rock Springs. After a few months, many of these prospects become donors. Now, Tayen wants to know the top three cities in which these new donors live. She will use that information to determine if it’s still true that people who live closer to Rock Springs are more likely to donate.

What clause do you add to the following query to sort the donors in each city from high to low?

A. ORDER BY CITY(DonorID) ASC

B. ORDER BY COUNT(DonorID) DESC

C. ORDER BY COUNT(DonorID) ASC

D. ORDER BY CITY(DonorID) DESC

The correct answer is B. ORDER BY COUNT(DonorID) DESC. Explain: To retrieve the number of donors in each city, sorted high to low, the correct SQL query is: