COURSE 5 – ANALYZE DATA TO ANSWER QUESTIONS

by
Contents hide
1 Week 3: Aggregating Data for Analysis Quiz Answers
2 AGGREGATING DATA FOR ANALYSIS INTRODUCTION
3 HANDS-ON ACTIVITY: COMBINE MULTIPLE PIECES OF DATA
4 TEST YOUR KNOWLEDGE ON VLOOKUP
5 HANDS-ON ACTIVITY: QUERIES FOR JOINS
6 TEST YOUR KNOWLEDGE ON USING JOINS TO AGGREGATE DATA
7 TEST YOUR KNOWLEDGE ON WORKING WITH SUBQUERIES
8 ANALYZE DATA TO ANSWER QUESTIONS WEEKLY CHALLENGE 3
9 AGGREGATING DATA FOR ANALYSIS CONCLUSION
Spread the love

Week 3: Aggregating Data for Analysis Quiz Answers

AGGREGATING DATA FOR ANALYSIS INTRODUCTION

In this module, students will learn the basic techniques for aggregating data for analysis in the Google Data Analytics Professional Certification course on Coursera. This course deals with bringing data from various sources-cell by cell in a spreadsheet or even through different tables in a database-that helps in drawing worthy insights and achieving business objectives.

As you learn how to perform these functions, procedures, and syntax, you arm yourself with a better base for making sound decisions that facilitate actionable strategies based on analysis results. For any data analyst, data aggregation is an essential skill that provides a foundation for progress in the field.

What You’ll Learn

  • Understand and apply functions and procedures for consolidating data from multiple spreadsheet cells.
  • Learn SQL functions and syntax to craft queries that combine data from multiple database tables effectively.
  • Use tools like VLOOKUP to query and manipulate data, including trimming, converting text to numeric formats, and creating summary tables.

HANDS-ON ACTIVITY: COMBINE MULTIPLE PIECES OF DATA

1. Imagine the employee Anika Patel asks you to confirm her pay rate. Without using the pivot table, which VLOOKUP function would return her pay rate based off of the imported data on Sheet1?

  • =VLOOKUP(B19, B15:J19, 9, false) (Correct)
  • =VLOOKUP(B20, B15:J20, 9, false)
  • =VLOOKUP(B19, B15:J19, 9, true)
  • =VLOOKUP(B19, B15:J19, 8, false)

Correct: In Microsoft Excel, the function =CONCATENATE(A7, ” “, B7) puts together the text from the two cells A7 and B7 with a space between them, giving “John Quincy Adams.” That is useful in Excel, but beware that it has done away with the CONCATENATE function in favor of TEXTJOIN or CONCAT in Google Sheets.

TEST YOUR KNOWLEDGE ON VLOOKUP

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

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

Correct: The formula =VALUE(F8) will convert the text string in spreadsheet cell F8 into a number. The VALUE function within the parentheses must mention the specific cell that contains the value you want to convert.

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

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

Correct: An absolute reference is used in convicts to lock a row, column, or both in a formula, so that the referenced values will remain constant when copying the formula to other cells.

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

  • True
  • False (Correct)

Correct: While TRUE instructs the VLOOKUP function to search for approximate matches, FALSE restricts the function to only searching for perfect matches.

4. The following is a selection from a spreadsheet:

C_5_W_3_Test_Your_Knowledge_ON_VLOOUP_1

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

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

Correct: For example, in order to search for the population of Nigeria using the formula =VLOOKUP(“Nigeria”, A2:C10, 2, FALSE), the components are:

5. The following is a selection from a spreadsheet:

C_5_W_3_Test_Your_Knowledge_ON_VLOOUP_2

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

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

Correct: To obtain the height of the building in Mecca, the appropriate formula would be =VLOOKUP(“Mecca”, A2:D7, 3, FALSE). In this case, “Mecca” is the lookup value, A2:D7 is the range of the table, 3 indicates the column number to retrieve from, and FALSE makes sure that the function searches for the exact match.

HANDS-ON ACTIVITY: QUERIES FOR JOINS

1. In the last query, you use a LEFT JOIN instead of an INNER JOIN to find the correct information. Beneath the query results, you’ll find that the number of rows in your joined table is 281. If you rerun the query with an INNER JOIN instead of a LEFT JOIN, how many rows would it return?

  • 274 (Correct)
  • 281
  • 301
  • 324

