Courseiva

CCNA Data Acquisition and Preparation Questions

75 of 208 questions · Page 2/3 · Data Acquisition and Preparation · Answers revealed

Practice these questionsDomain overviewAll questions
76
MCQhard

A data team is using web scraping to collect competitor pricing data. The target website has anti-scraping measures like CAPTCHAs and rate limiting. Which approach is most effective?

A.Use a single IP address
B.Disregard robots.txt
C.Use rotating proxies and respectful delays
D.Increase request frequency
AnswerC

Rotating proxies distribute requests across many IP addresses, defeating IP-based rate limiting, while respectful delays reduce request frequency to avoid triggering CAPTCHAs and detection. Together they sustain collection without overwhelming the target site or breaching its defensive thresholds.

Why this answer

Using rotating proxies and respectful delays is the most effective approach because it distributes requests across multiple IP addresses, avoiding rate limiting and IP bans, while delays reduce the load on the target server and mimic human behavior, helping to bypass anti-scraping measures like CAPTCHAs and rate limiting.

Exam trap

DA0-002 often tests the misconception that increasing request frequency or using a single IP can overcome anti-scraping measures, when in fact these approaches worsen blocking; the correct strategy involves rotation and throttling.

How to eliminate wrong answers

Option A is wrong because using a single IP address makes the scraper easily detectable and rate-limited or blocked, as all requests originate from one source. Option B is wrong because disregarding robots.txt is unethical and may lead to legal issues, and it does not help bypass technical anti-scraping measures; it can also result in IP bans. Option D is wrong because increasing request frequency exacerbates rate limiting and triggers more aggressive anti-scraping responses, making the scraper less effective.

Practice this question →
77
MCQeasy

A data analyst needs to count the number of customers who have placed at least one order. Which SQL query should be used?

A.SELECT DISTINCT COUNT(customer_id) FROM orders
B.SELECT SUM(customer_id) FROM orders
C.SELECT COUNT(customer_id) FROM orders
D.SELECT COUNT(DISTINCT customer_id) FROM orders
AnswerD

COUNT(DISTINCT customer_id) eliminates duplicate customer identifiers before counting, so each customer contributes exactly one to the total regardless of how many orders they placed. This directly satisfies the stem's requirement to count customers with at least one order, rather than counting order rows themselves.

Why this answer

COUNT(DISTINCT customer_id) counts each unique customer exactly once, which is precisely what is needed to answer 'how many customers placed at least one order' — a customer with multiple orders is counted only once. This is the standard SQL pattern for counting distinct entities in a fact table.

Exam trap

DA0-002 often tests the placement of DISTINCT inside the COUNT function — candidates who write SELECT DISTINCT COUNT(...) believe they are deduplicating, but DISTINCT applies to the aggregate result, not the input rows, so the count remains inflated.

How to eliminate wrong answers

Option A is wrong because SELECT DISTINCT COUNT(customer_id) applies DISTINCT to the result of COUNT(), which is already a single scalar value — it does not deduplicate customer_id before counting, so it returns the same result as COUNT(customer_id), i.e., the total number of order rows, not unique customers. Option B is wrong because SUM(customer_id) adds up the numeric values of customer_id, which is meaningless for counting customers and produces a nonsensical large number. Option C is wrong because COUNT(customer_id) counts every non-NULL order row, so a customer with five orders is counted five times, overstating the number of distinct customers.

Practice this question →
78
Multi-Selectmedium

A data analyst is assessing the quality of a newly acquired dataset from an external source. The analyst needs to evaluate two dimensions of data quality that directly affect the dataset's fitness for use in analysis. Which two dimensions should the analyst prioritize? (Choose two.)

Select 2 answers
A.Completeness
B.Accessibility
C.Portability
D.Scalability
E.Accuracy
AnswersA, E

Completeness measures the extent to which all required data is present. Missing values can lead to biased or invalid analysis results. In an externally acquired dataset, completeness is critical because missing data may indicate collection issues or gaps that affect the reliability of conclusions. Prioritizing completeness ensures that analyses are based on a full picture.

Why this answer

Completeness and accuracy are fundamental data quality dimensions that directly affect the validity of analysis. Completeness ensures no critical data is missing, while accuracy ensures the data correctly represents reality. These two dimensions are essential when evaluating an external dataset because they determine whether the data can be trusted for decision-making.

Other dimensions like accessibility, portability, and scalability are more about operational aspects.

Exam trap

The trap here is selecting operational or technical dimensions like accessibility or scalability instead of core data quality dimensions that directly impact analytical results.

Practice this question →
79
MCQeasy

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

A.UNIQUE
B.REMOVE DUPLICATES
C.DISTINCT
D.FILTER
AnswerC

DISTINCT eliminates duplicate rows from the result set, returning only unique job titles as the stem requires. Applied directly in the SELECT clause, it collapses repeated values across the specified column, satisfying the uniqueness constraint without needing GROUP BY or aggregate functions.

Why this answer

The DISTINCT keyword in a SELECT clause eliminates duplicate rows from the result set, returning only unique combinations of the selected columns. When applied to a single column like job_title, it returns each job title exactly once, which is precisely what the analyst needs. DISTINCT operates on the entire row of selected columns, so SELECT DISTINCT job_title FROM employees yields the unique list of titles.

Exam trap

The trap here is confusing the DDL constraint UNIQUE with the DML query keyword DISTINCT — candidates who have seen UNIQUE in CREATE TABLE statements may reflexively choose it for a SELECT query.

How to eliminate wrong answers

Option A is wrong because UNIQUE is not a SQL SELECT keyword — it is a constraint used in DDL (CREATE TABLE / ALTER TABLE) to enforce uniqueness on a column, not a query modifier. Option B is wrong because 'REMOVE DUPLICATES' is not valid SQL syntax in any major RDBMS (Oracle, SQL Server, PostgreSQL, MySQL); it is a conceptual description, not a keyword. Option D is wrong because FILTER is used in PostgreSQL as a clause on aggregate functions (e.g., COUNT(*) FILTER (WHERE ...)) and in some engines as a WHERE-like construct, but it does not deduplicate rows.

Practice this question →
80
MCQeasy

A healthcare organization collects patient questionnaire data via paper forms at clinics. The forms are scanned and sent to a central office, where staff manually enter data into an electronic system. This process is slow and error-prone. The organization wants to reduce manual entry errors and speed up data availability. Which method should they adopt?

A.Continue manual entry but double-check all entries
B.Use optical character recognition (OCR) to digitize the forms and automatically populate the database
C.Send forms to an external data processing company
D.Require patients to fill out forms online at home
AnswerB

OCR converts scanned glyph images into machine-encoded text, then template or field mapping populates the database directly, eliminating the transcription step where staff introduce keystroke errors. This satisfies both stated constraints: fewer manual entry errors and faster data availability, since digitisation occurs at scan time rather than awaiting central-office keying.

Why this answer

Using optical character recognition (OCR) to digitize the paper forms and automatically populate the database directly addresses the goal of reducing manual entry errors and speeding up data availability. OCR converts scanned images of text into machine-readable data, eliminating the need for manual transcription and enabling faster processing. This method is well-suited for structured forms like patient questionnaires.

Exam trap

The trap here is assuming that any automation (like outsourcing or online forms) solves the problem, but the question specifically asks for reducing manual entry errors and speeding data availability, which OCR directly addresses.

How to eliminate wrong answers

Option A is wrong because continuing manual entry with double-checking still relies on human transcription, which remains slow and error-prone. Option C is wrong because sending forms to an external data processing company may reduce internal effort but still involves manual entry (unless they use OCR), introduces data privacy risks, and does not inherently speed up availability. Option D is wrong because requiring patients to fill out forms online at home may not be feasible for all patients and does not address the existing paper-based workflow; it also shifts the burden to patients and may not integrate with current systems.

Practice this question →
81
MCQmedium

A data analyst is using pandas in Python to merge two DataFrames: sales (columns: sale_id, product_id, amount) and products (columns: product_id, product_name). Which pandas function should they use to combine these DataFrames on the 'product_id' column?

A.combine()
B.merge()
C.join()
D.concat()
AnswerB

merge() performs a database-style join on a shared key, so passing on='product_id' combines sales and products into one DataFrame with product_name attached to each sale. concat() only stacks frames, and join() defaults to index alignment, neither matching this key-based requirement.

Why this answer

The pandas merge function is used to combine DataFrames on common columns. The syntax is pd.merge(sales, products, on='product_id').

Practice this question →
82
MCQeasy

A data analyst is performing data profiling on a customer table. Which metric would best help identify missing values in the 'phone' column?

A.Cardinality
B.Null count
C.Mean
D.Row count
AnswerB

Null count directly quantifies absent entries in the phone column, satisfying the profiling goal of identifying missing values. Unlike distinct count or data type checks, it measures completeness per attribute, exposing the exact volume of nulls requiring remediation before analysis.

Why this answer

The null count metric directly measures the number of missing (NULL) values in a column, which is exactly what the analyst needs to identify missing phone numbers. Data profiling tools report null count per column as a standard completeness metric. Other metrics like cardinality or mean do not reveal missingness.

Exam trap

The trap is confusing cardinality with completeness — candidates see 'cardinality' and think it measures how many values are present, but it actually measures distinct values, not missing ones.

How to eliminate wrong answers

Option A is wrong because cardinality measures the number of distinct values in a column, which tells you about uniqueness, not missingness. Option C is wrong because mean is an arithmetic average of numeric values and is undefined or meaningless for a phone column, and it does not indicate missing values. Option D is wrong because row count gives the total number of rows in the table, not the number of missing values in a specific column.

Practice this question →
83
MCQmedium

A data engineer is ingesting JSON data from an IoT sensor network. The JSON records contain nested arrays and objects. The engineer needs to flatten the structure to load it into a relational table. Which approach is most appropriate?

A.Use a JSON parsing function to extract each field into separate columns, manually specifying the path for each nested element.
B.Store the entire JSON as a single string column and parse it during analysis using string functions.
C.Convert the JSON to XML first, then use XML parsing functions to extract data.
D.Use a SQL function like OPENJSON or JSON_TABLE to shred the JSON and return a relational rowset.
AnswerD

Functions like OPENJSON (SQL Server) or JSON_TABLE (MySQL, Oracle) are designed to parse JSON and output relational rows and columns. They can handle nested arrays by specifying paths and using CROSS APPLY or LATERAL joins. This approach is scalable and adapts to schema changes with minimal code changes.

Why this answer

The most appropriate method is to use a dedicated JSON shredding function like OPENJSON or JSON_TABLE. These functions are built to parse nested JSON and output a relational rowset, handling arrays and objects efficiently. They integrate with SQL queries and allow joining and filtering, making them ideal for loading into relational tables.

Exam trap

The trap here is underestimating the complexity of nested JSON and attempting to parse it with basic string functions, which fails with nested structures.

Practice this question →
84
MCQhard

A data engineer is loading a large CSV file into a relational staging table. Several columns contain numeric values with thousands separators, such as '1,234.56', and a few rows contain the text 'N/A' in those columns. The target columns are defined as DECIMAL. Which approach best prepares the data for a successful load while preserving the ability to audit rejected values?

A.Strip thousands separators, convert 'N/A' to NULL, validate the remaining values as numeric, and load rejected rows into an error table.
B.Load the values as strings into VARCHAR columns, then cast them during every downstream query.
C.Replace 'N/A' with 0 and load all values directly into the DECIMAL columns.
D.Set the database session to a locale that interprets commas as decimal points and load the file unchanged.
AnswerA

Removing thousands separators produces parseable numeric strings, mapping 'N/A' to NULL respects the DECIMAL nullability, and validating before load prevents type errors. Routing rejected rows to an error table preserves auditability. This combination satisfies the goal of a clean load while retaining visibility into records that could not be converted, which is essential for data quality monitoring and reprocessing.

Why this answer

Preparing numeric text with thousands separators and non-numeric tokens requires cleaning the format, mapping non-values to NULL, validating conversion, and isolating failures. Stripping separators and converting 'N/A' to NULL allows valid rows to load as DECIMAL, while an error table preserves rejected rows for audit and reprocessing. String storage, zero substitution, and locale changes either defer errors, fabricate data, or corrupt magnitudes.

