## Week 4 – Course challenge – Shuffle Q/A 1

• True
• False

• INNER JOIN
• OUTER JOIN
• LEFT JOIN
• RIGHT JOIN

#### 19. Scenario 2, continuedYour 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?

• AVG(“contributions_2019”) IN AvgLineTotal
• AVG(contributions_2019) AS AvgLineTotal
• AVG(“contributions_2019”) AS AvgLineTotal
• AVG(contributions_2019) = “AvgLineTotal”

#### 20. Scenario 2, continuedTayen 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 \$100 or great appear in Column O?

• =COUNTIF(02:2010,”<=100”)
• =COUNTIF(O2:O210,”>=100″)
• =SUMIF(02:2010,”>=100”)
• =SUMIF(O2:2010,”>=100″)

#### 22. Scenario 2, continuedYour 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?

• ORDER BY CITY(DonorID) ASC
• ORDER BY COUNT(DonorID) DESC
• ORDER BY CITY(DonorID) DESC
• ORDER BY COUNT(DonorID) ASC

#### 23. Scenario 1, Questions 1-7For 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.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 DatasetOr, if you don’t have a Google account, download the file directly from the attachment below.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 tools will enable you to sort your spreadsheet by city (Column K) in ascending order?

• Sort Range by Column K from A to Z
• Sort Range by Column K from Z to A
• Sort Sheet by Column K from A to Z
• Sort Sheet by Column K from Z to A

#### 24. Scenario 1, continuedNow, 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?

• Select Column M. Then, select Format > Conditional Formatting. Choose to format cells if they are greater than 100.
• Select Column M. Then, select Format > Conditional Formatting. Choose to format cells if text starts with 100.
• Select Column M. Then, select Format > Conditional Formatting. Choose to format cells if text contains 100.
• Select Column M. Then, select Format > Conditional Formatting. Choose to format cells if they are greater than or equal to 100.

• True
• False

#### 26. Scenario 1, continuedYou 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, what clause do you include in your query?

• WHERE Board_Member = “TRUE” AND Trustee = “TRUE”
• WHERE Board_Member = “TRUE” OR Trustee = “TRUE”
• WHERE Board_Member = TRUE AND Trustee = TRUE
• WHERE Board_Member = TRUE, Trustee = TRUE

• =RIGHT(L2,2)
• =RIGHT(2,L2)
• =LEFT(2,L2)
• =LEFT(L2,2)

## Shuffle Q/A 2

#### 29. Scenario 1, continuedYou 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?

• List
• VLOOKUP
• Data validation
• Find

• SEQUENCE
• ORGANIZE
• ARRANGE BY
• ORDER BY

### Devendra Kumar

Project Management Apprentice at Google