Analyze Data to Answer Questions
Weekly challenge 1
1.
Question 1
Fill in the blank: The _____ phase of the data analysis process includes organizing data, formatting and adjusting data, getting input from others, and transforming data by observing relationships between data points and making calculations.
1 / 1 point
act
process
prepare
analyze
2.
Question 2
During which of the four phases of analysis do you gather the relevant datasets into an usable structure for a project?
1 / 1 point
Organize data
Format and adjust data
Get input from others
Transform data
3.
Question 3
You are performing a calculation during your analysis of a dataset. Which phase of analysis are you in?
1 / 1 point
Transform data
Get input from others
Format and adjust data
Organize data
4.
Question 4
Fill in the blank: Sorting ranks data based on a specific _____ that you select.
1 / 1 point
metric
calculation
model
observation
5.
Question 5
A data analyst is sorting spreadsheet data. They use the spreadsheet tool Sort Sheet. What does this tool do?
1 / 1 point
It allows the analyst to sort by a specific sorted row.
It sorts all of the data in a spreadsheet by a specific sorted column.
It allows the analyst to sort a specific selection of cells only.
It sorts all of the data in a spreadsheet by the ranking of a specific sorted row.
6.
Question 6
A data analyst sorts a spreadsheet range between cells A1 and E50. They sort in descending order by the fourth column, Column D. What is the syntax they are using?
1 / 1 point
=SORT(A1:E50, 4, TRUE)
=SORT(A1:E50, D, TRUE)
=SORT(A1:E50, D, FALSE)
=SORT(A1:E50, 4, FALSE)
7.
Question 7
You are querying a database that contains data about music. Each album is given an ID number. You are only interested in data related to the album with ID number 3. The album IDs are listed in the album_id column.
You write the following SQL query, but it is incorrect. What is wrong with the query?
SELECT * FROM Track WHERE album = 3
1 / 1 point
In line 3, album is not capitalized.
In line 3, album should be album_id.
SELECT, FROM, and WHERE should are capitalized.
Line 3 contains an equal sign.
8.
Question 8
You write the SQL query below. However this query is incorrect. What is wrong with it?
SELECT * FROM invoice WHERE billing_city = “Chicago” ORDER total
1 / 1 point
Line 4 is missing column = between ORDER and total.
Line 4 is missing the BY between ORDER and total.
SELECT, FROM, WHERE, and ORDER are capitalized.
In line 3, “Chicago” has quotation marks.
Weekly challenge 2
1.
Question 1
An analyst has financial data that is formatted as Canadian dollars, but it should be formatted as U.S. dollars. What spreadsheet tool can help them select the right format?
1 / 1 point
Format as Currency
Format as Money
Format as Dollars
Format as Number
2.
Question 2
A data analyst allows their colleagues access to their data spreadsheet. In order to protect the structured data and formulas from accidental changes, what spreadsheet tool can the data analyst use?
1 / 1 point
Data validation
Find
Pop-up menus
Conditional formatting
3.
Question 3
A utility company uses a spreadsheet to track the number of consecutive months each customer has paid their bill on time. They use a spreadsheet tool to apply color to the cells when the number of consecutive months is 12 or greater. What tool are they using?
1 / 1 point
Add color
Data validation
CONVERT
Conditional formatting
4.
Question 4
You are working with a SQL database with tables for flight routes in Canada. The table contains one column with the names of the departure airports. A different column in the same table contains the names of the arrival airports. What function can you use in your query to combine the arrival and departure airport names into a new column?
1 / 1 point
JOIN
CONCAT
GROUP
COMBINE
5.
Question 5
You are querying a database of automobile sales to determine how many SUV models were available in green. For your project, you only need the first 50 records. What clause should you add to the following SQL query?
SELECT SUV FROM vehicle_table WHERE color = “green”
1 / 1 point
LIMIT,50
LIMIT_50
LIMIT = 50
LIMIT 50
6.
Question 6
A data analyst is working with a spreadsheet that has very long text strings. Rather than counting the characters themselves to determine the number of characters they contain, what tool can they use?
1 / 1 point
The LEN function
The MID function
The CHAR function
The COUNT function
7.
Question 7
Spreadsheet cell H8 contains the text string “Marketing”. To return the substring “market”, what is the correct syntax?
1 / 1 point
=RIGHT(6,H8)
=RIGHT(H8, 6)
=LEFT(H8, 6)
=LEFT(6,H8)
8.
Question 8
You are using the FIND function to identify the position of the whitespace in the string in cell A6. Which of the following is the correct function syntax for this purpose?
1 / 1 point
FIND(A6, “ “)
FIND(“ “, A6)
FIND(A6, _ )
FIND(“_”, A6)
Weekly challenge 3
1.
Question 1
A data analyst is working with data that has been collected over time and stored in different databases. What process must they perform if they are to calculate the statistics of this data?
1 / 1 point
Data aggregation
Data grouping
Data mapping
Data composition
2.
Question 2
A data analyst is performing numerical calculations on the data in their spreadsheet. Ahead of these calculations, they use the VALUE function. Why might they do this?
1 / 1 point
To find the average of all the numbers in the spreadsheet
To get a list of all the distinct numbers in the data
To convert the numbers in the data from text to numerical values
To sum up all the numbers in the spreadsheet
3.
Question 3
A data analyst is using a VLOOKUP function in a column in their spreadsheet. They enter the function in a column to the left of the data columns. Why would they do this?
1 / 1 point
VLOOKUP will only search values in columns to the right of the column it is entered into.
VLOOKUP will then search the data twice.
VLOOKUP will return all the values found to the left of its column.
VLOOKUP will delete the values in the columns to the left of its column.
4.
Question 4
A data analyst uses an absolute reference to lock a function array so rows and columns don’t change if the function is copied. What symbol is used to create an absolute reference?
1 / 1 point
Dollar sign ($)
Ampersand (&)
Hashtag (#)
Asterisk (*)
5.
Question 5
The following is a selection from a spreadsheet:
N/A | A | B | C |
1 | Country | Population in 2020 | Growth in population 2000-2020 |
2 | China | 1,439,323,776 | 13.4 % |
3 | India | 1,380,004,385 | 37.1 % |
4 | United States | 331,002,651 | 17.3 % |
5 | Indonesia | 273,523,615 | 27.7% |
6 | Pakistan | 220,892,340 | 44.9% |
7 | Brazil | 212,559,417 | 21.9% |
8 | Nigeria | 206,139,589 | 66.3% |
9 | Bangladesh | 164,689,383 | 27.9% |
10 | Russia | 145,934,462 | -0.8% |
To search for the population of Pakistan, what is the correct VLOOKUP syntax?
1 / 1 point
=VLOOKUP(Pakistan, A2*B10, 2, false)
=VLOOKUP(Pakistan, A2:B10, 3, false)
=VLOOKUP(“Pakistan”, A2:B10, 2, false)
=VLOOKUP(“Pakistan”, A2:B10, 3, false)
6.
Question 6
When creating a SQL query, which JOIN clause returns only records with matching values in two or more database tables?
1 / 1 point
LEFT
OUTER
RIGHT
INNER
7.
Question 7
The COUNT DISTINCT function includes repeating values when returning values in a specified range.
1 / 1 point
True
False
8.
Question 8
Which of the following terms describe a subquery? Select all that apply.
1 / 1 point
Nested query
Inner select
Inner query
Small query
Weekly challenge 4
1.
Question 1
A data analyst wants to calculate the number of rows that have a SKU value of “K102145”. Which function could they use?
1 / 1 point
=COUNTIF(K102145=G2:G30)
=COUNTIF(G2:G30,“=K102145”)
=COUNTIF(G2:G30,K102145)
=COUNTIF(G2:G30,“K102145”)
2.
Question 2
A data analyst wants to use a single function to multiply two ranges and then add the multiplied values. What single function can they use to accomplish this?
1 / 1 point
SUM
SUMIFS
SUMPRODUCT
SUMIF
3.
Question 3
What is the purpose of using pivot tables?
1 / 1 point
To allow quick copying from one table to another
To allow the use of SQL in spreadsheets
To view data in multiple ways to find insights and trends
To multiply two arrays and add the results
4.
Question 4
How many different columns have been added to the values section of the pivot table editor?
Direction | |||
Date | Values | Down | Up |
2/3 | MAX of A | 300 | 100 |
MIN of C | 12 | 1 | |
2/4 | MAX of A | 100 | 100 |
MIN of C | 14 | 19 | |
2/5 | MAX of A | 450 | |
MIN of C | 9 |
0 / 1 point
1
6
2
3
Review the video on pivot tables.
5.
Question 5
What is the purpose of the EXTRACT function in SQL?
1 / 1 point
Return a specific portion of a date
Return a specific key-value pair from a JSON object
Calculate the mathematical extract operation
Calculate using data extracted from other tables
6.
Question 6
When writing custom calculations in SQL, what characters can be used to group calculations to change the order of calculation?
1 / 1 point
Parentheses – ( )
Curly Braces – { }
Square Brackets – [ ]
Quotation Marks – “ “
7.
Question 7
What is the process of checking and rechecking the quality of your data so that it is complete, accurate, secure, and consistent?
1 / 1 point
Data augmentation
Data validation
Data visualization
Data-driven development
8.
Question 8
What is the purpose of the <> operator in SQL?
1 / 1 point
To add two values
To return the remainder of a division operation
To check if two values are not equal
To set a value equal to another
9.
Question 9
What is a reason to use a WITH AS clause in a SQL statement?
1 / 1 point
The result is temporary.
The result is a pivot table.
The result calculates faster.
The result is a visualization.
10.
Question 10
Which of the following SQL statements correctly implements a WITH AS clause?
1 / 1 point
WITH AS my_table AS SELECT * FROM other_table;
WITH my_table AS SELECT * FROM other_table;
WITH AS my_table AS (SELECT * FROM other_table);
WITH my_table AS (SELECT * FROM other_table);
Course challenge
1.
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.
![](https://d3c33hcgiwev3.cloudfront.net/imageAssetProxy.v1/WaEfVvs2Q9qhH1b7NqPaTw_f88507339bd64f958375b05add6117f8_dotted-line-right.png?expiry=1680220800000&hmac=-5C2ea4VtQMAWsxiX4yHldAejatI_UH2V4wlW_-4Wlg)
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 functions will enable you to sort your spreadsheet by city (Column K) in ascending order?
0 / 1 point
=SORT(A2:R210, K, TRUE)
=SORT(A2:R210, 11, TRUE)
=SORT(A2:R210, 11, ASC)
=SORT(A2:R210, K, ASC)
2.
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 _____.
1 / 1 point
the find tool
data validation
the LIST function
VLOOKUP
You decide to use data validation. Data validation allows you to control what can and cannot be entered in your worksheet in order to avoid typos. It does this by adding drop-down lists with predetermined options, such as each city name.
3.
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?
1 / 1 point
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 text starts with 100.
Select Column M. Then, select Format > Conditional Formatting. Choose to format cells if they are greater than or equal to 100.
Select Column M. Then, select Format > Conditional Formatting. Choose to format cells if they are greater than 100.
4.
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?
1 / 1 point
=LEFT(5,L2)
=RIGHT(L2,5)
=LEFT(L2,5)
=RIGHT(5,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).
What portion of your SQL statement will instruct the database to combine these two columns into a new column called “address”?
1 / 1 point
CONCAT(street_address to unit) AS address
JOIN(street_address to unit) AS address
CONCAT(street_address, ” to “, unit) AS address
JOIN(street_address, ” to “, unit) AS address
6.
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?
1 / 1 point
ARRANGE BY
ORGANIZE
SEQUENCE
ORDER BY
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.
7.
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, what is the correct query?
1 / 1 point
SELECT * FROM Donation_Form_List WHERE Board_Member = TRUE, Trustee = TRUE
SELECT * FROM Donation_Form_List WHERE Board_Member = “TRUE” AND Trustee = “TRUE”
SELECT * FROM Donation_Form_List WHERE Board_Member = “TRUE” OR Trustee = “TRUE”
SELECT * FROM Donation_Form_List WHERE Board_Member = TRUE AND Trustee = TRUE
8.
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 records with matching values in both tables?
1 / 1 point
INNER JOIN
OUTER JOIN
LEFT JOIN
RIGHT JOIN
9.
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?
1 / 1 point
AVG(contributions_2019) = “AvgLineTotal” |
AVG(“contributions_2019”) IN AvgLineTotal |
AVG(contributions_2019) AS AvgLineTotal |
AVG(“contributions_2019”) WHERE AvgLineTotal |
10.
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?
1 / 1 point
=SUM(O2,P2)
=SUM(O2*P2)
=SUM(O2/P2)
=SUM(“O2,P2”)
11.
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.
Which spreadsheet function do you use to count how many donations of $100 or greater appear in Column O (Contributions 2018)?
1 / 1 point
COUNTIF
TOTAL
MAX
SUMIF
12.
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.
This SQL query can be used to identify the percentage of contributions from prospects compared to total donors:
![query](https://d3c33hcgiwev3.cloudfront.net/imageAssetProxy.v1/6e410dbc-381b-47e3-8382-351a96a321f8image18.png?expiry=1680220800000&hmac=ahSFwdYOBuRjndPHvqsXn2yA1prmxJ8qaI_jLyOFBYg)
0 / 1 point
True
False
13.
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?
1 / 1 point
![query](https://d3c33hcgiwev3.cloudfront.net/imageAssetProxy.v1/6e410dbc-381b-47e3-8382-351a96a321f8image20.png?expiry=1680220800000&hmac=p1rWXR1yslruroxhu2sLdGpefNuANVr7_aWoyff2n-A)