# Analyze Data to Answer Questions - Module 4 challenge
1. **A data analyst at an ocean conservancy manually recalculates the new column ocean\_currents. They want to identify any rows with values that do not match those in the original column, ocean\_tides. Which SQL clauses would enable them to do so? Select all that apply.**
* WHERE ocean\_currents >< ocean\_tides
* WHERE ocean_currents != ocean_tides
* WHERE ocean_currents <> ocean_tides
* WHERE ocean\_currents !! ocean\_tides
2. **Fill in the blank: The SQL command GROUP BY groups table rows with \_\_\_\_\_ values into summary rows.**
* the same
* decreasing
* null
* increasing
3. **What will this spreadsheet function return?**
```apache
=SUMIF(K20:K70, ”>=50”, L20:L70)
```
* The sum of all values in cells L20 to L70 that correspond to values in cells K20 to K70 that are greater than or equal to 50.
* The sum of all values in cells K20 to K70 for which the value in cells L20 to L70 is greater than or equal to 50.
* The count of the number of cells in the array K20:K70 that have a value greater than or equal to 50.
* The sum of any values in cells K20 to K70 and cells L20 to L70 that are greater than or equal to 50.
4. **Which of the following statements accurately describe pivot tables? Select all that apply.**
* A pivot table can be used to sort, reorganize, or group data.
* The columns of a pivot table organize and display values vertically.
* The rows of a pivot table organize and display values vertically.
* The filters section of a pivot table is used to apply filters based on specific criteria.
5. **A data analyst at an engineering company calculates the number of spreadsheet rows that contain the value turbine. Which function do they use?**
* =COUNTIF(C1:C100,“turbine”)
* \=COUNTIF(C1:C100,turbine)
* \=COUNTIF(turbine=C1:C100)
* \=COUNTIF(C1:C100,“=turbine”)
6. **What is a primary reason data analysts use temporary tables or similar structures (like WITH clauses) when working with SQL?**
* To establish universal naming conventions for tables that all database systems recognize automatically.
* To permanently alter the data types of columns in the original source tables.
* To store intermediate results or frequently used subsets of data, making complex queries more manageable and potentially more efficient.
* To automatically ensure that the underlying source data is refreshed in real-time whenever the temporary table is queried.
7. **Which SQL statement will create a temporary table?**
* SELECT new\_table FROM old\_table;
* CREATE TABLE new\_table AS ( FROM old\_table );
* WITH new_table AS ( SELECT * FROM old_table WHERE z = 5 );
* WITH new\_table = ( SELECT \* FROM old\_table );
8. **Which column is set as a value in this pivot table?**
MAX of Time Period | Type | |
Month | East | West |
Jan. | | 25 |
Jan. | 67 | 11 |
Feb. | 26 | 45 |
Mar. | 98 | |
* Time Period
* Type
* MAX
* Month