Google Data Analytics Professional Certificate Analyze Data to Answer Questions Flashcards

24 sample cards from the Analyze Data to Answer Questions deck

Sampled from the end of the deck, so these are different cards from the ones shown on the syllabus page.

1. Name the four main SQL JOIN types and what rows each returns.

   INNER JOIN: only rows with matching keys in both tables. LEFT JOIN: all rows from the left table plus matches from the right (NULL where none). RIGHT JOIN: all rows from the right table plus matches from the left. FULL OUTER JOIN: all rows from both tables, matched where possible and NULL elsewhere.

2. What is an aggregate function in SQL, and what do SUM, AVG, COUNT, MIN, and MAX return?

   An aggregate function performs a calculation over a set of rows and returns a single summary value. SUM adds numeric values; AVG returns the arithmetic mean $\bar{x} = \frac{\sum_{i=1}^{n} x_i}{n}$; COUNT returns the number of rows or non-null values; MIN and MAX return the smallest and largest values.

3. In SQL, what is the difference between COUNT(*) and COUNT(DISTINCT column)?

   COUNT(*) counts all rows in the result set, including duplicates and rows with NULLs. COUNT(DISTINCT column) counts only the unique non-null values in that column — e.g., the number of distinct customers rather than the number of orders.

4. What does the SQL GROUP BY clause do, and how does it work with aggregate functions?

   GROUP BY collapses rows that share the same value(s) in specified column(s) into groups, so aggregate functions compute one result per group. Example: SELECT region, SUM(sales) FROM orders GROUP BY region returns total sales per region. Every non-aggregated column in the SELECT must appear in the GROUP BY.

5. How do the spreadsheet functions COUNT and COUNTA differ?

   COUNT counts only cells containing numeric values (including dates); COUNTA counts all non-empty cells regardless of type — text, numbers, Booleans, and errors. Use COUNTA to count entries, COUNT to count numbers.

6. What do the spreadsheet functions SUMIF and COUNTIF do, and what is the basic syntax of SUMIF?

   COUNTIF counts cells in a range that meet one condition; SUMIF adds the values in a range where a corresponding condition is met. Syntax: =SUMIF(criteria_range, criterion, sum_range), e.g., =SUMIF(B2:B100, "West", C2:C100) sums column C where column B equals "West". The plural forms SUMIFS/COUNTIFS support multiple conditions.

7. What is the SUMPRODUCT function and when is it used?

   SUMPRODUCT multiplies corresponding entries of arrays and returns the sum of those products: for arrays $a$ and $b$ it computes $\sum_{i=1}^{n} a_i \times b_i$. It is used for calculations like total revenue from paired price and quantity columns: =SUMPRODUCT(A2:A10, B2:B10).

8. What is a pivot table, and what are its four main parts?

   A pivot table is a spreadsheet tool that summarizes, groups, and reorganizes data without changing the source data. Its four parts are: Rows (categories down the side), Columns (categories across the top), Values (the aggregated calculations such as SUM, COUNT, AVERAGE), and Filters (criteria restricting which source data is included).

9. Why are pivot tables useful during the analyze phase of data analysis?

   They quickly summarize large datasets, group data by categories and time periods, compute aggregates (sums, averages, counts) per group, reveal patterns and trends, allow drill-down and re-arrangement by dragging fields, and enable comparisons — all without writing formulas or altering the raw data.

10. In a pivot table, what is a calculated field?

    A calculated field is a new value field defined by a formula that uses other fields in the pivot table (e.g., Profit = Revenue − Cost, or a ratio like $\frac{\text{Profit}}{\text{Revenue}}$). It performs the calculation on the summarized pivot data without adding a column to the source data.

11. What summarization functions can a pivot table's Values area apply, and what is the usual default?

    Common options include SUM, COUNT/COUNTA, AVERAGE, MIN, MAX, and measures like standard deviation or variance. The default is typically SUM for numeric fields and COUNT for text fields; you can change the aggregation per value field.

12. What is the syntax of the VLOOKUP function, and what does each argument mean?

    =VLOOKUP(search_key, range, index, is_sorted). search_key: the value to find; range: the table to search, with the key in its first column; index: the column number in the range whose value to return (first column = 1); is_sorted: FALSE for an exact match (recommended), TRUE for an approximate match on sorted data.