Exam trap

The trap here is treating 'N/A' as equivalent to zero or assuming locale settings can safely reinterpret thousands separators without corrupting numeric magnitude.

Practice this question →
85
MCQeasy

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

A.Values below the mean or above the mean
B.Values below Q1 - IQR or above Q3 + IQR
C.Values below Q2 - 2*IQR or above Q2 + 2*IQR
D.Values below Q1 - 1.5*IQR or above Q3 + 1.5*IQR
AnswerD

Values falling below Q1 − 1.5×IQR or above Q3 + 1.5×IQR are flagged as outliers, satisfying the stem's IQR-method constraint. The 1.5 multiplier defines Tukey's fences, the standard threshold distinguishing mild outliers from the bulk of the distribution.

Why this answer

The IQR (interquartile range) method defines outliers as values that fall below Q1 - 1.5*IQR or above Q3 + 1.5*IQR, where IQR = Q3 - Q1. This is Tukey's standard fence rule used in box-and-whisker plots. The 1.5 multiplier is the conventional threshold for 'mild' outliers, while 3.0*IQR marks 'extreme' outliers.

Exam trap

The trap here is confusing mean-based outlier detection (z-score) with quartile-based detection (IQR) — candidates who remember '1.5' but forget the Q1/Q3 base, or who substitute Q2, pick the wrong option.

How to eliminate wrong answers

Option A is wrong because the mean is not used in the IQR method — outliers are defined relative to quartiles, not the arithmetic mean, and mean-based thresholds are highly sensitive to the very outliers being detected. Option B is wrong because it omits the 1.5 multiplier; using only 1*IQR would flag far too many normal observations as outliers. Option C is wrong because it uses Q2 (the median) as the base and a 2*IQR multiplier, which does not match Tukey's standard fence definition — the correct base is Q1 and Q3 with a 1.5 multiplier.

Practice this question →
86
MCQeasy

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?

A.df['sales'].apply(sum)
B.df.pivot_table(values='sales', index='product', aggfunc='sum')
C.df.groupby('product')['sales'].sum()
D.df.merge(df, on='product')
AnswerC

groupby('product') partitions rows by category, then selecting ['sales'] and calling sum() aggregates each group into total sales per product. This satisfies the per-product aggregation requirement; pivot_table or value_counts would reshape or count rather than sum the sales column.

Why this answer

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.

Exam trap

The trap here is confusing element-wise operations (apply, map) with group-wise aggregation (groupby), leading candidates to pick apply(sum) thinking it aggregates when it actually operates per element.

How to eliminate wrong answers

Option A is wrong because df['sales'].apply(sum) applies Python's built-in sum to each element of the sales column (each scalar), which either errors or returns the same values — it does not group by product at all. Option B is wrong because pivot_table is designed for reshaping/aggregating into a pivot grid and, while it can technically aggregate, it is overkill and semantically intended for cross-tabulation, not simple per-group totals. Option D is wrong because df.merge(df, on='product') is a self-join that duplicates rows and performs no aggregation whatsoever.

Practice this question →
87
Matchingmedium

Match each database concept to its definition.

Drag a concept onto its matching description — or click a concept then click the description.

Concepts
Matches

Unique identifier for each record in a table

Field that links to primary key in another table

Structure to speed up data retrieval

Virtual table based on a query result

Process to reduce data redundancy

Why these pairings

Primary keys uniquely identify records, foreign keys link tables, indexes speed retrieval, and normalization reduces redundancy. Common confusions include swapping primary and foreign key definitions.

Practice this question →
88
MCQhard

A data engineer is extracting data from a REST API that returns JSON. The API paginates results with a 'next_page' token. The engineer needs to load all pages into a database. Which approach should the engineer use to ensure all data is acquired?

A.Use a while loop that continues to call the API with the 'next_page' token until the token is null, parsing and loading each page.
B.Send parallel requests for each page number until an empty response is received.
C.Make a single API call with a large 'limit' parameter to retrieve all records at once.
D.Use the 'next_page' token as a query parameter in a single request to retrieve all subsequent pages.
AnswerA

Pagination tokens are designed to be followed sequentially until no more pages remain. A while loop that checks for a null token ensures all pages are retrieved. This is the standard method for consuming paginated APIs and guarantees complete data extraction.

Why this answer

Following the 'next_page' token in a loop until it is null is the correct way to handle API pagination. This ensures that every page is requested and processed, resulting in a complete dataset. It also respects the API's design and any rate limits.

Exam trap

The trap here is assuming that a single large request can bypass pagination, but APIs enforce limits and pagination tokens must be followed sequentially.

Practice this question →
89
MCQeasy

In SQL, you want to retrieve all products whose names start with 'Pro'. Which WHERE clause should you use?

A.WHERE product_name LIKE '%Pro%'
B.WHERE product_name LIKE 'Pro_'
C.WHERE product_name = 'Pro'
D.WHERE product_name LIKE 'Pro%'
AnswerD

LIKE with the wildcard 'Pro%' matches any product_name beginning with the literal characters Pro, since % represents zero or more trailing characters. Equality (=) would require an exact full-string match, failing the stem's prefix requirement.

Why this answer

LIKE with pattern 'Pro%' matches strings starting with 'Pro' followed by any characters. '%Pro%' matches any string containing 'Pro', 'Pro_' matches 'Pro' plus one character, and 'Pro' is exact match.

Practice this question →
90
MCQeasy

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?

A.CONCAT()
B.UPPER()
C.LENGTH()
D.SUBSTRING()
AnswerA

CONCAT() joins two or more string values into one, directly satisfying the requirement to merge first_name and last_name into a full_name column. Unlike the concatenation operator, it handles NULL inputs by treating them as empty strings, avoiding a NULL result when either name column is missing.

Why this answer

CONCAT() joins two or more strings together.

Practice this question →
91
MCQhard

You have a hierarchical table 'Employees' with columns emp_id, emp_name, manager_id (referencing emp_id). You need to generate a full reporting chain from a given employee up to the CEO. Which SQL construct is most appropriate?

A.Recursive CTE with UNION ALL
B.Non-recursive CTE
C.Window function with PARTITION BY
D.Self-join with multiple JOINs
AnswerA

A recursive CTE references its own result set, using an anchor member for the starting employee and a recursive member joining manager_id to emp_id. UNION ALL combines each level, walking the hierarchy upward until the CEO is reached.

Why this answer

A recursive CTE with UNION ALL is the standard SQL construct for traversing hierarchical data such as an employee-manager reporting chain. The anchor member selects the starting employee, and the recursive member joins back to the Employees table on manager_id = emp_id, iterating until the CEO is reached. UNION ALL preserves all rows in the chain without deduplication overhead.

Exam trap

DA0-002 often tests whether candidates recognize that hierarchical traversal requires recursion — the trap is choosing a self-join with a fixed number of JOINs, which works only for a known depth and silently misses deeper chains.

How to eliminate wrong answers

Option B is wrong because a non-recursive CTE cannot reference itself and therefore cannot traverse an arbitrary-depth hierarchy. Option C is wrong because window functions with PARTITION BY operate on a fixed result set and cannot iteratively walk parent-child relationships. Option D is wrong because a self-join with multiple JOINs only works for a fixed, known depth (e.g., three levels) and fails for variable-depth hierarchies like a reporting chain of unknown length.

Practice this question →
92
Multi-Selecthard

A data engineer is profiling a newly acquired customer table before loading it into a warehouse. The table has a customer_id column that should be unique, a signup_date column stored as text in 'YYYY-MM-DD' format, and a country column with values such as 'US', 'USA', 'United States', and 'U.S.'. The engineer must document which data quality dimensions are violated and plan remediation. Which TWO actions best address the identified data quality issues? (Choose two.)

Select 2 answers
A.Standardize the country values to a single reference list using a mapping table or CASE expression.
B.Impute missing signup_date values with the current date to avoid nulls in the column.
C.Delete all rows where the customer_id appears more than once to enforce uniqueness.
D.Leave the country values as-is because they all refer to the same country and analysts can interpret them.
E.Convert the signup_date text column to a native date data type during the load.
AnswersA, E

The country column contains multiple representations of the same country, which violates consistency. Mapping variants like 'USA', 'United States', and 'U.S.' to one canonical code resolves the inconsistency and enables reliable grouping and joins. This directly addresses the identified quality issue through a documented, repeatable transformation rather than ad hoc fixes.

Why this answer

The profile reveals a consistency problem in the country column and a type-conformance problem in the signup_date column. Standardizing country values and converting the date text to a native date type both directly remediate identified defects. Deleting duplicate IDs, ignoring inconsistent labels, or fabricating missing dates either destroys valid data or hides problems instead of resolving them.

Exam trap

The trap here is treating every anomaly as something to remove or overwrite, when the correct response is to remediate the specific quality dimension that is actually violated.

Practice this question →
93
MCQmedium

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?

A.Correlation
B.Standard deviation
C.Mean
D.Mode
AnswerA

Correlation quantifies both the strength and direction of a linear relationship between two continuous variables, returning a value from -1 to +1. This matches the analyst's EDA goal precisely, unlike covariance, which indicates direction but is scale-dependent.

Why this answer

