What SQL function do you use to select all data

Course 5 – Analyze Data to Answer Questions Quiz Answers

Week 4 : Performing Data Calculations

GOOGLE DATA ANALYTICS PROFESSIONAL CERTIFICATION

Complete Coursera Study Guide

pERFORMING DATA CALCULATIONS – INTRODUCTION

Google Data Analytics with Coursera provides learners the opportunity to learn about performing data calculations. This part of the course will cover formulas, functions and pivot tables in Google Spreadsheets as well as SQL queries for managing temporary tables. With this knowledge, students will be able to gain a better understanding of how to perform data calculations effectively.

Google Data Analytics with Coursera also provides learners the chance to gain insights into the benefits of using SQL to manage temporary tables, which can be an integral part of data analysis. Google Data Analytics with Coursera is a great way to learn how to perform data calculations and understand the importance of SQL in data analytics.

Let’s get started!

Learning Objectives

  • Describe the use of functions to conduct basic calculations on data in spreadsheets
  • Discuss the use of pivot tables to conduct calculations on data in spreadsheets
  • Demonstrate an understanding of the use of SQL queries to complete calculations
  • Explain the importance of the data-validation process for ensuring accuracy and consistency in analysis
  • Discuss the use of SQL queries to manage temporary tables
  • Reflect on how conditional statements can be used to create complex queries and functions
  • Generate multiple points of summary based on a wide variety of conditions using COUNTIF, SUMIF, MAXIF, and AVERAGEIF

Hands-On Activity: Working with conditions

1. In this activity, you tested the query =COUNTIF(B2:B21, “NY”), which returned the value 6. Suppose you want to determine how many of those 6 salespeople have only 1 client. You run the query =COUNTIFS(B2:B21, “NY”, C2:C21, “1”) to find this information. What value does this return?

In this activity you tested the query =countif(B2:B21, “NY”, C2:C21, “1”)

in this activity you tested the query =countif(B2:B21, "NY", C2:C21, "1")
  • 1
  • 5
  • 4 (Correct)
  • 3

Correct: The query =COUNTIFS(B2:B21, “NY”, C2:C21, “1”) returns the value 4, since there are 4 salespeople in New York that have only one client. To find this information, you used the COUNTIFS function with additional constraints. Going forward, you can use other conditional functions to find specific information from your data, which will help you carry out more complex analyses.

Test your knowledge on Data Calculation

1. What is the correct spreadsheet formula for multiplying 50 and 233?

  • 50×233
  • =50*233 (Correct)
  • =50×233
  • 50*233

Correct: =50*233 is the correct formula for multiplying 50 and 233. Formulas begin with an equal sign (=). This is followed by the values to be computed. An asterisk (*) is the multiplication operator in spreadsheets.

2. The following is a selection of a spreadsheet:

You are trying to determine what percentage of your monthly income is spent on big-ticket items
in this activity you tested the query =countif(B2:B21, "NY", C2:C21, "1")

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″) (Correct)
  • =SUMIF(B2:B12,<150)
  • =SUMIF(B2:B12,>150)

Correct: To add together only the values from Column B that cost more than $150, the correct syntax is =SUMIF(B2:B12,”>150″). B2:B12 is the range, and more than 150 (>150) is the criteria.

3. A data analyst is working with a spreadsheet from a cosmetics company.

You may click the link to create a copy of the dataset: Cosmetics Inc.

Which of the following is an example of an array in this spreadsheet?

  • Cells D7 and D14
  • All cells with values greater than 100
  • The values in cells B2 through B31 (Correct)
  • All cells with number values

Correct: The values in cells B2 through B31 together are an example of an array. An array is a collection of values in spreadsheet cells.

Hands-on activity: Explore movie data with pivot tables

1. Which movie genre generates the most profit on average?

  • Adventure (Correct)
  • Thriller
  • Fantasy
  • Comedy

Correct: To find out which movie genre generates the most profit on average, you created a pivot table with a calculated field and sorted the data accordingly. Going forward, you will be able to use pivot tables to quickly summarize data to draw insights and even create visualizations directly in your spreadsheet. This will help you in the future as you continue to work with spreadsheets as a data analyst.

Test your knowledge on using pivot tables

1. The following is a sample pivot table from a furniture company’s spreadsheet:

What is the purpose of the pivot table in this spreadsheet?

