Week 4: Performing data calculations

1. A data analyst is working with a spreadsheet from a furniture company. To use the template for this spreadsheet, click the link below and select “Use Template.”

Link to template: Sample Transaction Table.

Or, if you don’t have a Google account, download the file directly from the attachment below.

The syntax of which of the following formulas would allow the analyst to count purchase sizes of two or more?

  • =COUNTIF(G2:G30, “>=2”)
  • =COUNTIF(H2:H30, “>=2”)
  • =SUMIF(H2:H30, “=4”)
  • =SUMIF(G2:G30, “<=1”)

 

2. You are working in a spreadsheet and use the SUMIF function in the formula below as part of your analysis.

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

Which part of this formula is the criteria or condition?

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

 

3. 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?The analyst wants to figure out the value of all of the items in the spreadsheet. Which formula will calculate the total price of all of the items?

  • It multiplies the values in the first range, then multiplies the values in the second range .
  • 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.
  • It adds the ranges, then multiplies them by the last value in the second array.

4. You create a pivot table in a spreadsheet containing movie data. To use the template for this spreadsheet, click the link below and select “Use Template.”

Link to template: Movie Data Project.

Or, if you don’t have a Google account, download the file directly from the attachment below.

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

  • Box Office Revenue
  • Movie Title
  • Genre
  • Budget

 

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

  • Multiplication
  • Addition
  • Subtraction
  • Division

6. You are working with a database table that contains data about music. The table includes columns for track_id, track_name, composer, and milliseconds (duration of the music track). You are only interested in data about the classical musician Johann Sebastian Bach. You want to know the duration of each Bach track in seconds. You decide to divide milliseconds by 1000 to get the duration in seconds, and use the AS command to store the result in a new column called secs.

Add a statement to your SQL query that calculates the duration in seconds for each track and stores it in a new column as secs.

NOTE: The three dots (...) indicate where to add the statement.

What is the duration in seconds of the track with Id number 3408?

  • 307
  • 120
  • 153
  • 193

 

7. You are working with a database table that contains data about music. The table includes columns for album_id and milliseconds (duration of the music tracks on each album). You want to find out the total duration for each album in milliseconds, and store the result in a new column named total_duration.

You write the SQL query below. Add a GROUP BY clause that will group the data by album Id number.

What is the total duration of the album with Id number 2?

  • 257252
  • 959711
  • 342562
  • 858088

8. You are working with a database table that contains invoice data. The table includes columns for billing_state, billing_country, and total. You want to know the average total price for the invoices billed to the state of Wisconsin. You decide to use the AVG function to find the average total, and use the AS command to store the result in a new column called average_total.

Add a statement to your SQL query that calculates the average total and stores it in a new column as average_total.

NOTE: The three dots (...) indicate where to add the statement.

What is the average total for Wisconsin?

  • 5.54
  • 5.78
  • 6.08
  • 5.37

9. A data analyst wants to calculate the number of rows that have a value of “shipped”. Which function could they use?

  • =MAX(G2:G30,”=shipped”)
  • =SUM(G2:G30,”=shipped”)
  • =COUNT(G2:G30,”=shipped”)
  • =COUNTIF(G2:G30,”=shipped”)

10. You are working in a spreadsheet and use the SUMIF function in the following formula as part of your analysis.

=SUMIF(D2:D10,”>=50”,E2:E10)

Which part of this formula indicates the range of values to be added?

  • E2:E10
  • >=50
  • D2:D10
  • =SUMIF

11. You create a pivot table and want to add up the total of all cells for each row and column value in the pivot table. Which function in the values menu would you use to summarize the data?

  • AVERAGE
  • SUM
  • PRODUCT
  • COUNTA

12. What column is set as a value in the following pivot table?

  • Direction
  • Duration
  • MAX
  • Date

13. In the following SQL query, which column is part of an addition operation that creates a new column?

SELECT
Yes_Responses,
No_Responses,
Total_Surveys,
Yes_Responses + No_Responses AS Responses_Per_Survey
FROM
Survey_1

  • Total_Surveys
  • Responses_Per_Survey
  • Yes_Responses
  • Survey_1

14. What SQL operator is used to return the remainder of a division operation?

  • /
  • !=
  • <>
  • %

15. What is the purpose of using data validation during your analysis process?

  • To ensure that you are able to use every piece of data from your raw data
  • To guarantee that all of your stakeholders will be happy with your results
  • To ensure that all data is complete, accurate, secure, and consistent
  • To guarantee that visualizations are visually pleasing

16. What is the purpose of the operator in SQL?

  • To add two values
  • To return the remainder of a division operation
  • To check if two values are not equal
  • To set a value equal to another

17. What is a reason to use a temporary table instead of a standard table in SQL?

  • A temporary table allows functions that are unavailable to standard tables.
  • A temporary table calculates formulas using less memory than standard tables.
  • A temporary table calculates formulas faster than standard tables.
  • A temporary table allows analysts to repeatedly work with the same subset of data.

18. Which of the following SQL queries adds a table into the database?

  • SELECT * FROM table GROUP BY columnA ORDER BY columnB;
  • CREATE TABLE my_table AS (SELECT * FROM other_table);
  • SELECT * FROM table;
  • WITH my_table AS (SELECT * FROM other_table WHERE x = 0);

