Course 5: Analyze Data to Answer Questions, all weekly challenge quiz answers of this course are provided in this article from week 1 to week 4 to help students solving this exam.

Q1. In the data analysis process, which of the following refers to a phase of analysis? Select all that apply.

• Visualize the data
• Organize data into understandable sections
• Get input from others
• Format data using sorts and filters

Q2. During which phase of analysis can you find a correlation between two variables?

• Get input from others
• Organize data
• Transform data

Q3. You are performing a calculation during your analysis of a dataset. Which phase of analysis are you in?

• Transform data
• Get input from others
• Organize data

Q4. Typically, a data analyst uses filters when they want to expand the amount of data they are working with.

• True
• False

Q5. A data analyst is sorting spreadsheet data. They want to make sure that, when they rearrange the data, data across rows is kept together. What technique should they use to sort the data?

• Sort Column
• Sort Sheet
• Sort Together
• Sort Rows

Q6. A data analyst uses a function to sort a spreadsheet range between cells H1 and K65. They sort in ascending order by the first column, Column H. What is the syntax they are using?

• =SORT(H1:K65, 1, TRUE)
• =SORT(H1:K65, A, FALSE)
• =SORT(H1:K65, A, TRUE)
• =SORT(H1:K65, 1, FALSE)

Q7. A data analyst is querying a database that contains data about dental equipment inventory. They are only interested in data related to cleaning products. Which of the following sections of an SQL statement would return the correct result?

• WHERE “Cleaning”
• WHERE product = “Cleaning”
• ORDER BY “Cleaning”
• ORDER BY product = “Cleaning”

Q8. A data analyst would write the following section of a SQL query to sort Golden Retrievers, ordered by birth date, in ascending order:

WHERE Breed = "Golden Retriever" ORDER BY Birth_date

• True
• False

Q1. An analyst notes that the “160” in cell A9 is formatted as text, but it should be Australian dollars. What spreadsheet tool can help them select the right format?

• CURRENCY
• Format as Currency
• EXCHANGE
• Format as Dollar

Q2. You are creating a spreadsheet to help you with your job search. Every time you find an interesting job, you add it to the spreadsheet. Then, you want to indicate two possible options: Need to Apply or Applied. What spreadsheet tool will save you time by enabling you to create a dropdown list with Need to Apply and Applied as the possible options?

• Data validation
• FIND
• Conditional formatting

Q3. You are using a spreadsheet to keep track of your newspaper subscriptions. You add color to indicate if a subscription is current or has expired. Which spreadsheet tool changes how cells appear when values meet each expiration date?

• CONVERT
• Data validation
• Conditional formatting

Q4. A data analyst wants to write a SQL query to combine data from two columns and into a new column. What function can they use?

• CONCAT
• JOIN
• COMBINE
• GROUP

Q5. You are querying a database of ice cream flavors to determine which stores are selling the most mint chip. For your project, you only need the first 80 records. What clause should you add to the following SQL query?

SELECT flavors FROM ice_cream_table WHERE flavor = "mint_chip"

• LIMIT = 80
• LIMIT_80
• LIMIT,80
• LIMIT 80

Q6. A data analyst is working with a spreadsheet that has very long text strings. They use a function to count the number of characters in cell G11. What is the correct syntax?

• =LEN(G,11)
• =LEN(G11)
• =LEN(G:G11)
• =LEN(“G11”)

Q7. Spreadsheet cell L6 contains the text string “Function.” To return the substring “Fun,” what is the correct syntax?

• =RIGHT(3,L6)
• =LEFT(L6, 3)
• =RIGHT(L6, 3)
• =LEFT(3,L6)

Q8. Fill in the blank: When working with a database, data analysts can use the _____ function to locate specific characters in a string.

• IDENTIFY
• WHERE
• FIND
• FROM

Q1. Fill in the blank: Data aggregation involves creating a _____ collection of data that originally came from multiple sources.

• modified
• summarized
• localized
• expanded

Q2. A data analyst uses the SUM function to add together numbers from a spreadsheet. However, after getting a zero result, they realize the numbers are actually text. What function can they use to convert the text to a numeric value?

• FIGURE
• DIGIT
• VALUE
• CONVERT

Q3. When using VLOOKUP, there are some common limitations that data analysts should be aware of. One of these limitations is that VLOOKUP can only return a value from the data to the left of the matched value.

• True
• False

