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

Question 1

During data profiling, an analyst wants to identify the number of distinct values in a column. Which SQL function should be used?

Accepted Answer

COUNT(DISTINCT column). COUNT(DISTINCT column) returns the number of unique non-null values.

Answer

DISTINCT(column)

Answer

COUNT(*)

Answer

COUNT(column)

Question 2

A data analyst needs to sample 1000 customers from a database of 100,000 customers for a survey, ensuring every customer has an equal chance of selection. Which sampling method is most appropriate?

Accepted Answer

Simple random sampling. Simple random sampling gives each individual an equal chance of being selected.

Answer

Cluster sampling

Answer

Stratified sampling

Answer

Systematic sampling

Question 3

In a dataset of employee salaries, the analyst notices one value that is significantly higher than the rest. Using the IQR method, which values are typically considered outliers?

Accepted Answer

Values beyond Q1 - 1.5*IQR or Q3 + 1.5*IQR. Outliers are values less than Q1 - 1.5*IQR or greater than Q3 + 1.5*IQR.

Answer

Values beyond Q1 - 3*IQR or Q3 + 3*IQR

Answer

Values beyond mean ± 2 standard deviations

Answer

Values beyond min and max

Question 4

A data analyst wants to combine first_name and last_name columns into a single full_name column in a SQL query. Which string function should be used?

Accepted Answer

CONCAT(). CONCAT() joins two or more strings together.

Answer

UPPER()

Answer

LENGTH()

Answer

SUBSTRING()

Question 5

A data analyst needs to calculate the running total of sales for each product over time. Which window function clause is essential for this calculation?

Accepted Answer

PARTITION BY product_id ORDER BY sale_date. For a running total of sales for each product over time, you must group the calculation by product (PARTITION BY product_id) and order by sale_date (ORDER BY sale_date). The default window frame in SQL (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) suffices for running totals. Option B provides both PARTITION BY product_id and ORDER BY sale_date, making it essential.

Answer

ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW

Answer

PARTITION BY product_id

Answer

ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Question 6

An analyst is performing EDA and wants to measure the strength and direction of linear relationship between two continuous variables. Which statistical measure should they compute?

Accepted Answer

Correlation. Correlation coefficient (Pearson's r) measures linear relationship strength and direction.

Answer

Standard deviation

Answer

Mean

Answer

Mode

Question 7

A data analyst is validating referential integrity between orders and customers tables. Which TWO of the following checks should the analyst perform?

Accepted Answer

Check that no customer is deleted while having orders. Referential integrity ensures foreign keys match primary keys and no orphaned records.

Answer

Check that every order has a non-null order_id

Answer

Check that customer names are unique

Answer

Check that order amounts are positive

Question 8

An analyst is using a CTE to compute hierarchical data. Which TWO statements about recursive CTEs are true?

Accepted Answer

The recursive member must reference the CTE name. Recursive CTEs consist of an anchor member (initial SELECT) that does not reference the CTE, making option B correct. The recursive member must reference the CTE name, as stated in option A. While UNION ALL is commonly used, it is not a strict requirement; some implementations allow alternative set operators, so option D is not universally true. Options C and E are false: recursive CTEs handle hierarchies beyond numerical sequences and can be used with GROUP BY.

Answer

Recursive CTEs can only be used for numerical sequences

Answer

Recursive CTEs must include a UNION ALL operator

Answer

Recursive CTEs cannot be used with GROUP BY

Question 9

A data analyst wants to export a summary report from a DataFrame in pandas. Which THREE methods are commonly used for data export?

Accepted Answer

to_csv(). pandas provides to_csv, to_excel, and to_json for exporting data.

Answer

to_pickle()

Answer

to_sql()

Question 10

A data analyst is using SQL to filter a sales table for transactions that occurred in either 'Q1' or 'Q3' of 2023 and have a sale amount greater than $100. Which WHERE clause correctly implements this condition?

Accepted Answer

WHERE quarter = 'Q1' AND amount > 100 OR quarter = 'Q3' AND amount > 100. Both C and D correctly implement the filter. SQL's operator precedence gives AND higher priority than OR, so option C is parsed as (quarter = 'Q1' AND amount > 100) OR (quarter = 'Q3' AND amount > 100), which is equivalent to (quarter = 'Q1' OR quarter = 'Q3') AND amount > 100. Option D expresses the same condition directly with parentheses.

Answer

WHERE quarter IN ('Q1','Q3') OR amount > 100

Answer

WHERE quarter = 'Q1' OR quarter = 'Q3' AND amount > 100

Question 11

A data analyst is reviewing a dataset of customer transactions and wants to assess data quality by profiling the 'order_date' column. Which TWO profiling tasks are most appropriate for this date column? (Select TWO).

Accepted Answer

Count of null values. Profiling a date column typically includes checking for null values and verifying the data type. Cardinality and pattern analysis are more relevant for categorical or string columns; variance is for numeric data.

Answer

Pattern analysis (e.g., format consistency)

Answer

Variance

Answer

Cardinality (number of unique values)

Question 12

An e-commerce company wants to analyze sales performance across product categories. The dataset includes transaction amounts and a column 'category' with values (Electronics, Clothing, Home). The analyst decides to use stratified sampling to ensure proportional representation. Which THREE steps are required to implement this? (Select THREE).

Accepted Answer

Calculate the proportion of each category in the population. Stratified sampling requires dividing the population into strata (categories), then randomly sampling from each stratum in proportion to its size. Combining strata or simple random sampling without stratification would not achieve proportional representation.

Answer

Select every 10th transaction from the entire dataset

Answer

Combine all categories into a single group and perform simple random sampling

Question 13

A data analyst is using pandas to clean a DataFrame that contains missing values in the 'age' and 'income' columns. Which THREE pandas methods are appropriate for handling missing data? (Select THREE).

Accepted Answer

dropna(). Common pandas methods for missing data include dropna (remove rows with NaN), fillna (replace NaN with a value), and apply with a custom function. Merge is for combining DataFrames; pivot_table is for reshaping.

Answer

pivot_table()

Answer

merge()

Question 14

An analyst needs to identify outliers in a numeric column 'transaction_amount' using the interquartile range (IQR) method. Which TWO steps are part of this process? (Select TWO).

Accepted Answer

Subtract 1.5 times the IQR from Q1 and add 1.5 times the IQR to Q3 to define bounds. The IQR method involves calculating Q1 and Q3 to find IQR, then defining lower and upper bounds as Q1 - 1.5*IQR and Q3 + 1.5*IQR. Computing mean and standard deviation is for Z-score method; calculating median alone is insufficient.

Answer

Calculate the median of the column

Answer

Sort the data and remove the top and bottom 5%

Answer

Compute the mean and standard deviation of the column

Question 15

A data analyst uses a Common Table Expression (CTE) to query hierarchical employee data (manager_id references employee_id). Which THREE statements about recursive CTEs are correct? (Select THREE).

Accepted Answer

The anchor member is the first part of the CTE that does not reference the CTE itself. Recursive CTEs require the WITH RECURSIVE clause (or WITH in some DBMS that imply recursion). The UNION ALL is typical to combine anchor and recursive members. Anchor member is the starting set; recursive member references the CTE itself. The anchor member is defined before the recursive member.

Answer

Recursive CTEs cannot be used to generate a series of numbers

Answer

The recursive member cannot reference the CTE name