Module 3: Aggregate data for analysis

Looking answers for ‘analyze data to answer questions module 3 challenge’?

In this post, I provide accurate answers and detailed explanations for Module 3: Aggregate data for analysis of Course 5: Analyze Data to Answer Questions – Google Data Analytics Professional Certificate.

Whether you’re preparing for quizzes or brushing up on your knowledge, these insights will help you master the concepts effectively. Let’s dive into the correct answers and detailed explanations for each question.

Test your knowledge on VLOOKUP

Practice Quiz

1. To change a text string in spreadsheet cell F8 to a numerical value, what is the correct function?

  • =MATCH(F8)
  • =VALUE(F8) ✅
  • =CONVERT(F8)
  • =NUM(F8)

Explanation:
The VALUE function converts a text string that appears as a number into an actual numeric value.

  • =MATCH(F8) is used to find the position of a value in a range, not for converting text to a number.
  • =CONVERT(F8) is used to convert units (like distance or weight), not for changing text to numbers.
  • =NUM(F8) is not a valid function in spreadsheets.

2. What is the purpose of an absolute reference within a function, such as "$C$3"?

  • To lock rows and columns so they won’t change when a function is copied ✅
  • To remove unnecessary instructions from a formula or function
  • To make formulas and functions unconditional
  • To represent missing values in a formula or function

Explanation:
An absolute reference (e.g., $C$3) locks both the row and column in a formula. When you copy the formula, the reference to C3 will not change, unlike a relative reference that adjusts as you copy the formula to other cells.

  • Absolute references are not used to remove instructions or make formulas unconditional.
  • It does not represent missing values.

3. In VLOOKUP, TRUE tells the function to search for exact matches, and FALSE tells the function to look for approximate matches.

  • True
  • False ✅

Explanation:
In VLOOKUP, TRUE is used to search for approximate matches, while FALSE is used to search for exact matches.

  • This is the opposite of what the statement suggests, so the correct answer is False.

4. The following is a selection from a spreadsheet:

To search for the population of Nigeria, what is the correct VLOOKUP syntax?

  • =VLOOKUP(Nigeria, A2:C10, 3, false)
  • =VLOOKUP(Nigeria, A2,C10, 2, true)
  • =VLOOKUP(“Nigeria”, A2:C10, 2, false) ✅
  • =VLOOKUP(Nigeria, A2:C10, 3, true)

Explanation:
The correct VLOOKUP syntax is:

  • "Nigeria" is the lookup value.
  • A2:C10 is the range to search within.
  • 2 is the column number from which to return the value (Population is in column B, which is the 2nd column).
  • false specifies that the lookup should find an exact match.

5. The following is a selection from a spreadsheet:

To search for the height of the building in Mecca, what is the correct VLOOKUP syntax?

  • =VLOOKUP(“Mecca”, A2:D7, 3, false) ✅
  • =VLOOKUP(Mecca, A2:D7, 2, false)
  • =VLOOKUP(Mecca, A2,D7, 3, true)
  • =VLOOKUP(Mecca, A2:D7, 2, true)

Explanation:
The correct VLOOKUP syntax is:

  • "Mecca" is the lookup value.
  • A2:D7 is the range to search within.
  • 3 is the column number from which to return the value (Height is in column C, which is the 3rd column).
  • false specifies that the lookup should find an exact match.

Test your knowledge on using JOINS to aggregate data

Practice Quiz

6. A data analyst wants to retrieve only records from a database that have matching values in two different tables. Which JOIN function should they use?

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

Explanation:
An INNER JOIN retrieves only the records where there is a match in both tables. If a record in one table does not have a corresponding match in the other table, it will not be included in the result.

  • LEFT JOIN retrieves all records from the left table, along with matching records from the right table (if any).
  • OUTER JOIN (or FULL OUTER JOIN) retrieves records with matching values from both tables and also includes records that do not have matches in either table.
  • RIGHT JOIN is the opposite of LEFT JOIN, retrieving all records from the right table and matching records from the left table.

