DA0-002 Data Acquisition and Preparation Practice Test 12 — 15 Questions

Question 1

A data analyst needs to perform a stratified random sample of a customer database. Which TWO steps are essential for this sampling method? (Select two.)

Accepted Answer

Randomly select a proportional number from each stratum. Option D is correct because stratified random sampling begins by partitioning the population into homogeneous subgroups called strata, typically based on a shared characteristic such as age, region, or customer tier, so that each subgroup is internally similar. Option C is correct because, after the strata are formed, the analyst must draw a random sample from each stratum, usually in a number proportional to that stratum's size in the population, ensuring the sample reflects the population's structure. Option A is incorrect because simple random sampling on the whole population ignores the strata and is a different sampling method. Option B is incorrect because randomly selecting entire clusters describes cluster sampling, not stratified sampling. Option E is incorrect because selecting every nth customer is systematic sampling, which does not require dividing the population into strata.

Answer

Use simple random sampling on the whole population

Answer

Randomly select entire clusters of customers

Answer

Select every nth customer from a list

Question 2

A data analyst needs to count the number of distinct product categories in a table named 'products'. Which SQL function should be used in the SELECT clause?

Accepted Answer

COUNT(DISTINCT category). The correct syntax to count unique non-null values in a column is COUNT(DISTINCT column_name). This function first eliminates duplicate values in the specified column and then counts the remaining distinct entries. In this scenario, COUNT(DISTINCT category) will return the number of unique product categories present in the 'products' table, ignoring any NULLs.

Answer

COUNT(category)

Answer

DISTINCT COUNT(category)

Answer

COUNT(*) WHERE category IS NOT NULL

Question 3

A data analyst wants to identify customers whose last name starts with 'Mc' from the 'customers' table. Which WHERE clause condition should be used?

Accepted Answer

last_name LIKE 'Mc%'. The LIKE operator with the '%' wildcard matches any sequence of zero or more characters, so 'Mc%' correctly finds all last names beginning with 'Mc' (e.g., 'McDonald', 'McIntyre', 'Mc'). The underscore '_' matches exactly one character, and the equals operator requires an exact literal match, so neither can express a prefix pattern.

Answer

last_name LIKE 'Mc_'

Answer

last_name IN ('Mc%')

Answer

last_name = 'Mc%'

Question 4

A data analyst is reviewing sales data and wants to find orders where the order total is between $100 and $500, inclusive. Which WHERE clause is correct?

Accepted Answer

total BETWEEN 100 AND 500. The BETWEEN operator is inclusive of both endpoints, so 'total BETWEEN 100 AND 500' returns rows where total is greater than or equal to 100 and less than or equal to 500 — exactly the inclusive range requested. It is equivalent to 'total >= 100 AND total <= 500' but more concise and readable.

Answer

total > 100 AND total < 500

Answer

total IN (100, 500)

Answer

total >= 100 OR total <= 500

Question 5

A data analyst runs the query: SELECT AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 60000. What is the purpose of the HAVING clause?

Accepted Answer

It filters departments where the average salary exceeds $60,000.. HAVING filters groups after aggregation, so HAVING AVG(salary) > 60000 removes entire department groups whose computed average salary does not exceed $60,000. It operates on the aggregated result, not on individual rows, which is why it can reference aggregate functions like AVG.

Answer

It orders departments by average salary descending.

Answer

It returns only the department with the maximum average salary.

Answer

It filters individual employee rows with salary > 60000 before grouping.

Question 6

Which SQL function can be used to extract the year from a date column 'order_date'?

Accepted Answer

YEAR(order_date). The YEAR function extracts the year portion from a date.

Answer

DATEDIFF(year, order_date)

Answer

DATEADD(year, order_date)

Answer

FORMAT(order_date, 'yyyy')

Question 7

A data analyst wants to assign a unique sequential integer to each row in a result set, starting at 1, based on the order of the 'sales_amount' column descending. Which window function should be used?

Accepted Answer

ROW_NUMBER() OVER (ORDER BY sales_amount DESC). ROW_NUMBER() assigns a unique sequential integer to each row starting at 1, with no ties and no gaps, based on the ORDER BY clause. Since the requirement is a unique sequential integer per row ordered by sales_amount DESC, ROW_NUMBER() is the correct function.

Answer

DENSE_RANK() OVER (ORDER BY sales_amount DESC)

Answer

RANK() OVER (ORDER BY sales_amount DESC)

Answer

NTILE(1) OVER (ORDER BY sales_amount DESC)

Question 8

A data analyst is using a recursive CTE to traverse an organizational hierarchy. What is the purpose of the anchor member in the recursive CTE?

Accepted Answer

It provides the initial seed or starting rows for the recursion.. The anchor member initializes the recursion with the base result set.

Answer

It filters the final output of the recursive CTE.

Answer

It specifies how to join the CTE with itself recursively.

Answer

It defines the termination condition for the recursion.

Question 9

