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

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

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.

What will this query return?

 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

In this spreadsheet, which function will search for the surface area of Lake Huron?

• \=VLOOKUP("Huron", B2:C10, 2, false)

• \=VLOOKUP(Huron, A2:B10, 3, false)

• \=VLOOKUP(Huron, A2:C10, false)

• =VLOOKUP("Huron", A2:B10, 2, false)

Fill in the blank: A SQL clause containing HAVING adds a filter to a _____ instead of the underlying table.

• row

• column

• query

• statement

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

• EXCHANGE

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

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.

What does this code do?

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.

What does this code do?

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.