7. You are writing a SQL query to instruct a database to count values in a specified range. You only want to count each value once, even if it appears multiple times. Which function should you include in your query?

  • COUNT VALUES
  • COUNT DISTINCT ✅
  • COUNT RANGE
  • COUNT

Explanation:
The COUNT DISTINCT function counts the number of unique values in a specified range, ignoring duplicate values.

  • COUNT VALUES is not a valid SQL function.
  • COUNT RANGE is not a valid function in SQL.
  • COUNT counts all values, including duplicates.

8. A data analyst wants to temporarily name a column in their query to make it easier to read and write. What technique should they use?

  • Tagging
  • Filtering
  • Aliasing ✅
  • Naming

Explanation:
Aliasing is used to assign a temporary name to a column or table in a SQL query, often for the sake of readability and ease of use.

  • Tagging, Filtering, and Naming are not correct techniques in SQL for temporarily renaming a column.

Test your knowledge on working with subqueries

Practice Quiz

9. Which of the following queries contain subqueries? Select all that apply.


 

 

Explanation:

  • Subqueries are queries that are nested inside another query. The presence of a query inside parentheses in the WHERE, IN, or NOT IN clause indicates a subquery.
    • In the first query, there is no subquery because the query simply orders the data by employee_id and start_time.
    • The second, third, and fourth queries contain subqueries:
      • In the second query, the subquery returns a list of department_ids to match in the outer query.
      • In the third query, the subquery returns the maximum salary to match the price.
      • In the fourth query, the subquery returns a list of customer_ids to compare against the customer_id in the outer query.

10. Fill in the blank: A data analyst uses aliasing to make it easier to read and write a query. Aliasing involves temporarily _____ a table or column in a query.

  • copying
  • removing
  • hiding
  • naming ✅

Explanation:
Aliasing involves temporarily assigning a new name to a table or column within a query to make it easier to reference, especially in complex queries.

  • Copying, removing, and hiding are not accurate descriptions of aliasing.

11. When working with subqueries, the outer query executes first.

  • True
  • False ✅

Explanation:
When working with subqueries, the inner query (the subquery) executes first. Its result is then used by the outer query.

  • The inner query is executed and its results are returned to the outer query to perform the final operation.

Module 3 challenge

Graded Quiz

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

  • expanded
  • localized
  • modified
  • summarized ✅

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

  • VALUE ✅
  • FIGURE
  • CONVERT
  • DIGIT

14. 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 ✅

Explanation:
VLOOKUP can only search for a value in the first column of the data range and return a value from columns to the right. It cannot return a value from columns to the left of the matched value.

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

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

16. The following is a selection from a spreadsheet:

To search for the growth in population in Indonesia, what is the correct VLOOKUP syntax?

  • =VLOOKUP(“Indonesia”, A2:C10, 3, false) ✅
  • =VLOOKUP(Indonesia, A2*C10, 3, false)
  • =VLOOKUP(“Indonesia”, A2:C10, 2, false)
  • =VLOOKUP(Indonesia, A2:C10, 2, false)

17. An INNER JOIN is a function that returns records with matching values in two or more tables. An OUTER JOIN is a function that combines RIGHT and LEFT JOIN to return all matching records in both tables.

  • True ✅
  • False

18. A data analyst writes a query that asks a database to return the number of rows in a specified range. Which function do they use?

  • COUNT DISTINCT ✅
  • RANGE 
  • COUNT
  • RETURN RANGE

19. Fill in the blank: In a SQL statement, the _____ is the name of the segment that executes first. Select all that apply.

  • central select
  • inner query ✅
  • central query

  • inner select ✅

20. In data analytics, what is the process of gathering data from multiple sources and combining it into a single, summarized collection?

  • Data composition
  • Data aggregation ✅
  • Data grouping
  • Data mapping

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

  • 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
  • To find the average of all the numbers in the spreadsheet

22. You create a function using data values from a specified array. You notice that it works correctly only some of the time. You verify that the function was used correctly and you ask a colleague for their input. They ask if you locked the data array. What does this mean? Select all that apply.

  • The data array has been made an absolute reference. ✅
  • The columns in the array cannot be changed. ✅
  • The data is accessible with a password.
  • The rows in the array cannot be changed. ✅