What is the purpose of the pivot table in this spreadsheet?

  • To organize all of the data into a smaller format
  • To calculate the sum of individual prices for each product type (Correct)
  • To find the average price of each product
  • To summarize data about each product

Correct: The purpose of the pivot table is to calculate the sum of individual purchase prices for each product type. It also shows a grand total for all of the product purchases.

2. How could the pivot table be adjusted to show the same data, but only for products categorized as beige?

How could the pivot table be adjusted to show the same data, but only for products categorized as beige?
  • Sort the current row by product color
  • Summarize the values by product
  • Add a new column labeled beige
  • Add a filter to show only beige products (Correct)

Correct: To show the same data, but only for products categorized as beige, add a filter to show only beige products.

3. Which spreadsheet tool should you use if you want to find an average value using values generated within a pivot table?

Which spreadsheet tool should you use if you want to find an average value using values generated within a pivot table
  • Data validation
  • A calculated field (Correct)
  • A filter
  • Conditional formatting

Correct: To find an average value using values generated within a pivot table, use a calculated field. A calculated field is a new field within a pivot table that carries out certain calculations based on the values of other fields.

hands-on activity: Calculations in sql

1. Using the same average formula as the last query you wrote, write a query to find the average weekly ridership from the years 2016-2018. What is the average weekly ridership for the Atlantic Av – Barclays Ctr station in this timeframe?

  • 49255
  • 42672.33 (Correct)
  • 13212.67
  • 4903.67

Correct: The average weekly ridership for the Atlantic Av – Barclays Ctr from the years 2016-2018 is 42672.33. To find the station’s total average weekly ridership, you would have to use a SQL calculation to add the three columns ridership_2016, ridership_2017, and ridership_2018, then divide the sum by 3. Going forward, you can use SQL to perform mathematical calculations with data and analyze patterns in real-world situations.

Test your knowledge on SQL Calculations

1. You are working with a database table that contains invoice data. The table includes columns for invoice_line_id (line items for each invoice), invoice_id, unit_price, and quantity (the number of purchases in each line item). Each invoice contains multiple line items. You want to know the total price for each of the first 5 line items in the table. You decide to multiply unit price by quantity to get the total price for each line item, and use the AS command to store the total in a new column called line_total.

Add a statement to your SQL query that calculates the total price for each line item and stores it in a new column as line_total.

NOTE: The three dots (…) indicate where to add the statement.

What total appears in row 1 of your query result

What total appears in row 1 of your query result?

  • 7.92
  • 1.98
  • 3.96
  • 0.99 (Correct)

Correct: You add the statement unit_price * quantity AS line_total to calculate the total price for each invoice and store it in a new column as line_total. The complete query is SELECT invoice_line_id, invoice_id, unit_price, quantity, unit_price * quantity AS line_total FROM invoice_items LIMIT 5. The AS command gives a temporary name to the new column.

The total 0.99 appears in row 1 of your query result.

2. In a SQL query, which calculation does the modulo (%) operator perform?

  • It converts a decimal to a percent
  • It finds the square root of a number
  • It returns the remainder of a division calculation (Correct)
  • It applies an exponent to a value

Correct: The modulo operator returns the remainder of a division calculation when included in a SQL query.

3. 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 (Correct)
  • Firstquarterexpenses
  • first quarter expenses
  • first+quarter+expenses

Correct: You can rename the column first_quarter_expenses. Using underscores between words helps avoid potential issues while keeping the names readable.

Liking our content? Then, don’t forget to ad us to your bookmarks so you can find us easily!

hands-on Activity: From spreadsheet to bigquery

1. What is the earliest year included in this dataset?

  • 2017 (Correct)
  • 2018
  • 2019
  • 2020

Correct: 2017 is the earliest year included in this dataset. To find the date range of this dataset, you used MIN and MAX functions in SQL to determine the earliest and latest years. You were able to pull this observation without actually scrolling through all of the data manually, which is a key skill when working with larger datasets.

Test your knowledge on data validation

1. What are the goals of checking and rechecking the quality of your data during data validation? Select all that apply.

  • Data is consistent (Correct)
  • Data is secure (Correct)
  • Data is complete and accurate (Correct)
  • Data is sorted and filtered

