The analysis process Week 1

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.

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

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?

n 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").

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.

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.

The 4 phases of analysis

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

R

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

Openness (or open data)

Free access, usage, and sharing of data

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.

Analysis

The process used to make sense of the data collected

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.

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.

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.

Best practices for searching online

Thinking skills Data analytics terms Basic knowledge of tools

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.

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.

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.

Incorrectly formatted data can :

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

Sort range

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

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.

Filtering

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

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.

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 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.

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.

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.

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.

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.

Mental model

Your thought process and the way you approach a problem