Aggregating Data for Analysis INTRODUCTION

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?

Test your knowledge on Vlookup

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

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

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

4. The following is a selection from a spreadsheet:

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

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?

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?

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?

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?

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?

Test your knowledge on working with subqueries

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

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.

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

GOOGLE DATA ANALYTICS COURSERA ANSWERS AND STUDY GUIDE

Weekly Breakdown | Google Study Guides | Back to Top

Analyze Data to Answer Questions Weekly Challenge 3

1. In data analytics, what is data aggregation?

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?

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

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.

5.1. The following is a selection from a spreadsheet:

5.2. The following is a selection from a spreadsheet:

5.3. The following is a selection from a spreadsheet:

5.4. The following is a selection from a spreadsheet:

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.

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

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

Aggregating Data for Analysis CONCLUSION