Correct: Checking and rechecking the quality of your data during data validation process helps ensure the data is complete, accurate, secure and consistent.

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

  • Data structure
  • Data type
  • Data consistency (Correct)
  • Data range

Correct: This is a check for data consistency. During a data consistency check, you confirm that the data makes sense in the context of other related data.

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

  • IDs that are repeated
  • IDs with more than eight characters
  • IDs in the wrong column
  • IDs with text (Correct)

Correct: Completing a data-type check will help you identify customer IDs that contain text. The data type for IDs should be numeric only.

hands-on activity: create temporary tables

1. In a past activity, you learned about the importance of using the right type of join. In this activity, you wrote a query with an INNER JOIN to join your temporary table with the original bikeshare_trips table. Which station ID would your query return if you used a FULL JOIN instead of an INNER JOIN? 

  • 3798 (Correct)
  • 2758
  • 2575
  • 3575

Correct: If you used a FULL JOIN instead of an INNER JOIN to join your temporary table to the bikeshare_trips table, you would return the station ID 3798. This is because the temp table you create has to be joined to the original in a specific way in order to return the correct answer. Going forward, you can use temp tables to perform more complex queries like this, which will help you analyze complex data throughout your career.

Test your knowledge on using SQL with temporary tables

1. When are temporary tables automatically deleted?

  • After ending the session in a SQL database (Correct)
  • After running a query in your SQL database
  • After running a report from the table
  • After completing all calculations in the table

Correct: Temporary tables are automatically deleted after ending the session in a SQL database.

2. The following SQL query contains information about bike trips:

The following SQL query contains information about bike trips: What data will appear in the temporary table created through this query

What data will appear in the temporary table created through this query?

  • A random subset of bike trips
  • Bike trips that lasted exactly 60 minutes (Correct)
  • The total number of bike trips
  • Bike trips equal to or more than one hour

Correct: This temporary table will show bike trips that lasted exactly 60 minutes. The name of the table is “1_hr_trips” and the query includes the condition that trips in the table equal one hour.

3. What benefit does a CREATE TABLE statement add to a temporary table?

  • Access for anyone to use the table (Correct)
  • Automated calculations
  • Metadata about the data in the table
  • Specific naming conventions

Correct: A CREATE TABLE statement provides access for anyone to use the temporary table. The SELECT INTO statement is better suited for one person.

Analyze Data to Answer Questions Weekly Challenge 4

1. A data analyst is working with a spreadsheet from a furniture company. To use the template for this spreadsheet, click the link below and select “Use Template.”

The analyst inputs a function to find the number of product prices that are less than $150.00. Which formula will return that result?

  • =COUNTIF(G2:G30, “>=150”)
  • =COUNTIF(G2:G30, “<150”) (Correct)
  • =SUMIF(G2:G30, “>150”)
  • =SUMIF(G2:G30, “<150”)

Correct: The COUNTIF formula =COUNTIF(G2:G30, “<150”) will allow the analyst to count all product price values in Column G that are less than $150.

2. You are working in a spreadsheet and use the SUMIF function in the formula below as part of your analysis.

=SUMIF(A1:A25, ”<10”, C1:C25)

Which part of this formula is the criteria or condition?

  • =SUMIF
  • A1:A25
  • C1:C25
  • ”<10” (Correct)

Correct: The criteria or condition for this SUMIF formula is “<10”. This means that if any values in the range A1 through A25 are less than 10, their corresponding values in the range C1 through C25 will be added together.

3. A data analyst is working in a spreadsheet and uses the SUMPRODUCT function in the formula below as part of their analysis.

=SUMPRODUCT(A2:A10,B2:B10)

How does the SUMPRODUCT function calculate the cell ranges identified in the parentheses?

  • It adds the ranges, then multiplies them by the last value in the second array.
  • It multiplies the ranges, then adds the sum of the products of the two ranges. (Correct)
  • It adds the values in the first range, then adds the values in the second range.
  • It multiplies the values in the first range, then multiplies the values in the second range .

Correct: =SUMPRODUCT(A2:A10,B2:B10) calculates the cell ranges by multiplying each value in the first range by its corresponding value in the second range (the results are the products). Then, the formula adds those products together.

Liking our content? Then, don’t forget to ad us to your bookmarks so you can find us easily!

4. You create a pivot table in a spreadsheet containing movie data. To use the template for this spreadsheet, click the link below and select “Use Template.”

