WGU D465 — Data Applications
Complete Study Guide — Questions & Answers
Coursera 1: Modules 3 & 4 | Coursera 2: Modules 2, 4 & 5
COURSERA 1 — MODULE 3: Aggregate Data for Analysis
Aggregating Data with Excel: VLOOKUP
Q: What is the applied use of the =VALUE() function?
A: It converts a text string that represents a number into an actual numeric value. This is useful
when imported data stores numbers as text, which prevents calculations from working correctly.
Q: What is the applied use of removing duplicates?
A: Removing duplicates eliminates repeated rows of data from a dataset so that each record is
unique. This ensures accuracy when aggregating or analyzing data.
Q: What are the four syntaxes (arguments) of the VLOOKUP function?
A: 1. lookup_value — the value to search for. 2. table_array — the range of cells containing the
data. 3. col_index_num — the column number in the table from which to return a value. 4.
[range_lookup] — TRUE for approximate match, FALSE for exact match.
Q: What are single quotation marks used for in the VLOOKUP function?
A: Single quotation marks are used to reference sheet names that contain spaces or special
characters (e.g., 'Sheet Name'!A:B).
Q: What are commas used for in the VLOOKUP function?
A: Commas separate each argument (parameter) within the VLOOKUP function.
Q: What indicates an exact match in the VLOOKUP function?
A: FALSE (or 0) as the fourth argument indicates an exact match.
Q: What are $ used for in the VLOOKUP function?
A: Dollar signs ($) create absolute cell references, locking the row and/or column so the reference
does not change when the formula is copied to other cells.
Q: Can VLOOKUP return more than one value per cell?
A: No. VLOOKUP returns only one value per cell — the first match it finds.
Q: Can VLOOKUP look for data in columns to the left?
A: No. VLOOKUP can only search the leftmost column of the table array and return values from
columns to the right.
Q: What is the advantage of locking or protecting the spreadsheet?
A: Locking/protecting prevents accidental edits to formulas or data, preserving data integrity and
ensuring calculations remain accurate.
Q: Select 2 common reasons for using VLOOKUP.
, a. Calculating the sum of two columns
b. Merging data from two different spreadsheets ✓
c. Returning the time and data from the left column of the workbook
d. Populating data from the specified column with an exact match
Correct answers: b and d
Aggregating Data with SQL: JOINs
Q: How are LEFT JOIN and RIGHT JOIN different?
A: LEFT JOIN returns all records from the left table and matching records from the right table
(unmatched right rows return NULL). RIGHT JOIN does the opposite — all records from the right
table and matching records from the left table.
Q: How are INNER JOIN and OUTER JOIN different?
A: INNER JOIN returns only the rows where there is a match in both tables. OUTER JOIN (FULL
OUTER JOIN) returns all rows from both tables, filling in NULLs where there is no match on either
side.
Q: What is the difference between a primary key and a foreign key?
A: A primary key is a unique identifier for each record in its own table. A foreign key is a field in one
table that references the primary key of another table, creating a relationship between the two
tables.
Q: If JOIN is typed without specifying a type, which JOIN does SQL default to?
a. LEFT
b. OUTER
c. INNER ✓
d. RIGHT
Q: Why would we use AS in our SELECT statement before writing our JOIN?
A: AS creates an alias — a temporary, shorter name for a table or column. This makes the query
easier to read and write, especially when working with multiple tables.
Q: What is the purpose of aliases for a table or column?
A: Aliases give temporary, readable names to tables or columns, simplifying complex queries and
making output column headers more meaningful.
Q: Why would we use COUNT DISTINCT instead of COUNT?
A: COUNT DISTINCT counts only unique (non-duplicate) values, whereas COUNT counts all
values including duplicates. Use COUNT DISTINCT when you need to know how many different
values exist.
Q: Provide an example where COUNT is more accurate than COUNT DISTINCT.
A: When counting total transactions or total rows (e.g., total orders placed), COUNT is more
accurate because you want every occurrence, including repeats.
Q: When using subqueries, is the statement containing the subquery the inner query or the
outer query?
a. Inner
b. Outer ✓
Complete Study Guide — Questions & Answers
Coursera 1: Modules 3 & 4 | Coursera 2: Modules 2, 4 & 5
COURSERA 1 — MODULE 3: Aggregate Data for Analysis
Aggregating Data with Excel: VLOOKUP
Q: What is the applied use of the =VALUE() function?
A: It converts a text string that represents a number into an actual numeric value. This is useful
when imported data stores numbers as text, which prevents calculations from working correctly.
Q: What is the applied use of removing duplicates?
A: Removing duplicates eliminates repeated rows of data from a dataset so that each record is
unique. This ensures accuracy when aggregating or analyzing data.
Q: What are the four syntaxes (arguments) of the VLOOKUP function?
A: 1. lookup_value — the value to search for. 2. table_array — the range of cells containing the
data. 3. col_index_num — the column number in the table from which to return a value. 4.
[range_lookup] — TRUE for approximate match, FALSE for exact match.
Q: What are single quotation marks used for in the VLOOKUP function?
A: Single quotation marks are used to reference sheet names that contain spaces or special
characters (e.g., 'Sheet Name'!A:B).
Q: What are commas used for in the VLOOKUP function?
A: Commas separate each argument (parameter) within the VLOOKUP function.
Q: What indicates an exact match in the VLOOKUP function?
A: FALSE (or 0) as the fourth argument indicates an exact match.
Q: What are $ used for in the VLOOKUP function?
A: Dollar signs ($) create absolute cell references, locking the row and/or column so the reference
does not change when the formula is copied to other cells.
Q: Can VLOOKUP return more than one value per cell?
A: No. VLOOKUP returns only one value per cell — the first match it finds.
Q: Can VLOOKUP look for data in columns to the left?
A: No. VLOOKUP can only search the leftmost column of the table array and return values from
columns to the right.
Q: What is the advantage of locking or protecting the spreadsheet?
A: Locking/protecting prevents accidental edits to formulas or data, preserving data integrity and
ensuring calculations remain accurate.
Q: Select 2 common reasons for using VLOOKUP.
, a. Calculating the sum of two columns
b. Merging data from two different spreadsheets ✓
c. Returning the time and data from the left column of the workbook
d. Populating data from the specified column with an exact match
Correct answers: b and d
Aggregating Data with SQL: JOINs
Q: How are LEFT JOIN and RIGHT JOIN different?
A: LEFT JOIN returns all records from the left table and matching records from the right table
(unmatched right rows return NULL). RIGHT JOIN does the opposite — all records from the right
table and matching records from the left table.
Q: How are INNER JOIN and OUTER JOIN different?
A: INNER JOIN returns only the rows where there is a match in both tables. OUTER JOIN (FULL
OUTER JOIN) returns all rows from both tables, filling in NULLs where there is no match on either
side.
Q: What is the difference between a primary key and a foreign key?
A: A primary key is a unique identifier for each record in its own table. A foreign key is a field in one
table that references the primary key of another table, creating a relationship between the two
tables.
Q: If JOIN is typed without specifying a type, which JOIN does SQL default to?
a. LEFT
b. OUTER
c. INNER ✓
d. RIGHT
Q: Why would we use AS in our SELECT statement before writing our JOIN?
A: AS creates an alias — a temporary, shorter name for a table or column. This makes the query
easier to read and write, especially when working with multiple tables.
Q: What is the purpose of aliases for a table or column?
A: Aliases give temporary, readable names to tables or columns, simplifying complex queries and
making output column headers more meaningful.
Q: Why would we use COUNT DISTINCT instead of COUNT?
A: COUNT DISTINCT counts only unique (non-duplicate) values, whereas COUNT counts all
values including duplicates. Use COUNT DISTINCT when you need to know how many different
values exist.
Q: Provide an example where COUNT is more accurate than COUNT DISTINCT.
A: When counting total transactions or total rows (e.g., total orders placed), COUNT is more
accurate because you want every occurrence, including repeats.
Q: When using subqueries, is the statement containing the subquery the inner query or the
outer query?
a. Inner
b. Outer ✓
