# Analyze Data to Answer Questions - Module 3 challenge
1. **While using** `VLOOKUP`**, you encounter an error because some of your spreadsheet values have leading and trailing spaces. What function should you use to eliminate these spaces?**
* TRIM
* NOSPACE
* CUT
* VALUE
2. **Fill in the blank: The spreadsheet function \_\_\_\_\_ can be used to tally the number of cells in a range that are not empty.**
* RETURN
* RANGE
* COUNT DISTINCT
* COUNT
3. **A data professional writes the following formula: =SUM($A$6:$A$60). What are the purposes of the dollar signs ($)? Select all that apply.**
* Perform the calculation more efficiently.
* Ensure rows and columns do not change.
* Create an absolute reference.
* Sum the values in cells A6 to A60 regardless of whether the formula is copied.
4. **What will this query return?**
```sql
SELECT *
FROM Equipment_table
LEFT JOIN Computer_table
```
* All records in the **Equipment_table** table and any matching rows from the **Computer_table**
* All records in both **Equipment\_table** and **Computer\_table**
* All records in **Computer\_table** and any matching rows from **Equipment\_table**
* All rows from **Equipment\_table** joined together with **Computer\_table**
5. **In this spreadsheet, which function will search for the surface area of Lake Huron?**
| A | B | C |
1 | Lake | Surface area (sq. miles) | Water type |
2 | Caspian Sea | 143,000 | Saline |
3 | Superior | 31,700 | Freshwater |
4 | Victoria | 26,590 | Freshwater |
5 | Huron | 23,000 | Freshwater |
7 | Tanganyika | 12,600 | Freshwater |
8 | Balkhash | 6,300 | Saline |
9 | Athabasca | 3,030 | Freshwater |
10 | Urmia | 2,320 | Freshwater |
* \=VLOOKUP("Huron", B2:C10, 2, false)
* \=VLOOKUP(Huron, A2:B10, 3, false)
* \=VLOOKUP(Huron, A2:C10, false)
* =VLOOKUP("Huron", A2:B10, 2, false)
6. **Fill in the blank: A SQL clause containing** `HAVING` **adds a filter to a \_\_\_\_\_ instead of the underlying table.**
* row
* column
* query
* statement
7. **A junior data analyst in a marketing department works with a spreadsheet containing email click-through data. To calculate the average click-through rate for a campaign, the analyst uses a function to convert the number of clicks to numeric values. What function do they use?**
* NUM
* PROCESS
* VALUE
* EXCHANG**E**
8. Which query will select all columns from the **customer** table and alias the table to **cust**?
* SELECT \* FROM customer TO cust
* SELECT \* FROM customer NEW cust
* SELECT \* FROM customer ALIAS cust
* SELECT * FROM customer AS cust
9. **What are the benefits of using subqueries in SQL? Select all that apply.**
* Subqueries can make projects easier and more efficient.
* Subqueries make code more readable and maintainable.
* Subqueries cannot be used within the same query.
* Subqueries can be nested in a **SET** command.
10. **What does this code do?**
```sql
SELECT account_table.* FROM ( SELECT * FROM transaction.sf_model_feature_2014_01 WHERE day_of_week = 'Monday' ) AS account_table WHERE account_table.availability = 'NO'
```
* It creates a temporary table named account\_table containing all transactions from transaction.sf\_model\_feature\_2014\_01.
* It selects all columns from the table containing transactions on Monday and filters them to display only the transactions that are unavailable.
* It filters the transaction.sf\_model\_feature\_2014\_01 table to include only transactions on Fridays.
* It selects all data from the transaction.sf\_model\_feature\_2014\_01 table.
11. **What does this code do?**
```sql
SELECT e.employee_id, e.first_name, e.last_name, e.salary FROM employees e WHERE e.salary > ( SELECT AVG(salary) FROM employees WHERE department_id = e.department_id );
```
* It finds employees who earn more than the average salary in their department.
* It calculates the average salary for all employees.
* It groups employees by department.
* It joins the employees table with itself.