In order to find out how much box office revenue each genre earned, you would use the SUM function in the Values menu to summarize the data.

  • True (Correct)
  • False

Correct: You would use the SUM function to figure out how much box office revenue each genre earned. In the pivot table, the SUM function would add the total revenue separately for each genre.

5. A data analyst uses the following query to perform basic calculations on their data. The variables in the query have the following values: yes_responses = 10, no_responses = 12, total surveys = 22. What is the value of the Responses_Per_Survey variable?

A data analyst uses the following query to perform basic calculations on their data. The variables in the query have the following values: yes_responses = 10, no_responses = 12, total surveys = 22. What is the value of the Responses_Per_Survey variable?
  • 44
  • 11
  • 22
  • 1 (Correct)

Correct: The value of the Responses_Per_Survey variable is 1. In this query, the analyst first calculates the sum of the “yes” and “no” responses, then divides the sum by the total surveys.

6. You are working with a database table that contains data about music. The table includes columns for track_id, track_name, composer, and milliseconds (duration of the music track). You are only interested in data about the classical musician Johann Sebastian Bach. You want to know the duration of each Bach track in seconds. You decide to divide milliseconds by 1000 to get the duration in seconds, and use the AS command to store the result in a new column called secs.

Add a statement to your SQL query that calculates the duration in seconds for each track and stores it in a new column as secs.

NOTE: The three dots (…) indicate where to add the statement.

What is the duration in seconds of the track with Id number 3408?

What is the duration in seconds of the track with Id number 3408?

  • 193
  • 120 (Correct)
  • 307
  • 153

Correct: You add the statement milliseconds / 1000 AS secs to calculate the duration in seconds for each track and store it in a new column as secs. The complete query is SELECT track_id, track_name, composer, milliseconds, milliseconds / 1000 AS secs FROM track WHERE composer = “Johann Sebastian Bach”. The AS command gives a temporary name to the new column.

The duration of the track with Id number 3408 is 120 seconds.

7. You are working with a database table that contains invoice data. The table includes columns for invoice_id and quantity (the number of purchases included in each line item of an invoice). Each invoice contains multiple line items. You want to find out the total number of purchases for each invoice, and store the result in a new column as total_purchases.

You write the SQL query below. Add a GROUP BY clause that will group the data by invoice Id number.

What is the total number of purchases for the invoice with Id number 4?

What is the total number of purchases for the invoice with Id number 4?

  • 2
  • 4
  • 14
  • 9 (Correct)

Correct: You add the clause GROUP BY invoice_id to group the data by customer Id number. The complete query is SELECT invoice_id, SUM(quantity) AS total_purchases FROM invoice_item GROUP BY invoice_id. The GROUP BY command groups rows that have the same values from a table into summary rows. GROUP BY is always placed as the last command in a SELECT-FROM-WHERE query.

8. You are working with a database table that contains invoice data. The table includes columns for billing_country and total. You want to know the average total price for the invoices billed to the country of India. You decide to use the AVG function to find the average total, and use the AS command to store the result in a new column called average_total.

Add a statement to your SQL query that calculates the average total and stores it in a new column as average_total.

NOTE: The three dots (…) indicate where to add the statement.

What is the average total for India?

What is the average total for India?

  • 5.37
  • 5.78 (Correct)
  • 5.64
  • 6.02

Correct: You add the statement AVG(total) AS average_total to calculate the average total and store it in a new column as average_total. The complete query is SELECT billing_country, AVG(total) AS average_total FROM invoice WHERE billing_country = “India”. The AVG function is an aggregate function that returns the average value of a group of values. The AS command gives a temporary name to the new column.

The average total for India is 5.78.

GOOGLE DATA ANALYTICS COURSERA ANSWERS AND STUDY GUIDE

Liking our content? Then don’t forget to add us to your bookmarks so you can find us easily!

Weekly Breakdown | Google Study Guides | Back to Top

Analyze Data to Answer Questions Course Challenge

1. Scenario 1, Questions 1-7

For the past six months, you have been working for a direct-mail marketing firm as a junior marketing analyst. Direct mail is advertising material sent to people through the mail. These people can be current or prospective customers, clients, or donors. Many charities depend on direct mail for financial support.