Correlation (typically Pearson's r for linear relationships) is the standardized measure of both the strength and direction of a linear relationship between two continuous variables, ranging from -1 to +1. A positive value indicates a positive linear association, a negative value a negative one, and the magnitude indicates strength. Standard deviation, mean, and mode are univariate descriptive statistics and say nothing about the relationship between two variables.

Exam trap

The trap here is confusing univariate descriptive statistics (mean, standard deviation, mode) with bivariate measures of association; candidates who skim may pick standard deviation because it sounds 'statistical' without noticing the question asks about the relationship between two variables.

How to eliminate wrong answers

Option B is wrong because standard deviation measures the spread/dispersion of a single variable around its mean, not the relationship between two variables. Option C is wrong because the mean is a measure of central tendency for one variable and provides no information about association. Option D is wrong because the mode is the most frequent value in a distribution — a univariate categorical/descriptive statistic with no role in measuring linear relationships.

Practice this question →
94
MCQmedium

During data acquisition, a data engineer uses a tool to extract data from a source system incrementally based on a timestamp column. Which method is being used?

A.Change data capture (CDC)
B.Snapshot extraction
C.Full extraction
D.Manual extraction
AnswerA

Change data capture extracts only rows changed since the last run, using the timestamp column as the high-water mark to identify new or modified records. This incremental pattern avoids full reloads, satisfying the requirement to extract data incrementally rather than in bulk.

Why this answer

The method being used is change data capture (CDC), specifically timestamp-based CDC. CDC is a technique used to identify and capture changes made to data in a source system since the last extraction, often by using a timestamp column (e.g., last_modified) to select only rows that have changed. This allows for incremental extraction, reducing load and improving efficiency.

Exam trap

DA0-002 often tests the confusion between CDC and full extraction; candidates must recognize that incremental extraction based on a timestamp is a form of CDC.

How to eliminate wrong answers

Option B is wrong because snapshot extraction involves taking a full copy of the data at a point in time, not incremental based on a timestamp. Option C is wrong because full extraction extracts all data every time, which is not incremental. Option D is wrong because manual extraction implies human intervention, not an automated tool-based incremental process.

Practice this question →
95
MCQeasy

A data analyst at a retail bank is preparing a daily transaction file for loading into the analytics warehouse. The source system exports dates in the format 'MM/DD/YYYY', but the target warehouse requires ISO 8601 format 'YYYY-MM-DD'. The analyst needs to transform the date column without changing the underlying date value. Which transformation should the analyst perform?

A.Apply a regular expression to reorder the month, day, and year components into YYYY-MM-DD.
B.Change the source system's regional date setting so exports use ISO 8601.
C.Use a date parsing and formatting function to interpret MM/DD/YYYY and output YYYY-MM-DD.
D.Store the date column as a string and let the warehouse interpret the format at query time.
AnswerC

Parsing the source string with the known input format and then formatting it to ISO 8601 preserves the actual date value while meeting the target schema requirement. Native date functions also validate calendar constraints and handle leap years and month lengths correctly. This is the standard, maintainable approach for changing a date representation during data preparation.

Why this answer

Converting a date representation while preserving the value is a standard data transformation task. Using a native parse-and-format function with an explicit input format ensures the calendar date is interpreted correctly and emitted as ISO 8601. Regex reordering, source-system changes, or untyped strings either risk invalid dates, affect unrelated systems, or defer correctness to query time, none of which meets the warehouse loading requirement cleanly.

Exam trap

The trap here is assuming any string manipulation that rearranges characters is equivalent to proper date conversion, when only a date-aware function validates and preserves the actual calendar value.

Practice this question →
96
MCQmedium

A healthcare analytics team is acquiring a monthly extract of patient encounter records from a partner hospital. The extract arrives as a compressed CSV with a documented layout, but the team notices that the record count has dropped by roughly 15 percent compared with the prior month and that several encounters near month-end are absent. Which acquisition control should the team apply FIRST to determine whether the issue is a delivery problem or a source-system problem?

A.Apply imputation to estimate the missing encounters so monthly reporting is not delayed.
B.Escalate to the partner hospital's IT department and request a full re-extraction without further investigation.
C.Run a reconciliation check comparing the delivered record count and date range against the agreed extract specification and the prior month's profile.
D.Immediately rewrite the transformation jobs to handle a smaller dataset and reload the warehouse.
AnswerC

A reconciliation check establishes whether the file matches expected volume and coverage before any transformation. Comparing count and date range against the specification and the previous month isolates whether the gap is in delivery or in the source. This is a low-cost, first-line control that produces evidence for the partner conversation and prevents premature changes to downstream logic.

Why this answer

When a recurring extract shows an unexpected volume or coverage change, the first control is reconciliation against the agreed specification and historical profile. That comparison determines whether the file is incomplete in transit or the source system produced fewer records. Only after the cause is known should the team remediate, whether by requesting a corrected file or adjusting downstream processing.

Acting on downstream code or imputing data before diagnosis risks compounding the error.

Exam trap

The trap here is jumping to remediation or imputation when the correct first step is a reconciliation control that establishes where the shortfall occurred.

Practice this question →
97
MCQmedium

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?

A.import csv; df = csv.read('sales.csv')
B.import pandas as pd; df = pd.read('sales.csv')
C.import numpy as np; df = np.read_csv('sales.csv')
D.import pandas as pd; df = pd.read_csv('sales.csv')
AnswerD

`pd.read_csv('sales.csv')` is pandas' dedicated CSV parser, returning a DataFrame directly from the file path. The import aliases pandas as `pd`, satisfying the stem's requirement to read `sales.csv` into a DataFrame in one line, with no extra arguments needed for a standard comma-delimited file.

Why this answer

The correct pandas idiom is pd.read_csv('sales.csv'), which returns a DataFrame. pandas is conventionally imported as pd, and read_csv is the dedicated CSV parser that handles delimiters, headers, dtypes, and encoding. This is the canonical one-liner every pandas user writes.

Exam trap

The trap is that all four options look syntactically plausible — candidates who don't actually use pandas daily may pick pd.read() or np.read_csv() because they sound reasonable, but only read_csv is the real API.

How to eliminate wrong answers

Option A is wrong because the standard library csv module has no csv.read() function — it exposes csv.reader() and csv.DictReader(), which return iterators of rows, not a DataFrame. Option B is wrong because pandas has no pd.read() method; the correct method name is read_csv (or read_table, read_excel, etc.). Option C is wrong because numpy has no np.read_csv() function — numpy provides np.loadtxt() and np.genfromtxt() for text files, and neither returns a pandas DataFrame.

Practice this question →
98
MCQhard

A company is merging two databases from different departments. In Database A, customer IDs are integers. In Database B, customer IDs are alphanumeric strings. To merge, the data analyst must reconcile these differences. Which step should be taken first?

A.Drop the ID column and use a surrogate key
B.Convert all IDs to integers using CAST
C.Perform data profiling to understand the ID formats and relationships
D.Create a mapping table based on the first character
AnswerC

Profiling first reveals each database's ID format, value patterns and relationships before any mapping or conversion. Reconciling integer versus alphanumeric IDs requires knowing actual contents and referential links, so profiling satisfies the stem's requirement to understand formats and relationships.

Why this answer

Data profiling is the essential first step before any transformation or mapping. It allows the analyst to examine the actual formats, patterns, and relationships in both ID columns (e.g., whether Database B's alphanumeric IDs contain embedded numeric sequences or consistent prefixes). Without profiling, any conversion or mapping would be based on assumptions that could lead to data loss or incorrect merges.

Exam trap

The trap here is that candidates assume immediate conversion (Option B) is the simplest solution, but the exam tests the principle that data profiling must precede any transformation to avoid irreversible data corruption.

How to eliminate wrong answers

Option A is wrong because dropping the ID column and using a surrogate key discards the existing business meaning and relationships, which may be critical for linking records across departments. Option B is wrong because converting all IDs to integers using CAST will fail on alphanumeric strings that contain non-numeric characters, causing errors or data loss. Option D is wrong because creating a mapping table based solely on the first character is arbitrary and ignores the full ID structure, leading to incorrect or incomplete mappings.

Practice this question →
99
MCQhard

A data pipeline log shows the above error. Which data transformation should be applied during acquisition?

A.Skip rows that cause errors
B.Preprocess the string to remove non-numeric characters, then convert to DECIMAL
C.Use CAST(transaction_amount AS DECIMAL(10,2)) in SQL
D.Change the target column type to VARCHAR
AnswerB

Removing non-numeric characters before casting satisfies the acquisition-stage requirement to cleanse malformed values. DECIMAL conversion then preserves precision for monetary figures, unlike FLOAT. This preprocessing step prevents the cast failure recorded in the pipeline log, ensuring the transformation occurs before data lands in the target store.

Why this answer

The error indicates that the pipeline encountered a string with non-numeric characters (e.g., '$1,234.56') when trying to load it into a DECIMAL column. Preprocessing the string to remove non-numeric characters (like currency symbols, commas) before conversion ensures the data is clean and parseable, which is a standard data transformation during acquisition to handle dirty source data.

Exam trap

The trap here is that candidates assume CAST in SQL can handle any string-to-number conversion, but CAST strictly requires a valid numeric string and will throw an error for non-numeric characters, making preprocessing essential.

How to eliminate wrong answers

Option A is wrong because skipping rows that cause errors would result in data loss and is not a proper transformation; it ignores the root cause of the dirty data. Option C is wrong because using CAST(transaction_amount AS DECIMAL(10,2)) in SQL would still fail if the string contains non-numeric characters, as CAST does not automatically strip them. Option D is wrong because changing the target column type to VARCHAR would avoid the conversion error but defeats the purpose of storing numeric data for calculations, leading to data integrity and performance issues.

Practice this question →
100
MCQmedium

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?

A.SELECT COUNT(email) FROM table WHERE email = NULL
B.SELECT SUM(CASE WHEN email IS NULL THEN 1 END) FROM table
C.SELECT COUNT(ISNULL(email)) FROM table
D.SELECT COUNT(*) FROM table WHERE email IS NULL
AnswerD

`COUNT(*)` tallies every row returned by the `WHERE` clause, and `IS NULL` is the only predicate that reliably tests for the absence of a value, since `email = NULL` evaluates to UNKNOWN and matches nothing. This directly satisfies the stem's requirement to count rows whose email column holds NULL.

Why this answer

SELECT COUNT(*) FROM table WHERE email IS NULL correctly counts rows where email is NULL. COUNT(*) counts all rows in the filtered result set, and IS NULL is the only valid comparison operator for NULL in SQL. This is the standard, portable way to count NULLs.

Exam trap

The trap is the seductive '= NULL' syntax — it looks natural to beginners but is always wrong in SQL, and the exam expects you to know that NULL requires IS NULL.

How to eliminate wrong answers

Option A is wrong because 'email = NULL' never evaluates to TRUE in SQL — NULL comparisons use three-valued logic, so '= NULL' returns UNKNOWN and matches zero rows; also COUNT(email) ignores NULLs anyway. Option B is wrong because SUM(CASE WHEN email IS NULL THEN 1 END) returns NULL when no rows match (SUM of all NULLs is NULL), and it lacks an ELSE 0 clause, so it fails on empty result sets — it's also more verbose than needed. Option C is wrong because ISNULL() is a function that returns a value (e.g., replaces NULL with a substitute), not a boolean predicate; COUNT(ISNULL(email)) counts non-NULL results of the function, which is not the same as counting NULL emails.

Practice this question →
101
MCQmedium

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?

A.It orders departments by average salary descending.
B.It filters departments where the average salary exceeds $60,000.
C.It returns only the department with the maximum average salary.
D.It filters individual employee rows with salary > 60000 before grouping.
AnswerB

HAVING filters groups after aggregation, so it applies the condition to each department's computed AVG(salary), retaining only those exceeding $60,000. Unlike WHERE, which filters individual rows before grouping, HAVING operates on aggregate results — exactly the constraint the stem requires when filtering on an aggregate function.

Why this answer

HAVING filters groups after aggregation, so HAVING AVG(salary) > 60000 removes entire department groups whose computed average salary does not exceed $60,000. It operates on the aggregated result, not on individual rows, which is why it can reference aggregate functions like AVG.

Exam trap

The trap is confusing WHERE (row-level, pre-aggregation) with HAVING (group-level, post-aggregation); candidates often pick the option that filters individual rows.

How to eliminate wrong answers

Option A is wrong because ordering is done with ORDER BY, not HAVING; HAVING never sorts results. Option C is wrong because HAVING applies a threshold filter, not a top-1 selection — it returns all qualifying departments, and picking the max would require ORDER BY ... LIMIT 1 or a subquery.

Option D is wrong because filtering individual employee rows before grouping is the job of WHERE, which runs before GROUP BY; HAVING runs after aggregation and cannot reference non-aggregated row-level predicates in the same way.

Practice this question →
102
Multi-Selectmedium

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.)

Select 3 answers
A.REPLACE
B.UPPER
C.LENGTH
D.TRIM
E.CONCAT
AnswersA, B, D

REPLACE substitutes specified substrings within a string, letting analysts strip or correct unwanted characters during standardisation. This satisfies the text-cleaning requirement by enabling consistent transformation of messy values, and is a standard SQL string function.

Why this answer

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.

Practice this question →
103
MCQhard

A financial analyst is integrating data from multiple stock exchanges. One exchange provides trade timestamps in UTC, another in Eastern Time. The analyst needs accurate time synchronization for time-series analysis. What is the best approach?

A.Keep original timezones and add a timezone offset column
B.Use the local time of the analyst's location
C.Convert all timestamps to a single timezone (e.g., UTC) during ETL
D.Ignore timezone differences if analysis is intraday
AnswerC

Normalising every timestamp to a single timezone such as UTC during ETL removes the offset ambiguity between exchanges, so time-series joins and orderings align correctly. Converting at load time, not query time, satisfies the accurate time synchronisation constraint for analysis.

Why this answer

Converting all timestamps to a single timezone (e.g., UTC) during ETL ensures that all time-series data is directly comparable and sortable without ambiguity. UTC is a global standard that avoids daylight saving time (DST) shifts, making it ideal for financial analysis where precise ordering of trades across exchanges is critical. This approach eliminates the need for runtime timezone conversions, reducing errors and improving query performance.

Exam trap

The trap here is that candidates might think adding a timezone offset column preserves information and is sufficient, but it still requires runtime conversion and can be error-prone with DST; the exam expects recognition that normalization to UTC during ETL is the best practice for time-series analysis.

How to eliminate wrong answers

Option A is wrong because keeping original timezones with an offset column still requires runtime calculations to compare timestamps, and offsets can change with DST, leading to inconsistencies. Option B is wrong because using the analyst's local time introduces an arbitrary reference that varies by location and is not standardized, making cross-exchange analysis unreliable. Option D is wrong because ignoring timezone differences even for intraday analysis can cause misalignment of trades that occur near market open/close across different timezones, leading to incorrect sequence and correlation analysis.

Practice this question →
104
Multi-Selecthard

A data analyst is performing EDA on a dataset with numerical features. Which methods are appropriate for identifying outliers? (Select TWO).

Select 2 answers
A.Mean imputation
B.Pearson correlation coefficient
C.Z-score method
D.Standard deviation alone
E.Interquartile range (IQR) method
AnswersC, E

The z-score method flags values whose standardised distance from the mean exceeds a threshold, typically ±3. It suits numerical features and satisfies the outlier-detection requirement by quantifying deviation in standard deviation units, assuming an approximately normal distribution.

Why this answer

The Z-score method (C) is correct because it quantifies how many standard deviations a value lies from the mean, so observations with |z| above a threshold such as 3 (or 2.5) are flagged as outliers in numerical features. The Interquartile range (IQR) method (E) is also correct because it defines outliers as values below Q1 − 1.5×IQR or above Q3 + 1.5×IQR, making it a robust, distribution-agnostic detection technique. Mean imputation (A) is not an outlier-detection method at all; it is a missing-value handling technique that replaces NaNs with the column mean.

The Pearson correlation coefficient (B) measures the linear relationship between two variables, not the extremeness of individual values, so it cannot identify outliers. Standard deviation alone (D) only describes data spread and, without a rule like the Z-score, does not flag specific observations as outliers.

Exam trap

DA0-002 often tests the confusion between descriptive statistics (mean, standard deviation, correlation) and actual outlier detection rules (Z-score, IQR), tempting candidates to select standard deviation alone as if it were a detection method.

Practice this question →
105
MCQmedium

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?

A.NULL
B.0
C.The number of rows with NULL city
D.The number of non-NULL cities
AnswerC

COUNT(*) counts every row in the group regardless of NULL values, so a group where city is NULL returns the total number of rows having a NULL city. COUNT(city) would instead return zero for that group.

Why this answer

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.

Exam trap

DA0-002 often tests the difference between COUNT(*) and COUNT(column) with NULLs, tricking candidates into thinking COUNT(*) returns NULL or 0 when the grouped column is NULL.

How to eliminate wrong answers

Option A is wrong because COUNT(*) never returns NULL; it returns an integer row count even for all-NULL groups. Option B is wrong because 0 is what COUNT(city) would return for a NULL group, not COUNT(*), which counts the rows themselves. Option D is wrong because the number of non-NULL cities describes what COUNT(city) would produce across groups, not the row count for the NULL group.

Practice this question →
106
MCQeasy

A retail company's data analytics team needs to acquire point-of-sale (POS) transaction data from 200 stores daily. Each store sends a CSV file via email at the end of the day. The files often arrive late, have inconsistent column names (e.g., "StoreID", "Store_ID", "store_id"), and occasionally contain corrupted rows. The team manually processes these files, leading to frequent errors and delays. The company wants to automate the acquisition process to ensure data is available by 9 AM the next business day with high quality. Which approach best addresses these issues?

A.Create a script to automatically download email attachments, validate and standardize columns, and flag corrupted rows for review
B.Hire a data entry contractor to manually check and re-enter data
C.Ask stores to use a standardized web form to enter data directly into a cloud database
D.Implement a VPN so stores can connect to the central database and write transactions in real time
AnswerA

Automated download, column standardisation and corrupted-row flagging directly address the three stated problems: late manual handling, inconsistent column names such as StoreID versus store_id, and corrupted rows. Scripted validation enforces quality and delivers data by 9 AM without manual intervention.

Why this answer

It directly addresses all three issues: automating the retrieval of email attachments (handling late arrivals), standardizing inconsistent column names via a script (e.g., mapping 'StoreID', 'Store_ID', 'store_id' to a canonical schema), and implementing validation logic to flag corrupted rows for manual review. This approach ensures data is processed reliably by 9 AM without manual intervention, meeting the automation and quality requirements.

Exam trap

The trap here is that candidates may choose Option C or D because they seem more 'modern' or 'direct,' but they fail to recognize that the question specifically requires handling existing CSV files and late arrivals, which a script-based ETL approach (Option A) directly solves without requiring stores to change their behavior or infrastructure.

How to eliminate wrong answers

Option B is wrong because hiring a data entry contractor introduces manual processing, which is the root cause of delays and errors, and does not automate the acquisition process. Option C is wrong because asking stores to use a standardized web form shifts the burden to 200 stores, which is impractical to enforce uniformly and does not address the existing CSV files or late arrivals; it also introduces new integration complexity without solving the immediate data pipeline issue. Option D is wrong because implementing a VPN for real-time writes requires significant network infrastructure changes, assumes stores have stable high-speed internet, and does not handle the existing CSV files or the need for batch processing by 9 AM; real-time writes also increase the risk of data corruption without validation.

Practice this question →
107
MCQhard

An organization is acquiring data from an external vendor. The vendor provides a flat file with inconsistent delimiters and missing values. Which step should be performed first in data acquisition?

A.Data integration
B.Data profiling
C.Data transformation
D.Data cleansing
AnswerB

Profiling examines the vendor file's actual structure, delimiter patterns, null rates and value distributions before any transformation. This reveals the specific inconsistencies and missing values the stem describes, so the analyst can design correct parsing and cleansing logic rather than guessing at the file's true shape.

Why this answer

Data profiling is the first step because it examines the data to understand its structure, quality, and issues (like inconsistent delimiters and missing values) before any further processing. Option A (Data integration) is wrong because integration combines data from multiple sources and should follow profiling. Option C (Data transformation) is wrong because transforming data requires first understanding its current state through profiling.

Option D (Data cleansing) is wrong because cleansing is performed after profiling identifies the issues.

Practice this question →
108
Multi-Selecteasy

A data analyst is validating a dataset acquired from an external source. Which TWO actions are appropriate for data quality assessment?

Select 2 answers
A.Check for missing values in critical fields
B.Delete any rows with null values without review
C.Validate data format against expected schema
D.Immediately load all data into production
E.Transform data to match target system without verification
AnswersA, C

Checking for missing values in critical fields directly satisfies the requirement to assess completeness, one of the core data quality dimensions. Nulls in mandatory columns, such as customer identifiers or transaction dates, invalidate downstream aggregation and reporting, so quantifying them during validation exposes gaps before analysis begins.

Why this answer

Option A is correct because checking for missing values in critical fields is a core data quality assessment step that identifies nulls or gaps in essential attributes, which can skew analysis or break downstream processing. Option C is correct because validating data format against the expected schema confirms that each column's data type, structure, and constraints (e.g., date formats, numeric ranges, string lengths) match requirements, catching inconsistencies from the external source. Option B is not appropriate because deleting rows with null values without review can silently discard valid records and hide data quality issues rather than assessing them.

Option D is wrong because loading unvalidated data directly into production risks propagating errors and corrupting downstream systems. Option E is wrong because transforming data to match the target system without verification skips the assessment step and can mask or introduce quality problems.

Exam trap

The trap here is that candidates may confuse data cleaning (which includes deletion or transformation) with data quality assessment, which is the diagnostic step that should occur before any irreversible actions like deletion or production loading.

Practice this question →
109
MCQeasy

A marketing company is building a customer segmentation model. The data team has access to two sources: a CRM database with customer demographics and purchase history, and a third-party data provider that offers social media activity scores. The CRM data is updated daily, while the third-party data is refreshed weekly on Sundays. The analyst needs to create a unified dataset for the model training scheduled for Wednesday morning. The analyst runs a SQL query to join the two tables on CustomerID, but the resulting dataset has far fewer rows than expected. Upon investigation, the analyst finds that many customers in the CRM do not have matching records in the third-party data. Additionally, some customers in the third-party data have multiple entries due to unresolved duplicates. The analyst must produce the most complete dataset possible while maintaining data quality. Which course of action should the analyst take?

A.First deduplicate the third-party data by keeping the most recent record per CustomerID, then perform a LEFT JOIN from CRM to the deduplicated third-party data.
B.Perform an INNER JOIN on CustomerID and then remove duplicates from the result.
C.Use only the third-party data because it provides the social media scores needed for segmentation.
D.Perform a LEFT JOIN from the third-party data to CRM, then aggregate duplicates by averaging scores.
AnswerA

Deduplicating the third-party table to one row per CustomerID prevents the join from multiplying CRM records, and a LEFT JOIN preserves every CRM customer, including those with no social media match, maximising completeness while maintaining quality.

Why this answer

It first resolves the duplicate issue in the third-party data by keeping the most recent record per CustomerID, ensuring each customer has a single, current social media score. Then, a LEFT JOIN from CRM to the deduplicated third-party data preserves all CRM customers, maximizing completeness while maintaining data quality. This approach aligns with the goal of producing the most complete dataset for model training, as the CRM is the primary source with daily updates.

Exam trap

The trap here is that candidates may choose an INNER JOIN (Option B) thinking it ensures data quality by only including matched records, but they overlook the requirement for completeness, which necessitates preserving all CRM customers even without third-party matches.

How to eliminate wrong answers

Option B is wrong because an INNER JOIN would exclude CRM customers without matching third-party records, reducing dataset completeness, and removing duplicates after the join does not address the root cause of multiple entries in the third-party data. Option C is wrong because using only third-party data discards the CRM's daily-updated demographics and purchase history, which are essential for segmentation and would result in an incomplete dataset. Option D is wrong because a LEFT JOIN from third-party data to CRM would prioritize third-party customers, potentially losing CRM-only customers, and averaging scores across duplicates introduces data quality issues by conflating multiple records into a single value without considering recency or validity.

Practice this question →
110
MCQmedium

What is the primary purpose of the HAVING clause in the query shown?

A.Sort the results in descending order
B.Join two tables
C.Filter rows before grouping
D.Filter groups after aggregation
AnswerD

HAVING filters rows after GROUP BY has aggregated them, so it can test aggregate results such as SUM or COUNT. WHERE cannot do this because it evaluates individual rows before grouping occurs, making HAVING the only clause that satisfies the post-aggregation filtering requirement.

Why this answer

The HAVING clause is used to filter groups after the GROUP BY clause has aggregated the data. In SQL, WHERE filters individual rows before aggregation, while HAVING applies conditions to the results of aggregate functions like SUM, COUNT, or AVG. Option D is correct because the query uses HAVING to restrict which grouped results appear in the final output.

Exam trap

The trap here is confusing WHERE and HAVING: candidates often pick 'Filter rows before grouping' because they think all filtering happens before aggregation, but HAVING specifically filters groups after aggregation, not individual rows.

How to eliminate wrong answers

Option A is wrong because sorting is performed by the ORDER BY clause, not HAVING; HAVING has no sorting functionality. Option B is wrong because joining tables is done with JOIN (or FROM with comma-separated tables) and ON conditions, not with HAVING. Option C is wrong because filtering rows before grouping is the role of the WHERE clause; HAVING operates after aggregation, on groups, not on individual rows.

Practice this question →
111
MCQeasy

A data analyst needs to collect customer sentiment data from social media platforms. Which data acquisition method is most appropriate?

A.Conduct a survey
B.Organize focus groups
C.Use web scraping
D.Query the internal CRM
AnswerC

Web scraping extracts publicly posted comments and posts from social platforms at scale, which is the only listed method that directly captures sentiment text. This satisfies the stem's constraint of collecting sentiment data from social media rather than structured internal sources.

Why this answer

Web scraping is the most appropriate method because it allows the data analyst to programmatically extract unstructured customer sentiment data (e.g., posts, comments, reviews) directly from social media platforms using HTTP requests and HTML parsing. Unlike surveys or focus groups, scraping can collect large volumes of real-time, publicly available data without relying on self-reported or curated responses.

Exam trap

CompTIA often tests the distinction between primary data collection (surveys, focus groups) and secondary data acquisition (web scraping, APIs), where candidates mistakenly choose a primary method for a task that requires large-scale, unsolicited external data.

How to eliminate wrong answers

Option A is wrong because conducting a survey collects self-reported, structured data from a controlled sample, which is not suitable for capturing organic, unsolicited sentiment from social media platforms in real time. Option B is wrong because organizing focus groups gathers qualitative feedback from a small, moderated group, which lacks the scale and authenticity of public social media sentiment and introduces moderator bias. Option D is wrong because querying the internal CRM retrieves structured customer data from internal systems (e.g., purchase history, support tickets), not the unstructured, external social media content needed for sentiment analysis.

Practice this question →
112
Multi-Selectmedium

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

Select 2 answers
A.Check that every order has a non-null order_id
B.Check that no customer is deleted while having orders
C.Check that every customer_id in orders exists in customers
D.Check that customer names are unique
E.Check that order amounts are positive
AnswersB, C

Referential integrity requires that a parent customer row cannot be removed while dependent order rows still reference it. Verifying no customer is deleted while orders exist confirms the foreign key constraint is enforced, preventing orphaned order records.

Why this answer

Referential integrity ensures that foreign key values in a child table always match a primary key value in the parent table, so option C is correct: verifying that every customer_id in orders exists in customers confirms each order references a valid customer and detects orphaned rows. Option B is also correct because preventing deletion of a customer who still has orders (or enforcing ON DELETE RESTRICT/NO ACTION or cascading appropriately) preserves the parent-child relationship and avoids orphaned orders. Option A is wrong because a non-null order_id is a primary key/entity integrity check, not referential integrity.

Option D is wrong because uniqueness of customer names is a data-quality/business rule unrelated to foreign key relationships. Option E is wrong because positive order amounts are a domain or business-rule validation, not a referential integrity check.

Exam trap

The trap is conflating referential integrity with other constraint types — candidates often pick NOT NULL or uniqueness checks because they 'sound like' data quality checks, but referential integrity is strictly about foreign-key relationships between tables.

Practice this question →
113
Multi-Selecteasy

Which TWO are common methods for acquiring internal data? (Choose two.)

Select 2 answers
A.Social media APIs
B.Transaction logs
C.Government databases
D.ERP systems
E.Web scraping
AnswersB, D

Transaction logs capture every committed change within internal systems, providing a granular, timestamped record of operational activity that satisfies the requirement for acquiring internal data. Unlike external sources such as purchased datasets, they originate entirely within the organisation's own infrastructure, making them a canonical internal acquisition method.

Why this answer

Transaction logs (B) are a common internal data source because they are generated by an organization's own systems, capturing events such as purchases, clicks, or system activity within the company's operational environment. ERP systems (D) are also a core internal data source, since they store and manage enterprise operational data like finance, inventory, HR, and supply chain records generated inside the organization. By contrast, social media APIs (A), government databases (C), and web scraping (E) are external data acquisition methods, as they pull data from sources outside the organization's direct control.

Exam trap

The trap here is that candidates may confuse 'internal data' with 'publicly available data' or 'data from third-party sources,' leading them to select social media APIs or government databases, which are external, not internal.

Practice this question →
114
Multi-Selectmedium

A data analyst needs to perform a stratified random sample of a customer database. Which TWO steps are essential for this sampling method? (Select two.)

Select 2 answers
A.Use simple random sampling on the whole population
B.Randomly select entire clusters of customers
C.Randomly select a proportional number from each stratum
D.Divide the population into homogeneous subgroups (strata)
E.Select every nth customer from a list
AnswersC, D

After dividing the population into strata, drawing a random sample from each stratum in proportion to its size preserves the population's composition. This proportional random selection is what makes the sample stratified rather than a simple random sample.

Why this answer

Option D is correct because stratified random sampling begins by partitioning the population into homogeneous subgroups called strata, typically based on a shared characteristic such as age, region, or customer tier, so that each subgroup is internally similar. Option C is correct because, after the strata are formed, the analyst must draw a random sample from each stratum, usually in a number proportional to that stratum's size in the population, ensuring the sample reflects the population's structure. Option A is incorrect because simple random sampling on the whole population ignores the strata and is a different sampling method.

Option B is incorrect because randomly selecting entire clusters describes cluster sampling, not stratified sampling. Option E is incorrect because selecting every nth customer is systematic sampling, which does not require dividing the population into strata.

Exam trap

The trap is confusing stratified sampling with cluster or systematic sampling — candidates see 'random selection' in multiple options and pick the wrong method because they miss that stratification requires both dividing into strata AND proportional selection within each.

Practice this question →
115
MCQmedium

A data analyst is tasked with combining customer data from a CRM system and a billing system. The CRM uses a GUID for customer ID, while billing uses an integer. Which approach should the analyst use to ensure a reliable merge?

A.Standardize the customer ID format and use it as the join key.
B.Use the customer name as the join key.
C.Merge using a cross-join and then filter manually.
D.Perform a fuzzy match on the customer address.
AnswerA

Standardising both identifiers to a single string format lets the GUID and integer values match exactly, satisfying the stem's requirement for a reliable merge across the CRM and billing systems. Without this alignment, type mismatches cause failed or partial joins, since a GUID can never equal an integer directly.

Why this answer

Standardizing the customer ID format (e.g., converting the billing integer to a GUID or mapping both to a common string key) ensures a consistent join key across heterogeneous systems. This eliminates type mismatch errors and guarantees that each customer record can be matched reliably, as GUIDs are globally unique and integers are typically sequential, so direct comparison would fail without transformation.

Exam trap

The trap here is that candidates may assume customer name or address are sufficient join keys due to their human readability, underestimating the importance of unique, system-agnostic identifiers for reliable data merging.

How to eliminate wrong answers

Option B is wrong because customer names are not guaranteed to be unique (e.g., multiple customers named 'John Smith') and may have formatting inconsistencies (e.g., case, spaces), leading to incorrect or missed matches. Option C is wrong because a cross-join produces a Cartesian product of all rows, which is computationally expensive and requires manual filtering that is error-prone and does not leverage any reliable key for accurate merging. Option D is wrong because fuzzy matching on addresses is imprecise and computationally intensive; addresses can have variations (e.g., 'St.' vs 'Street') and may not uniquely identify a customer (e.g., multiple customers at the same address), making it unreliable for a deterministic merge.

Practice this question →
116
MCQmedium

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?

A.COMBINE(first_name, last_name)
B.CONCAT(first_name, ' ', last_name)
C.JOIN(first_name, last_name)
D.first_name + ' ' + last_name
AnswerB

CONCAT accepts multiple arguments and returns their concatenation, so it joins first_name, a literal space and last_name into one string within the SELECT clause. The space must be supplied explicitly as a separate argument, since CONCAT does not insert separators between values.

Why this answer

CONCAT(first_name, ' ', last_name) is the standard SQL function for joining strings, and it correctly inserts a literal space between the two columns. CONCAT accepts multiple arguments and returns a single concatenated string, which can be aliased as full_name in the SELECT clause.

Exam trap

The trap is the '+' operator, which looks intuitive for concatenation but is dialect-specific and often performs numeric addition instead — the exam expects the portable CONCAT() answer.

How to eliminate wrong answers

Option A is wrong because COMBINE() is not a standard SQL function — no major RDBMS implements it for string concatenation. Option C is wrong because JOIN() is not a string function; JOIN is a relational operator for combining tables, not columns. Option D is wrong because the '+' operator for string concatenation is not portable — it works in SQL Server and some dialects but fails or performs numeric addition in others (e.g., PostgreSQL requires ||, and MySQL treats + as numeric addition).

Practice this question →
117
Multi-Selectmedium

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

Select 2 answers
A.Create a sampling plan
B.Build a predictive regression model
C.Deploy the model to production
D.Identify outliers using the IQR method
E.Calculate correlation between variables
AnswersD, E

The IQR method flags values falling below Q1 minus 1.5×IQR or above Q3 plus 1.5×IQR, exposing extreme observations. This satisfies the EDA requirement because outlier detection is a core exploratory step, revealing data quality issues and distribution shape before modelling begins.

Why this answer

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.

Practice this question →
118
MCQmedium

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?

A.Pattern analysis
B.Cardinality analysis
C.Referential integrity check
D.Data type verification
AnswerA

Pattern analysis validates values against an expected format such as a regular expression for email addresses, flagging entries missing the domain extension. Range, uniqueness or completeness checks would not detect a malformed structure within an otherwise populated field.

Why this answer

Pattern analysis examines the format and structure of values against an expected pattern (e.g., a regex for valid emails), making it the right technique to detect values like 'user@example' that violate the expected email format. It surfaces format inconsistencies, not just missing or duplicate values.

Exam trap

The trap is confusing 'data type' with 'data format' — candidates see a string column and assume type verification suffices, but format violations require pattern analysis, not type checks.

How to eliminate wrong answers

Option B is wrong because cardinality analysis measures the number of distinct values in a column — it would tell you there are many unique emails but would not flag that a specific value is malformed. Option C is wrong because referential integrity checks verify that foreign key values exist in a parent table; email format has nothing to do with cross-table relationships. Option D is wrong because data type verification only confirms values are stored as strings (or the expected type) — 'user@example' is a valid string, so type checking passes even though the format is wrong.

Practice this question →
119
MCQhard

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?

A.ROW_NUMBER()
B.DENSE_RANK()
C.NTILE()
D.RANK()
AnswerB

DENSE_RANK() assigns consecutive ranks without gaps when ties occur, directly satisfying the requirement to avoid gaps while ranking products by total sales within each category. Unlike ROW_NUMBER(), which gives arbitrary distinct values to tied rows, DENSE_RANK() preserves equal ranking for ties and continues sequentially, matching the dense rank constraint.

Why this answer

DENSE_RANK() assigns ranks without gaps when ties occur — if two products tie for rank 1, the next product gets rank 2, not 3. This matches the requirement to 'avoid gaps' while still assigning equal ranks to ties. It is used with OVER (PARTITION BY category ORDER BY total_sales DESC).

Exam trap

The trap is the subtle difference between RANK() and DENSE_RANK() — candidates who remember 'RANK' but forget the gap behavior pick RANK() and fail the 'avoiding gaps' requirement.

How to eliminate wrong answers

Option A is wrong because ROW_NUMBER() assigns a unique sequential number to every row regardless of ties, so tied products get different numbers — it does not produce true ranks. Option C is wrong because NTILE(n) divides rows into n roughly equal buckets, which is for percentile-style grouping, not ranking by value. Option D is wrong because RANK() leaves gaps after ties — if two products tie at rank 1, the next gets rank 3, which violates the 'avoiding gaps' requirement.

Practice this question →
120
MCQmedium

A retail analytics team loads a nightly CSV export into their warehouse. During validation, the analyst notices that the 'order_date' column, defined as DATE in the target schema, contains values like '2023-13-45' and 'N/A' in several rows. The ETL job currently fails silently on these rows. Which data acquisition and preparation action BEST addresses the root cause while preserving as much data as possible?

A.Change the target column type from DATE to VARCHAR so all incoming values load without error.
B.Reject the entire nightly file and request a corrected export from the source system.
C.Impute today's date for any row where the order_date value cannot be parsed.
D.Apply validation rules that flag invalid dates for quarantine while loading conforming rows, and log rejected records.
AnswerD

This preserves valid records, isolates the malformed dates into a quarantine set for review, and creates an audit trail that makes the previously silent failures visible. It directly addresses the root cause by enforcing type and range validation at ingestion instead of letting bad values pass through. The conforming rows still load, so downstream reporting is not blocked while the source issue is investigated.

Why this answer

Validating at ingestion, quarantining malformed dates, and logging rejects keeps the pipeline resilient while surfacing the true defect. It loads every trustworthy row, gives the team evidence to fix the upstream export, and stops the silent-failure pattern. Changing types or imputing values merely relocates or amplifies the error.

Exam trap

The trap here is assuming that making the load succeed — by widening the column type or imputing values — is the same as fixing the data quality problem.

Practice this question →
121
MCQmedium

A data analyst at a healthcare provider is reconciling patient records from two source systems. System A stores dates in 'MM/DD/YYYY' format, while System B stores dates in 'DD/MM/YYYY' format. During integration, the analyst notices that some records from System B have been incorrectly parsed, resulting in invalid dates. Which data preparation technique should the analyst apply to ensure consistent date interpretation?

A.Apply a regular expression to extract the day, month, and year components, then reassemble them in a standard 'YYYY-MM-DD' format.
B.Use a CAST function to convert the string dates to a DATE data type, relying on the database's default date format.
C.Convert all dates to Unix timestamps using a built-in function that automatically detects the format.
D.Sort the dates as strings and then manually correct any that appear out of order.
AnswerA

This approach explicitly parses each component based on known source formats and reconstructs a standardized date, avoiding ambiguity. It works because the analyst knows the exact format of each source system, so extracting and reassembling ensures correct interpretation regardless of locale settings.

Why this answer

The core issue is ambiguous date formats across sources. Explicitly parsing and reconstructing dates based on known source formats eliminates ambiguity. This method ensures each component is correctly identified before standardization, preventing misinterpretation that occurs when relying on default settings or automated detection.

Exam trap

The trap here is assuming that a CAST or conversion function will automatically handle ambiguous date formats correctly.

Practice this question →
122
MCQmedium

A data analyst needs to combine sales data from multiple regional databases with different schemas. Which process is best?

A.Data federation
B.ETL (Extract, Transform, Load)
C.Data replication
D.Data virtualization
AnswerB

ETL transforms data before loading, so each regional database's differing schema is reconciled in a staging area first. This directly satisfies the stem's constraint of combining sources with mismatched schemas, producing one consistent target structure. ELT would instead load raw, inconsistent schemas and defer transformation, complicating cross-regional joins.

Why this answer

ETL is correct because it extracts data from each source, transforms it to reconcile differing schemas (column names, types, keys, units), and loads it into a unified target. Schema heterogeneity across regional databases is exactly the transformation problem ETL is designed to solve. The transformed, conformed data can then be queried consistently.

Exam trap

The trap is confusing federation/virtualization (query-in-place, no persistence) with ETL (transform-and-persist), causing candidates to pick a lighter-weight option that cannot reconcile schemas.

How to eliminate wrong answers

Option A is wrong because data federation queries sources on demand and does not physically reconcile or persist a unified schema — it leaves schema differences to be handled at query time and is poor for heavy transformation. Option C is wrong because replication copies data verbatim between systems and does not transform or harmonize differing schemas. Option D is wrong because data virtualization presents a logical view without materializing transformed data, so it does not resolve persistent schema conflicts for analytics workloads.

Practice this question →
123
MCQmedium

A company wants to collect real-time clickstream data from its website. Which acquisition method is most suitable?

A.Streaming API
B.Web scraping
C.Batch processing nightly
D.Manual entry
AnswerA

A streaming API ingests events continuously as they occur, satisfying the real-time clickstream requirement. Unlike batch extraction, which introduces latency by collecting data at scheduled intervals, streaming delivers each click immediately for processing. This makes it the suitable acquisition method when low-latency, continuous event capture is the constraint.

Why this answer

A streaming API is the most suitable method for collecting real-time clickstream data because it enables continuous, low-latency ingestion of events as they occur. Unlike batch or manual methods, a streaming API (e.g., using WebSockets or HTTP/2 Server-Sent Events) pushes each click event immediately to the data pipeline, satisfying the real-time requirement.

Exam trap

CompTIA often tests the distinction between 'real-time' and 'near-real-time' or 'batch' methods, and the trap here is that candidates may confuse web scraping (which can be automated frequently) with true streaming, not realizing that scraping is still a pull-based, scheduled operation that cannot match the push-based immediacy of a streaming API.

How to eliminate wrong answers

Option B (Web scraping) is wrong because it is a pull-based technique that typically retrieves static HTML pages at intervals, not real-time event streams, and is inefficient for high-frequency click data. Option C (Batch processing nightly) is wrong because it introduces a delay of up to 24 hours, failing the real-time requirement. Option D (Manual entry) is wrong because it is error-prone, non-scalable, and cannot capture high-velocity clickstream data in real time.

Practice this question →
124
MCQhard

A data analyst is preparing a dataset for a machine learning model. The dataset contains a categorical column 'color' with values 'red', 'green', 'blue', and 'yellow'. The analyst needs to transform this column into a numerical format suitable for the model. Which technique should be used?

A.One-hot encoding
B.Binary encoding
C.Hashing
D.Label encoding
AnswerA

One-hot encoding creates binary columns for each category, representing the presence of each color. This avoids implying any ordinal relationship, making it ideal for nominal categorical data like colors. The model can then treat each color as a separate feature without assuming order, which is essential for accurate learning.

Why this answer

One-hot encoding is the correct technique because it creates separate binary features for each color, eliminating any implied order. This is crucial for nominal data where categories have no inherent ranking. Other encoding methods like label encoding introduce ordinality, which can degrade model performance by suggesting false relationships.

Exam trap

The trap here is choosing label encoding for its simplicity, but it incorrectly imposes an order on nominal categories, which can mislead the model.

Practice this question →
125
MCQmedium

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

A.Simple random sampling
B.Cluster sampling
C.Systematic sampling
D.Stratified sampling
AnswerD

Stratified sampling divides the population into distinct regions (strata) and draws samples from each in proportion to its size, directly satisfying the requirement for proportional regional representation. Unlike simple random sampling, it guarantees every region appears at its correct weight, eliminating the under-representation that random chance can produce.

Why this answer

Stratified sampling divides the population into distinct subgroups (strata) based on a shared characteristic — here, region — and then draws a proportional random sample from each stratum. This guarantees that each region is represented in the sample in proportion to its size in the population, which is exactly what the analyst requires. Simple random sampling could, by chance, under- or over-represent certain regions.

Exam trap

DA0-002 often tests the distinction between stratified sampling (proportional representation of known subgroups) and cluster sampling (sampling whole naturally occurring groups), which candidates frequently confuse because both involve dividing the population.

How to eliminate wrong answers

Option A is wrong because simple random sampling selects individuals from the entire population without regard to region, so small regions may be underrepresented or missed entirely by chance. Option B is wrong because cluster sampling divides the population into clusters and samples entire clusters, which reduces cost but does not guarantee proportional representation of each region. Option C is wrong because systematic sampling picks every kth element from a list, which can introduce periodicity bias and does not ensure proportional regional representation.

Practice this question →
126
MCQmedium

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?

A.TRIM
B.LTRIM
C.REPLACE
D.SUBSTRING
AnswerA

TRIM removes leading and trailing whitespace characters from a string, directly eliminating the leading spaces found in the email column cells. Other string functions such as REPLACE or LTRIM target different patterns or only one side, whereas TRIM satisfies the exact cleaning requirement stated.

Why this answer

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.

Exam trap

The trap is selecting LTRIM because the question mentions 'leading spaces,' but TRIM is the more complete and standard function for removing spaces from both ends, and exams often expect the general-purpose function.

How to eliminate wrong answers

Option B is wrong because LTRIM only removes leading spaces, not trailing spaces; while it would fix the leading spaces, TRIM is more comprehensive and is the standard answer for removing spaces from both ends. Option C is wrong because REPLACE substitutes all occurrences of a specified substring, which could remove internal spaces in emails and is not targeted at leading spaces. Option D is wrong because SUBSTRING extracts a portion of a string based on position and length; it does not remove spaces.

Practice this question →
127
Multi-Selecthard

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

Select 3 answers
A.Sort the result set by hire_date
B.Filter groups after aggregation using HAVING
C.Filter rows where manager_id is NULL using IS NULL
D.Filter rows where the name starts with 'J' using LIKE
E.Filter rows where salary is between 50,000 and 70,000 using BETWEEN
AnswersC, D, E

IS NULL tests for the absence of a value, correctly filtering rows where manager_id holds no data. Equality operators cannot match NULL because SQL treats it as unknown, so IS NULL is the only valid predicate for this filter.

Why this answer

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.

Exam trap

DA0-002 often tests the WHERE vs HAVING distinction — candidates incorrectly use WHERE for aggregate filtering or confuse ORDER BY (sorting) with WHERE (filtering).

Practice this question →
128
MCQmedium

A data analyst at a retail chain is importing a CSV file into a database. The file contains a 'transaction_date' column with values like '2023-13-01' and '2023-02-30'. The target column is defined as DATE. The analyst needs to ensure that invalid dates are flagged and not loaded. Which approach best handles this data quality issue during acquisition?

A.Use a Python script with the pandas library to read the CSV and apply the to_datetime function with errors='coerce' before loading into the database.
B.Set the database column to accept NULL values and load all rows, allowing the database to automatically convert invalid dates to NULL.
C.Load the data into a staging table with the column as VARCHAR, then use a function like TRY_CAST or TO_DATE with error handling to identify invalid dates.
D.Use a regular expression to validate the date format and reject rows that do not match the pattern.
AnswerC

Loading into a staging table with a string column allows the data to be ingested without conversion errors. Then, using a function like TRY_CAST (SQL Server) or TO_DATE with error handling (Oracle) can attempt conversion and return NULL or an error for invalid dates. This isolates invalid rows for review, ensuring only valid dates move to the final DATE column.

Why this answer

The correct approach is to stage the data as strings and then use database functions that attempt conversion with error handling. This allows invalid dates to be identified without failing the entire load, and only valid dates are moved to the final DATE column. This method is robust, scalable, and integrates with SQL-based ETL processes.

Exam trap

The trap here is assuming that format validation (like regex) is sufficient to catch invalid dates, but it cannot detect semantic errors such as month 13 or February 30.

Practice this question →
129
MCQmedium

Refer to the exhibit. If the date column is stored as a string in 'MM/DD/YYYY' format, what will be the result?

A.Incorrect results because string comparison is lexicographic.
B.NULL values
C.Error because DATE type is expected.
D.Correct results because string comparison works for dates.
AnswerA

The different format causes lexicographic comparison to fail.

Why this answer

When dates are stored as strings in 'MM/DD/YYYY' format, string comparison is lexicographic (character-by-character). This means that '01/02/2023' (January 2) would be considered greater than '12/31/2022' because '0' > '1' at the first character, leading to incorrect chronological ordering. The comparison does not interpret the string as a date value.

Exam trap

CompTIA often tests the misconception that string comparison of dates in 'MM/DD/YYYY' format will yield correct chronological order, but the trap is that lexicographic comparison compares month first, not year, leading to incorrect results.

How to eliminate wrong answers

Option B is wrong because string comparison does not produce NULL values; it simply compares strings lexicographically and returns a valid boolean result. Option C is wrong because no error occurs; the database or application will perform string comparison without expecting a DATE type, as the column is defined as a string. Option D is wrong because string comparison does not work correctly for dates in this format; lexicographic order does not match chronological order for 'MM/DD/YYYY' strings.

Practice this question →
130
Multi-Selecthard

A data analyst is preparing a dataset for predictive modeling and must handle missing values in several numeric and categorical columns. The team needs defensible, documented choices rather than ad hoc deletion. Which TWO actions are appropriate for handling missing data in this scenario? (Choose two.)

Select 2 answers
A.Create an explicit missing-value indicator column alongside an imputed value so the model can learn from the missingness pattern.
B.Impute numeric missing values with the column mean and categorical missing values with the mode, without further review.
C.Document the missingness rate per column and investigate whether values are missing at random before choosing a treatment.
D.Replace all missing numeric values with zero so the column contains no nulls and requires no further processing.
E.Drop every row that contains any missing value across all columns to guarantee a complete dataset.
AnswersA, C

An indicator preserves the information that a value was absent, which is valuable when missingness correlates with the target. Pairing it with an imputed value keeps the record usable while letting the model distinguish imputed from observed cases. This is a documented, reproducible technique that satisfies the demand for defensible handling.

Why this answer

Defensible missing-data handling starts with diagnosing the extent and pattern of missingness, then applies a treatment matched to that pattern. Combining an indicator column with an imputed value preserves both usability and the signal contained in absence. Blind mean or mode substitution, blanket row deletion, and zero-filling all introduce bias without documentation.

Exam trap

The trap here is treating missing-data handling as a mechanical fill step and overlooking that the pattern of missingness itself can be informative and must be diagnosed first.

Practice this question →
131
MCQeasy

A data analyst needs to merge two customer tables from different sources. One table uses 'CUST_ID' as the primary key, the other uses 'CustomerID'. To ensure accurate merging, the analyst should first:

A.Perform a fuzzy match on names
B.Normalize the key column names to a common format
C.Remove duplicate rows from both tables
D.Aggregate data by region
AnswerB

Mismatched key names such as 'CUST_ID' and 'CustomerID' cause the join to fail or produce a cartesian result. Standardising both columns to one common name and format lets the merge match rows correctly, satisfying the requirement for accurate joining across sources.

Why this answer

Normalizing key column names to a common format (Option B) is the correct first step because the merge operation requires a consistent join key. Without aligning 'CUST_ID' and 'CustomerID' to a single name and data type, the database or ETL tool will treat them as different columns, resulting in a cross join or an error. This step ensures referential integrity and enables an accurate inner or outer join based on the primary key.

Exam trap

The trap here is that candidates assume deduplication (Option C) is the most critical first step, but without first standardizing the join keys, any deduplication logic would operate on mismatched or incomplete data, leading to incorrect results.

How to eliminate wrong answers

Option A is wrong because performing a fuzzy match on names is an advanced, resource-intensive technique used only when exact key values are unavailable or inconsistent; it is unnecessary when the tables already have primary key columns that can be standardized. Option C is wrong because removing duplicate rows before aligning key names could inadvertently delete legitimate records that only appear duplicated due to key naming differences, and deduplication should occur after the merge or as a separate quality step. Option D is wrong because aggregating data by region is a post-merge analytical operation that has no bearing on resolving key column mismatches and would corrupt the granularity needed for accurate joining.

Practice this question →
132
MCQeasy

A data analyst is tasked with gathering data from a legacy system that only exports CSV files. The files contain headers but no data types. Which tool would best facilitate initial data exploration?

A.Hadoop
B.Tableau
C.SQL database
D.Python pandas
AnswerD

Python pandas reads CSV files directly with `read_csv`, inferring column data types automatically despite the header-only source. This satisfies the stem's constraint of untyped legacy exports, enabling immediate exploration through `head()`, `info()` and `describe()` without prior schema definition or manual type assignment.

Why this answer

Python pandas is the best tool for initial data exploration of CSV files because its read_csv() function automatically infers data types, handles headers, and provides immediate exploratory methods like .info(), .describe(), and .head(). It requires no schema definition upfront, making it ideal for legacy exports with unknown types.

Exam trap

The trap is choosing a tool that requires predefined schemas (SQL) or is meant for downstream visualization (Tableau) instead of recognizing pandas as the flexible, schema-inferring exploration tool for raw CSVs.

How to eliminate wrong answers

Option A (Hadoop) is wrong because Hadoop is a distributed storage and processing framework designed for large-scale batch workloads, not quick interactive exploration of a single CSV. Option B (Tableau) is wrong because it is a visualization tool that connects to data sources but is not optimized for programmatic type inference and statistical profiling of raw CSVs. Option C (SQL database) is wrong because loading a CSV into SQL requires defining a schema and data types first, which contradicts the scenario where types are unknown.

Practice this question →
133
MCQmedium

During EDA, an analyst calculates the Z-score for each data point in a dataset. A data point with a Z-score of 3.5 is identified. What does this indicate?

A.The data point has a high frequency
B.The data point is exactly at the mean
C.The data point is likely an outlier
D.The data point is within the interquartile range
AnswerC

A Z-score of 3.5 lies beyond three standard deviations from the mean, satisfying the stem's outlier criterion. Under a normal distribution roughly 99.7% of values fall within three standard deviations, so such an extreme standardised deviation is statistically improbable and warrants flagging as a likely outlier during EDA.

Why this answer

A Z-score of 3.5 means the data point lies 3.5 standard deviations above the mean. In most distributions, values beyond ±3 standard deviations are statistically rare (about 0.3% of a normal distribution) and are commonly flagged as outliers. This is the standard EDA heuristic for outlier detection.

Exam trap

DA0-002 often tests whether candidates confuse Z-score (standard deviations from mean) with IQR-based outlier detection or with frequency counts, luring them to pick 'high frequency' or 'within IQR' answers.

How to eliminate wrong answers

Option A is wrong because Z-score measures distance from the mean in standard deviation units, not frequency or count of occurrences. Option B is wrong because a Z-score of 0 indicates the point equals the mean; 3.5 is far from it. Option D is wrong because the interquartile range (IQR) is a separate outlier method (typically 1.5×IQR rule); Z-score does not describe IQR membership.

Practice this question →
134
Drag & Dropmedium

Drag and drop the steps to perform a data audit in the correct order.

Drag or tap steps into the slots.

Steps
Order
1Step 1
2Step 2
3Step 3
4Step 4

Why this order

Data audit begins with inventory, quality assessment, compliance check, documentation, and recommendations.

Practice this question →
135
Multi-Selecthard

A data analyst is integrating data from two source systems into a single customer dataset. Source A uses a customer ID format like 'CUST-12345', while Source B uses '12345'. Additionally, Source A records dates in 'MM/DD/YYYY' format, while Source B uses 'YYYY-MM-DD'. Which two data preparation tasks are essential to ensure the integrated dataset is consistent and usable? (Choose two.)

Select 2 answers
A.Apply encryption to all customer ID fields.
B.Convert all date values to a single standardized format.
C.Standardize the customer ID format across both sources.
D.Remove all records with missing values.
E.Aggregate the data by customer to reduce row count.
AnswersB, C

Converting dates to a uniform format ensures temporal comparisons and calculations are valid. Different date formats can lead to misinterpretation, sorting errors, and failed date functions. Standardizing dates is essential for consistent time-based analysis and reporting across the integrated dataset.

Why this answer

The key challenges are inconsistent customer ID formats and date formats across sources. Standardizing both ensures records can be matched and time-based analysis is accurate. Encryption, missing value removal, and aggregation do not directly solve these format inconsistencies and may introduce other issues.

Exam trap

The trap here is focusing on data security or missing values instead of the format harmonization needed for integration.

Practice this question →
136
MCQhard

A data engineer is designing a data pipeline to ingest streaming data from IoT sensors. The sensors send data every second, and the pipeline must handle bursts of up to 10,000 messages per second. Which approach is most appropriate for capturing this data before processing?

A.Directly write each message to a relational database
B.Load directly into a data warehouse
C.Use a message queue to buffer the incoming data
D.Store data in flat files and process in nightly batches
AnswerC

A message queue decouples producers from consumers, buffering bursts of up to 10,000 messages per second so the ingestion tier is not overwhelmed. This satisfies the stem's burst-handling constraint by absorbing spikes and letting downstream processing drain at its own rate.

Why this answer

A message queue (e.g., Apache Kafka, Amazon Kinesis, or RabbitMQ) provides an asynchronous buffer that decouples the high-velocity ingestion (up to 10,000 messages/second) from downstream processing. This allows the pipeline to absorb burst traffic without overwhelming the processing layer, ensures data durability, and supports replayability in case of failures.

Exam trap

CompTIA often tests the misconception that relational databases or data warehouses can handle real-time streaming ingestion at scale, when in fact they require a buffering layer like a message queue to absorb bursts and decouple ingestion from processing.

How to eliminate wrong answers

Option A is wrong because directly writing each message to a relational database (RDBMS) at 10,000 messages/second would cause severe write contention, lock contention, and I/O bottlenecks, leading to dropped data and unacceptable latency. Option B is wrong because loading directly into a data warehouse (e.g., Snowflake, Redshift) is designed for batch or micro-batch ingestion, not for real-time streaming at this scale; it would incur high costs and fail to handle bursty throughput without prior buffering. Option D is wrong because storing data in flat files and processing in nightly batches introduces unacceptable latency (up to 24 hours) for streaming IoT data, and the file system cannot reliably handle 10,000 writes per second without data loss or corruption.

Practice this question →
137
MCQeasy

A data analyst is importing a CSV file that contains a mixture of numeric and text fields. What is the most common issue when importing?

A.Duplicate rows
B.Missing header row
C.Data types being incorrectly inferred
D.File size limitation
AnswerC

CSV files carry no type metadata, so the import engine must guess each column's type from sampled values. Mixed numeric and text fields cause misinference — for example, leading-zero codes becoming integers or numeric-looking text converting to numbers — which is the most common CSV import problem.

Why this answer

When importing CSV files, the most common issue is that the import tool (e.g., Excel, pandas, SQL Server Import Wizard) automatically infers data types based on the first rows it reads. Mixed numeric and text fields often cause the tool to guess wrong — for example, treating a numeric column with a stray text value as text, or converting leading-zero codes (like ZIP codes) to integers and losing the zeros. This type inference mismatch is the classic CSV import pitfall.

Exam trap

DA0-002 often tests the misconception that CSV import issues are about file size or duplicates, when the real culprit is automatic data type inference on mixed columns.

How to eliminate wrong answers

Option A is wrong because duplicate rows are a data quality issue that may or may not exist, but they are not an inherent import problem caused by mixed types. Option B is wrong because a missing header row is a file structure issue, not a type inference issue, and many import tools can handle headerless files with manual configuration. Option D is wrong because file size limitations are a platform constraint (e.g., Excel's 1,048,576 row limit) and are unrelated to the mixture of numeric and text fields.

Practice this question →
138
MCQmedium

A marketing analyst is combining two datasets: one containing campaign IDs and spend, and another containing campaign IDs and impressions. The first dataset has 1,200 rows and the second has 950 rows. After an inner join on campaign_id, the result has 1,050 rows. Which statement best explains this result?

A.The inner join retained only rows where campaign_id exists in both datasets, so campaigns present in only one dataset were excluded.
B.The inner join produced a Cartesian product because campaign_id was not unique in one of the datasets.
C.The result indicates referential integrity was enforced, so orphaned campaign IDs were automatically repaired.
D.The join failed to include unmatched rows because the analyst should have used a cross join to preserve all campaigns.
AnswerA

An inner join returns only the intersection of keys from both tables. With 1,200 and 950 rows, the result of 1,050 means many campaigns matched but some from each side did not. This is the expected behavior of an inner join and explains why the output is smaller than the larger input while still substantial, reflecting partial overlap between the two campaign lists.

Why this answer

An inner join returns only rows whose join key appears in both inputs. Given 1,200 and 950 source rows, an output of 1,050 indicates substantial but incomplete overlap, which is exactly what an inner join produces. A Cartesian product, cross join, or referential integrity enforcement would not yield this count.

The analyst observed normal inner join behavior reflecting partial key matching between the two campaign datasets.

Exam trap

The trap here is assuming any row count change during a join indicates an error, when an inner join legitimately reduces rows to the matching key intersection.

Practice this question →
139
MCQeasy

A marketing team wants to analyze customer sentiment from social media posts. Which data acquisition method is most appropriate?

A.Internal database query
B.Physical sensor data
C.Web scraping from public social media APIs
D.Survey questionnaire
AnswerC

Public social media APIs expose sentiment-bearing posts as structured JSON, letting the team acquire text at scale without breaching platform terms. This satisfies the requirement to analyse customer sentiment from social media, since scraping public APIs yields the raw opinion data the model needs.

Why this answer

Web scraping from public social media APIs is the most appropriate method for analyzing customer sentiment from social media posts because it directly collects the unstructured text data (posts, comments, tweets) that contains sentiment. Social media platforms provide APIs (e.g., Twitter API, Facebook Graph API) that allow programmatic access to public posts, enabling large-scale data acquisition. This method is real-time, scalable, and captures the authentic voice of customers, which is essential for sentiment analysis.

Internal databases and surveys do not capture social media data, and physical sensors are irrelevant to text-based sentiment.

Exam trap

The trap here is confusing data acquisition methods: candidates might think internal databases contain social media data or that surveys can capture unsolicited sentiment, but the key is recognizing that social media posts are external, unstructured text best obtained via APIs or scraping.

How to eliminate wrong answers

Option A is wrong because internal database queries only access data already stored within the organization, which does not include external social media posts. Option B is wrong because physical sensor data measures environmental or physical phenomena (e.g., temperature, motion), not text-based social media content. Option D is wrong because survey questionnaires are structured, solicited responses that may not reflect spontaneous social media sentiment and are limited in scale and timeliness.

Practice this question →
140
MCQhard

An e-commerce company is merging customer data from three legacy systems. Two systems use email as unique identifier, but one system allows multiple customers per email. The third uses phone number. To create a unified customer view, the analyst should first:

A.Request the IT team to modify the legacy system
B.Build a customer matching rule that uses multiple attributes (email, phone, name) with a confidence score
C.Use email as primary key and ignore conflicts
D.Assign new unique IDs and discard existing identifiers
AnswerB

Email alone cannot be the match key because one legacy system permits duplicate customers per email, and phone alone is equally unreliable. A multi-attribute rule with a confidence score resolves this by scoring combined agreement across email, phone and name, letting the analyst merge only above a chosen threshold.

Why this answer

Merging data from systems with different identifier schemas requires a probabilistic matching approach. Using multiple attributes (email, phone, name) with a confidence score allows the analyst to resolve conflicts where email is not unique and phone numbers may be missing or formatted differently, creating a unified customer view without forcing a single key.

Exam trap

The trap here is that candidates assume a single unique identifier (email) can be forced as a primary key, ignoring the real-world data quality issue of non-unique emails, which the question explicitly states.

How to eliminate wrong answers

Option A is wrong because modifying legacy systems is often impractical, costly, and outside the analyst's scope; the question asks what the analyst should do first, not a long-term IT project. Option C is wrong because using email as primary key and ignoring conflicts would lose data integrity when one email maps to multiple customers, violating the goal of a unified view. Option D is wrong because assigning new unique IDs and discarding existing identifiers eliminates the ability to link records back to source systems and loses valuable matching context, making deduplication impossible.

Practice this question →
141
MCQeasy

In SQL, which string function would you use to remove leading and trailing spaces from a column named 'city'?

A.TRIM
B.RTRIM
C.LTRIM
D.CLEAN
AnswerA

TRIM removes both leading and trailing spaces from a string, returning the cleaned value for the city column. It precisely matches the requirement to strip spaces from both ends, unlike LTRIM or RTRIM which handle only one side.

Why this answer

The SQL TRIM function removes both leading and trailing spaces (or specified characters) from a string. Applied to a column like 'city', TRIM(city) returns the value with all surrounding whitespace stripped, which is exactly what the question asks for.

Exam trap

The trap is that LTRIM and RTRIM sound like they handle 'trimming' generically — candidates who don't recall that TRIM handles both sides may pick one of the directional variants.

How to eliminate wrong answers

Option B is wrong because RTRIM only removes trailing (right-side) spaces, leaving leading spaces intact. Option C is wrong because LTRIM only removes leading (left-side) spaces, leaving trailing spaces intact. Option D is wrong because CLEAN is not a standard SQL string function — it is not part of ANSI SQL and does not exist in major databases like PostgreSQL, MySQL, or SQL Server for this purpose.

Practice this question →
142
MCQeasy

A data analyst is profiling a dataset and notices that the 'age' column contains negative values and values exceeding 120. The analyst needs to address these anomalies. Which data preparation technique is most appropriate?

A.Data aggregation
B.Imputation
C.Normalization
D.Outlier detection and treatment
AnswerD

Negative ages and ages above 120 are outliers that likely represent data entry errors. Outlier detection identifies such values, and treatment (e.g., removal, capping, or correction) addresses them. This technique is specifically designed to handle values that fall outside a plausible range, ensuring data quality.

Why this answer

The most appropriate technique is outlier detection and treatment because negative ages and ages over 120 are statistically implausible and likely errors. This method identifies and corrects or removes such values, ensuring the dataset's integrity. Other techniques like imputation or normalization do not address the underlying invalidity of the data.

Exam trap

The trap here is confusing outlier treatment with imputation, which is for missing values, or normalization, which only rescales data without fixing errors.

Practice this question →
143
Multi-Selectmedium

Which THREE are best practices for data profiling during acquisition? (Choose three.)

Select 3 answers
A.Immediately normalize data
B.Check for completeness
C.Assess data types
D.Identify outliers
E.Skip validation for trusted sources
AnswersB, C, D

Ensuring all required fields are populated is essential.

Why this answer

Checking for completeness (Option B) is a best practice during data acquisition because it ensures that all required fields and records are present before further processing. Incomplete data can lead to incorrect analysis or failed transformations, so profiling for missing values or nulls is a fundamental validation step.

Exam trap

The trap here is that candidates confuse 'best practices for acquisition' with 'best practices for transformation,' leading them to select normalization (Option A) as an immediate step rather than a later processing stage.

Practice this question →
144
MCQhard

A data analyst is using a recursive CTE to traverse an organizational hierarchy. What is the purpose of the anchor member in the recursive CTE?

A.It provides the initial seed or starting rows for the recursion.
B.It filters the final output of the recursive CTE.
C.It specifies how to join the CTE with itself recursively.
D.It defines the termination condition for the recursion.
AnswerA

The anchor member supplies the seed rows that begin the recursion, typically the hierarchy's root nodes. Without it, the recursive member has no starting set to iterate from, so the CTE cannot traverse the organisational hierarchy. It executes once, then the recursive member repeatedly joins against its output until no rows remain.

Why this answer

The anchor member initializes the recursion with the base result set.

Practice this question →
145
Matchingmedium

Match each data analysis technique to its primary purpose.

Drag a concept onto its matching description — or click a concept then click the description.

Concepts
Matches

Model relationships between variables

Group similar data points without labels

Analyze data points collected over time

Compare means across multiple groups

Test association between categorical variables

Why these pairings

The correct matches are: Regression with predicting continuous outcomes, Clustering with grouping similar data, Classification with assigning categories, and PCA with reducing dimensionality. Common confusions include swapping regression and clustering definitions.

Practice this question →
146
Multi-Selectmedium

A data analyst is profiling a newly acquired customer dataset before loading it into a warehouse. The analyst notices that the 'country' column contains values such as 'USA', 'United States', 'U.S.A.', and 'US'. Which TWO actions are appropriate for standardizing this column during data preparation? (Choose two.)

Select 2 answers
A.Delete rows that do not exactly match the most common value for the country column.
B.Use an ISO 3166 country code list as the reference and map each variant to its corresponding alpha-2 or alpha-3 code.
C.Apply a fuzzy matching algorithm to cluster similar strings and assign the most frequent value as the canonical form.
D.Convert all values to uppercase and remove periods, then treat the results as distinct categories.
E.Create a mapping table that translates each variant to a single canonical country name or code.
AnswersB, E

ISO 3166 provides an authoritative, unambiguous set of country codes, so mapping variants to alpha-2 or alpha-3 codes yields a stable, internationally recognized canonical form. It avoids ambiguity in names and supports reliable joins and aggregation. This is a best practice for country data standardization and complements a mapping table with an external standard.

Why this answer

Standardizing a country column with known variants requires deterministic translation to a canonical form. A mapping table documents each variant explicitly, and ISO 3166 codes provide an authoritative reference that removes name ambiguity. Together they yield consistent values suitable for joins and aggregation.

Normalization alone leaves synonyms unresolved, fuzzy clustering risks merging distinct entities, and deleting rows sacrifices valid data rather than cleaning it.

Exam trap

The trap here is believing that simple case and punctuation normalization is sufficient standardization, when synonyms like 'United States' still remain distinct without a mapping or reference standard.

Practice this question →
147
Multi-Selectmedium

An analyst wants to use Python (pandas) to compute the average sales amount per region from a DataFrame 'df' with columns 'region' and 'sales'. Which TWO pandas operations are needed? (Select TWO).

Select 2 answers
A.df.fillna(0)
B.df.pivot_table(index='region', values='sales', aggfunc='mean')
C.df['sales'].apply(np.sqrt)
D.df.merge(df2, on='region')
E.df.groupby('region')['sales'].mean()
AnswersB, E

`pivot_table` groups rows by the `region` column and applies `aggfunc='mean'` to the `sales` values, producing one averaged figure per region. This directly satisfies the stem's requirement to compute average sales amount per region, collapsing many rows into a single aggregated result keyed by region.

Why this answer

Option B, df.pivot_table(index='region', values='sales', aggfunc='mean'), is correct because pivot_table with index='region' groups rows by region, selects the 'sales' column via values='sales', and applies the mean aggregation through aggfunc='mean', directly producing the average sales per region. Option E, df.groupby('region')['sales'].mean(), is correct because groupby('region') splits the DataFrame by region, ['sales'] selects the sales column, and .mean() computes the arithmetic average of sales within each group, yielding the same per-region averages. The other options do not compute grouped averages: A (df.fillna(0)) only replaces missing values with zero, C (df['sales'].apply(np.sqrt)) applies a square-root transformation element-wise, and D (df.merge(df2, on='region')) joins two DataFrames on the region key without any aggregation.

Exam trap

The trap is overcomplicating the question — candidates may look for a merge or a fillna step, but the core operation is simply group-and-aggregate, which both groupby().mean() and pivot_table() accomplish.

Practice this question →
148
MCQmedium

An analyst receives a dataset of website sessions where the session_duration_seconds column contains several negative values and a few values exceeding 86,400 seconds. The analyst must prepare this data for analysis of average session length. Which action best addresses this data quality issue while preserving analytical integrity?

A.Replace all out-of-range values with the column mean so the average remains stable.
B.Take the absolute value of negative durations and cap all values at 86,400 seconds.
C.Leave the values unchanged because outliers are a natural part of real-world data.
D.Flag the out-of-range values, investigate their source, and correct or exclude them using a documented rule before computing the average.
AnswerD

Negative durations are logically impossible and durations beyond a day are implausible for a single session, so they indicate measurement or pipeline errors. Flagging and investigating them, then applying a documented correction or exclusion rule, preserves integrity and makes the average meaningful. Documenting the rule also keeps the process auditable and repeatable.

Why this answer

Negative and implausibly large session durations are invalid values that would distort an average. The sound approach is to flag them, investigate the cause, and apply a documented correction or exclusion rule. Transforming them into plausible values or ignoring them hides the defect, while retaining them uncritically treats impossible data as natural variation.

Exam trap

The trap here is treating invalid values such as negative durations as ordinary outliers, when they are logically impossible and must be investigated rather than simply retained or smoothed.

Practice this question →
149
MCQhard

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?

A.A window function with ROW_NUMBER()
B.A recursive CTE
C.A GROUP BY clause with aggregation
D.A self-join with a LEFT JOIN
AnswerB

A recursive CTE references its own result set, walking manager_id links upward from each employee until the NULL root is reached, producing hierarchy levels. Self-joins need a known depth, and window functions cannot traverse variable-length parent-child chains.

Why this answer

Recursive CTE can traverse hierarchical data to compute levels.

Practice this question →
150
MCQmedium

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?

A.total > 100 AND total < 500
B.total IN (100, 500)
C.total BETWEEN 100 AND 500
D.total >= 100 OR total <= 500
AnswerC

BETWEEN is inclusive at both bounds, so total BETWEEN 100 AND 500 returns rows where the total equals 100 or 500 as well as every value in between, satisfying the stem's inclusive requirement without needing separate >= and <= comparisons.

Why this answer

The BETWEEN operator is inclusive of both endpoints, so 'total BETWEEN 100 AND 500' returns rows where total is greater than or equal to 100 and less than or equal to 500 — exactly the inclusive range requested. It is equivalent to 'total >= 100 AND total <= 500' but more concise and readable.

Exam trap

DA0-002 often tests the misconception that BETWEEN is exclusive of its endpoints, when it is actually inclusive — and also tests the AND vs OR confusion in range conditions.

How to eliminate wrong answers

Option A is wrong because 'total > 100 AND total < 500' uses strict inequalities, excluding orders exactly equal to $100 or $500, which violates the inclusive requirement. Option B is wrong because 'total IN (100, 500)' matches only the two exact values 100 and 500, not the range between them. Option D is wrong because 'total >= 100 OR total <= 500' uses OR instead of AND, which is always true for any value (every number is either >= 100 or <= 500), returning all rows.

Practice this question →
← PreviousPage 2 of 3 · 208 questions totalNext →

Ready to test yourself?

Try a timed practice session using only Data Acquisition and Preparation questions.

Start 20-question session