DA0-002 Practice Exam 54 — 20 Questions

Question 1

A data analyst wants to retrieve the top 5 highest-paid employees from an 'employees' table, including ties. Which SQL clause should be used?

Accepted Answer

TOP 5 WITH TIES. In many SQL dialects, LIMIT can be used to restrict rows. However, to include ties, some databases offer WITH TIES with FETCH FIRST or TOP. Standard SQL: FETCH FIRST 5 ROWS WITH TIES. TOP 5 alone does not include ties. RANK() with WHERE clause can work but is more complex.

Answer

ORDER BY salary DESC LIMIT 5

Answer

HAVING salary > 50000

Answer

WHERE ROWNUM <= 5

Question 2

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. Upper threshold = Q3 + 1.5 * IQR. IQR = 70,000 - 40,000 = 30,000. So upper = 70,000 + 45,000 = 115,000.

Answer

130,000

Answer

85,000

Answer

100,000

Question 3

A small business wants to acquire customer feedback through a short questionnaire emailed after purchase. Which data acquisition method does this represent?

Accepted Answer

Survey. A survey is a structured data collection method where respondents answer predefined questions, typically via a form or questionnaire. In this scenario, the business is using a short questionnaire emailed after purchase to gather customer feedback, which directly aligns with the definition of a survey as a data acquisition method.

Answer

Transaction log

Answer

Interview

Answer

Observation

Question 4

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

Question 5

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 6

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 pandas function read_csv reads a CSV file into a DataFrame.

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 7

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. COUNT(*) counts all rows; WHERE email IS NULL filters only null rows.

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 8

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 concatenates strings; in some DBMS, || or + is used, but CONCAT is standard.

Answer

COMBINE(first_name, last_name)

Answer

JOIN(first_name, last_name)

Answer

first_name + ' ' + last_name

Question 9

A data quality assessment reveals that a column named 'email' contains values like 'user@example' (missing domain extension). Which data profiling technique would best identify such pattern violations?

Accepted Answer

Pattern analysis. Pattern analysis involves checking values against expected patterns (e.g., regex for email format). Cardinality counts distinct values, referential integrity checks relationships between tables, and data type verification checks data types.

Answer

Cardinality analysis

Answer

Referential integrity check

Answer

Data type verification

Question 10

A data analyst is writing a query to rank products by total sales within each category, showing dense rank and avoiding gaps. Which window function should be used?

Accepted Answer

DENSE_RANK(). DENSE_RANK() assigns ranks without gaps, so tied values get the same rank and the next rank is the next consecutive number.

Answer

ROW_NUMBER()

Answer

NTILE()

Answer

RANK()

Question 11

A data analyst wants to ensure a sample proportionally represents different regions in a population. Which sampling method should be used?

Accepted Answer

Stratified sampling. Stratified sampling divides the population into strata (regions) and samples proportionally from each.

Answer

Simple random sampling

Answer

Cluster sampling

Answer

Systematic sampling

Question 12

When communicating uncertainty in a report, which of the following is the most appropriate way to convey the reliability of a survey result showing 75% customer satisfaction?

Accepted Answer

"We are 95% confident that the true satisfaction rate is between 72% and 78%.". Confidence intervals are standard for communicating uncertainty around a point estimate.

Answer

"The satisfaction rate might be lower or higher."

Answer

"75% of customers are satisfied."

Answer

"The margin of error is 3%."

Question 13

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. BETWEEN is inclusive of both endpoints.

Answer

total > 100 AND total < 500

Answer

total IN (100, 500)

Answer

total >= 100 OR total <= 500

Question 14

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. The HAVING clause filters groups after aggregation, whereas WHERE filters rows before grouping.

Answer

WHERE

Answer

GROUP BY

Answer

FILTER

Question 15

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 16

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 strata (e.g., age groups) and samples proportionally from each stratum.

Answer

Systematic sampling

Answer

Simple random sampling

Answer

Cluster sampling

Question 17

A data analyst needs to perform stratified sampling on a customer database to ensure proportional representation across three regions: North (40%), South (30%), and West (30%). The total sample size required is 1,000. How many customers should be sampled from the North region?

Accepted Answer

400. Stratified sampling with proportional allocation: sample size per stratum = (stratum proportion) * total sample size. North = 0.40 * 1000 = 400.

Answer

333

Answer

500

Answer

300

Question 18

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 removes duplicate rows from the result set, returning only unique values.

Answer

TOP

Answer

UNIQUE

Answer

FILTER

Question 19

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 20

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. ORDER BY salary DESC sorts descending, and LIMIT/TOP/FETCH restricts rows.

Answer

HAVING salary

Answer

ORDER BY salary ASC

Answer

GROUP BY salary