Skip to content

Module 3 challenge :Analyze Data to Answer Questions (Google Data Analytics Professional Certificate) Answers 2025

Question 1

You use VLOOKUP but it fails due to extra spaces in names.

✅ TRIM
❌ NOSPACE
❌ VALUE
❌ CUT

Explanation:
TRIM() removes all extra spaces from text except for single spaces between words.
Example:

=TRIM(A1)

This cleans “Liza␣␣Campbell” → “Liza Campbell”.

Question 2

To count unique “paid” orders instead of total orders.

✅ COUNT DISTINCT
❌ COUNT
❌ RANGE
❌ RETURN

Explanation:
COUNT DISTINCT counts unique values — avoiding duplicates.
Example:

SELECT COUNT(DISTINCT order_id) FROM orders WHERE status='paid';

Question 3

=AVERAGE($C$1:$C$100) — what do the dollar signs ($) do?

✅ Average the values regardless of formula copy
✅ Ensure rows and columns do not change
✅ Create an absolute reference
❌ Perform calculation more efficiently

Explanation:
Dollar signs create absolute references, so $C$1:$C$100 remains constant even when copied to other cells.

Question 4

What will this SQL query return?

SELECT *
FROM Books_table
LEFT JOIN Biography_table

✅ All records in Books_table and any matching rows from Biography_table
❌ All records in both tables
❌ All records in Biography_table and matching from Books_table
❌ All rows joined together

Explanation:
A LEFT JOIN keeps all rows from the left table (Books_table), plus matching rows from the right table (Biography_table). Unmatched right-side data will show NULLs.

Question 5

VLOOKUP for Lake Urmia’s water type:

✅ =VLOOKUP(“Urmia”, A2:C10, 3, FALSE)
❌ Others

Explanation:
=VLOOKUP(lookup_value, table_array, column_index, [range_lookup])

  • Lookup value: "Urmia"

  • Table range: A2:C10

  • Column 3 = Water type

  • FALSE = exact match

Question 6

HAVING allows you to filter queries using ____ functions.

✅ Aggregate
❌ GROUP BY
❌ ORDER BY
❌ Join

Explanation:
HAVING filters results of aggregate functions like COUNT(), AVG(), SUM().
Example:

SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

Question 7

Convert number of clicks to numeric values:

✅ VALUE
❌ NUM
❌ PROCESS
❌ EXCHANGE

Explanation:
VALUE() converts text that looks like numbers into real numeric values, enabling calculations.

Question 8

Alias the customer table as “cust”:

✅

SELECT *
FROM customer AS cust;

❌ FROM customer ALIAS cust
❌ FROM customer NEW cust
❌ FROM customer TO cust

Explanation:
AS assigns a table alias for shorthand reference in queries.

Question 9

Benefits of subqueries in SQL:

✅ Subqueries make code more readable and maintainable.
✅ Subqueries can make projects easier and more efficient.
❌ Subqueries can be nested in SET command.
❌ Subqueries cannot be used in the same query.

Explanation:
Subqueries simplify complex logic, improve clarity, and allow for filtering or aggregation within another query.

Question 10

What does this SQL code do?

SELECT account_table.*
FROM (
SELECT *
FROM transaction.sf_model_feature_2014_01
WHERE day_of_week = 'Monday'
) AS account_table
WHERE account_table.availability = 'NO';

✅ Selects all columns from transactions on Monday and filters only unavailable ones.
❌ Creates a temporary table
❌ Selects all data from table
❌ Filters Fridays

Explanation:
It first filters for Monday transactions, stores them as a temporary alias (account_table), then filters those where availability='NO'.

Question 11

What does this SQL code do?

SELECT e.employee_id, e.first_name, e.last_name, e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id
);

✅ Finds employees earning more than the average salary in their department.
❌ Calculates average for all employees
❌ Groups employees by department
❌ Joins employees table with itself

Explanation:
The subquery computes the average department salary, and the main query finds employees exceeding that average.

🧾 Summary Table

Q# ✅ Correct Answer(s) Key Concept
1 TRIM Remove extra spaces
2 COUNT DISTINCT Count unique entries
3 Absolute reference ($) Fixed cell ranges
4 LEFT JOIN Keep all left table rows
5 =VLOOKUP(“Urmia”, A2:C10, 3, FALSE) Lookup exact match
6 Aggregate HAVING filters group results
7 VALUE Convert text → number
8 FROM customer AS cust Table alias
9 1 & 4 ✅ Benefits of subqueries
10 Filter unavailable Monday transactions Nested subquery
11 Employees above dept avg salary Correlated subquery