Course 5 – Analyze Data to Answer Questions Quiz Answers

The Google Data Analytics course offered through Coursera will teach you how to work within spreadsheets and SQL for data calculations. This part of the wider course will cover using formulas, functions, and pivot tables with Google Spreadsheets, as well as SQL queries for managing temporary tables. It will help improve your capability in effective data calculations-in fact.

This is further supplemented by the course’s information on how to use SQL as a powerful weapon in dealing with temporary tables, which would capture an important actions for data analysis. Full reading of this course will give one an understanding of how SQL and spreadsheet tools could be used for the calculations and analyses of the data.

Learning objectives:

To understand the use of functions in simple calculations of data in spreadsheets.
Using pivot tables, learn to conduct calculations for spreadsheet data.
Ability to use SQL queries in measures.
Realizing the importance of data validation for an accurate and consistent analysis.
To be able to master SQL queries to manage temporary tables.
Using conditional statements to generate complex queries and functions.
Learn how to summarize according to various conditions by using COUNTIF, SUMIF, MAXIF, and AVERAGEIF functions.

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: This formula =COUNTIFS(B2:B21, “NY”, C2:C21, “1”) gives the output 4 in that it counts the number of sales persons in New York (column B), who have one customer exactly (column C). Using the COUNTIFS function allows to provide more than one condition under which data may be filtered or analyzed with great precision. As you keep working on the data, other condition-based functions such as SUMIFS, AVERAGEIFS, MAXIFS, and MINIFS may be used for much more complex analysis and to extract specific insights on various relationships.

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: The correct method of multiplying 50 and 233 in a spreadsheet is indeed the formula =50*233. In spreadsheets, all formulas start with an equal sign, which denotes that a calculation is to be executed. The asterisk indicates multiplication. It is understood through you behaving as a user that it has been told to multiplay with the two values. This formula once fed into the spreadsheet will return 11,650.

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: This is the correct syntax: =SUMIF(B2:B12,”>150″), to add up data in Column B where the figures exceed $150. The range is B2 to B12 and the criteria is “>150”.

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: A2 through B31 can be viewed in terms of an array. An array is collection of values that can be stored in spreadsheet cells and is most commonly used in some form or the other in calculations or data analysis.

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: In order to know to which movie genre earns the maximum profit on average, you create a pivot table with a calculated field and sorted the data. Henceforth, pivot tables will be your best friend in quickly summarizing data, getting results, and even visualizing everything right within your spreadsheet. This will serve well as you progress toward data analysis and 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?

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 pivot table is there to give out a total of downloaded prices from purchases of each product type. It totals everything by grants viewing in a grand total for all purchases from products to allow a consolidated review of the data for easier analysis.

2. 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: By adding filter to the pivot table, same data can be displayed just for items classified as beige. Simply apply a filter for the product category field and specify the filter criteria as “beige” in this case. Filtered this way, the results will return entries for that specific desired product category only-beige.

3. 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 average value using values generated within a pivot table, you can make use of a calculated field. A calculated field is an additional field within the pivot table which carries out calculations on the data from other fields in the pivot table. For example, if you create a calculated field with the average of certain values from the pivot table, you can further analyze the data directly within the pivot table.

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: It states that an average ridership for the Atlantic Ave-Barclays Ctr station during the years from 2016 to 2018 is 42,672.33. In order to calculate the overall average weekly ridership for the station, SQL statements will be used to add the value of the columns ridership_2016, ridership_2017, and ridership_2018, then take a proportion of the sum by three.

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?

7.92
1.98
3.96
0.99 (Correct)

Correct: That way you can reference the computed column directly as line_total in your result set without ever having to change the original table structure.

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: Indeed! SQL%demonstreathe is used to The modulo operator returns the result remaining when two numbers are divided into each other. This operation would most likely be required when you require that type of examining results after division between two numbers.

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: Yes, it is true that the modulo operator (%) in SQL gives the results of the remainder from the division between two numbers. It is often used in situations where the remainder needs to be found.

Hands-on Activity: From spreadsheet to bigquery

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

2017 (Correct)
2018
2019
2020