Correct: Of course, what I mean is that an INNER JOIN limits the number of universities in the query results to those universities that only have matching mascots because the resultant 274 rows from the query were returned back after an INNER JOIN. Hence, an INNER JOIN rather than a LEFT JOIN would eliminate universities without mascots in the resultant set. Properly knowing which type of JOIN to use is important in order to truly be able to merge data from multiple tables because it will specify what is brought back in conjunction with the data. Thus, in the future, if the identification of the correct JOIN type can be applied appropriately, it will enable the proper analysis and combining of the data.

TEST YOUR KNOWLEDGE ON USING JOINS TO AGGREGATE DATA

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

  • INNER JOIN (Correct)
  • RIGHT JOIN
  • LEFT JOIN
  • OUTER JOIN

Correct: True! The INNER JOIN is an operation that most analysts use to extract records that have matching values in two different tables. This type of join includes only those rows where the two tables have corresponding entries; elsewhere, an entry that has no counterpart is excluded. An INNER JOIN operation is typically used when someone wants to combine information according to a common field with emphasis on the records commonly found in both tables.

2. 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 DISTINCT
  • COUNT VALUES (Correct)
  • COUNT
  • COUNT RANGE

Correct: COUNT(DISTINCT…) operator has to be used in your query to instruct the database to return unique values for a specified range.

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

  • Filtering
  • Aliasing (Correct)
  • Tagging
  • Naming

Correct: To use an alias while writing a query, i.e., when the column is made easier to read and referenced temporarily, the analyst can do so.

TEST YOUR KNOWLEDGE ON WORKING WITH SUBQUERIES

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

C_5_W_3_Test_Your_Knowledge_On_Working_With_Subqueries_1

Correct: The three queries with statements in parentheses contain subqueries.

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

  • Naming (Correct)
  • removing
  • copying
  • hiding

Correct: Aliasing involves temporarily naming a table or column in a query.

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

  • True
  • False (Correct)

Correct: The inner query is executed before any other processing is done with the outer query.

ANALYZE DATA TO ANSWER QUESTIONS WEEKLY CHALLENGE 3

1. In data analytics, what is data aggregation?

  • 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 moving certain data points to a higher rank or position.
  • The process of gathering data from multiple sources and combining it into a single, summarized collection. (Correct)

Correct: In order words, the data “aggregation” refers to the process of collating data from several sources into a single dataset that summarizes it all.

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

  • PROCESS
  • CONVERT
  • VALUE (Correct)
  • EXCHANGE

Correct: Analysts should use VALUE function to convert a word into a numeric value.

3. When using VLOOKUP, there are some common limitations that data analysts should be aware of. Identify these limitations. Select all that apply.

  • VLOOKUP can only return a value from the data to the right of the column of the matched value. (Correct)
  • VLOOKUP only returns the first match it finds, even if there are many possible matches. (Correct)
  • VLOOKUP only returns matches it finds while searching through a row.
  • VLOOKUP can only return a value from the data to the left of the column it’s typed into.

Correct: The fact that VLOOKUP returns only the first match found is one limitation, even if there are multiple matches. Further, for VLOOKUP to work, it must be done with values in columns to the right.

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

  • absolute reference (Correct)
  • accurate reference
  • authentic reference
  • arbitrary reference

Correct: Surrounding a table array by dollar signs creates an absolute reference, so the array is locked such that the rows and columns do not change when you copy the function.

5.1. The following is a selection from a spreadsheet:

C_5_W_3_Weekly_Challenge_1

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

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

Correct: When searching for Brazil’s population, the argument should be defined as follows: =VLOOKUP(“Brazil”, A2:B10, 2, FALSE). In this case, the lookup value is “Brazil”, the table array is A2:B10, the number 2 indicates which column holds the value you want, and FALSE tells her to find an exact match.

5.2. The following is a selection from a spreadsheet:

C_5_W_3_Weekly_Challenge_1

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

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

Correct: The population of Pakistan is to be looked up using the following formula: =VLOOKUP(“Pakistan”, A2:B10, 2, FALSE). Here “Pakistan” is the lookup value, the A2:B10 is the table array, while 2 refers to the column number from which the value should be retrieved, and FALSE is for making sure the function brings an exact match.

5.3. The following is a selection from a spreadsheet:

