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

Question 1

A data analyst wants to extract the year from a date column 'order_date' in a SQL database. Which function should be used?

Accepted Answer

YEAR(order_date). The YEAR() function is a standard SQL date function that extracts the year from a date or datetime expression. It takes a single argument, the date column, and returns an integer representing the year. This is the most direct and appropriate function for the task.

Answer

DATEADD(year, order_date, 0)

Answer

DATEDIFF(year, order_date, GETDATE())

Answer

GETDATE()

Question 2

In pandas, you have a DataFrame 'df' with columns 'product' and 'sales'. You want to calculate the total sales per product. Which method should you use?

Accepted Answer

df.groupby('product')['sales'].sum(). The groupby('product')['sales'].sum() pattern is the canonical pandas idiom for split-apply-combine aggregation: it groups rows by the 'product' column, selects the 'sales' Series, and applies the sum aggregator to each group, returning a Series indexed by product. This is the most direct and efficient way to compute total sales per product.

Answer

df['sales'].apply(sum)

Answer

df.pivot_table(values='sales', index='product', aggfunc='sum')

Answer

df.merge(df, on='product')

Question 3

A data analyst is cleaning text data in a SQL database. Which THREE string functions are commonly used to standardize and clean text? (Choose three.)

Accepted Answer

REPLACE. REPLACE is correct because it substitutes specific substrings (e.g., removing stray characters or fixing inconsistent tokens) within a string, which is a core standardization step. UPPER is correct because it converts all characters to uppercase, enforcing consistent casing so values like 'usa' and 'USA' match during cleaning and comparison. TRIM is correct because it removes leading and trailing spaces (or specified characters), eliminating whitespace artifacts that break joins, grouping, and equality checks. LENGTH does not belong because it only returns the number of characters in a string and does not modify or standardize the data. CONCAT does not belong because it merely joins strings together, which is a transformation for combining values rather than cleaning or standardizing text.

Answer

LENGTH

Answer

CONCAT

Question 4

A data analyst needs to retrieve only unique job titles from the 'employees' table. Which SQL keyword should be used in the SELECT clause?

Accepted Answer

DISTINCT. The DISTINCT keyword in the SELECT clause removes duplicate rows from the result set, so 'SELECT DISTINCT job_title FROM employees' returns each unique job title only once. It is the standard SQL keyword defined in the SQL standard for deduplication of result rows. DISTINCT applies to the entire selected column list, not just one column.

Answer

TOP

Answer

UNIQUE

Answer

FILTER

Question 5

A dataset contains sales transactions with columns 'order_date', 'amount', and 'region'. The analyst wants to calculate the total sales per region for orders placed in 2023, but only include regions where total sales exceed $10,000. Which SQL clause should be used to filter the aggregated results?

Accepted Answer

HAVING. HAVING is the SQL clause that filters rows after aggregation, so it can reference aggregate functions like SUM(amount) and apply conditions such as SUM(amount) > 10000. WHERE runs before GROUP BY and cannot reference aggregates, so it cannot filter on total sales per region.

Answer

WHERE

Answer

GROUP BY

Answer

FILTER

Question 6

In a table 'employee_hierarchy' with columns 'employee_id', 'manager_id', and 'employee_name', an analyst needs to generate a list of all employees under a specific manager, including multiple levels of subordinates. Which SQL construct is most appropriate for querying this hierarchical data efficiently?

Accepted Answer

Recursive CTE. Recursive CTEs are designed to handle hierarchical data by repeatedly joining a CTE to itself until all levels are included.

Answer

Window function with PARTITION BY

Answer

Subquery in WHERE clause

Answer

Self-JOIN with WHERE clause

Question 7

A data analyst runs a query to count the number of customers in each city. The query uses COUNT(*) and GROUP BY city. However, the result includes NULL for some cities. What will COUNT(*) return for a group where the city is NULL?

Accepted Answer

The number of rows with NULL city. COUNT(*) counts rows, not non-NULL values, so for a group where city IS NULL it returns the number of rows in that group. The NULL city forms its own group under GROUP BY because SQL treats NULLs as equal for grouping purposes. This differs from COUNT(city), which would return 0 for that group because it ignores NULLs.

Answer

NULL

Answer

0

Answer

The number of non-NULL cities

Question 8

A data analyst is performing data profiling on a customer dataset. Which metric would best reveal the number of distinct values in the 'state' column?

Accepted Answer

Cardinality. Cardinality measures the number of distinct values in a column, so it directly reveals how many unique states appear in the 'state' column. This is a standard data profiling metric used to understand column variability and potential categorical encoding needs. Mean, row count, and null count do not provide distinct value counts.

