Google Data Analytics - Analyze Data to Answer Questions - Course 5

You are working with a dataset from a local community college. You sort the students alphabetically by last name. This is an example of which phase of analysis? 1. Transform data 2. Get input from others 3. Format and adjust data 4. Organize data (into a dataset)

3. Format and adjust data

An analyst notes that the "160" in cell A9 is formatted as text, but it should be Australian dollars. What spreadsheet tool can help them select the right format? 1. EXCHANGE 2. CURRENCY 3. Format as Currency 4. Format as Dollar

3. Format as Currency

You ask volunteers at a theater production which tasks they have already completed and add that data to a spreadsheet containing all required tasks. You will use the information provided by the volunteers to figure out which tasks still need to be done. This is an example of which phase of analysis? 1. Organize data (into a dataset) 2. Format and adjust data 3. Get input from others 4. Transform data

3. Get input from others

A data analyst wants to retrieve only records from a database that have matching values in two different tables. Which JOIN function should they use? 1. RIGHT JOIN 2. LEFT JOIN 3. INNER JOIN 4. OUTER JOIN

3. INNER JOIN

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? 1. It changes the value of the text string to zero 2. It searches for text strings within the spreadsheet that match the value 3. It converts the text string to a numerical value 4. It filters any text strings in the spreadsheet that equal the value

3. It converts the text string to a numerical value

A data analyst wants to add a spreadsheet dropdown list with three options: Draft, Edit, and Final. Which option from the Data Validation menu should they select? 1. Custom formula 2. Date 3. List of items 4. Text

3. List of items

A data analyst is sorting data in a spreadsheet. They select a specific collection of cells in order to limit the sorting to just specified cells. Which spreadsheet tool are they using? 1. Sort Sheet 2. Limit Sort 3. Sort Range 4. Limit Range

3. Sort Rnage

________ involves arranging data into a meaningful order to make it easier to understand, analyze, and visualize. 1. Filtering 2. Organizing 3. Sorting 4. Prioritizing

3. Sorting

You are performing a calculation during your analysis of a dataset. Which phase of analysis are you in? 1. Organize data 2. Get input from others 3. Transform data 4. Format and adjust data

3. Transform data

Data aggregation involves creating a _______ collection of data that originally came from multiple sources. 1. expanded 2. localized 3. summarized 4. modified

3. summarized

You are analyzing data about the capitals of different countries. In your SQL database, you have one column with the name of the countries and another column with the names of the capitals. What function can you use in your query to combine the countries and capitals into a new column? 1. JOIN 2. COMBINE 3. GROUP 4. CONCAT

4. CONCAT

You are creating a spreadsheet to help you with your job search. Every time you find an interesting job, you add it to the spreadsheet. Then, you want to indicate two possible options: Need to Apply or Applied. What spreadsheet tool will save you time by enabling you to create a dropdown list with Need to Apply and Applied as the possible options? 1. Find 2. Conditional formatting 3. Pop-up menus 4. Data validation

4. Data validation

Which spreadsheet menu function is used to sort all data in a spreadsheet by the ranking of a specific sorted column? 1. Sort Range 2. Sort Data 3. Sort By Rank 4. Sort Sheet

4. Sort Sheet

In the function =VLOOKUP*K2, 'Sheet 4'!A:B, 2, TRUE), what does the word TRUE indicate? 1. TRUE tells VLOOKUP to start at the top of the specified range 2. TRUE tells VLOOKUP to search for exact matches 3. TRUE tells VLOOKUP to search for as many matches as it can find in the specified range 4. TRUE tells VLOOKUP to search for approximate matches

4. TRUE tells VLOOKUP to search for approximate matches

A data analyst creates an absolute reference around a function array. What is the purpose of the absolute reference? 1. To keep a function array consistent so rows and columns will automatically change if the function is copied 2. To copy a function and apply it to all rows and columns 3. To automatically change numeric values to currency values 4. To lock the function array so rows and columns don't change if the function is copied

4. To lock the function array so rows and columns don't change if the function is copied

The data validation process is a form of data ________. 1. reporting 2. formatting 3. transformation 4. cleaning

4. cleaning

A data analyst uses ________ to decide which data is relevant to their analysis and which data types and variables are appropriate. 1. database relationships 2. database references 3. database normalization 4. database organization

4. database organization

A data analyst uses aliasing to make it easier to read and write a query. Aliasing involves temporarily _________ a table or column in a query. 1. removing 2. hiding 3. copying 4. naming

4. naming

A data analyst wants to calculate the number of rows that have a value of "shipped". Which function could they use? =SUM(G2:G30,"=shipped") =MAX(G2:G30,"=shipped") =COUNT(G2:G30,"=shipped") =COUNTIF(G2:G30,"=shipped")

=COUNTIF(G2:G30,"=shipped")

Spreadsheet cell H8 contains the text string "Marketing". To return the substring "Market", what is the correct syntax? =LEFT(H8, 6) =RIGHT(H8, 6) =RIGHT(6,H8) =LEFT(6,H8)

=LEFT(H8, 6)

A data analyst is working with a spreadsheet that has very long text strings. They use a function to count the number of characters in cell B9. What is the correct syntax of the function? =LEN(B:B9) =LEN("B9") =LEN(B,9) =LEN(B9)

=LEN(B9)

Spreadsheet cell B5 contains the text string "Fixed Price". To return the substring "Price", what is the correct syntax? =LEFT(B5, 7) =RIGHT(B5, 5) =LEFT(7,B5) =RIGHT(7,B5)

=RIGHT(B5, 5)

In a spreadsheet, cell J10 contains the date and time value 2/23/2021 7:00. What is the correct syntax to return only the four-digit time portion of the cell value? =RIGHT(J10, 4) =LEFT(J10, 4) =LEFT(4.J10) =RIGHT(4,J10)

=RIGHT(J10, 4)

Question 4) At this point, you notice that the information about state and zip code is in the same cell. However, your company's mailing list software requires states to be on a separate line from zip codes. What function do you use to move the 5-digit zip code in cell L2 into its own column?

=RIGHT(L2,5)

Question 1a) Which of the following functions will enable you to sort your spreadsheet by city (Column K) in ascending order?

=SORT(A2:R210, 11, TRUE)

A data analyst uses a function to sort a spreadsheet range between cells H1 and K65. They sort in ascending order by the first column, Column H. What is the syntax they are using? =SORT(H1:K65, A, FALSE) =SORT(H1:K65, 1, TRUE) =SORT(H1:K65, A, TRUE) =SORT(H1:K65, 1, FALSE)

