# analyze data to answer questions weekly challenge 4

#### 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”)

• ”<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
• Subtraction
• Division

• 307
• 120
• 153
• 193

• 257252
• 959711
• 342562
• 858088

• 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”)

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

• AVERAGE
• SUM
• PRODUCT
• COUNTA

• 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_SurveyFROM Survey_1

• Total_Surveys
• Responses_Per_Survey
• Yes_Responses
• Survey_1

• /
• !=
• <>
• %

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

• 3
• 2
• 6
• 1

• 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”)

• SUM
• SUMPRODUCT
• SUMIF
• SUMIFS

• 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

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