13. State three key limitations or common pitfalls of VLOOKUP.

    1) It only searches the leftmost column of the range and can only return values from columns to its right. 2) It returns only the first match, so duplicates in the key column are silently ignored. 3) Mismatched data types or stray spaces (fix with VALUE/TRIM), unlocked references when copying (use absolute references like \$A\$2:\$C\$100), and using TRUE (approximate match) by accident all produce wrong results or #N/A errors.

14. How can you handle the #N/A error that VLOOKUP returns when no match is found?

    Wrap the lookup in IFERROR (or IFNA) to substitute a friendlier result, e.g., =IFERROR(VLOOKUP(A2, Data!$A$2:$C$100, 3, FALSE), "Not found"). #N/A means no match was found — it is informational, not necessarily a broken formula.

15. What is the SQL equivalent of a spreadsheet VLOOKUP, and how is it written?

    A JOIN on a shared key — typically a LEFT JOIN to keep all rows from the main table. Example: SELECT o.order_id, c.customer_name FROM orders o LEFT JOIN customers c ON o.customer_id = c.customer_id; This 'looks up' customer_name for each order, like VLOOKUP returning a column from another table.

16. In spreadsheets, what is the difference between absolute and relative cell references?

    A relative reference (A2) shifts when the formula is copied to other cells; an absolute reference ($A$2) stays fixed. Dollar signs lock the column, the row, or both ($A2 locks column only, A$2 locks row only). Absolute references are essential when copying lookups or constants across many rows.

17. What is the difference between a formula and a function in spreadsheets?

    A formula is any instruction the user writes to perform a calculation, beginning with = (e.g., =A1+B1/2). A function is a preset, named command built into the spreadsheet that performs a specific calculation (e.g., SUM, AVERAGE, VLOOKUP); functions are used inside formulas.

18. What is the standard order of operations spreadsheets follow when evaluating a formula like =A1+B1*C1^2?

    Parentheses first, then exponents, then multiplication and division (left to right), then addition and subtraction (left to right) — PEMDAS. So =A1+B1*C1^2 computes $A1 + B1 \times C1^{2}$; use parentheses like =(A1+B1)*C1^2 to change the order.

19. How would you compute a percentage change between an old value and a new value, in math and in a spreadsheet?

    Percentage change $= \frac{\text{new} - \text{old}}{\text{old}} \times 100\%$. In a spreadsheet with old in A2 and new in B2: =(B2-A2)/A2, then format the cell as a percentage.

20. How do you perform arithmetic calculations directly in a SQL SELECT statement?

    Use the operators +, -, *, / (and % for modulo) on columns and constants, giving the result a name with AS. Example: SELECT quantity, unit_price, quantity * unit_price AS total_price FROM order_items; The alias total_price labels the calculated column.

21. What is a subquery (nested query) in SQL, and in which clauses can it appear?

    A subquery is a complete SELECT statement placed inside another query, enclosed in parentheses; the inner query runs first and its result feeds the outer query. Subqueries can appear in the SELECT clause (scalar value), the FROM clause (derived table), and the WHERE/HAVING clauses (often with IN, EXISTS, or comparison operators).

22. Write the general pattern of a SQL query that returns all employees earning more than the company average salary.

    SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); The scalar subquery computes the average once, and the outer WHERE compares each row's salary against it — something a plain WHERE with AVG() cannot do directly.

23. What is a temporary table in SQL, how is it created, and how long does it last?

    A temporary table is a table that exists only for the duration of the current database session (or query script) and is dropped automatically afterward. It is created with CREATE TEMP TABLE name AS SELECT ... (or SELECT ... INTO #name in some dialects). It is useful for storing intermediate results that will be reused several times without changing the source database.

24. What is a CTE (Common Table Expression) in SQL, what is its syntax, and how does it differ from a temporary table?

    A CTE is a named temporary result set defined with the WITH clause that exists only for the single query that follows: WITH cte_name AS (SELECT ...) SELECT ... FROM cte_name; Unlike a temporary table, it is not stored — it lasts only for that one statement — and it improves readability by breaking complex queries into named steps; multiple CTEs are chained with commas.

What this deck covers

Analyze Data to Answer Questions flashcards FAQ