=SORT(H1:K65, 1, TRUE)

You are calculating the sum of a range of cells from A2 through F2. What is the correct syntax for this calculation? =SUM(A2:F2) =SUM(A2+F2) =SUM(A2,F2) =SUM(A2*F2)

=SUM(A2:F2)

Question 10) Now that you provided her with the average donation amount, Tayen decides to invite 50 people to the grand opening of a new community garden. You return to your New Donor List spreadsheet to determine how much each donor gave in the past two years. You will use that information to identify the 50 top donors and invite them to the event. What is the correct syntax to add the contribution amounts in cells O2 and P2?

=SUM(O2,P2)

You are trying to determine what percentage of your monthly income is spent on big-ticket items, such as rent and groceries. To add together only the values from Column B that cost more than $150, what is the correct syntax? =SUMIF(B2:B12,<150) =SUMIF(B2:B12,"<150") =SUMIF(B2:B12,">150") =SUMIF(B2:B12,>150)

=SUMIF(B2:B12,">150")

You want to calculate a company's annual revenue using financial data. You use the SUMPRODUCT function to multiply each of the values in the arrays B3:B7 and C3:C7, then add them together. What is the correct syntax to complete this calculation? =SUMPRODUCT+(B3:B7/C3:C7) SUMPRODUCT(B3,B7:C3,C7) =SUMPRODUCT(B3:B7,C3:C7) =(SUMPRODUCT)(B3:B7*C3:C7)

=SUMPRODUCT(B3:B7,C3:C7)

To search for the population of Brazil, what is the correct VLOOKUP syntax? =VLOOKUP(Brazil, A2:B10, 3, false) =VLOOKUP("Brazil", A2:B10, 2, false) =VLOOKUP(Brazil, A2:B10, 2, false) =VLOOKUP(Brazil, A2,B10, 3, false)

=VLOOKUP("Brazil", A2:B10, 2, false)

The following is a selection from a spreadsheet: To search for the height of the building in Mecca, what is the correct VLOOKUP syntax? =VLOOKUP("Mecca", A2:D7, 3, false) =VLOOKUP(Mecca, A2:D7, 2, true) =VLOOKUP(Mecca, A2:D7, 2, false) =VLOOKUP(Mecca, A2,D7, 3, true)

=VLOOKUP("Mecca", A2:D7, 3, false)

The following is a selection from a spreadsheet: To search for the population of Nigeria, what is the correct VLOOKUP syntax? =VLOOKUP(Nigeria, A2:C10, 3, false) =VLOOKUP("Nigeria", A2:C10, 2, false) =VLOOKUP(Nigeria, A2:C10, 3, true) =VLOOKUP(Nigeria, A2,C10, 2, true)

=VLOOKUP("Nigeria", A2:C10, 2, false)

R

A programing language frequently used for statistical analysis, visualization, and other data analysis

In SQL, what is a subquery? A query nested within another query. A query written beneath another query. A query that contains optional instructions. A query that introduces the main query.

A query nested within another query.

In data analytics, what is the process of gathering data from multiple sources and combining it into a single, summarized collection? Data composition Data mapping Data aggregation Data grouping

Data aggregation

Question 2) You notice that many cells in the city column, Column K, are missing a value. So, you use the zip codes to research the correct cities. Now, you want to add the cities to each donor's row. However, you are concerned about making a mistake, such as a spelling error. What spreadsheet tool can you use to add a drop-down list with predetermined options for each city name?

Data validation

You are responsible for maintaining the integrity of a dataset. Multiple analysts are working with this spreadsheet. What spreadsheet tool can you use to ensure that accidental changes are not recorded in the data? Find Data validation Conditional formatting Pop-up menus

Data validation

Fill in the blank: A data analyst uses _____ to decide which data is relevant to their analysis and which data types and variables are appropriate.

Database organization enables analysts to make decisions about which data is relevant to pull for a specific analysis. It also helps them decide which data types and variables are appropriate.

A data analyst wants to add a spreadsheet dropdown list with three options: Draft, Edit, and Final. Which option from the Data Validation menu should they select? List of items Date Custom formula Text

List of items

When working with a new dataset, how can you ensure that your data is valid? Fill in missing values with values that will favor your initial hypothesis. Manually check the calculations of calculated columns. Convert all data to JavaScript Object Notation (JSON). Personally collect all data that you use in your analysis.

Manually check the calculations of calculated columns.

A data analyst writes a query in SQL. Inside this query, they have a second query. What is this second query called? Select all that apply. Central query Nested query Smaller query Subquery

Nested query Subquery

Sort range

Nothing else on the spreadsheet is rearranged besides the specified cells in a column

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? OUTER JOIN INNER JOIN ALL JOIN MATCH JOIN

OUTER JOIN

Question 8) Your company's direct-mail campaign was very successful, and Food Justice Rock Springs has continued partnering with Directly Dynamic. One thing you've been working on is assigning all donors identification numbers. This will enable you to clean and organize the lists more effectively. Meanwhile, another team member has been creating a prospect list that contains data about people who have indicated interest in getting involved with Food Justice Rock Springs. These people are also assigned a unique ID. Now, you need to compare your donor list with the dataset in your database and collect certain data from both. What SQL function will combine RIGHT and LEFT JOIN to return all matching records in both tables?

OUTER JOIN

R is a programming language frequently used for what tasks? Select all that apply.

R is a programming language frequently used for data analysis, statistical analysis, and visualization.

Fill in the blank: Sorting ranks data based on a specific _____ that you select.

Sorting ranks data based on a specific metric that you select. This involves arranging the data into a meaningful order to make it easier to understand, analyze, and visualize.

A data analyst is using the following formula: =COUNTIF(C2:C50, "=100"). Which part of the formula names the condition that the data must meet in order to be counted? COUNTIF C50 C2 =100

=100

What is the correct spreadsheet formula for multiplying 50 and 233? 50x233 =50x233 50*233 =50*233

=50*233

A spreadsheet cell contains the coldest temperature ever recorded in New Zealand: -22 °Celsius. What function will display that temperature in Fahrenheit?

=CONVERT(-22, "C", "F") will display -22 °C in Fahrenheit.

To convert the temperature in cell B2 in a Google spreadsheet from degrees Celsius to degrees Fahrenheit, what is the correct syntax for the CONVERT function? =CONVERT(B2, "F", "C") =CONVERT(B2, "C", "F") =CONVERT(B2, "Celsius", "Fahrenheit") =CONVERT(B2, "Fahrenheit", "Celsius")