Correct: It is through the year 2017 that the data set begins. The MIN and MAX functions in SQL allowed me to find the oldest and latest years in order to create a date range but not scrolling through that huge data set manually, an important skill in working with large data sets.

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: Periodic checking and rechecking of the quality of your data validate that the data itself 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: In fact, this is not a mere inspection as far as the consistency of data across systems is concerned; this checks the entity itself data-wise and ensures even that it follows logically and consistently with all the other data to make sense.

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: Hence, if a FULL JOIN would be applied to join the temp table with the trips table rather than an INNER JOIN, the final result would include the station ID 3798. It can be clearly understood that the temporary table must connect to the original table in the way suggested to produce the correct output. After that, temp tables will assist you in doing much more complicated query execution such as this one and further expand your data analytical capabilities in 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: When one session ends in a SQL database, temporary tables disappear automatically at the end of the general session.

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

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: The temporary table titled “1_hr_trips” will list the bike trips that lasted precisely sixty minutes. It stipulates a condition upon the inclusion of trips in that particular table if the duration of the trip is exactly 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 grants everyone access to temporary table, whereas it would be better to use SELECT INTO for creating that temporary table just for you.

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: This SUMIF function will add up corresponding values in the range, C1 to C25, if the values in range A 1 to A 25 are found to be less than 10. The point of the condition statement here is that “value<10”.

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: The formula that calculated is given by =sumproduct(A2:A10, B2:B10). This formula takes the products of each corresponding value in the two ranges: A2:A10 and B2:B10, and adds these products together in order to provide you with the final answer.

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?

193
120 (Correct)
307
153

Correct: The expression milliseconds / 1000 AS secs is a formula to define the duration in seconds for each track and store the value in the new column “secs.”

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?

2
4
14
9 (Correct)

Correct: When using the GROUP BY command, rows that have the same values within a table are grouped together into what will be called summary rows. It is important to note, however, that such command always comes last in any SELECT-FROM-WHERE query since this command does on a summary basis the entire operation after all selected columns are indicated, followed by any other condition, before it is called to group.

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?

5.37
5.78 (Correct)
5.64
6.02

Correct: The average of several values is obtained from a collection of values using the AVG aggregate function. As for the newly generated column that presents the average of the calculated figures, it shall temporarily be named by the AS command “average_total.”

The average total for India is 5.78.

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: Sort by column K from A to Z in order to arrange your spreadsheet according to city in ascending order. Alternatively, use the SORT function with the 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: Enables the control of entries within the worksheet, thereby reducing the chances of errors such as typos, through data validation. Giving, for example, a list of cities to select from a drop-down list ensures that only valid entries can be made.

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: Apply to the cells the corresponding conditional formatting and then set up a rule that would apply conditional formatting to cells where the values 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: Use the RIGHT function with the syntax: =RIGHT(L2, 5) to pull out into its own column the 5-digit zip codes from Column L.

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: CONCAT Combines Two Columns To Form A New Column, In This Case “address.” For Example: The Use Of The Following Syntax: =CONCAT(A2,” “,B2) Concatenates Result From Columns A And B With A Space In Between Them And Places The Result In A New ‘address’ Column.

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: Utilize the ORDER BY function in your SQL query to sort all records in the Donation_Form_List by zip code. In your case, zip code is the specific column based upon which the results will be classified in the ascending or descending order through the ORDER BY function.

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?

Correct: It leaves only the records where the position is “Board of Trustees” or “Board of Directors.” If you have a list of alternatives, the first one is the one which corresponds to that condition.

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:

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: You can use the formula =SUM(O2,P2) to add the values in cells O2 and P2. Alternatively, for the same output, you may 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?

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:

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:

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)}
Date	Values	Down	Up
12/3	MAX of A	300	100
^{(blank cell)}	MIN of C	12	1
12/4	MAX of A	100	100
^{(blank cell)}	MIN of C	14	19
12/5	MAX of A	450	^{(blank cell)}
^{(blank cell)}	MIN of C	9	^{(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.

CREATE TABLE
SELECT INTO (CORRECT)
DROP TABLE
WITH TEMP

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.

Skip irrelevant steps.
Only use each step once.
Complete the steps in any order.
Overlap the steps as needed. (CORRECT)

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

Coursera provides a learning channel for students on how to work with Google Data Analytics through effective calculations. This section includes essential formula functions, pivot tables and SQL queries. Getting this knowledge allows students to work better with data and understand more principles in data management.

Enroll at Coursera today and enjoy the experience of learning!