Your company, Directly Dynamic, creates direct-mail pieces with its in-house staff of graphic designers, expert mail list services, and on-site printing. Your team has just been hired by a local nonprofit, Food Justice Rock Springs. The mission of Food Justice Rock Springs is to eliminate food deserts by establishing local gardens, providing mobile pantries, educating residents, and more. Click below to read the email from Tayen Bell, vice president of marketing and outreach.

You begin by reviewing the dataset. To use the template for this dataset, click the link below and select “Use Template.”

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 Range by Column K from Z to A
  • Sort Sheet by Column K from Z to A
  • Sort Range by Column K from A to Z
  • Sort Sheet by Column K from A to Z (Correct)

Correct: To sort your spreadsheet by city in ascending order, Sort Sheet by Column K from A to Z. You can also use the SORT function syntax =SORT(A2:R210, 11, TRUE).

2. Scenario 1, continued

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

What spreadsheet tool allows you to control what can and cannot be entered in your worksheet in order to avoid typos?

  • Data validation (Correct)
  • Find
  • List
  • VLOOKUP

Correct: Data validation allows you to control what can and cannot be entered in your worksheet in order to avoid typos. It does this by adding drop-down lists with predetermined options, such as each city name.

3. Scenario 1, continued

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?

  • The COUNTA function
  • Data validation
  • The MAX function
  • Conditional formatting (Correct)

Correct: To change how cells appear, use conditional formatting. Choose to format cells if they are greater than or equal to 100.

4. Scenario 1, continued

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.

To move the 5-digit zip code in cell L2 into its own column, you use the function =LEFT(L2,5).

  • True
  • False (Correct)

Correct: To move the 5-digit zip codes in Column L into their own column, you use the RIGHT function: =RIGHT(L2,5).

5. Scenario 1, continued

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”?

  • COALESCE
  • COMBINE
  • CAST
  • CONCAT (Correct)

Correct: The CONCAT function is used to instruct a database to combine two columns into a new column called “address.”

6. Scenario 1, continued

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.

What SQL function do you use to select all data from the Donation_Form_List organized by zip code?

  • ARRANGE BY
  • SEQUENCE
  • ORDER BY (Correct)
  • ORGANIZE

Correct: To select all data from the Donation_Form_List organized by zip code, you use the ORDER BY function. The ORDER BY function sorts results returned in a query.

7. Scenario 1, continued

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, what is the correct query?

To retrieve only those records that include people who have served on the board of trustees or on the board of directors, what is the correct query?

Correct: To retrieve only those records that include people who have served on the board of trustees or on the board of directors, the correct WHERE statement is the first option from the list.

8. Scenario 2, Questions 8-13

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 return records with matching values in both tables?

  • OUTER JOIN
  • INNER JOIN (Correct)
  • LEFT JOIN
  • RIGHT JOIN

Correct: An INNER JOIN function will return records with matching values in both tables.

9. Scenario 2, continued

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 (Correct)
  • False

Correct: To return average contributions in 2020, the correct portion of the SQL query is:

AVG(contributions_2020) AS AvgLineTotal

10. Scenario 2, continued

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 syntax adds the contribution amounts in cells O2 and P2? Select all that apply.

  • =O2+P2 (Correct)
  • =SUM(O2,P2) (Correct)
  • =(O2/P2)
  • =O2,P2

Correct: To add cells O2 and P2, use the function =SUM(O2,P2). You can also use the formula =O2+P2.

11. Scenario 2, continued

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.

What is the correct syntax to count how many donations of $100 or greater appear in Column O (Contributions 2018)?

  • =COUNTIF(O2:O210,”>=100″) (Correct)
  • =COUNTIF(O2:O210>=100)
  • =COUNTIF(O2:O210″>=100″)
  • =COUNTIF(O2:O210,>=100)

Correct: To count how many donations of $100 or greater appear in Column Q, the correct syntax is =COUNTIF(O2:O210,”>=100″).

12. Scenario 2, continued

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.

Which SQL query can be used to calculate the percentage of contributions from prospects?

Which SQL query can be used to calculate the percentage of contributions from prospects?
Which SQL query can be used to calculate the percentage of contributions from prospects?

Correct: To identify the percentage of contributions from prospects, the correct query is the first option above

13. Scenario 2, continued

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.

To retrieve the number of donors in each city, sorted high to low, you use the following query:

To retrieve the number of donors in each city, sorted high to low, you use the following query:
  • True
  • False (Correct)

