5. Analyze Data to Answer Questions
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
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)
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
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 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
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
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 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
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 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
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
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
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
Data aggregation involves creating a _______ collection of data that originally came from multiple sources. 1. expanded 2. localized 3. summarized 4. modified
3. summarized
When working with subqueries, the outer query executes first. 1. True 2. False
2. False
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
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
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
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")
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
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 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
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)
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
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
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
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
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
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 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
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
________ 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
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