DA0-002 Practice Test 5 — 10 Questions

Question 1

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?

Accepted Answer

It provides the initial seed or starting rows for the recursion.. The anchor member initializes the recursion with the base result set.

Answer

It filters the final output of the recursive CTE.

Answer

It specifies how to join the CTE with itself recursively.

Answer

It defines the termination condition for the recursion.

Question 2

A company uses an OLTP system for processing customer transactions. Which characteristic is most important for this system to ensure that each transaction is processed reliably, even if multiple users access the system simultaneously?

Accepted Answer

It follows ACID properties. ACID properties (Atomicity, Consistency, Isolation, Durability) are the foundational guarantees that ensure each transaction in an OLTP system is processed reliably, even when multiple users access the system concurrently. Atomicity ensures all-or-nothing execution, Consistency preserves database invariants, Isolation prevents concurrent transactions from interfering with each other, and Durability guarantees committed transactions survive failures. These properties directly address the requirement for reliable transaction processing under simultaneous access.

Answer

It uses a columnar storage format

Answer

It stores data in a denormalized schema

Answer

It supports complex analytical queries

Question 3

Which database index type is most commonly used for exact-match lookups and range queries in a B-tree structure?

Accepted Answer

B-tree index. A B-tree index is the correct answer because it maintains sorted data in a balanced tree structure, enabling both exact-match lookups (via equality searches) and efficient range queries (via ordered traversal of leaf nodes). This dual capability makes it the standard index type in relational databases like MySQL, PostgreSQL, and Oracle for general-purpose querying.

Answer

Hash index

Answer

Clustered index

Answer

Bitmap index

Question 4

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

Accepted Answer

Calculate the proportion of each category in the population. Stratified sampling requires first partitioning the population into homogeneous subgroups, so option C is correct: dividing the dataset into three strata based on the 'category' column (Electronics, Clothing, Home) creates the strata. Next, option A is correct because the analyst must calculate each category's proportion of the total population to determine how many samples each stratum should contribute for proportional representation. Option B is also correct because, after determining stratum sizes, a random sample must be drawn from each stratum with size proportional to its population proportion, which is the defining allocation step of proportional stratified sampling. Option D is incorrect because selecting every 10th transaction describes systematic sampling, not stratified sampling, and it ignores the category strata. Option E is incorrect because merging all categories into one group and performing simple random sampling removes the stratification and does not guarantee proportional representation across categories.

Answer

Select every 10th transaction from the entire dataset

Answer

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

Question 5

A healthcare analytics team is building a classification model to predict patient readmission within 30 days. The dataset contains 10,000 records with 30 features, including demographics, vital signs, lab results, and medication history. The target variable is imbalanced: 85% no readmission, 15% readmission. The team used logistic regression with default settings and achieved an accuracy of 85%, but the model predicted 'no readmission' for all patients. The lead analyst suspects the model is not learning due to class imbalance. The team has time to implement one corrective action before the next model review. Which action should the team take?

Accepted Answer

Apply SMOTE to oversample the readmission class. SMOTE (Synthetic Minority Oversampling Technique) directly addresses the class imbalance by generating synthetic samples for the minority class (readmission). This forces the logistic regression model to learn decision boundaries that separate the two classes, rather than defaulting to the majority class prediction. With 85% majority and 15% minority, accuracy alone is misleading, and SMOTE is a proven technique to improve recall for the minority class.

Answer

Remove features with low variance to reduce noise

Answer

Use accuracy as the evaluation metric to monitor improvement

Answer

Switch to a random forest model with default settings

Question 6

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:

Accepted Answer

Normalize the key column names to a common format. 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.

Answer

Perform a fuzzy match on names

Answer

Remove duplicate rows from both tables

Answer

Aggregate data by region

Question 7

A data analyst needs to combine data from two tables: one containing customer information and another containing order details. The analyst wants to include all customers, even those who have not placed any orders. Which type of join should be used?

Accepted Answer

LEFT JOIN. A LEFT JOIN returns all rows from the left table (customers) and matching rows from the right table (orders). If a customer has no orders, the order columns will contain NULLs. This satisfies the requirement to include all customers, even those without orders.

Answer

FULL OUTER JOIN

Answer

INNER JOIN

Answer

RIGHT JOIN

Question 8

A hospital's analytics team is designing a new repository for electronic health records. The records include patient demographics, lab results, and physician notes, and the schema must remain flexible because new lab test types are added frequently. The team needs to enforce relationships between patients, encounters, and lab orders while keeping the ability to evolve the schema. Which data model best fits these requirements?

Accepted Answer

Relational model. The requirement to enforce relationships between patients, encounters, and lab orders points to a model with primary and foreign keys, which is the relational model. It also supports schema evolution through controlled migrations when new lab test types appear. Key-value, graph, and document models offer flexibility or traversal but do not natively enforce the structured relationships clinical records demand.

Answer

Document model

Answer

Key-value model

Answer

Graph model

Question 9

Drag and drop the steps to normalize a database table from 1NF to 3NF in the correct order.

Accepted Answer

Step 1: Achieve 1NF by removing repeating groups and ensuring atomic values. Step 2: Achieve 2NF by removing partial dependencies. Step 3: Achieve 3NF by removing transitive dependencies.. Normalization proceeds from 1NF to 2NF to 3NF, then table creation and foreign keys.

Answer

Step 1: Achieve 3NF by removing transitive dependencies. Step 2: Achieve 2NF by removing partial dependencies. Step 3: Achieve 1NF by removing repeating groups.

Answer

Step 1: Achieve 2NF by removing partial dependencies. Step 2: Achieve 1NF by removing repeating groups. Step 3: Achieve 3NF by removing transitive dependencies.

Answer

Step 1: Achieve 1NF by removing repeating groups. Step 2: Achieve 3NF by removing transitive dependencies. Step 3: Achieve 2NF by removing partial dependencies.

Question 10

A data analyst wants to segment customers into groups based on their purchasing behavior. The dataset includes numerical features such as annual income and purchase frequency. Which algorithm is most appropriate for this task?

Accepted Answer

K-means clustering. K-means clustering is an unsupervised learning algorithm that partitions data points into k groups based on similarity of numerical features, which is exactly what customer segmentation requires. Since the task is to discover natural groupings in unlabeled data using features like annual income and purchase frequency, K-means is the standard and most appropriate choice.

Answer

Linear regression

Answer

Logistic regression

Answer

Chi-square test