=CONVERT(B2, "C", "F")

You are working with a dataset that has a large number of duplicate data values. What function can you use to determine the number of unique data values in a specified range? COUNT COUNT DISTINCT RETURN VALUES RETURN

COUNT DISTINCT

A spreadsheet cell contains the coldest temperature ever recorded in New Zealand: -22 Celsius. What function will display that temperature in Fahrenheit? 1. =CONVERT(-22, "C", "F") 2. =CONVERT(-22, C, F) 3. =CONVERT(-22, F, C) 4. =CONVERT(-22, "F", "C")

1. =CONVERT(-22, "C", "F")

What function can be used to return the number of characters in cell B8 so you can confirm that it contains exactly 20 characters? 1. =LEN(B8) 2. =LEN(20,B8) 3. =LEN(20) 4. =LEN(B8,20)

1. =LEN(B8)

In SQL, what is a subquery? 1. A query nested within another query 2. A query written beneath another query 3. A query that contains optional instructions 4. A query that introduces the main query

1. A query nested within another query

What benefit does a CREATE TABLE statement add to a temporary table? 1. Access for anyone to use the table 2. Specific naming conventions 3. Automated calculations 4. Metadata about the data in the table

1. Access for anyone to use the table

Which phase of the data analysis process has the goal of identifying trends and relationships? 1. Analyze 2. Act 3. Process 4. Prepare

1. Analyze

You are writing a SQL query to instruct a database to count values in a specified range. You only want to count each value once, even if it appears multiple times. Which function should you include in your query? 1. COUNT DISTINCT 2. COUNT 3. COUNT VALUES 4. COUNT RANGE

1. COUNT DISTINCT

Which of the following are methods for creating a version of a temporary table using SQL? Select all that apply. 1. CREATE TEMP TABLE statements 2. WHERE clauses 3. WITH clauses 4. CREATE TABLE statements

1. CREATE TEMP TABLE statements 3. WITH clauses 4. CREATE TABLE statements

Conditional formatting can be used for which spreadsheet tasks? Select all that apply. 1. Color-coding cells that contain dates after today 2. Highlighting cells that contain the word "ready" 3. Adding a dropdown menu 4. Allowing users to input only structured data and formulas

1. Color-coding cells that contain dates after today 2. Highlighting cells that contain the word "ready"

You're analyzing patient data for a health care company. During the data-validation process, you notice that the first date of service for some of the patients is later than the most recent date of service. Which type of data-validation check are you completing? 1. Data consistency 2. Data range 3. Data structure 4. Data type

1. Data consistency

A data analyst is working on a dataset and begins the transform data phase of analysis. What are some of the actions that they will take? Select all that apply. 1. Finding a correlation in data 2. Filtering data 3. Performing a calculation with data 4. Sorting data

1. Finding a correlation in data 3. Performing a calculation with data

During analysis, you complete a data-validation check for errors in customer identification (ID) numbers. Customer IDs must be eight characters and can contain numbers only. Which of the following customer ID errors will a data-type check help you identify? 1. IDs with text 2. IDs that are repeated 3. IDs in the wrong column 4. IDs with more than eight characters

1. IDs with text

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? 1. JOIN 2. CONNECT 3. LINK 4. MERGE

1. JOIN

Which of the following terms describe a subquery? Select all that apply. 1. Nested query 2. Small query 3. Inner select 4. Inner query

1. Nested query 3. Inner select 4. Inner query

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? 1. OUTER JOIN 2. INNER JOIN 3. ALL JOIN 4. MATCH JOIN

1. OUTER JOIN

The 4 phases of analysis

1. Organize data 2. Format and adjust data 3. Get input from others 4. Transform data

Which menu sort function is used to keep data together across rows? 1. Sort Sheet 2. Sort Column 3. Sort Row 4. Sort Range

1. Sort Sheet

A data analyst is sorting spreadsheet data. They want to make sure that, when they rearrange the data, data across rows is kept together. What technique should they use to sort the data? 1. Sort Sheet 2. Sort Together 3. Sort Rows 4. Sort Column

1. Sort sheet

A data analyst wants to organize a database to show only the 100 most recent real estate sales in stamford, Connecticut. How can they do that? 1. The data analyst should add a filter to return only sales in Stamford, Connecticut, then sort the most recent sales at the top of their list 2. The data analyst should filter out sales in Stamford, Connecticut, then sort the least recent sales at the top of their list 3. The data analyst should add a filter to return only sales in stamford, Connecticut, then sort the least recent sales at the top of their list 4. The data analyst should filter out sales in stamford, Connecticut, then sort the most recent sales at the top of their list

1. The data analyst should add a filter to return only sales in Stamford, Connecticut, then sort the most recent sales at the top of their list

What is the goal of the analysis phase of the data analysis process? 1. To identify trends and relationships in data 2. To describe data structures 3. To make generalizations about data 4. To generate new data

1. To identify trends and relationships in data

What is the purpose of an absolute reference within a function, such as "$C$3"? 1. To lock rows and columns so they won't change when a function is copied 2. To represent missing values in a formula or function 3. To remove unnecessary instructions from a formula or function 4. To make formulas and functions unconditional

1. To lock rows and columns so they won't change when a function is copied

During which of the four phases of analysis can you find a correlation between two variables? 1. Transform data 2. Get input from others 3. Organize data 4. Format and adjust data

1. Transform data

You are working with three datasets about voter turnout in your county. First, you identify relationships and patterns between the datasets. Then, you use formulas and functions to make calculations based on your data. This is an example of which phase of analysis? 1. Transform data 2. Organize data (into a dataset) 3. Get input from others 4. Format and adjust data

1. Transform data

An INNER JOIN is a function that returns records with matching values in two or more tables. An OUTER JOIN is a function that combines RIGHT and LEFT JOIN to return all matching records in both tables. 1. True 2. False

1. True

In spreadsheets, data analysts can sort a range from the Data tab in the menu or by typing a function directly into an empty cell. 1. True 2. False

1. True

When using VLOOKUP, there are some common limitations that data analysts should be aware of. One of these limitations is that VLOOKUP only returns the first match it finds, even if there are many possible matches within the column. 1. True 2. False

1. True

When you use the menu function Sort Range, nothing in your spreadsheet gets rearranged except for the fields in the specified range. 1. True 2. False

1. True

