Preparing for VLOOKUP

Would you like to follow along with the instructor using the same spreadsheet? To use the spreadsheet template, click the link below and select "Use Template."

Link to template: Converting numerical and text values

OR

If you don't have a Google account, you can download the spreadsheet directly from the attachment below:

Converting numerical and text values - Preparing for VLOOKUP

A data analyst applies the VALUE function to a text string that represents a number, but is formatted as text. What does the VALUE function do to that text string?

A. It converts the text string to a numerical value.

B. It filters any text strings in the spreadsheet that equal the value.

C. It searches for text strings within the spreadsheet that match the value.

D. It changes the value of the text string to zero.

The correct answer is A. It converts the text string to a numerical value. Explain: The VALUE function converts the text string to a numerical value.

VLOOKUP in action

Would you like to follow along with the instructor using the same spreadsheet? To use the template for the spreadsheet, click the links below and select "Use Template." The template has two tabs for the worksheets, Employee Hours and Employee Rates.

Link to template: VLOOKUP in Action Example

OR

If you don't have a Google account, you can download the spreadsheet from the attachment below:

VLOOKUP in Action Example

Fill in the blank: If you enter FALSE as the last input parameter in a VLOOKUP function, VLOOKUP will search for _____.

A. an exact match

B. the closest match

C. a text string

D. a numeric value

The correct answer is A. an exact match. Explain: If you enter FALSE as the last input parameter in a VLOOKUP function, VLOOKUP will search for an exact match.

Identifying common VLOOKUP errors

VLOOKUP has certain limitations. One limitation is that it only returns the first match it finds within the specified range. Another limitation is that VLOOKUP can only search through the first column in a spreadsheet. True or False?

A. True

B. False

It is false statement. Explain: VLOOKUP only returns the first match it finds within a specified range and can only search in columns to the right.

In the function =VLOOKUP(K2,'Sheet 4'!A:B,2,TRUE), what does the word TRUE indicate?

A. TRUE tells VLOOKUP to start at the top of the specified range.

B. TRUE tells VLOOKUP to search for approximate matches.

C. TRUE tells VLOOKUP to search for as many matches as it can find in the specified range.

D. TRUE tells VLOOKUP to search for exact matches.

The correct answer is B. TRUE tells VLOOKUP to search for approximate matches. Explain: In the function =VLOOKUP(K2,'Sheet 4'!A:B,2,TRUE), TRUE tells VLOOKUP to search for approximate matches.

Understanding JOINS

A data analyst wants to combine rows from four tables in a database. Which SQL clause combines two or more tables based on a related column?

A. CONNECT

B. JOIN

C. MERGE

D. LINK

The correct answer is B. JOIN. Explain: JOIN is a SQL clause that combines rows from two or more tables based on a related column. INNER JOIN, OUTER JOIN, LEFT JOIN, and RIGHT JOIN variations are available. If JOIN is used, an INNER JOIN is assumed.

If the BigQuery editor flags the table names in your query as unrecognizable, include the dataset name by substituting employee_data.employees for the employees table and employee_data.departments for the departments table, as shown in line 6 and line 8 below:

SELECT
  employees.name AS employee_name,
  employees.role AS employee_role,
  departments.name AS department_name
FROM
  employee_data.employees
INNER JOIN
  employee_data.departments ON
  employees.department_id = departments.department_id

Outputs:

A data analyst is working with two tables in a database. Which JOIN clause enables them to combine RIGHT and LEFT JOIN functionality to return matching records from either table?

A. OUTER JOIN

B. INNER JOIN

C. ALL JOIN

D. MATCH JOIN

The correct answer is A. OUTER JOIN. Explain: The OUTER JOIN clause enables them to combine RIGHT and LEFT JOIN functionality to return matching records from either table.

COUNT and COUNT DISTINCT

An alternate syntax uses the AS keyword to assign an alias name:

FROM 
     warehouse_orders.Orders AS orders 

Queries can run with or without the AS keyword. But using AS enables an alias to stand out so the query is easier to read.

Question: What is the key difference between COUNT and COUNT DISTINCT in a database query?

A. COUNT returns the number of values in a specified range. COUNT DISTINCT returns all values in a specified range.

B. COUNT returns the total values in a specified range. COUNT DISTINCT returns the number of rows in a specified range.

C. COUNT returns the number of columns in a specified range. COUNT DISTINCT only returns the distinct values within those columns.

D. COUNT returns the number of rows in a specified range. COUNT DISTINCT only returns the distinct values in a specified range.

The correct answer is D. COUNT returns the number of rows in a specified range. COUNT DISTINCT only returns the distinct values in a specified range. Explain: COUNT returns the number of rows in a specified range. COUNT DISTINCT only returns the distinct values in a specified range.

Using subqueries to aggregate data

To follow along with the instructor, you will need the Warehouse Orders dataset in BigQuery. If you didn't previously upload the CSV files for this dataset, follow the instructions in the Optional: Upload the warehouse dataset to BigQuery reading before you continue with this video.