C_5_W_3_Weekly_Challenge_1

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

  • =VLOOKUP(Nigeria, A2,B10, 3, false)
  • =VLOOKUP(“Nigeria”, A2:B10, 2, false) (Correct)
  • =VLOOKUP(Nigeria, A2:B10, 3, false)
  • =VLOOKUP(Nigeria, A2:B10, 2, false)

Correct: To query the Nigerian population, the formula is as follows: =VLOOKUP(“Nigeria”, A2:B10, 2, FALSE). Here, the argument “Nigeria” refers to the lookup value; the range A2:B10 is the table array. Alphabetical column number is given by the digit 2, which specifies the column to return the value from in the table, and FALSE ensures that the function returns an exact match.

5.4. The following is a selection from a spreadsheet:

C_5_W_3_Weekly_Challenge_1

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

  • =VLOOKUP(Nigeria, A2,B10, 3, false)
  • =VLOOKUP(“Nigeria”, A2:B10, 4, false) (Correct)
  • =VLOOKUP(Nigeria, A2:B10, 3, false)
  • =VLOOKUP(Nigeria, A2:B10, 2, false)

Correct: The syntax for searching the population of Indonesia is = VLOOKUP(“Indonesia”, A2:B10, 2, FALSE). In this, “Indonesia” is the lookup value, A2:B10 is the table array, 2 is related to which column number from which the value should be returned, and FALSE is to ensure that the function returns the exact match.

6. 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 (Correct)
  • False

Correct: The INNER JOIN clause yields records which have equivalent values in two or more tables. An OUTER JOIN is a function that combines LEFT JOIN and RIGHT JOIN to return all matching records as well as records from one table with no matching record in another. The types of OUTER JOIN include the LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.

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

  • True
  • False (Correct)

Correct: The COUNT DISTINCT function is used within data ranges to count how many unique values are in it by excluding the reference to duplicate values.

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

  • Inner select (Correct)
  • Inner query (Correct)
  • Small query
  • Nested query (Correct)

Correct: A subquery can also be called – an inner query, an inner select or a nested query.

9. While using VLOOKUP, you encounter an error because some of your spreadsheet values have leading and trailing spaces. What function should you use to eliminate these spaces?

  • TRIM (CORRECT)
  • NOSPACE
  • CUT
  • VALUE

10. Fill in the blank: The spreadsheet function _____ can be used to tally the number of cells in a range that are not empty.

  • RETURN
  • COUNT (CORRECT)
  • COUNT DISTINCT
  • RANGE

11. A data analyst writes the following formula: =MAX($E$5:$E$500). What are the purposes of the dollar signs ($)? Select all that apply. 

  • Perform the calculation more efficiently.
  • Ensure rows and columns do not change. (CORRECT)
  • Create an absolute reference. (CORRECT)
  • Find the maximum value in the range E5 to E500 regardless of whether the formula is copied. (CORRECT)

12. What will this query return?

1 SELECT *

2 FROM Equipment_table

3 LEFT JOIN Computer_table

  • All records in the computer table and any matching rows from the equipment table
  • All rows from the equipment table joined together with the computer table (CORRECT)
  • All records in the equipment table and any matching rows from the computer table
  • All records in both the equipment table and the computer table

13. In this spreadsheet, which function will search for the surface area of Lake Huron?

(n/a) A B C
1 Lake Surface area (sq. miles) Water type
2 Caspian Sea 143,000 Saline
3 Superior 31,700 Freshwater
4 Victoria 26,590 Freshwater
5 Huron 23,000 Freshwater
7 Tanganyika 12,600 Freshwater
8 Balkhash 6,300 Saline
9 Athabasca 3,030 Freshwater
10 Urmia 2,320 Freshwater
  • =VLOOKUP(Huron, A2:C10, false)
  • =VLOOKUP(“Huron”, A2:B10, 2, false) (CORRECT)
  • =VLOOKUP(“Huron”, B2:C10, 2, false)
  • =VLOOKUP(Huron, A2:B10, 3, false)

14. Fill in the blank: A SQL clause containing HAVING adds a _____ to a query instead of the underlying table.

  • Subquery
  • Join
  • Filter (CORRECT)
  • Limit

15. A data analyst at a retail store works with a spreadsheet containing sales data. In order to calculate sales tax correctly for customer orders, the analyst ensures all amounts are converted to numeric values. What function do they use?

  • EXCHANGE
  • PROCESS
  • CONVERT
  • VALUE (CORRECT)