19. What is the purpose of using pivot tables?

  • To multiply two arrays and add the results
  • To allow quick copying from one table to another
  • To view data in multiple ways to find insights and trends
  • To allow the use of SQL in spreadsheets

20. How many different columns have been added to the values section of the pivot table editor?

  • 3
  • 2
  • 6
  • 1

21. What SQL keyword is used to define a name for a calculated column?

  • SELECT
  • AS
  • FROM
  • WITH

22. A data analyst uses the following formula to calculate a new row in a SQL query. What best describes the result of the formula?

(colA + colB) / colC = new_col

  • colB is added to colA then the result is multiplied by colC.
  • colB is subtracted from colA then the result is multiplied by colC.
  • colB is added to colA then the result is divided by colC.
  • colB is divided by colC then the result is added to colA.

23. What is the process of checking and rechecking the quality of your data so that it is complete, accurate, secure, and consistent?

  • Data-driven development
  • Data visualization
  • Data augmentation
  • Data validation

24. A data analyst finds some data that seems inconsistent. What is the first thing they should do?

  • Remove the inconsistent values.
  • Convert the inconsistent values to JSON.
  • Fill the odd values with filler values.
  • Determine if the inconsistent values are valid.

25. What is a reason to use a WITH AS clause in a SQL statement?

  • The result is temporary.
  • The result is a pivot table.
  • The result calculates faster.
  • The result is a visualization.

26. Which of the following SQL statements can be used to create temporary tables in SQL?

  • WITH my_table FROM (SELECT * FROM other_table);
  • WITH my_table AS (SELECT * FROM other_table WHERE x = 0);
  • CREATE TABLE my_table AS (SELECT * FROM other_table);
  • SELECT * FROM table;

27. A data analyst wants to calculate the number of rows that have a SKU value of “K102145”. Which function could they use?

  • =COUNTIF(G2:G30,K102145)
  • =COUNTIF(K102145=G2:G30)
  • =COUNTIF(G2:G30,“=K102145”)
  • =COUNTIF(G2:G30,“K102145”)

28. A data analyst wants to use a single function to multiply two ranges and then add the multiplied values. What single function can they use to accomplish this?

  • SUM
  • SUMPRODUCT
  • SUMIF
  • SUMIFS

29. Which values of Date and Direction are used to calculate the value 450 in the following pivot table?

  • 2/3 and Down
  • 2/4 and Up
  • 2/5 and Down
  • 2/4 and Down

30. When writing custom calculations in SQL, what characters can be used to group calculations to change the order of calculation?

  • Parentheses – ( )
  • Curly Braces – { }
  • Quotation Marks – “ “
  • Square Brackets – [ ]

31. A data analyst is trying to manually recalculate a column that was present in their dataset. They want to find rows where the values in their column do not match the values in the original column. Which of the following SQL clauses could they use?

  • WHERE original_column !! recalcualted_column
  • WHERE original_column NOT EQUALS recalcualted_column
  • WHERE original_column <> recalcualted_column
  • WHERE original_column ~= recalcualted_column

32. When working with a new dataset, how can you ensure that your data is valid?

  • Personally collect all data that you use in your analysis.
  • Manually check the calculations of calculated columns.
  • Convert all data to JavaScript Object Notation (JSON).
  • Fill in missing values with values that will favor your initial hypothesis.

33. Which of the following statements about temporary tables is correct?

  • They must be created using the WITH AS SQL clause.
  • They must be created using the WITH AS SQL clause.
  • They are declared by enclosing a FROM statement between ##.
  • They are a special feature of BigQuery unavailable in other RDBMS.

34. A data analyst wants to calculate the number of rows that have a value less than 150. Which function could they use?

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

35. What is the purpose of the EXTRACT function in SQL?

  • Calculate using data extracted from other tables
  • Return a specific key-value pair from a JSON object
  • Return a specific portion of a date
  • Calculate the mathematical extract operation

36. Which portion of a pivot table do you change if you want to use a different calculation to combine the results?

  • Filter
  • Columns
  • Values
  • Rows

37. Which of the following statements about temporary tables is correct?

  • They must be created using the WITH AS SQL clause.
  • They are automatically deleted when the SQL database session ends.
  • They are declared by enclosing a FROM statement between ##.
  • They are a special feature of BigQuery unavailable in other RDBMS.

38. Which of the following SQL queries adds a table into the database? Select all that apply.

  • CREATE TABLE my_table AS (SELECT * FROM other_table);
  • WITH my_table AS (SELECT * FROM other_table WHERE x = 0);
  • SELECT * FROM table;
  • SELECT * FROM table GROUP BY columnA ORDER BY columnB;

39. Which of the following SQL statements correctly implements a WITH AS clause?

  • WITH my_table AS (SELECT * FROM other_table);
  • WITH AS my_table AS (SELECT * FROM other_table);
  • WITH AS my_table AS SELECT * FROM other_table;
  • WITH my_table AS SELECT * FROM other_table;

Leave a Reply