Correct: To retrieve the number of donors in each city, sorted high to low, DESC must be included. ASC will sort the donors low to high. The correct query is:

To retrieve the number of donors in each city, sorted high to low, DESC must be included. ASC will sort the donors low to high

14. A data analyst at a beverage producer manually recalculates the new column carbonated_bev. They want to identify any rows with values that do not match those in the original column, all_bev. Which SQL clauses would enable them to do so? Select all that apply.

  • WHERE all_bev !! carbonated_bev
  • WHERE all_bev >< carbonated_bev
  • WHERE all_bev != carbonated_bev (CORRECT)
  • WHERE all_bev <> carbonated_bev (CORRECT)

15. Fill in the blank: The SQL command GROUP BY groups table rows with _____ values into summary rows. 

  • increasing
  • decreasing
  • the same (CORRECT)
  • null

16. What will this spreadsheet function return? 

=SUMIF(H1:H50, ”>=50”, J1:J50)

  • The sum of all values in cells H1 to H50 for which the value in cells J1 to J50 is greater than or equal to 50.
  • The sum of all values in cells J1 to J50 that correspond to values in cells H1 to H50 that are greater than or equal to 50. (CORRECT)
  • The sum of any values in cells H1 to H50and cells J1 to J50 that are greater than or equal to 50.
  • The count of the number of cells in the array H1:H50 that have a value greater than or equal to 50.

17. Which of the following statements accurately describe pivot tables? Select all that apply.

  • The calculated field in a pivot table is used to apply filters based on specific criteria.
  • The values in a pivot table are used to calculate and count data. (CORRECT)
  • The rows of a pivot table organize and group data horizontally. (CORRECT)
  • A pivot table is a data summarization tool. (CORRECT)

18. A data professional in the trucking industry calculates the number of spreadsheet rows that contain the SKU value BK09876. Which function do they use?

  • =COUNTIF(G2:G30,BK09876)
  • =COUNTIF(G2:G30,“BK09876”) (CORRECT)
  • =COUNTIF(G2:G30,“=BK09876”)
  • =COUNTIF(BK09876=G2:G30)

19. Fill in the blank: The _____ statement copies data from one table into a new table without adding the new table to the database. 

  • CREATE TABLE
  • WITH TEMP
  • DROP TABLE
  • SELECT INTO (CORRECT)

20. Which SQL statement will create a temporary table?

  • 1 WITH new_table = ( 2    SELECT *  3    FROM old_table 4 );
  • 1 CREATE TABLE new_table AS ( 2    FROM old_table 3 );
  • 1WITH new_table AS ( 2    SELECT *  3   FROM old_table WHERE z = 5 (CORRECT) 4 );
  • 1 SELECT new_table 2 FROM old_table;

21. How many different columns have been added to the values section of the pivot table editor?

(blank cell)(blank cell)Direction(blank cell)
DateValuesDownUp
12/3MAX of A300100
(blank cell)MIN of C121
12/4MAX of A100100
(blank cell)MIN of C1419
12/5MAX of A450(blank cell)
(blank cell)MIN of C9(blank cell)
  • 1
  • 3
  • 6
  • 2 (CORRECT)

22. A data analyst at an ocean conservancy manually recalculates the new column ocean_currents. They want to identify any rows with values that do not match those in the original column, ocean_tides. Which SQL clauses would enable them to do so? Select all that apply.

  • WHERE ocean_currents !! ocean_tides
  • WHERE ocean_currents >< ocean_tides
  • WHERE ocean_currents <> ocean_tides (CORRECT)
  • WHERE ocean_currents != ocean_tides (CORRECT)

23.  Fill in the blank: The SQL command _____ combines table rows with the same values into summary rows. 

  • ORDER BY
  • TABLE
  • WITH
  • GROUP BY (CORRECT)

24. What will this spreadsheet function return? 

=SUMIF(E2:E10, ”>=50”, F2:F10)

  • The sum of all values in cells E2 to E10 for which the value in cells F2 to F10 is greater than or equal to 50.
  • The count of the number of cells in the array E2:E10 that have a value greater than or equal to 50.
  • The sum of all values in cells F2 to F10 that correspond to values in cells E2 to E10 that are greater than or equal to 50. (CORRECT)
  • The sum of any values in cells E2 to E10 and cells F2 to F10 that are greater than or equal to 50.