16. Which query will select all columns from the operations table and alias them to ops?

  • 1 SELECT * 2 FROM operations NEW ops
  • 1 SELECT * 2 FROM operations TO ops
  • 1 SELECT * (CORRECT) 2 FROM operations AS ops
  • 1 SELECT * 2 FROM operations ALIAS ops

17. A junior data analyst writes the following formula: =AVERAGE($C$1:$C$100). What are the purposes of the dollar signs ($)? Select all that apply. 

  • Perform the calculation more efficiently.
  • Create an absolute reference. (CORRECT)
  • Average the values in cells C1 to C100 regardless of whether the formula is copied. (CORRECT)
  • Ensure rows and columns do not change. (CORRECT)

18. What will this query return?

1 SELECT *

2 FROM Inventory_table

3 LEFT JOIN Scrap_table

  • All records in both the inventory table and the scrap table
  • All records in the inventory table and any matching rows from the scrap table (CORRECT)
  • All records in the scrap table and any matching rows from the inventory table
  • All rows from the inventory table joined together with the scrap table

19. Fill in the blank: A SQL clause containing HAVING can only be used with _____ functions.

  • join
  • ORDER BY
  • Aggregate (CORRECT)
  • GROUP BY

20. Which query will select all columns from the customers table and alias them to cust?

  • 1 SELECT * 2 FROM customer TO cust
  • 1 SELECT * (CORRECT) 2 FROM customer AS cust
  • 1 SELECT * 2 FROM customer NEW cust
  • 1 SELECT * 2 FROM customer ALIAS cust

21. You use VLOOKUP in a spreadsheet containing weather data. While searching for rainfall levels in Chicago, you encounter an error because your spreadsheet value has a trailing space after the city name. What function should you use to eliminate this space?

  • VALUE
  • NOSPACE
  • TRIM (CORRECT)
  • CUT

22.  Fill in the blank: The spreadsheet function _____ can be used to add up the number of times a range of cells contains the value “paid.”

  • COUNT (CORRECT)
  • RANGE
  • RETURN
  • COUNT DISTINCT

23. A data professional writes the following formula: =SUM($A$6:$A$60). What are the purposes of the dollar signs ($)? Select all that apply. 

  • Perform the calculation more efficiently
  • Sum the values in cells A6 to A60 regardless of whether the formula is copied. (CORRECT)
  • Ensure rows and columns do not change. (CORRECT)
  • Create an absolute reference. (CORRECT)

24. What will this query return?

1 SELECT *

2 FROM Books_table

3 LEFT JOIN Biography_table

  • All records in the biography table and any matching rows from the books table
  • All rows from the books table joined together with the biography table
  • All records in the books table and any matching rows from the biography table (CORRECT)
  • All records in both the books table and the biography table

25.  In this spreadsheet, which function will search for the surface area of Lake Victoria?

(n/a) A B C
1 Lake Surface area (sq. miles) Water type
2 Caspian Sea 143,000 Saline
3 Superior 31,700 Freshwater
4 Victoria 26,590 Freshwater
5 Huron 23,000 Freshwater
7 Tanganyika 12,600 Freshwater
8 Balkhash 6,300 Saline
9 Athabasca 3,030 Freshwater
10 Urmia 2,320 Freshwater
  • =VLOOKUP(“Victoria”, B2:C10, 2, false)
  • =VLOOKUP(Victoria, A2:B10, 3, false)
  • =VLOOKUP(“Victoria”, A2:B10, 2, false) (CORRECT)
  • =VLOOKUP(Victoria, A2:C10, false)

26. Which query will select all columns from the highways table and alias them to hwys?

  • 1 SELECT * 2 FROM highways ALIAS hwys
  • 1 SELECT * 2 FROM highways TO hwys
  • 1 SELECT * (CORRECT) 2 FROM highways AS hwys
  • 1 SELECT * 2 FROM highways NEW hwys

27. You use VLOOKUP to search for the name “Liza Campbell.” However, the function doesn’t work properly because your spreadsheet has a repeated space between the first and last name. What function should you use to eliminate this space?

  • TRIM (CORRECT)
  • CUT
  • NOSPACE
  • VALUE

28. In this spreadsheet, which function will search for the water type of Lake Urmia?

