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

Question 1

A data analyst wants to randomly select 100 customers from a database for a survey, ensuring that the sample reflects the proportion of male and female customers in the population. Which sampling method is most appropriate?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Stratified sampling. Stratified sampling ensures proportional representation of subgroups (strata).

Answer

Cluster sampling

Answer

Simple random sampling

Answer

Systematic sampling

Question 2

In a table with columns 'employee_id' and 'manager_id', a data analyst needs to retrieve the hierarchy level of each employee, where the top manager has manager_id NULL. Which SQL feature is best suited?

Accepted Answer

A recursive CTE. Recursive CTE can traverse hierarchical data to compute levels.

Answer

A window function with ROW_NUMBER()

Answer

A GROUP BY clause with aggregation

Answer

A self-join with a LEFT JOIN

Question 3

A data analyst is using pandas in Python to clean a dataset. Which method is most appropriate to replace missing numerical values with the median of the column?

Accepted Answer

df.fillna(df.median()). fillna with median replaces missing values with median.

Answer

df.interpolate()

Answer

df.dropna()

Answer

df.replace(np.nan, df.mean())

Question 4

An analyst wants to identify outliers in a dataset using the IQR method. Which values are typically considered outliers?

Accepted Answer

Values below Q1 - 1.5*IQR or above Q3 + 1.5*IQR. Outliers are values below Q1 - 1.5*IQR or above Q3 + 1.5*IQR.

Answer

Values below the mean or above the mean

Answer

Values below Q1 - IQR or above Q3 + IQR

Answer

Values below Q2 - 2*IQR or above Q2 + 2*IQR

Question 5

A dataset contains transaction amounts with a few extremely high values. The analyst wants to reduce the impact of these outliers on the average. Which measure of central tendency is most robust?

Accepted Answer

Median. Median is not affected by extreme values, while mean is sensitive.

Answer

Mean

Answer

Mode

Answer

Standard deviation

Question 6

A data analyst uses a CTE to simplify a complex query. Which keyword is used to define a CTE?

Accepted Answer

WITH. The WITH clause introduces a CTE.

Answer

DEFINE

Answer

CTE

Answer

DECLARE

Question 7

A data analyst is exploring a sales dataset and wants to identify columns that are likely to be foreign keys. Which TWO characteristics would indicate a foreign key?

Accepted Answer

The column name ends with '_id'. Foreign keys typically match primary keys in another table and have a name suggestive of the relationship.

Answer

The column contains NULL values

Answer

The column is of integer data type

Answer

The column has a UNIQUE constraint

Question 8

A data analyst is using Python pandas to perform exploratory data analysis. Which THREE methods are commonly used to assess data quality and distributions?

Accepted Answer

df.describe(). describe() gives summary statistics, info() shows data types and non-null counts, and value_counts() shows frequency distributions.

Answer

df.transpose()

Answer

df.sort_values()

Question 9

A data analyst needs to sample records from a large dataset for a quick analysis. Which TWO sampling methods are examples of probability sampling?

Accepted Answer

Simple random sampling. Simple random sampling and systematic sampling are probability-based methods where every element has a known chance of selection.

Answer

Snowball sampling

Answer

Convenience sampling

Answer

Quota sampling

Question 10

A data analyst runs the following query: SELECT DISTINCT city FROM customers. What is the primary purpose of using the DISTINCT keyword in this query?

Accepted Answer

To remove duplicate city names. DISTINCT removes duplicate rows from the result set. In this query, it returns each unique city name only once.

Answer

To sort the cities alphabetically

Answer

To count the number of cities

Answer

To filter cities that start with a specific letter

Question 11

In a sales database, an analyst needs to retrieve all orders where the order amount is between $100 and $500. Which WHERE clause should be used?

Accepted Answer

WHERE amount BETWEEN 100 AND 500. The BETWEEN operator is inclusive and is the standard way to filter a range of values.

Answer

WHERE amount IN (100, 500)

Answer

WHERE amount >= 100 AND amount <= 500

Answer

WHERE amount > 100 AND amount < 500

Question 12

A data analyst uses the following query: SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department HAVING AVG(salary) > 50000. What is the purpose of the HAVING clause in this query?

Accepted Answer

To filter groups (departments) based on the average salary. HAVING filters groups after aggregation, similar to WHERE but for aggregated results.

Answer

To ensure only departments with more than 50000 employees are shown

Answer

To filter individual employee records before grouping

Answer

To sort departments by average salary

Question 13

A data analyst wants to concatenate first_name and last_name columns with a space in between. Which string function combination should be used in SQL?

Accepted Answer

CONCAT(first_name, ' ', last_name). CONCAT joins strings; adding a space produces 'First Last'.

Answer

first_name + ' ' + last_name

Answer

SUBSTRING(first_name, 1, 1) + '.' + last_name

Answer

CONCAT(first_name, last_name)

Question 14

An analyst needs to retrieve the year from an order_date column (datetime type). Which function should be used in SQL?

Accepted Answer

EXTRACT(YEAR FROM order_date). EXTRACT(YEAR FROM order_date) is the standard SQL function to get the year part.

Answer

FORMAT(order_date, 'yyyy')

Answer

DATEADD(YEAR, order_date, 0)

Answer

YEAR(order_date)

Question 15

A data analyst is using a window function to assign a unique rank to each employee within their department based on salary, with ties receiving the same rank and leaving gaps. Which function should be used?

Accepted Answer

RANK(). RANK() assigns the same rank to ties and leaves gaps (e.g., 1,1,3). DENSE_RANK() does not leave gaps.

Answer

DENSE_RANK()

Answer

ROW_NUMBER()

Answer

NTILE()