DS0-001 Practice Test 23 — 15 Questions

Question 1

A data analyst is using a subquery in the WHERE clause to filter customers who have placed at least one order. The subquery returns customer_id values from the orders table. Which SQL construct correctly expresses this requirement?

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

Accepted Answer

SELECT * FROM customers WHERE customer_id IN (SELECT customer_id FROM orders);. Filtering customers who have at least one order is a semi-join pattern. The IN operator with a subquery compares each outer customer_id against the set of customer_id values from orders, returning customers with at least one match. A scalar equality fails with multiple rows, an uncorrelated EXISTS returns all customers when orders is non-empty, and ANY without a comparison operator is invalid syntax.

Answer

SELECT * FROM customers WHERE customer_id ANY (SELECT customer_id FROM orders);

Answer

SELECT * FROM customers WHERE customer_id = (SELECT customer_id FROM orders);

Answer

SELECT * FROM customers WHERE EXISTS (SELECT customer_id FROM orders);

Question 2

A data systems team runs an analytics workload on a database that is also used for OLTP. Reports have become slower over time, and the team suspects that an index is being scanned instead of seeked. They want to capture the actual execution plan for a specific report query to confirm the operator used. Which action should they take?

Accepted Answer

Run the query with Include Actual Execution Plan enabled in the client tool.. The actual execution plan is generated after the query runs and reflects the operators and runtime behavior, making it the correct way to confirm whether an index scan or seek occurred. Estimated plans may not match runtime behavior, and I/O statistics or historical Query Store data do not directly show the actual operator for a specific execution.

Answer

Run the query with SET STATISTICS IO ON and compare logical reads.

Answer

Enable Query Store and inspect the plan for the query's query_id.

Answer

Run the query with SET SHOWPLAN_XML ON to capture the estimated plan before execution.

Question 3

To prevent unauthorized access to specific columns in a 'Salary' table, which object should be created for the reporting team?

Accepted Answer

View. A view is a virtual table defined by a SELECT statement that exposes only the columns and rows the creator chooses to include. By creating a view that selects only non-sensitive columns from the 'Salary' table and granting the reporting team SELECT permission on the view (rather than on the base table), column-level access is restricted without duplicating data. This is the standard SQL mechanism for column-level security when row-level or table-level grants are too coarse.

Answer

Index

Answer

Sequence

Answer

Stored Procedure

Question 4

A financial services firm is implementing a data governance program and must classify data assets. Which two characteristics are used to determine whether data should be classified as confidential under a typical data classification policy? (Choose two.)

Accepted Answer

The data contains personally identifiable information (PII) such as Social Security numbers or account numbers.. Confidential classification hinges on content and impact: data containing PII or whose disclosure would cause material harm warrants the confidential tier. Storage technology, access frequency, and age are operational attributes that do not determine sensitivity and therefore should not drive classification decisions.

Answer

The data is accessed by more than ten users per day.

Answer

The data is stored in a relational database rather than a NoSQL store.

Answer

The data is older than seven years and therefore archived.

Question 5

A data analyst is writing a SQL query to retrieve unique department names from the 'Employees' table. Which keyword should be utilized to ensure the results do not contain duplicate entries?

Accepted Answer

DISTINCT. The DISTINCT keyword is the correct choice because it eliminates duplicate rows from the result set, returning only unique values for the selected columns. When applied as SELECT DISTINCT department FROM Employees, the database engine sorts or hashes the intermediate result to remove duplicates before returning rows. This is the ANSI SQL standard mechanism specifically designed for deduplication of query output.

Answer

GROUP BY

Answer

UNIQUE

Answer

FILTER

Question 6

Which type of data is defined as 'sensitive' and usually requires enhanced protection?

Accepted Answer

Personally Identifiable Information (PII). Personally Identifiable Information (PII) is data that can identify a specific individual, such as names, SSNs, addresses, or biometric data, and is classified as sensitive, requiring enhanced protection under regulations like GDPR, CCPA, and HIPAA. PII is the canonical example of sensitive data in data governance frameworks.

Answer

Temporary cache files

Answer

Publicly available news data

Answer

Static CSS files

Question 7

An analyst is writing an SQL query to retrieve total sales per region, but the result must only show regions with more than 100 transactions. Which clause is required?

Accepted Answer

HAVING. The HAVING clause is used to filter the results of aggregate functions like SUM or COUNT. Since the requirement is to show only regions with more than 100 transactions, which is an aggregate condition, HAVING is required. WHERE cannot be used with aggregate functions.

Answer

ORDER BY

Answer

LIMIT

Answer

WHERE

Question 8

A data platform team is implementing encryption for a new MongoDB cluster that stores customer PII. The security policy requires protecting data at rest and ensuring that compromised credentials for one tenant cannot expose another tenant's data. Which two measures should the team implement to meet these requirements? (Choose two.)

Accepted Answer

Configure Client-Side Field Level Encryption for the tenant-identifying fields. Protecting data at rest requires encrypting storage files with keys held outside the database host, which WiredTiger encryption with KMS-managed keys provides. Preventing one tenant's compromised credentials from exposing another tenant's data requires cryptographic separation at the field level, which Client-Side Field Level Encryption with per-tenant keys delivers. Together these two measures satisfy both clauses of the security policy.

Answer

Enable role-based access control with a shared administrative account

Answer

Store encryption keys in a configuration file on each mongod host

Answer

Enable TLS for all client and intra-cluster connections

Question 9

Which TWO database features are best used to handle massive volumes of data while maintaining fast query speeds for historical analytics?

Accepted Answer

