Week 2: Formatting and Adjusting Data Quiz Answers
Organizing Data to Begin Analysis INTRODUCTION
Organizing and formatting the data is indeed a step in the analysis. In this part of the course, you will learn about SQL query construction for joining different datasets and other techniques for organization and formatting data with the Google Data Analytics Professional Certificate program presented on Coursera.
User with meaningful input and feedback would be critical in speeding up the user’s grasp of some terms and their relevance at workplace application. By the end of this course, learners will know how to well organize and format data in analysis, leading to maximum exploitation of the analytics tool.
Learning Outcomes:
- Show understanding in the conversion and formatting of data
- Demonstrate proficiency in joining multiple datasets using spreadsheet and SQL queries
- Engage in discussions on the importance of seeking feedback and support from others in learning
Hands-On Activity: Combine multiple pieces of data
1. In an empty cell in the CONCAT function exercise spreadsheet, type the function =CONCATENATE(A7, ” “, B7). What does this function return?
- John QuincyAdams
- #N/A
- JohnQuincyAdams
- John Quincy Adams (Correct)
Correct: CONCATENATE(A7, ” “, B7) returns “John Quincy Adams” in both Microsoft’s Excel and Google Sheets. But it is equally possible to use =CONCAT(A7, ” “, B7) to arrive at the same result in Microsoft Excel since CONCAT is a function valid in that program. In Google Sheets, however, an error would arise if one tries to use this formula as it would only use CONCAT as for two strings. In fact, most intelligent, real-time coherent deviations from this would involve the use of =CONCATENATE to keep conditions error-free in both platforms.
Test your knowledge on converting and formatting data
1. 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)
- =CONVERT(-22, F, C)
- =CONVERT(-22, “C”, “F”) (Correct)
- =CONVERT(-22, “F”, “C”)
Correct: =CONVERT(-22, “C”, “F”) will display -22 °C in Fahrenheit.
2. 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?
- Deny Help Text
- Reject Invalid Inputs (Correct)
- Remove Validation
- Forbid Entry
Correct: Select Reject Invalid Inputs to maintain the integrity of your spreadsheet formulas, even when incorrect data is input. This way invalid data can’t be entered into any cells, and it flags incorrect entries to keep your formulas and calculations intact. It is.
3. 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?
- Filtering
- CONVERT
- Data validation
- Conditional formatting (Correct)
Correct: The data analyst uses conditional formatting, a spreadsheet feature that enables changing the visual representation of a cell based on a condition. This can include criteria such as defined values or anything else wherein the boundaries are made recognizable through format changes in color of cell content, font, and other formats. Using conditional formatting highlights patterns or trends in data that should facilitate quick analysis and interpretation.
Test your knowledge on combine multiple datasets
1. Fill in the blank: In SQL, _____ can be used to combine strings from multiple tables in order to create a new string.
- COMBINE
- CONCATENATE
- CONCAT (Correct)
- CONNECT
Correct: CONCAT is the function you can use in SQL to combine strings stored in different columns or even in different tables into a single string. This is useful whenever you need to unite single pieces of information as first and last names or when using more than one table in a query.
2. 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.
What playlist appears in row 2 of your query result?
- Music
- Movies (Correct)
- Audiobooks
- TV Shows
Correct: The part of the LIMIT 4 clause that limits the query towards the return of only the first 4 rows from the playlist table is this: SELECT * FROM playlist LIMIT 4. The clause defines the return maximum number of rows by the query.
3. What function can be used to return the number of characters in cell B8 so you can confirm that it contains exactly 20 characters?
- =LEN(20, B8)
- =LEN(20)
- =LEN(B8) (Correct)
- =LEN(B8, 20)
Correct: The function evaluates and prints the number of characters contained in the text cell B8. It is a LEN that counts the total length of a string including the number of spaces and punctuation marks.
Analyze Data to Answer Questions Weekly Challenge 2
1. An analyst working for a British school system just downloaded a dataset that was created in the United States. 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?
- CURRENCY
- Format as Currency (Correct)
- EXCHANGE
- Format as Pounds
Correct: You can now “Format as Currency” for the number by applying the right currency format displaying it with the relevant currency symbol, decimal places, and thousands separators for the selected currency.
2. You are preparing a project tracker spreadsheet. Next to each project task, you need to add the name of the team member responsible. What spreadsheet tool will save you time by enabling you to create a drop-down list with team members’ names as the possible options?
- Conditional formatting
- Data validation (Correct)
- Find
- Pop-up menus
Correct: Data validation is basically used to create drop down lists of predefined options, such as the names of people in the team. It thus restricts the user to select just from the provided list, ensuring consistency and reducing possible risks associated with entry errors.
3. 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?
- Data validation
- Add color
- CONVERT
- Conditional formatting (Correct)
Correct: Conditional formatting is a technique that allows you to apply styles to cells based on certain specified conditions. For example, a cell could be formatted differently if its value is greater than or equal to a number or equals certain text. Also a cell’s color may be changed, its font format being changed, or it can apply other formats.
4. You are analyzing data about the capitals of different countries. In your SQL database, you have one column with the names 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?
- COMBINE
- GROUP
- CONCAT (Correct)
- JOIN
Correct: The CONCATENATE function is a function that can be used to take more than one text string from a variety of sources, and it can be used to join two or more text strings to create a single string. These could possibly be values from different cells or text that was entered directly and is useful for merging data seamlessly in one cell.
5. 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?
- LIMIT 12 (Correct)
- LIMIT,12
- LIMIT = 12
- LIMIT_12
Correct: To return only the first 12 records, type LIMIT 12.
6. Fill in the blank: 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.
- values
- fields
- characters (Correct)
- substrings
Correct: The LEN function can be understood as counting the number of characters in a text string,whether it is empty spaces or punctuation marks. It usually returns the total length of that string in terms of the number of characters.
7. Spreadsheet cell E13 contains the text string “Database”. To return the substring “Data”, what is the correct syntax?
- =LEFT(E13, 4) (Correct)
- =LEFT(4,E13)
- =RIGHT(4,E13)
- =RIGHT(E13, 4)
Correct: The function is used to retrieve the first four characters from the text string that is found in cell E13. The LEFT function has the ability to extract a set number of characters from the leftmost side of the text. Thus, in this manner, it can get the first four characters from the string in cell E13 and then display it. For example, if the string starts with “Data,” it will return “Data”.
8. When working with a spreadsheet, data analysts use the find function to locate specific characters in a string. Find is case-sensitive, so it’s necessary to input the substring exactly how it appears.
- True (Correct)
- False
Correct: Find is case-sensitive, so it’s necessary to input the substring exactly how it appears.
9. Fill in the blank: A data professional at a junior college uses the spreadsheet _____ function to locate specific characters from a listing of course codes.
- IDENTIFY
- FROM
- FIND (CORRECT)
- WHERE
10. A data analyst works with a spreadsheet containing product information that often has very long text strings. To check for consistency, they use a function to count the number of characters in cell P12. What is the correct syntax of the function?
- =LEN(P,12)
- =LEN(P:12)
- =LEN(P:P12)
- =LEN(P12) (CORRECT)
11. You prepare a project tracker spreadsheet. Next to each project is the name of the team member responsible. What spreadsheet tool will create a drop-down list with team member names to save you time when assigning the projects?
- Data validation (CORRECT)
- Conditional formatting
- Find
- Pop-up menus
12. An analyst works with a dataset of financial data that is formatted in U.S. dollars. However, it should be formatted as Japanese yen. What spreadsheet tool should they use to correct the format?
- Format as currency (CORRECT)
- Format as yen
- Format as money
- Format as amount
13. You use a SQL database to evaluate rideshare efficiency. The database table contains one column of pick-up locations and another column of drop-off locations. What function will combine the pick-up and drop-off locations into a new column?
- COMBINE
- GROUP
- JOIN
- CONCAT (CORRECT)
14. A data professional uses a spreadsheet to track the total purchases made by each member of the company’s procurement department. What spreadsheet tool can they use to change how the cells appear when someone has made more than $5,000 in purchases?
- Conditional formatting (CORRECT)
- CONVERT
- Add color
- Data validation
15. Fill in the blank: A junior data analyst working with interest rate data uses the SQL function _____ to adjust 6.23456789012345678901234567890% to just one decimal place.
- SHORTEN
- DECIMAL
- TRUNCATE
- ROUND (CORRECT)
16. Spreadsheet cell L2 contains the text string Function. To return the substring Fun, what is the correct syntax?
- =LEFT(L2, 3) (CORRECT)
- =RIGHT(3, L2)
- =LEFT(3, L2)
- =RIGHT(L2, 3)
17. A data analyst works with a spreadsheet containing medical record numbers that must be exactly 30 digits long. To ensure accuracy, they use a function to count the number of characters in cell H10. What is the correct syntax of the function?
- =LEN(30)
- =LEN(H10, 30)
- =LEN(H10) (CORRECT)
- =LEN(30, H10)
18. You use a spreadsheet to help you with an upcoming gift exchange. Every time you find an interesting gift idea, you add it to the spreadsheet. What spreadsheet tool will create a drop-down list with Need to Purchase and Purchased as the possible options?
- Conditional formatting
- Pop-up menus
- Find
- Data validation (CORRECT)
19. An analyst works with financial data that is formatted as Euros, but it should be formatted as pesos. What spreadsheet tool should they use to correct the format?
- Format as Euros
- Format as money
- Format as currency (CORRECT)
- Format as peso
20. You use a SQL database to analyze airline flight routes in Africa. The database table contains one column of departure airports and another column of arrival airports. What function will combine the departure and arrival airport names into a new column?
- JOIN
- CONCAT (CORRECT)
- COMBINE
- GROUP
21. A data analyst at a symphony orchestra uses a spreadsheet to track how many concerts require more than 65 musicians. What spreadsheet tool can they use to change how cells appear when values equal 65 or more?
- Conditional formatting (CORRECT)
- Data validation
- Add color
- CONVERT
22. Spreadsheet cell H9 contains the text string Tableau. To return the substring Tab, what is the correct syntax?
- =LEFT(3, H9)
- =RIGHT(H9, 3)
- =RIGHT(3, H9)
- =LEFT(H9, 3) (CORRECT)
23. Fill in the blank: A data analyst at a logistics company uses the spreadsheet _____ function to locate specific characters from a shipping confirmation email.
- IDENTIFY
- WHERE
- FROM
- FIND (CORRECT)
24. You use a spreadsheet to organize a list of upcoming tasks at work. Column A contains the list of tasks, and column B notes their priority: high, medium, or low. What spreadsheet tool will create a drop-down list of the priorities in column B?
- Data validation (CORRECT)
- Pop-up menus
- Conditional formatting
- Find
25. A data analyst in human resources uses a spreadsheet to track employee work anniversaries. What spreadsheet tool can they use to add color to the cells containing the name of any employee who has worked for the business for at least five years?
- Conditional formatting (CORRECT)
- Data validation
- CONVERT
- Add color
26. Fill in the blank: A data analyst analyzing the boiling points of various liquids uses the SQL function _____ to adjust 356.727801 °C to just three decimal places.
- DECIMAL
- ROUND (CORRECT)
- SHORTEN
- TRUNCATE
27. You use a spreadsheet to help you with an upcoming gift exchange. Every time you find an interesting gift idea, you add it to the spreadsheet. What spreadsheet tool will create a drop-down list with Need to Purchase and Purchased as the possible options?
- Find
- Conditional formatting
- Data validation (CORRECT)
- Pop-up menus
28. An analyst notes that the “85” in cell A3 is formatted as text, but it should be Australian dollars. What spreadsheet tool should they use to correct the format?
- Format as currency (CORRECT)
- Format as money
- Format as dollars
- Format as amount
29. Fill in the blank: A data professional analyzing gasoline prices uses the SQL function _____ to adjust the number $3.5698726 to just two decimal places.
- ROUND (CORRECT)
- DECIMAL
- TRUNCATE
- SHORTEN
Organizing Data to Begin Analysis CONCLUSION
It is no good to wait before you finally come to the stage of analyzing your actual data; if anything, it is important that data be really formatted and prepared properly while making way for this session of the course. You will understand the switching and formatting of data, including how SQL queries can join various different sets of data.
Then, turn and learn the concept behind feedback and peer support. You can, after all, leverage that into some new wisdom and skill for your future endeavors. Consequently, you have invested in Coursera, allowing you to tap those resources into applying knowledge and learning through working experiences.