23. The following is a selection from a spreadsheet:

To search for the population of Bangladesh, what is the correct VLOOKUP syntax?

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

Explanation: The correct VLOOKUP function syntax is:

  • "Bangladesh": the value you are looking for.
  • A2:B10: the range of data where the lookup will happen.
  • 2: the column number from which to return the result (Population in 2020 is in column 2).
  • false: ensures an exact match.

24. A data analyst writes a query in SQL with the RIGHT JOIN function

FROM fiction_table
RIGHT JOIN
books_table

What does this function do?

  • It returns all the records in the fiction table and only the records from the books table with matching values.
  • It returns all records in both the fiction table and the books table.
  • It returns only the records with values that match from both tables.
  • It returns all records in the books table and only the records from the fiction table with matching values. ✅

25. The COUNT DISTINCT function includes repeating values when returning values in a specified range.

  • True
  • False ✅

Explanation:
 The COUNT DISTINCT function counts only unique values in a specified range, excluding duplicates.

26. A data analyst writes a query in SQL. Inside this query, they have a second query. What is this second query called? Select all that apply.

  • Subquery ✅
  • Central query
  • Smaller query
  • Nested query ✅

27. One of the limitations of the VLOOKUP function is that it can only search columns to the right of the column into which it is entered. What is another limitation of VLOOKUP?

  • It will only return the last match it finds.
  • It can only be used on numerical data.
  • It can only be used with text data.
  • It will only return the first match it finds. ✅

28. A data analyst wraps the data array for their function in dollar signs ($). What does this do? Select all that apply.

  • It converts the data to currency.
  • It makes it so that columns cannot be changed. ✅
  • It makes it so that rows cannot be changed. ✅
  • It creates an absolute reference. ✅

29. The following is a selection from a spreadsheet:

To search for the population of Brazil, what is the correct VLOOKUP syntax?

  • =VLOOKUP(“Brazil”, A2:B10, 2, false) ✅
  • =VLOOKUP(Brazil, A2:B10, 2, false)
  • =VLOOKUP(Brazil, A2,B10, 3, false)
  • =VLOOKUP(Brazil, A2:B10, 3, false)

30. You are writing a query that contains the COUNT function. What should this query return?

  • The number of rows in a specified range ✅
  • The number of times the query has been run
  • The sum of all values in a specified range
  • The number of columns in a specified range

31. A data analyst wants to be sure all of the numbers in a spreadsheet are numeric. What function should they use to convert text to numeric values?

  • VALUE ✅
  • PROCESS
  • CONVERT
  • EXCHANGE

32. The following is a selection from a spreadsheet:

To search for the population of Pakistan, what is the correct VLOOKUP syntax?

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

33. A data analyst writes the following query in SQL with the LEFT JOIN function:

FROM music_table
LEFT JOIN
Entertainment_table

What does this function do?

  • It returns all records in the music table and only the records from the entertainment table with matching values. ✅
  • It returns only the records with values that match from both tables.
  • It returns all the records in the entertainment table and only the record from the music table with matching values.
  • It returns all records in both the music table and the entertainment table.

34. When working with subqueries, which part of the query segment executes first?

  • The inner query ✅
  • The smaller query
  • The outer query
  • The larger query

35. In data analytics, what is data aggregation?

  • The process of moving certain data points to a higher rank or position.
  • The process of modifying data in order to make it suitable for analysis.
  • The process of ensuring a company’s data is properly stored, managed, and maintained.
  • The process of gathering data from multiple sources and combining it into a single, summarized collection. ✅

36. VLOOKUP can have problems when used on data values that have leading and trailing spaces. What function can be used to eliminate these spaces?

  • TRIM ✅
  • NOSPACE
  • VALUE
  • CUT

37. You are using the VLOOKUP function in a specific column in your spreadsheet. You know that one of VLOOKUP’s limitations is that it can only search in columns to the right of the column into which it is entered. What can you do if you also want the function to search the data found to the left?

  • Use VLOOKUP in the leftmost column
  • Use VLOOKUP in the rightmost column
  • Copy that data into new columns to the right
  • Make the data into an absolute reference