Table partitioning. Table partitioning (B) is correct because splitting a large table into smaller, manageable partitions—typically by date or range—lets the query engine scan only the relevant partitions (partition elimination), dramatically reducing I/O for historical analytics workloads. Columnstore indexes (C) are correct because they store data column-wise with compression and batch-mode processing, which is purpose-built for large-scale analytical scans and aggregations, delivering fast query speeds over massive data volumes. Clustered indexes on random UUIDs (A) are wrong because random values cause page splits and fragmentation, hurting write and read performance. Row-level triggers (D) add per-row procedural overhead and do nothing to accelerate analytical queries. Database mirroring (E) is a high-availability/DR feature that duplicates a database but provides no query-performance or analytical-scan benefit.

Answer

Clustered indexes on random UUIDs

Answer

Row-level triggers

Answer

Database mirroring

Question 10

A database administrator needs to add a new column named 'loyalty_points' of type INTEGER to an existing 'customers' table without affecting existing rows' data. Which SQL statement should the administrator execute?

Accepted Answer

ALTER TABLE customers ADD COLUMN loyalty_points INTEGER;. Adding a column to an existing table is a schema change performed with ALTER TABLE ... ADD COLUMN. This preserves existing rows and populates the new column with NULL or a specified default. UPDATE changes row values, INSERT adds rows, and CREATE TABLE builds new tables, so only the ALTER TABLE statement satisfies the requirement without disturbing existing data.

Answer

INSERT INTO customers (loyalty_points) VALUES (INTEGER);

Answer

UPDATE TABLE customers ADD loyalty_points INTEGER;

Answer

CREATE TABLE customers ADD loyalty_points INTEGER;

Question 11

You are a database administrator for a high-traffic e-commerce platform. The order history table has grown to 500 GB and queries filtering on order_date are performing poorly. You need to implement a partitioning strategy that allows you to quickly archive old orders by switching out partitions. Which partitioning approach should you use?

Accepted Answer

Range partitioning on the order_date column. Range partitioning on the order_date column is the correct approach because it aligns with the query filter and enables efficient archiving through partition switching. It allows the database engine to eliminate irrelevant partitions during queries, improving performance, and provides a simple way to move old data to an archive table by switching out a partition.

Answer

List partitioning on the order_status column

Answer

Hash partitioning on the order_id column

Answer

Round-robin partitioning across available filegroups

Question 12

A data systems technician is asked to recommend a partitioning strategy for a very large PostgreSQL table that stores time-series sensor readings. Queries almost always filter on the reading_timestamp column and return a narrow time window, while older data must remain available for compliance for seven years. The team wants the planner to prune partitions automatically and wants to keep maintenance simple. Which approach should the technician recommend?

Accepted Answer

Range partitioning on reading_timestamp, with one partition per month and a default partition for out-of-range rows.. Range partitioning on the timestamp column matches the dominant query predicate, enabling the planner to prune partitions and read only the months inside the requested window. Monthly granularity balances partition count against per-partition size, and the default partition preserves inserts until new ranges are declared. The other strategies either partition on the wrong column or distribute timestamps so pruning becomes impossible.

Answer

List partitioning on a device_id column, with one partition per sensor manufacturer.

Answer

Hash partitioning on reading_timestamp, with sixteen partitions distributed evenly.

Answer

No partitioning, but create a BRIN index on reading_timestamp and rely on sequential scans.

Question 13

A database administrator is deploying a new MongoDB 6.0 replica set to store product catalog data. The business requires that the database remain available for reads and writes even if a single data center becomes unreachable, and that writes be acknowledged by a majority of replica set members before returning success. Which deployment topology and write concern should the administrator configure?

Accepted Answer

Deploy a five-member replica set with members distributed across three data centers, and set the write concern to w:majority.. The requirement for continued reads and writes after a single data center failure means the replica set must retain a majority of voting members even when one site is lost. A five-member set across three data centers keeps a majority of three after losing one site. Using w:majority ensures writes are acknowledged by a majority of members, satisfying the durability requirement.

Answer

Deploy a three-member replica set in a single data center and set the write concern to w:majority.

Answer

Deploy a three-member replica set with members distributed across two data centers, and set the write concern to w:1.

Answer

Deploy a four-member replica set with two members in each of two data centers and set the write concern to w:2.

Question 14

A database administrator is deploying a new Microsoft SQL Server 2022 instance that will host a mission-critical OLTP database. The business requires zero data loss and automatic failover to a secondary server in a different subnet within the same data center. The administrator needs to configure a high-availability solution that supports automatic failover and synchronous commit. Which solution should the administrator implement?

Accepted Answer

Configure an Always On availability group with synchronous-commit mode and automatic failover.. The requirement for zero data loss and automatic failover in SQL Server 2022 points to Always On availability groups with synchronous-commit mode. This feature supports automatic failover without shared storage and works across different subnets. Failover cluster instances require shared storage, log shipping is manual, and database mirroring is deprecated.

Answer

Configure a failover cluster instance (FCI) with shared storage between the two servers.

Answer

Configure database mirroring in high-safety mode with automatic failover.

Answer

Configure log shipping with a short transaction log backup interval and automatic failover.

Question 15

A data analyst is examining a query that uses a LEFT JOIN between a customers table and an orders table. The analyst notices that some customers appear multiple times in the result set. Which of the following is the most likely cause?

Accepted Answer

The orders table has multiple orders for the same customer.. In a LEFT JOIN, each left-table row is paired with all matching right-table rows. Since a customer can have multiple orders, the customer will appear multiple times, once per order. This is the expected result of a one-to-many relationship and the most common reason for duplicate customer rows in such a join.

Answer

The join is using a non-unique column in the customers table.

Answer

The join condition is missing a filter on the orders table.

Answer

The customers table has duplicate customer IDs.