If you enter FALSE as the last input parameter in a VLOOKUP function, VLOOKUP will search for ___________. 1. an exact match 2. the closet match 3. a text string 4. a numeric value

1. an exact match

To change a text string in spreadsheet cell F8 to a numerical value, what is the correct function? 1. =NUM(F8) 2. =VALUE(F8) 3. =MATCH(F8) 4. =CONVERT(F8)

2. =VALUE(F8)

When are temporary tables automatically deleted? 1. After completing all calculations in the table 2. After ending the session in a SQL database 3. After running a query in your SQL database 4. After running a report from the table

2. After ending the session in a SQL database

A data analyst wants to temporarily name a column in their query to make it easier to read and write. What technique should they use? 1. Naming 2. Aliasing 3. Filtering 4. Tagging

2. Aliasing

What is the key difference between COUNT and COUNT DISTINCT in a database query? 1. COUNT returns the total values in a specified range. COUNT DISTINCT returns the number of rows in a specified range 2. COUNT returns the number of rows in a specified range. COUNT DISTINCT only returns the distinct values in a specified range 3. COUNT returns the number of values in a specified range. COUNT DISTINCT returns all values in a specified range 4. COUNT returns the number of columns in a specified range. COUNT DISTINCT only returns the distinct values within those columns

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

When using the Values menu in a pivot table, what does the Summarize By drop-down menu do? 1. Filters the values in ascending order 2. Changes the function applied to the values 3. Choose how to sort the values 4. Selects the values for the pivot table

2. Changes the function applied to the values

What are the goals of checking and rechecking the quality of your data during data validation? Select all that apply. 1. Data is sorted and filtered 2. Data is consistent 3. Data is secure 4. Data is complete and accurate

2. Data is consistent 3. Data is secure 4. Data is complete and accurate

An analyst uses = SORT to sort spreadsheet data in descending order. What do they type at the end of their sort function? 1. DESCEND 2. FALSE 3. Z - A 4. TRUE

2. FALSE

In VLOOKUP, TRUE tells the function to search for exact matches, and FALSE tells the function to look for approximate matches. 1. True 2. False

2. False

To narrow the scope of a query, a data analyst filters by a particular criteria. This type of filtering must be done one variable at a time. 1. True 2. False

2. False

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

2. False

When working with subqueries, the outer query executes first. 1. True 2. False

2. False

Typically, a data analyst uses filters when they want to expand the amount of data they are working with. 1. True 2. False

2. Fasle

In a SQL query, which calculation does the modulo(%) operator perform? 1. It applies an exponent to a value 2. It returns the remainder of a division calculation 3. It finds the square root of a number 4. It converts a decimal to a percent

2. It returns the remainder of a division calculation

A data analyst wants to ensure spreadsheet formulas continue to run correctly, even if someone enters the wrong data by mistake. Which data-validation menu option should they select to flag data entry errors? 1. Forbid Entry 2. Reject Invalid Inputs 3. Deny Help Text 4. Remove Validation

2. Reject Invalid Inputs

Which of the following statements accurately describe differences between the sort from a spreadsheet's Data tab and a written SORT function? Select all that apply. 1. The sort from a spreadsheet's Data tab can sort in ascending or descending order, while a written SORT function automatically assumes ascending order 2. The sort from a spreadsheet's Data tab can exclude a header row in the data range from being sorted, while the data range for a written SORT function should never contain a header row 3. The sort from a spreadsheet's Data tab can only sort data by a single sort condition or column, while a written SORT function can sort data by multiple columns 4. The sort from a spreadsheet's Data tab overwrites the cells containing the unsorted data with the sorted data, while a written SORT function inserts the sorted data in a different cell range

2. The sort from a spreadsheet's Data tab can exclude a header row in the data range from being sorted, while the data range for a written SORT function should never contain a header row 4. The sort from a spreadsheet's Data tab overwrites the cells containing the unsorted data with the sorted data, while a written SORT function inserts the sorted data in a different cell range

A calculated field within a pivot table is used to carry out calculations based on what? 1. The filtered values 2. The values of other fields 3. The syntax of the available formulas 4. The function in the calculated field

2. The values of other fields

During which of the four phases of analysis do you gather the relevant datasets for a project? 1. Get input from others 2. Transform data 3. Format and adjust data 4. Organize data

2. Transform data

A data analyst has a large number of sales records in a table. They want to perform calculations on a small subset of the table. Rather than filtering the data over and over, what should they do? 1. Use a backup table 2. Use a temporary table 3. Use an alternate table 4. Use a copy of the table

2. Use a temporary table

A data analyst uses the SUM function to add together numbers from a spreadsheet. However, after getting a zero result, they realize the numbers are actually text. What function can they use to convert the text to a numeric value? 1. DIGIT 2. VALUE 3. CONVERT 4. FIGURE

2. VALUE

A data analyst is working with a spreadsheet that has very long text strings. They use the LEN function to count the number of _________ in the text strings. 1. fields 2. characters 3. values 4. substrings

2. characters

Spreadsheet cell F2 contains the text string "Dashboard". To return the substring "board", what is the correct syntax? 1. =LEFT(F2, 5) 2. =RIGHT(5, F2) 3. =RIGHT(F2, 5) 4. =LEFT(5, F2)

3. =RIGHT(F2, 5)

A data analyst uses a function to sort a spreadsheet range between cells H1 and K65. They sort in ascending order by the first column, Column H. What is the syntax they are using? 1. =SORT(H1:K65, 1, FALSE) 2. =SORT(H1:K65, A, TRUE) 3. =SORT(H1:K65, 1 TRUE) 4. =SORT(H1:K65, A, FALSE)

3. =SORT(H1:K65, 1 TRUE)

In SQL, _______ can be used to combine strings from multiple tables in order to create a new string. 1. COMBINE 2. CONCAT 3. CONCATENATE 4. CONNECT

3. CONCAT

A data analyst writes a query that asks a database to return only distinct values in a specified range, rather than including repeating values. Which function do they use? 1. RETURN 2. RETURN VALUES 3. COUNT DISTINCT 4. COUNT

3. COUNT DISTINCT

A data analyst clicks on the Format Cells in drop-down menu and selects the option Text Is Exactly November. This changes the color of all the cells that contain the word November. What spreadsheet tool is the analyst using? 1. CONVERT 2. Filtering 3. Conditional formatting 4. Data validation

3. Conditional formatting

A data analyst in human resources uses a spreadsheet to keep track of employees' work anniversaries. They add color to any employee who has worked for the company for more than 10 years. Which spreadsheet tool changes how cells appear when values equal 10 or more? 1. Data validation 2. Add color 3. Conditional formatting 4. CONVERT

