DA0-002 Practice Exam 53 — 20 Questions

Question 1

A data analyst is presenting a time-series chart of monthly sales to executives. The sales dropped sharply in March due to a one-time supply chain disruption. Which storytelling technique would best help the audience understand this anomaly?

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

Accepted Answer

Add an annotation explaining the supply chain disruption. Adding an annotation directly on the chart provides immediate context for the March sales drop, allowing executives to understand the anomaly without leaving the visualization. This technique follows the principle of 'contextual annotation' in data storytelling, where key events are marked to prevent misinterpretation of trends. It preserves data integrity while clarifying the cause, which is essential for accurate decision-making.

Answer

Remove the March data point to avoid confusion

Answer

Use a different chart type to hide the drop

Answer

Use a moving average to smooth the drop

Question 2

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 3

A data analyst is preparing a dataset for a machine learning algorithm that assumes normally distributed features. Which TWO data transformation methods should the analyst consider to achieve this?

Accepted Answer

Square root transformation. Log transformation and square root transformation are both methods that can reduce skewness and help make data more normally distributed. Z-score standardization only centers and scales the data but does not change the shape of the distribution. One-hot encoding is for categorical variables, and min-max normalization is for scaling, not normality.

Answer

One-hot encoding

Answer

Z-score standardization

Answer

Min-max normalization

Question 4

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). COUNT(DISTINCT column) counts unique non-null values in a column.

Answer

COUNT(category)

Answer

DISTINCT COUNT(category)

Answer

COUNT(*) WHERE category IS NOT NULL

Question 5

In a dataset of customer orders, you need to count the number of distinct customers who have placed orders. Which SQL aggregate function should you use?

Accepted Answer

COUNT(DISTINCT customer_id). COUNT(DISTINCT column) counts the number of unique non-null values in a column. COUNT(*) counts all rows including duplicates, COUNT(column) counts non-null values including duplicates. DISTINCT alone is not an aggregate function.

Answer

DISTINCT COUNT(customer_id)

Answer

COUNT(customer_id)

Answer

COUNT(*)

Question 6

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

Accepted Answer

DISTINCT. DISTINCT removes duplicate rows from the result set, returning only unique values. In this case, SELECT DISTINCT job_title would return each job title only once.

Answer

UNIQUE

Answer

REMOVE DUPLICATES

Answer

FILTER

Question 7

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(). df.groupby('product')['sales'].sum() groups by product and sums sales. df.pivot_table can also do it but is more complex. df.merge is for joining, df.apply is for applying a function element-wise or row/column-wise.

Answer

df['sales'].apply(sum)

Answer

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

Answer

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

Question 8

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, unlike WHERE which filters rows before aggregation.

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 9

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 10

A data analyst wants to retrieve the top 5 highest-paid employees from a table named 'employees' that has columns 'employee_id', 'salary', and 'name'. Which SQL query should they use?

Accepted Answer

SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 5;. ORDER BY salary DESC sorts from highest to lowest, and LIMIT 5 restricts to the first 5 rows.

Answer

SELECT TOP 5 name, salary FROM employees ORDER BY salary DESC;

Answer

SELECT name, salary FROM employees ORDER BY salary ASC LIMIT 5;

Answer

SELECT name, salary FROM employees WHERE ROWNUM <= 5 ORDER BY salary DESC;

Question 11

An analyst writes a SQL query that uses a window function: SELECT employee_id, salary, LAG(salary, 1) OVER (ORDER BY salary DESC) AS prev_salary FROM employees. What does the LAG function return for the row with the highest salary?

Accepted Answer

NULL. LAG returns the previous row's value in the ordered partition. For the first row (highest salary), there is no previous row, so it returns NULL.

Answer

The same salary value

Answer

The next highest salary

Answer

Zero

Question 12

A data analyst discovers an anomaly in a dataset. Which two actions should be taken before reporting? (Choose TWO.)

Accepted Answer

Verify the data source and extraction process. Before reporting an anomaly, the data analyst must verify the data source and extraction process to ensure the anomaly is not due to a pipeline error, such as a misconfigured ETL job or a corrupted data feed. This step confirms data integrity and prevents false alarms based on extraction artifacts rather than genuine data issues.

Answer

Assume the anomaly is real and report it

Answer

Immediately alert all stakeholders

Answer

Remove the anomaly without documentation

Question 13

When creating a data story, what should be the FIRST step?

Accepted Answer

Identify the key message. The first step in creating a data story is to identify the key message because the entire narrative, data selection, and visualization choices must align with a central insight. Without a clear message, subsequent steps like gathering data or designing layouts risk being unfocused and failing to communicate actionable findings. This ensures the story is driven by the insight, not by the data or tools.

Answer

Design the layout of the report

Answer

Gather all available data

Answer

Choose visualization tools

Question 14

During exploratory data analysis, you calculate the IQR for a numeric column and find that several data points fall below Q1 - 1.5*IQR. These points are likely:

Accepted Answer

Outliers. The IQR method defines outliers as points below Q1 - 1.5*IQR or above Q3 + 1.5*IQR. Points below the lower fence are considered outliers. They are not necessarily errors, but potential outliers. They are not the median or mode.

Answer

Normal variations within the distribution

Answer

The mode of the dataset

Answer

The median of the dataset

Question 15

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 16

An analyst needs to aggregate sales data by region and product, then sort the results by total sales in descending order. Which SQL clauses are required? (Select THREE).

Accepted Answer

GROUP BY. GROUP BY is needed to aggregate, ORDER BY to sort, and DESC for descending order. SUM is an aggregate function but not a clause.

Answer

HAVING

Answer

SUM

Question 17

An organization uses a data warehouse for analytics. Which two characteristics are typical of a data warehouse compared to a data lake? (Select TWO.)

Accepted Answer

Optimized for complex queries and reporting. A data warehouse is optimized for complex queries and reporting (A) because it uses schema-on-write (D), meaning data is transformed and structured before loading to support fast analytical queries. In contrast, a data lake uses schema-on-read and stores raw data. ELT is typical of data lakes, not data warehouses.

Answer

Typically uses ELT process

Answer

Stores raw data in native format

Answer

Supports all data types including unstructured

Question 18

A marketing manager wants to visualize the conversion rate at each stage of a sales funnel, from leads to closed deals. The data shows the number of prospects at each stage. Which chart type is most appropriate for this pipeline analysis?

Accepted Answer

Funnel chart. A funnel chart is specifically designed to show progressive reduction as data moves through stages of a process.

Answer

Treemap

Answer

Waterfall chart

Answer

Stacked bar chart

Question 19

A data analyst wants to create a temporary result set that can be referenced within a single SQL statement. Which feature should be used?

Accepted Answer

CTE. CTE (Common Table Expression) defined with WITH clause creates a temporary named result set usable within the query.

Answer

Subquery

Answer

Temporary table

Answer

Derived table

Question 20

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

Accepted Answer

AVG. AVG calculates the arithmetic mean of a numeric column.

Answer

SUM

Answer

COUNT

Answer

MEDIAN