Google Data Analytics: Comprehensive Course Guide Q&A

Google Data Analytics - Analyze Data to

Answer Questions - Course 5

Question 1)

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? - ANSWERSort Sheet by Column K from A to Z




Question 1a)

Which of the following functions will enable you to sort your spreadsheet by city

(Column K) in ascending order? - ANSWER=SORT(A2:R210, 11, TRUE)




Question 2)

, 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? - ANSWERData validation




Question 3)

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 spreadsheet tools will enable you to change how cells appear if

they contain a value of $100 or more? - ANSWERConditional formatting




Question 4)

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? -

ANSWER=RIGHT(L2,5)




Question 5)

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).




Which SQL function will instruct the database to combine two columns into a new

column called "address"? - ANSWERCONCAT




Question 6)

, 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. To select all data from the

Donation_Form_List organized by zip code, you use the ORDER BY function. The syntax is:




SELECT *

FROM Donation_Form_List

ORDER BY zip_code




True or False? - ANSWERTrue




Question 7)

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.