3. Conditional formatting

When working with a spreadsheet, data analysts can use the _______ function to locate specific characters in a string. 1. FROM 2. IDENTIFY 3. FIND 4. WHERE

3. FIND

During which analysis phase does an analyst sort and filter data? 1. Transform 2. Get input from others 3. Format and adjust 4. Organize

3. Format and adjust

What SQL keyword is used to define a name for a calculated column? SELECT FROM WITH AS

AS

Data validation

Add dropdown lists with predetermined options Create custom checkboxes Protect structured data and formulas

Sort sheet

All of the data in a spreadsheet is sorted by the ranking of a specific sorted column - data across rows is kept together

You are working on a project about music and have a table of genres you need to sort. The Genres table contains the columns GenreId and Name. Write a SQL query to return the name of each genre from this table in alphabetical order.

Alternative and World are returned when making the following query: SELECT Name FROM genres ORDER BY Name Not specifying ascending or descending in your query will sort by ascending order by default. This is appropriate for sorting in alphabetical order.

During which analysis phase does an analyst sort and filter data?

An analyst sorts and filters data during the format and adjust analysis phase.

Which phase of the data analysis process has the goal of identifying trends and relationships?

Analyze

Which of the following are appropriate uses for a spreadsheet's data validation tool? Select all that apply. Avoiding invalid inputs to functions Protecting structured data Merging two or more columns. Adding drop down menus on cells

Avoiding invalid inputs to functions Protecting structured data Adding drop down menus on cells

Question 5) Next, you duplicate your dataset twice using the Sheet Menu. You rename the first sheet Donation Form List, and you remove the cities that are further than 50 miles from Rock Springs. You rename the second sheet Postcard List, and you remove the cities that are within 50 miles of Rock Springs. Then, you import these datasets into your company's mailing list database. In a mailing list database, you create two tables: Donation_Form_List and Postcard_List. You decide to clean the Donation_Form_List first. Your company's mailing list software requires units to be on the same line as street addresses. However, they are currently in two separate columns (street_address and unit). Which SQL function will instruct the database to combine two columns into a new column called "address"?

CONCAT

You are analyzing employee data for your company. In your SQL database, you have one column with the first names of the employees and another column with their last names. What function can you use in your query to combine the employee first names and last names into a new column? COMBINE CONCAT GROUP JOIN

CONCAT

You are analyzing employee data for your company. In your SQL database, you have one column with the first names of the employees and another column with their last names. What function can you use in your query to combine the employee first names and last names into a new column? JOIN CONCAT COMBINE GROUP

CONCAT

What is the key difference between COUNT and COUNT DISTINCT in a database query? COUNT returns the number of values in a specified range. COUNT DISTINCT returns all values in a specified range. COUNT returns the number of rows in a specified range. COUNT DISTINCT only returns the distinct values in a specified range. COUNT returns the total values in a specified range. COUNT DISTINCT returns the number of rows in a specified range. COUNT returns the number of columns in a specified range. COUNT DISTINCT only returns the distinct values within those columns.

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

Question 11) Tayen informs you that she's thinking about inviting anyone who donated at least $100 in 2018, as well. However, she only has five open spaces. She asks you to report how many people gave at least $100 so she can determine if they can also be invited to the event. Which spreadsheet function do you use to count how many donations of $100 or greater appear in Column O (Contributions 2018)?

COUNTIF

Which of the following are methods for creating a version of a temporary table using SQL? Select all that apply. WHERE clauses CREATE TABLE statements CREATE TEMP TABLE statements WITH clauses

CREATE TABLE statements CREATE TEMP TABLE statements WITH clauses

When using the Values menu in a pivot table, what does the Summarize By drop-down menu do? Chooses how to sort the values Selects the values for the pivot table Filters the values in ascending order Changes the function applied to the values

Changes the function applied to the values

You are querying a database that contains data about music. Each musical genre is given an ID number. You are only interested in data related to the genre with ID number 2. The genre IDs are listed in the genre_id column. You write the following SQL query, but it is incorrect. What is wrong with the query? SELECT * FROM Track WHERE composer = 2 Composer is not capitalized in line 3. SELECT, FROM, and WHERE are capitalized. Composer should be genre_id in line 3. Line 3 contains an equal sign.

Composer should be genre_id in line 3.

A data analyst in human resources uses a spreadsheet to keep track of employees' work anniversaries. They add color to any employee who has worked for the company for more than 10 years. Which spreadsheet tool changes how cells appear when values equal 10 or more? Add color Conditional formatting CONVERT Data validation

Conditional formatting

Question 3) Now, you decide to address Tayen's request to include a handwritten note in the direct-mail piece for anyone who gave at least $100 last year. Which of the following spreadsheet tools will enable you to change how cells appear if they contain a value of $100 or more?

Conditional formatting

You are using a spreadsheet to keep track of your newspaper subscriptions. You add color to indicate if a subscription is current or has expired. Which spreadsheet tool changes how cells appear when values meet each expiration date? Conditional formatting Data validation CONVERT Add color

Conditional formatting

Conditional formatting can be used for which spreadsheet tasks? Select all that apply

Conditional formatting is a spreadsheet tool that changes how cells appear when values meet specific conditions. It can be used to change a cell's color in order to highlight it.

You are analyzing the results of retail sales calculations. You click in cell H10 and find the following formula: =H9/G9. What operation is taking place in this cell? Subtraction Multiplication Division Addition

Division

You are working in a spreadsheet and use the SUMIF function in the following formula as part of your analysis. =SUMIF(D2:D10,">=50",E2:E10) Which part of this formula indicates the range of values to be added? E2:E10 >=50 =SUMIF D2:D10

E2:E10

To find the average of a set of values, a data analyst can type AVERAGE in both spreadsheets and SQL. TRUE FALSE

FALSE

Typically, a data analyst uses filters when they want to expand the amount of data they are working with.

False

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 False

False

When using VLOOKUP, there are some common limitations that data analysts should be aware of. One of these limitations is that VLOOKUP can only return a value from the data to the left of the matched value. True False

False

When working with spreadsheets, data analysts can use the WHERE function to locate specific characters in a string. True False

False