38. A data analyst creates an absolute reference around a function array. What is the purpose of the absolute reference?

  • To automatically change numeric values to currency values
  • To keep a function array consistent so rows and columns will automatically change if the function is copied
  • To lock the function array so rows and columns don’t change if the function is copied ✅
  • To copy a function and apply it to all rows and columns

Explanation:
 Absolute references (using $ symbols) lock a specific cell reference, preventing it from changing when the formula is copied or moved to other cells.

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

  • OUTER
  • INNER ✅
  • LEFT
  • RIGHT

Explanation:
The INNER JOIN clause returns only the records that have matching values in both tables. Records that do not have a match in both tables are excluded.

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

  • Data aggregation ✅
  • Data mapping
  • Data grouping
  • Data composition

41. A data analyst uses the TRIM function on their spreadsheet. Why might they do this?

  • They plan to convert all numbers from text into numeric.
  • VLOOKUP needs data values to have leading spaces.
  • VLOOKUP needs data values to have trailing spaces.
  • They plan to use VLOOKUP on the spreadsheet data.

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

  • Ampersand (&)
  • Asterisk (*)
  • Dollar sign ($) ✅
  • Hashtag (#)

43. What are some of the advantages of using subqueries in SQL? Select all that apply.

  • Subqueries can use special functions.
  • The logic is easier to read and understand. ✅
  • All of the logic is in one place. ✅
  • The query processes more efficiently. ✅

44. The VALUE function converts a numeric value into a text string in a spreadsheet.

  • True
  • False ✅

Explanation:
 The VALUE function converts a text string that represents a number into a numeric value, not the other way around.

45. A data analyst locks the rows and columns in their spreadsheet by wrapping their function’s data array in dollar signs ($). Why would they do this?

  • To avoid incorrect calculations caused by changing the array ✅
  • So that the data auto deletes after the function is used
  • So that other analysts’ functions can not access the arrayFeedback: 
  • To stop people from accessing sensitive information in the array

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

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

47. The following is a selection from a spreadsheet:

To search for the growth in population in China, what is the correct VLOOKUP syntax?

  • =VLOOKUP(China, A2*C10, 3, false)
  • =VLOOKUP(China, A2:C10, 2, false)
  • =VLOOKUP(“China”, A2:C10, 3, false) ✅
  • =VLOOKUP(“China”, A2:C10, 2, false)

48. You are exploring the employee retention rate for a department in your company. You realize that evaluating several other departments would also be a good idea. You decide to aggregate the data from all desired departments. What does this mean?

  • You have modified the data from each source in order to make it suitable for analysis.
  • You have collected the data from various sources into a single, summarized collection. ✅
  • You have investigated each of the various data sources individually and compared the results.
  • You have ensured that the departments’ data is properly stored, managed, and maintained.

Explanation:
 Aggregating data refers to collecting and combining data from various sources into a summarized form, such as calculating averages or totals.

49. A data analyst writes a query in SQL. Inside this query, they have a second query. What is another name for the first query? Select all that apply.

  • Outer query ✅
  • Central select
  • Outer select ✅
  • Subquery

Explanation:

  • The outer query is the main query that contains the subquery.
  • The outer select is part of the outer query in SQL that selects data after the subquery has been executed.
  • The central select and subquery are incorrect terms in this context. The subquery refers to the second query inside the outer query.

Related contents:

Module 1: Organize data for more effective analysis
Module 2: Format and adjust data
Module 4: Perform data calculations
Module 4: Course challenge

You might also like:

Course 1: Foundations: Data, Data, Everywhere
Course 2: Ask Questions to Make Data-Driven Decisions
Course 3: Prepare Data for Exploration
Course 4: Process Data from Dirty to Clean
Course 6: Share Data Through the Art of Visualization
Course 7: Data Analysis with R Programming
Course 8: Google Data Analytics Capstone: Complete a Case Study

Leave a Reply