A data analyst is profiling a dataset and finds that the 'email' column contains some NULL values. Which SQL query can be used to count how many rows have a NULL email?

Accepted Answer

SELECT COUNT(*) FROM table WHERE email IS NULL. SELECT COUNT(*) FROM table WHERE email IS NULL correctly counts rows where email is NULL. COUNT(*) counts all rows in the filtered result set, and IS NULL is the only valid comparison operator for NULL in SQL. This is the standard, portable way to count NULLs.

Answer

SELECT COUNT(email) FROM table WHERE email = NULL

Answer

SELECT SUM(CASE WHEN email IS NULL THEN 1 END) FROM table

Answer

SELECT COUNT(ISNULL(email)) FROM table

Question 10

Which data sampling method involves selecting every k-th element from a list after a random start?

Accepted Answer

Systematic sampling. Systematic sampling selects every k-th element from a list after choosing a random starting point. For example, with k=10 and a random start of 3, you pick elements 3, 13, 23, and so on. This makes it efficient for large, ordered populations while still providing a probabilistic sample.

Answer

Cluster sampling

Answer

Stratified sampling

Answer

Simple random sampling

Question 11

A data analyst is using pandas to read a CSV file named 'sales.csv'. Which line of code correctly reads the file into a DataFrame?

Accepted Answer

import pandas as pd; df = pd.read_csv('sales.csv'). The correct pandas idiom is pd.read_csv('sales.csv'), which returns a DataFrame. pandas is conventionally imported as pd, and read_csv is the dedicated CSV parser that handles delimiters, headers, dtypes, and encoding. This is the canonical one-liner every pandas user writes.

Answer

import csv; df = csv.read('sales.csv')

Answer

import pandas as pd; df = pd.read('sales.csv')

Answer

import numpy as np; df = np.read_csv('sales.csv')

Question 12

A data analyst is using the IQR method to identify outliers in a dataset. The first quartile (Q1) is 25 and the third quartile (Q3) is 45. What is the upper bound for identifying outliers?

Accepted Answer

75. The IQR method defines outliers as values below Q1 - 1.5*IQR or above Q3 + 1.5*IQR. Here, Q1=25, Q3=45, so IQR = 45-25 = 20. The upper bound is Q3 + 1.5*IQR = 45 + 1.5*20 = 45 + 30 = 75. Therefore, any value above 75 is considered an outlier.

Answer

85

Answer

65

Answer

55

Question 13

A data analyst needs to create a new column 'full_name' by concatenating 'first_name' and 'last_name' with a space. Which SQL function should be used in the SELECT clause?

Accepted Answer

CONCAT(first_name, ' ', last_name). CONCAT(first_name, ' ', last_name) is the standard SQL function for joining strings, and it correctly inserts a literal space between the two columns. CONCAT accepts multiple arguments and returns a single concatenated string, which can be aliased as full_name in the SELECT clause.

Answer

COMBINE(first_name, last_name)

Answer

JOIN(first_name, last_name)

Answer

first_name + ' ' + last_name

Question 14

A data analyst is investigating a correlation between two continuous variables. Which THREE of the following are appropriate steps in this exploratory data analysis? (Select THREE.)

Accepted Answer

Calculate the Pearson correlation coefficient. Option A is correct because the Pearson correlation coefficient (r) is the standard statistic for quantifying the strength and direction of a linear relationship between two continuous variables, which is exactly the analyst's goal. Option B is correct because a scatter plot visually reveals the form, direction, and strength of the relationship between the two continuous variables and can expose non-linearity that a single correlation value would hide. Option D is correct because box plots (or their underlying IQR-based rules) identify outliers that can disproportionately distort the Pearson correlation coefficient, so checking for them is a necessary data-quality step before trusting r. Option C is not appropriate here because a t-test compares means between groups (or against a hypothesized mean), not the association between two continuous variables. Option E is not appropriate because a contingency table summarizes counts of categorical variables, whereas both variables in this scenario are continuous.

Answer

Perform a t-test

Answer

Create a contingency table

Question 15

A data analyst wants to retrieve the top 5 highest-paid employees from the 'employees' table. Which SQL clauses could be used to achieve this? (Select TWO.)

Accepted Answer

ORDER BY salary DESC. Option A, ORDER BY salary DESC, is correct because it sorts the result set by the salary column in descending order, placing the highest-paid employees first so the top earners can be identified. Option D, LIMIT 5, is correct because it restricts the result set to only the first 5 rows, which after the descending sort yields exactly the top 5 highest-paid employees. Together, ORDER BY salary DESC LIMIT 5 produces the desired result. Option C, ORDER BY salary ASC, is wrong because ascending order returns the lowest-paid employees first, the opposite of what is needed. Option B, HAVING salary, is wrong because HAVING filters groups after aggregation and is not used to sort or limit rows. Option E, GROUP BY salary, is wrong because grouping by salary aggregates rows by distinct salary values rather than selecting the top 5 individual employees.

Answer

HAVING salary

Answer

ORDER BY salary ASC

Answer

GROUP BY salary