Question 7) You finish cleaning your datasets, so you decide to review Tayen's email one more time to make sure you completed the task fully. It's a good thing you checked because you forgot to identify people who have served on the board of directors or board of trustees. She wants to write them a thank-you note, so you need to locate them in the database. To retrieve only those records that include people who have served on the board of trustees or on the board of directors, you use the WHERE function. The syntax is: SELECT * FROM Donation_Form_List WHERE. Board_Member = "TRUE" AND Trustee = "TRUE" True or False?

False Correct syntax: SELECT * FROM Donation_Form_List WHERE. Board_Member = "TRUE" OR Trustee = "TRUE"

A data analyst investigating a data set is interested in showing only data that matches given criteria. What is this known as? Filtering Modeling Measuring Sorting

Filtering

To narrow the scope of a query, a data analyst filters by a particular criteria. This type of filtering must be done one variable at a time.

Filtering can be done by single variable or multiple variables, depending on the query's needs

During which of the four phases of analysis can you find a correlation between two variables?

Finding a correlation between two variables occurs while transforming data.

An analyst is working with a dataset of financial data. The numerical data is correct but it is formatted as U.S. dollars, and the analyst needs it to be in British pounds. What spreadsheet tool can help them select the right format? Format as Money Format as Dollars Format as Currency Format as Pounds

Format as Currency

An analyst notes that the "235" in cell B8 is formatted as text, but it should be Euros. What spreadsheet tool can help them select the right format? Format as Currency Format as Money Format as Euros Format as Number

Format as Currency

Openness (or open data)

Free access, usage, and sharing of data

During which of the four phases of analysis do you gather the relevant datasets for a project?

Get input from others

Conditional formatting can be used for which spreadsheet tasks? Select all that apply. Adding a dropdown menu Highlighting cells that contain the word "ready" Allowing users to input only structured data and formulas Color-coding cells that contain dates after today

Highlighting cells that contain the word "ready" Color-coding cells that contain dates after today

Question 8a) What SQL function will return records with matching values in both tables?

INNER JOIN

To convert the temperature in cell B2 in a Google spreadsheet from degrees Celsius to degrees Fahrenheit, what is the correct syntax for the CONVERT function?

In Google Sheets, to convert the temperature in cell B2 from degrees Celsius to degrees Fahrenheit, the correct syntax for the CONVERT function is =CONVERT(B2, "C", "F").

Fill in the blank: In SQL, _____ can be used to combine strings from multiple tables in order to create a new string.

In SQL, CONCAT can be used to combine strings from multiple tables in order to create a new string.

You are working with a database that contains invoice data about online music purchases. You are only interested in invoices sent to customers located in the city of Delhi. You want to sort the invoices by order total in ascending order. The order totals are listed in the total column. You write the SQL query below. However this query is incorrect. What is wrong with it? SELECT * FROM invoice WHERE billing_city = "Delhi" ORDER BY order_total In line 4, order_total should be total. Line 4 contains the word BY. In line 3, "Delhi" has quotation marks. SELECT, FROM, WHERE, and ORDER BY are capitalized.

In line 4, order_total should be total.

A data analyst is using the spreadsheet tool Sort Range. What purpose does this tool serve? It sorts all of the data in a spreadsheet by a specific sorted row. It allows the analyst to sort a specific selection of cells only. It sorts all of the data in a spreadsheet by the ranking of a specific sorted row. It allows the analyst to sort the data in a spreadsheet by a specific sorted column.

It allows the analyst to sort a specific selection of cells only.

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? It searches for text strings within the spreadsheet that match the value. It changes the value of the text string to zero. It converts the text string to a numerical value. It filters any text strings in the spreadsheet that equal the value.

It converts the text string to a numerical value.

In a SQL query, which calculation does the modulo (%) operator perform? It finds the square root of a number It returns the remainder of a division calculation It converts a decimal to a percent It applies an exponent to a value

It returns the remainder of a division calculation

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? LINK CONNECT MERGE JOIN

JOIN

You are querying a database of keynote speakers to determine who has expertise in zoology. For your project, you only need the first 12 records. What clause should you add to the following SQL query? SELECT speakers FROM keynote_table WHERE expertise = "zoology" LIMIT 12 LIMIT = 12 LIMIT_12 LIMIT,12

LIMIT 12

Incorrectly formatted data can :

Lead to mistakes Take time to fix Affect stakeholder's decision-making

A data analyst is writing a SQL query. Which commands would they use to first multiply values from a table, then place the resulting products in a new column? Select all that apply. SELECT AS DIVIDE FROM

SELECT DIVIDE FROM

Question 13) Your team creates a highly effective prospects list for Food Justice Rock Springs. After a few months, many of these prospects become donors. Now, Tayen wants to know the top three cities in which these new donors live. She will use that information to determine if it's still true that people who live closer to Rock Springs are more likely to donate. Which SQL query will retrieve the number of donors in each city, sorted high to low? SELECT COUNT(DonorID), City FROM new_donor_list GROUP BY COUNT ORDER BY City(DonorID) ASC SELECT COUNT(DonorID), City FROM new_donor_list GROUP BY COUNT ORDER BY City(DonorID) DESC SELECT COUNT(DonorID), City FROM new_donor_list GROUP BY City ORDER BY COUNT(DonorID) ASC SELECT COUNT(DonorID), City FROM new_donor_list GROUP BY City ORDER BY COUNT(DonorID) DESC

SELECT COUNT(DonorID), City FROM new_donor_list GROUP BY City ORDER BY COUNT(DonorID) DESC

Filtering

Showing only the data that meets a specific criteria while hiding the rest

Question 1) The client has asked you to send two separate mailings: one to people within 50 miles of Rock Springs; the other to anyone outside that area. So, to research each donor's distance from the city, you first need to find out where all of these people live. You could scroll through 209 rows of data, but you know there is a more efficient way to organize the cities. Which of the following tools will enable you to sort your spreadsheet by city (Column K) in ascending order?

Sort Sheet by Column K from A to Z

Which spreadsheet menu function is used to sort all data in a spreadsheet by the ranking of a specific sorted column?

Sort Sheet is used to sort all data in a spreadsheet by the ranking of a specific sorted column.

A data analyst is sorting data in a spreadsheet. They select a specific collection of cells in order to limit the sorting to just specified cells. Which spreadsheet tool are they using?

Sort range

A data analyst is sorting data in a spreadsheet. Which tool are they using if all of the data is sorted by the ranking of a specific sorted column and data across rows is kept together?

Sort sheet sorts all of the data in a spreadsheet by the ranking of a specific sorted column. Also, data across rows is kept together.

You are working with a dataset from a local community college. You sort the students alphabetically by last name. This is an example of which phase of analysis?