(n/a) A B C
1 Lake Surface area (sq. miles) Water type
2 Caspian Sea 143,000 Saline
3 Superior 31,700 Freshwater
4 Victoria 26,590 Freshwater
5 Huron 23,000 Freshwater
7 Tanganyika 12,600 Freshwater
8 Balkhash 6,300 Saline
9 Athabasca 3,030 Freshwater
10 Urmia 2,320 Freshwater

28. In this spreadsheet, which function will search for the water type of Lake Urmia?

(n/a) A B C
1 Lake Surface area (sq. miles) Water type
2 Caspian Sea 143,000 Saline
3 Superior 31,700 Freshwater
4 Victoria 26,590 Freshwater
5 Huron 23,000 Freshwater
7 Tanganyika 12,600 Freshwater
8 Balkhash 6,300 Saline
9 Athabasca 3,030 Freshwater
10 Urmia 2,320 Freshwater

28. In this spreadsheet, which function will search for the water type of Lake Urmia?

(n/a) A B C
1 Lake Surface area (sq. miles) Water type
2 Caspian Sea 143,000 Saline
3 Superior 31,700 Freshwater
4 Victoria 26,590 Freshwater
5 Huron 23,000 Freshwater
7 Tanganyika 12,600 Freshwater
8 Balkhash 6,300 Saline
9 Athabasca 3,030 Freshwater
10 Urmia 2,320 Freshwater

28. In this spreadsheet, which function will search for the water type of Lake Urmia?

(n/a) A B C
1 Lake Surface area (sq. miles) Water type
2 Caspian Sea 143,000 Saline
3 Superior 31,700 Freshwater
4 Victoria 26,590 Freshwater
5 Huron 23,000 Freshwater
7 Tanganyika 12,600 Freshwater
8 Balkhash 6,300 Saline
9 Athabasca 3,030 Freshwater
10 Urmia 2,320 Freshwater

28. In this spreadsheet, which function will search for the water type of Lake Urmia?

(n/a) A B C
1 Lake Surface area (sq. miles) Water type
2 Caspian Sea 143,000 Saline
3 Superior 31,700 Freshwater
4 Victoria 26,590 Freshwater
5 Huron 23,000 Freshwater
7 Tanganyika 12,600 Freshwater
8 Balkhash 6,300 Saline
9 Athabasca 3,030 Freshwater
10 Urmia 2,320 Freshwater

28. In this spreadsheet, which function will search for the water type of Lake Urmia?

28. In this spreadsheet, which function will search for the water type of Lake Urmia?

(n/a) A B C
1 Lake Surface area (sq. miles) Water type
2 Caspian Sea 143,000 Saline
3 Superior 31,700 Freshwater
4 Victoria 26,590 Freshwater
5 Huron 23,000 Freshwater
7 Tanganyika 12,600 Freshwater
8 Balkhash 6,300 Saline
9 Athabasca 3,030 Freshwater
10 Urmia 2,320 Freshwater
  • =VLOOKUP(Urmia, A2:C10, 2, false)
  • =VLOOKUP(“Urmia”, A2:C10, 3, false) (CORRECT)
  • =VLOOKUP(Urmia, A2:B10, false)
  • =VLOOKUP(“Urmia”, B2:C10, 2, false)

29.  Fill in the blank: To find out how many times a specific error occurs in a range of cells, the spreadsheet function _____ can be used.

  • COUNT (CORRECT)
  • COUNT DISTINCT
  • RANGE
  • RETURN

30. Fill in the blank: A SQL clause containing HAVING adds a filter to a _____ instead of the underlying table.

  • statement
  • column
  • row
  • query (CORRECT)

31.  A junior data analyst in a marketing department works with a spreadsheet containing email click-through data. To calculate the average click-through rate for a campaign, the analyst uses a function to convert the number of clicks to numeric values. What function do they use?

  • EXCHANGE
  • PROCESS
  • VALUE (CORRECT)
  • NUM

AGGREGATING DATA FOR ANALYSIS CONCLUSION

Having completed this module successfully, you should now be well acquainted with the various functions and extended procedures along with syntax used for combination and you should also be able to do this by using more than two cells from spreadsheets or multiple database tables using SQL queries. For more advanced learning on analyzing data, you may consider the Coursera community.

Coursera is a portal through which students may get courses from top universities and institutions in the world; which allow students to learn at their own pace. Joining this Coursera is a good opportunity in furthering education and advancing one’s career.

Leave a Comment