Q4. Fill in the blank: When writing a function, a data analyst wraps a table array in dollar signs. This is an _____ , which is used to lock the array so rows and columns don’t change if the function is copied.

• arbitrary reference
• accurate reference
• absolute reference
• authentic reference

Q5. The following is a selection from a spreadsheet:

 A B C 1 Country Population in 2020 (millions) 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?

• =VLOOKUP(Pakistan, A2:B10, 3, false)
• =VLOOKUP(“Pakistan”, A2:B10, 3, false)
• =VLOOKUP(Pakistan, A2*B10, 2, false)
• =VLOOKUP(“Pakistan”, A2:B10, 2, false)

Q6. When creating a SQL query, which JOIN clause returns all matching records in two or more database tables?

• OUTER
• RIGHT
• INNER
• LEFT

Q7. A data analyst writes a query that asks a database to return only distinct values in a specified range, rather than including repeating values. Which function do they use?

• RETURN
• COUNT DISTINCT
• RETURN VALUES
• COUNT

Q8. Which of the following terms describe a subquery? Select all that apply.

• Inner select
• Nested query
• Inner query
• Small query

Q1. You are analyzing sales data in a spreadsheet. Which of the following could you find out by using the MAX function?

• Total sales for the year
• Difference between two months of sales
• The month with the highest sales
• Sales per month over a year

Q2. A data analyst is working with a spreadsheet from a furniture company.

The analyst inputs a function to find the number of product prices that are less than \$150.00. Which formula will return that result?

• =SUMIF(G2:G30, “>150”)
• =COUNTIF(G2:G30, “<150”)
• =SUMIF(G2:G30, “<150”)
• =COUNTIF(G2:G30, “>=150”)

Q3. A data analyst is working in a spreadsheet and uses the SUMIF function in the formula below as part of their analysis.

=SUMIF(A1:A25, "<10", C1:C25)

Which part of this formula is the criteria or condition?

• “<10”
• A1:A25
• C1:C25
• =SUMIF

Q4. A data analyst is working in a spreadsheet and uses the SUMPRODUCT function in the formula below as part of their analysis.

=SUMPRODUCT(A2:A10,B2:B10)

How does the SUMPRODUCT function calculate the cell ranges identified in the parentheses?

• It multiplies the values in the first range, then multiplies the values in the second range.
• It adds the ranges, then multiplies them by the last value in the second array.
• It adds the values in the first range, then adds the values in the second range.
• It multiplies the ranges, then adds the sum of the products of the two ranges.

Q5. A data analyst creates a pivot table in a spreadsheet containing movie data.

If the analyst wants to summarize the data using the AVERAGE function in the Values menu, which spreadsheet columns could they add data from? Select all that apply.

• Box Office Revenue
• Budget
• Movie Title
• Genre

Q6. A data analyst uses the following SQL query to perform basic calculations on their data. Which types of operators is the analyst using in this SQL query? Select all that apply.

SELECT

Yes_Responses,

No_Responses,

Total_Surveys,

(Yes_Responses + No_Responses) / Total_Surveys AS Responses_Per_Survey

FROM

Survey_1

• Subtraction
• Multiplication
• Division

Q7. A data analyst uses the following query to perform a calculation on a company’s inventory. Which of the following will be the return in the “Overstock” column for this query?

SELECT

Total_Inventory % Total_Stores AS Overstock

FROM

Shipment_1

• The remainder when the values in “Total_Inventory” are divided by the values in “Total_Stores”
• The percentage of the “Total_Inventory” that is located in “Total_Stores”
• The difference between the values in “Total_Inventory” and the values in “Total_Stores”
• The combined total of the values in “Total_Inventory” and the values in “Total_Stores”

Q8. A data analyst completes a calculation in a SQL query using the AVG function. Which of the following best describes the return for this query?

SELECT

AVG (salary) AS avg_employee_salary

FROM

employees

WHERE

salary < 30000

• The number of all salaries in the “employees” table
• A single average of all of the salaries less than \$30,000
• A single count of salaries that average less than \$30,000
• The annual salary for each employee

Q9. Use the following SQL query to answer the question:

SELECT

location,

SUM(customer_orders) AS total_orders

FROM

bulk_orders

Which statement should you add after the FROM statement to organize rows by location?

• EXTRACT location
• WHERE location
• AS location
• GROUP BY location

Q10. Fill in the blank: The data validation process involves checking and rechecking the quality of your data to make sure that it is complete and _____. Select all that apply.

• cited
• accurate
• consistent
• secure