Sorting a list of students alphabetically is an example of formatting and adjusting data. This is a step analysts take to rearrange the data to make it easier to work with.

In spreadsheets, data analysts can sort a range from the Data tab in the menu or by typing a function directly into an empty cell.

Sorting a range and sorting a sheet can both be done from the menu and written as a function. Analysts can work from the Data tab in the menu or type a function directly into an empty cell.

Fill in the blank: _____ involves arranging data into a meaningful order to make it easier to understand, analyze, and visualize.

Sorting involves arranging data into a meaningful order to make it easier to understand, analyze, and visualize.

VLOOKUP can have problems when used on data values that have leading and trailing spaces. What function can be used to eliminate these spaces? CUT NOSPACE VALUE TRIM

TRIM

Question 12) The community garden grand opening was a success. In addition to the 55 donors Food Justice Rock Springs invited, 20 other prospects attended the event. Now, Tayen wants to know more about the donations that came in from new prospects compared to the original donors. This SQL query can be used to identify the percentage of contributions from prospects compared to total donors: SELECT event_contributions, Total_donors, Total_prospects, (Total_prospects / Total_donors * 100) AS Prospects_Percent FROM contributions_data True or False?

TRUE

In the function =VLOOKUP(K2,'Sheet 4'!A:B,2,TRUE), what does the word TRUE indicate? TRUE tells VLOOKUP to start at the top of the specified range. TRUE tells VLOOKUP to search for exact matches. TRUE tells VLOOKUP to search for approximate matches. TRUE tells VLOOKUP to search for as many matches as it can find in the specified range.

TRUE tells VLOOKUP to search for approximate matches.

A data analyst is working with a spreadsheet that has very long text strings. Rather than counting the characters themselves to determine the number of characters they contain, what tool can they use? The CHAR function The MID function The LEN function The COUNT function

The LEN function

A data analyst wants to add a spreadsheet dropdown list with three options: Draft, Edit, and Final. Which option from the Data Validation menu should they select?

The analyst should select List of Items. This option will enable them to select the three options Draft, Edit, Final.

You are working with a database table that contains data about playlists for different types of digital media. You are only interested in the first 4 playlists. You write the SQL query below. Add a LIMIT clause that will return only the first 4 playlists

The clause LIMIT 4 will return only the first 4 playlists. The complete query is SELECT * FROM playlist LIMIT 4. The LIMIT clause sets a limit on the number of rows your query returns. The Movies playlist appears in row 2 of your query result.

You are working with a database table that contains data about music genres. You want to sort the genres by name in ascending order. The genres are listed in the genre_name column. You write the SQL query below. Add an ORDER BY clause that will sort the genres by name in ascending order.

The clause ORDER BY genre_name will sort the genres by name in ascending order. The complete query is SELECT * FROM genre ORDER BY genre_name. The ORDER BY clause tells the database how to organize the data it returns. The ORDER BY clause sorts data in ascending order by default. The Blues genre appears in row 3 of your query result.

You are working with a database table that contains employee data. You want to sort the employees by hire date in descending order. The hire dates are listed in the hire_date column. You write the SQL query below. Add an ORDER BY clause that will sort the employees by hire date in descending order.

The clause ORDER BY hire_date DESC will sort the employees by hire date in descending order. The complete query is SELECT * FROM employee ORDER BY hire_date DESC. The ORDER BY clause tells the database how to organize the data it returns. The ORDER BY clause sorts data in ascending order by default. The DESC command is used to sort data in descending order. The employee Laura Callahan appears in row 1 of your query result.

You are working with a database that contains invoice data about online music purchases. You are only interested in invoices sent to customers located in the city of Delhi. You want to sort the invoices by order total in ascending order. The order totals are listed in the total column. You write the SQL query below. Add an ORDER BY clause that will sort the invoices by order total in ascending order.

The clause ORDER BY total will sort the invoices by order total in ascending order. The complete query is SELECT * FROM invoice WHERE billing_city = "Delhi" ORDER BY total. The ORDER BY clause tells the database how to organize the data it returns. The ORDER BY clause sorts data in ascending order by default. The total 3.96 appears in row 4 of your query result.

You are querying a database that contains data about music. You are only interested in data related to the jazz musician Miles Davis. The names of the musicians are listed in the composer column. You write the SQL query below. Add a WHERE clause that will return only data about music by Miles Davis.

The clause WHERE composer = "Miles Davis" will return only data about music by Miles Davis. The complete query is SELECT * FROM track WHERE composer = "Miles Davis". The WHERE clause filters results that meet certain conditions. The WHERE clause includes the name of the column, an equals sign, and the value(s) in the column to include. The track Now's The Time by Miles Davis appears in row 1 of your query result. 8. Question 8 You are working with a database that contains invoice data about online music purchases. Y

You are working with a database table that contains customer data. The country column designates the country where each customer is located. You want to find out which customers are located in Brazil. You write the SQL query below. Add a WHERE clause that will return only customers located in Brazil.

The clause WHERE country = "Brazil" will return only customers located in Brazil. The complete query is SELECT * FROM customer WHERE country = "Brazil". The WHERE clause filters results that meet certain conditions. The WHERE clause includes the name of the column, an equals sign, and the value(s) in the column to include. There are 5 customers located in Brazil.

A data analyst sorts a spreadsheet range between cells F19 and G82. They sort in ascending order by the second column, Column G. What is the syntax they are using?

The correct syntax is =SORT(F19:G82, 2, TRUE). The first part of the function sorts the data in the specified range. The 2 represents the second column. And a TRUE statement sorts in ascending order.

A data analyst wants to organize a database to show only the 100 most recent real estate sales in Stamford, Connecticut. How can they do that?

The data analyst should add a filter for only sales in Stamford, Connecticut, then sort the most recent sales at the top of their list.

What function can be used to return the number of characters in cell B8 so you can confirm that it contains exactly 20 characters?

The function =LEN(B8) will display the number of characters in cell B8. The LEN function returns the length of a string of text by counting the number of characters it contains.

When you use the menu function Sort Range, nothing in your spreadsheet gets rearranged except for the fields in the specified range.

The menu function Sort Range only sorts fields within the specified range. Data across rows are not kept together when sorted.

Analysis

The process used to make sense of the data collected

Which of the following statements accurately describe differences between the sort from a spreadsheet's Data tab and a written SORT function? Select all that apply.

The sort from a spreadsheet's Data tab can exclude a header row in the data range from being sorted, while the data range for a written SORT function should never contain a header row.