Answer

Mean

Answer

Row count

Answer

Null count

Question 9

A dataset contains a 'salary' column. The analyst wants to identify outliers using the IQR method. If Q1 = 40,000 and Q3 = 70,000, what is the upper threshold for a non-outlier?

Accepted Answer

115,000. The IQR method defines the upper fence as Q3 + 1.5 x IQR. Here IQR = 70,000 - 40,000 = 30,000, so 1.5 x IQR = 45,000 and the upper threshold is 70,000 + 45,000 = 115,000. Any salary above 115,000 is considered an outlier.

Answer

130,000

Answer

85,000

Answer

100,000

Question 10

An analyst is sampling a large customer database to estimate the average purchase amount. To ensure that the sample proportionally represents different customer segments (e.g., age groups), which sampling method should be used?

Accepted Answer

Stratified sampling. Stratified sampling divides the population into homogeneous subgroups (strata) based on a characteristic like age group, then samples proportionally from each stratum. This guarantees representation of every segment in the sample, which is exactly what the analyst needs to estimate the average purchase amount accurately across customer segments.

Answer

Systematic sampling

Answer

Simple random sampling

Answer

Cluster sampling

Question 11

Which SQL aggregate function would an analyst use to calculate the average value of a numeric column?

Accepted Answer

AVG. The AVG aggregate function computes the arithmetic mean of a numeric column, which is exactly what the analyst needs. It ignores NULL values by default and returns a single average value for the specified column or expression.

Answer

SUM

Answer

COUNT

Answer

MEDIAN

Question 12

A data analyst is cleaning a dataset and finds that some cells in the 'email' column contain leading spaces. Which string function should be used to remove these spaces?

Accepted Answer

TRIM. The TRIM function removes leading and trailing spaces (and sometimes other whitespace) from a string, which directly addresses the leading spaces in the email column. In most SQL dialects and data tools, TRIM is the standard function for stripping both leading and trailing spaces, making it the correct choice for cleaning the data.

Answer

LTRIM

Answer

REPLACE

Answer

SUBSTRING

Question 13

In a table 'sales_team' with columns 'salesperson', 'quarter', and 'revenue', an analyst wants to assign a rank to each salesperson within their quarter based on revenue, with the highest revenue getting rank 1. However, if two salespeople have the same revenue, they should receive the same rank, and the next rank should be the next consecutive integer (no gaps). Which window function should be used?

Accepted Answer

DENSE_RANK(). DENSE_RANK() assigns ranks without gaps, so if two salespeople tie for rank 1, the next rank is 2. This matches the requirement that ties receive the same rank and the next rank is consecutive. It is the correct window function for this scenario.

Answer

RANK()

Answer

NTILE(4)

Answer

ROW_NUMBER()

Question 14

A data analyst is conducting exploratory data analysis (EDA) on a dataset. Which TWO tasks are typically performed during EDA? (Select two.)

Accepted Answer

Identify outliers using the IQR method. Option D is correct because identifying outliers with the IQR method is a core EDA activity: you compute Q1 and Q3, derive IQR = Q3 − Q1, and flag values below Q1 − 1.5×IQR or above Q3 + 1.5×IQR to understand data quality and distribution. Option E is correct because calculating correlations (e.g., Pearson's r for linear relationships or Spearman's rank for monotonic ones) between variables is a standard EDA step to reveal associations and guide feature selection. Option A is not an EDA task; a sampling plan belongs to study/survey design and data collection planning, which precedes analysis. Option B is predictive modeling, a confirmatory phase that comes after EDA rather than during it. Option C is model deployment, an MLOps/production activity that occurs long after EDA.

Answer

Create a sampling plan

Answer

Build a predictive regression model

Answer

Deploy the model to production

Question 15

An analyst is using SQL to analyze employee data. Which THREE of the following are valid uses of the WHERE clause? (Select three.)

Accepted Answer

Filter rows where manager_id is NULL using IS NULL. Option C is correct because the WHERE clause can test for NULL values with the IS NULL predicate, so filtering rows where manager_id is NULL is a valid row-level filter. Option D is correct because WHERE supports the LIKE operator for pattern matching, so filtering names that start with 'J' (e.g., name LIKE 'J%') is a valid use. Option E is correct because WHERE supports the BETWEEN operator for range comparisons, so filtering salaries between 50,000 and 70,000 is a valid row-level filter. Option A is incorrect because sorting the result set is done with the ORDER BY clause, not WHERE. Option B is incorrect because filtering groups after aggregation is performed with the HAVING clause, not WHERE, which filters rows before grouping.

Answer

Sort the result set by hire_date

Answer

Filter groups after aggregation using HAVING