25. A data analyst at an engineering company calculates the number of spreadsheet rows that contain the value turbine. Which function do they use?

  • =COUNTIF(C1:C100,“=turbine”)
  • =COUNTIF(C1:C100,“turbine”) (CORRECT)
  • =COUNTIF(turbine=C1:C100)
  • =COUNTIF(C1:C100,turbine)

26. Fill in the blank: To copy data from one table into a _____, a data professional uses the SELECT INTO statement. 

  • table view
  • defined function
  • new table (CORRECT)
  • temporary table

27.  Which SQL statement will create a temporary table?

  • 1 SELECT * 2 FROM table;
  • 1 CREATE TABLE my_table AS ( 2 SELECT * 3    FROM other_table 4 );
  • 1 WITH my_table FROM ( 2    SELECT * 3    FROM other_table 4 );
  • 1 WITH my_table AS ( (CORRECT) 2    SELECT * 3    FROM other_table WHERE x = 0 4 );

28. Fill in the blank: To group table rows with the same values into_____, a data analyst uses the SQL command GROUP BY.

  • summary rows (CORRECT)
  • new columns
  • an aggregate table
  • a temporary table

29. Which of the following statements accurately describe pivot tables? Select all that apply.

  • The columns of a pivot table organize and group data horizontally
  • The calculated field in a pivot table is a new field that carries out calculations based on the values of other fields. (CORRECT)
  • A pivot table can be used to count, total, or average data. (CORRECT)
  • The filters section of a pivot table is used to apply filters based on specific criteria. (CORRECT)

30.  Fill in the blank: The _____ statement is useful for making a copy of a table with a specific condition without adding the new table to the database. 

  • CREATE TABLE
  • SELECT INTO (CORRECT)
  • DROP TABLE
  • WITH TEMP

31. A data analyst at a recycling company manually recalculates the new column materials_sorter. They want to identify any rows with values that do not match those in the original column, compost_sorter. Which SQL clauses would enable them to do so? Select all that apply.

  • WHERE materials_sorter !! compost_sorter
  • WHERE materials_sorter >< compost_sorter
  • WHERE materials_sorter <> compost_sorter (CORRECT)
  • WHERE materials_sorter != compost_sorter (CORRECT)

32. What will this spreadsheet function return? 

=SUMIF(K20:K70, ”>=50”, L20:L70)

  • The sum of any values in cells K20 to K70 and cells L20 to L70 that are greater than or equal to 50.
  • The sum of all values in cells L20 to L70 that correspond to values in cells K20 to K70 that are greater than or equal to 50. (CORRECT)
  • The count of the number of cells in the array K20:K70 that have a value greater than or equal to 50.
  • The sum of all values in cells K20 to K70 for which the value in cells L20 to L70 is greater than or equal to 50.

33. A data analyst at a party planning business reviews attendee counts. They calculate the number of spreadsheet rows that contain values less than 500. Which function do they use?

  • =COUNTIF(L10:L300,“>500”)
  • =COUNTIF(L10:L300,”<500”) (CORRECT)
  • =COUNTIF(“>500”,L10:L300)
  • =COUNTIF(”<500”,L10:L300)

34. Which SQL statement will create a temporary table?

  • 1 WITH temp_table FROM ( 2    SELECT * 3   = orig_table 4 );
  • 1 SELECT * 2 FROM temp_table;
  • 1 CREATE TABLE temp_table AS ( 2    SELECT orig_table 3 );
  • 1  WITH temp_table AS ( 2    SELECT * 3   FROM orig_table WHERE y = 1 (CORRECT) );

35.  Which of the following statements accurately describe pivot tables? Select all that apply.

  • The rows of a pivot table organize and display values vertically.
  • A pivot table can be used to sort, reorganize, or group data. (CORRECT)
  • The columns of a pivot table organize and display values vertically. (CORRECT)
  • The filters section of a pivot table is used to apply filters based on specific criteria. (CORRECT)

Performing data Calculation – conclusion

With the help of Coursera, students can learn about google data analytics and how to perform calculations effectively. This part of the course will cover formulas, functions, pivot tables, and SQL queries. By having this knowledge, individuals will be able gain a deeper understanding on how to manage data properly.

Join the learning experience in Coursera today!