Week 3: Aggregating data for analysis

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

  • expanded
  • localized
  • modified
  • summarized

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

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

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.

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

 

5. 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)

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
  • False

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

 

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

 

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

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

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

12. 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)

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

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

  • True
  • False

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

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

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

18. 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)

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

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

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

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

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

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

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

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

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

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

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

  • OUTER
  • INNER
  • LEFT
  • RIGHT

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

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

31. 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 (#)

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

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

  • True
  • False

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

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

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

36. 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)

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

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

Leave a Reply