Which of the following is an example of an array in this spreadsheet? All cells with number values All cells with values greater than 100 The values in cells B2 through B31 Cells D7 and D14

The values in cells B2 through B31

In the data analysis process, which of the following refers to a phase of analysis? Select all that apply.

There are four phases of analysis: organize data, format and adjust data, get input from others, and transform data by observing relationships between data points and making calculations.

Which of the following statements about temporary tables is correct? They are automatically deleted when the SQL database session ends. They are a special feature of BigQuery unavailable in other RDBMS. They are declared by enclosing a FROM statement between ##. They must be created using the WITH AS SQL clause.

They are automatically deleted when the SQL database session ends.

Best practices for searching online

Thinking skills Data analytics terms Basic knowledge of tools

An analyst sorts and filters data during the format and adjust analysis phase.

This is an example of getting input from others. Getting input involves soliciting information from other sources to inform your decisions.

You are working with three datasets about voter turnout in your county. First, you identify relationships and patterns between the datasets. Then, you use formulas and functions to make calculations based on your data. This is an example of which phase of analysis?

This is an example of transforming data, which involves identifying relationships and patterns in the data, and making calculations.

What is the purpose of the <> operator in SQL? To add two values To return the remainder of a division operation To set a value equal to another To check if two values are not equal

To check if two values are not equal

A data analyst wants to ensure spreadsheet formulas continue to run correctly, even if someone enters the wrong data by mistake. Which data-validation menu option should they select to flag data entry errors?

To ensure spreadsheet formulas continue to run correctly, even if someone enters the wrong data by mistake, select Reject Invalid Inputs to flag that data as invalid.

In a spreadsheet, cell J10 contains the date and time value 2/23/2021 7:00. What is the correct syntax to return only the four-digit time portion of the cell value?

To return only the time portion of the cell value, the syntax is =RIGHT(J10, 4). The time, 7:00, is located four characters from the right of the string.

A data analyst wants to sort a list of greenhouse shrubs by price from least expensive to most expensive. Which statement should they use?

To sort a list of greenhouse shrubs by price from least expensive to most expensive, they should use ORDER BY shrub_price.

An analyst uses =SORT to sort spreadsheet data in descending order. What do they type at the end of their sort function?

To sort a spreadsheet in descending order using the SORT function, the analyst types FALSE at the end of their sort function.

What is the purpose of using pivot tables? To allow the use of SQL in spreadsheets To view data in multiple ways to find insights and trends 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

A data analyst examining a data set attempts to determine if there are any patterns in the data. What phase of analysis is the analyst in? Transform data Get input from others Organize data Format and adjust data

Transform data

During which of the four phases of analysis can you find relationships and patterns within data? Get input from others Format and adjust data Transform data Organize data

Transform data

Which of the following actions might occur when transforming data? Select all that apply.

Transforming data means identifying relationships and patterns between the data, and making calculations based on the data you have.

An INNER JOIN is a function that returns records with matching values in two or more tables. An OUTER JOIN is a function that combines RIGHT and LEFT JOIN to return all matching records in both tables. True False

True

Question 6) Your database contains people who live in many areas of Wyoming. However, it's important to align your in-house data with the data from Food Justice Rock Springs. You also need to separate your data into the two lists: Donation_Form_List and Postcard_List. They will be based on each city's distance from Rock Springs. The zip codes are in a column called zip_code. To select all data from the Donation_Form_List organized by zip code, you use the ORDER BY function. The syntax is: SELECT * FROM Donation_Form_List ORDER BY zip_code True or False?

True

Question 9) Your next task is to identify the average contribution given by donors over the past two years. Tayen will use this information to set a donation minimum for inviting donors to an upcoming event. You have performed the calculations for 2019, so now you move on to 2020. To return average contributions in 2020 (contributions_2020), you use the AVG function. You use the following section of a SQL query to find this average and store it in the AvgLineTotal variable: AVG(contributions_2020) AS AvgLineTotal True or False?

True

VLOOKUP will search for the closest match, enter _____ .

True

Which of the following SQL statements correctly implements a WITH AS clause? WITH AS my_table AS (SELECT * FROM other_table); WITH my_table AS (SELECT * FROM other_table); WITH AS my_table AS SELECT * FROM other_table; WITH my_table AS SELECT * FROM other_table;

WITH my_table AS (SELECT * FROM other_table);

Sorting

When you arrange data into a meaningful order to make it easier to understand, analyze, and visualize

Customized sort order

When you sort data in a spreadsheet using multiple conditions

The database you use contains a Tracks table. The table contains the following columns: TrackId, Name, AlbumId, MediaTypeId, GenreId, Composer, Milliseconds, Bytes, and UnitPrice. Write a SQL query to pull all columns from the Tracks table for only tracks with Chris Cornell as the composer. Sort the results in descending order by GenreId.

You Know My Name is the first value returned in the Name column when making the following query: SELECT * FROM Tracks WHERE Composer='Chris Cornell' ORDER BY GenreId DESC When executed, this query returns a list of tracks that were composed by Chris Cornell. The list would be sorted by GenreId in descending order.

Mental model

Your thought process and the way you approach a problem

Fill in the blank: When writing a function, a data analyst wraps a table array in dollar signs. This is an _____ , which is used to lock the array so rows and columns don't change if the function is copied. arbitrary reference absolute reference accurate reference authentic reference

absolute reference

If you enter FALSE as the last input parameter in a VLOOKUP function, VLOOKUP will search for _____. an exact match the closest match a text string a numeric value

an exact match

Fill in the blank: The data validation process is a form of data _____. reporting formatting cleaning transformation

cleaning

A data analyst uses the following formula to calculate a new column in a SQL query. What best describes the result of the formula? (colA + colB) / colC = new_col colB is divided by colC then the result is added to colA. colB is subtracted from colA then the result is multiplied by colC. colB is added to colA then the result is multiplied by colC. colB is added to colA then the result is divided by colC.

colB is added to colA then the result is divided by colC.

You are working with a dataset with the column name "firstquarterexpenses." How can you rename this column to make it more readable? first+quarter+expenses first quarter expenses first_quarter_expenses Firstquarterexpenses

first_quarter_expenses

A data analyst clicks on the Format Cells in drop-down menu and selects the option Text Is Exactly November. This changes the color of all the cells that contain the word November. What spreadsheet tool is the analyst using?

he data analyst is using conditional formatting. Conditional formatting is a spreadsheet tool that changes how cells appear when values meet specific conditions.