DS0-001 Practice Test 11 — 15 Questions

Question 1

Which THREE of the following SQL aggregate functions are commonly supported in most relational databases?

Accepted Answer

SUM. SUM, COUNT, and AVG are fundamental aggregate functions supported across virtually all SQL standards.

Answer

CONCAT

Answer

SUBSTR

Question 2

When assessing data privacy compliance, which THREE categories of data are typically protected under laws like CCPA or GDPR?

Accepted Answer

Biometric data. Biometric data (B) is explicitly classified as sensitive personal data under GDPR Article 9 and is protected under CCPA/CPRA as a sensitive personal information category, so it is a correct answer. Personally Identifiable Information (C) is the core category of protected data under both GDPR (personal data) and CCPA (personal information), covering identifiers like names, addresses, and email addresses. Financial account numbers (D) are protected under both regimes—GDPR treats financial data as personal data, and CCPA/CPRA specifically lists account numbers and financial information as protected personal information. Publicly listed company stock prices (A) are public market data, not personal data tied to an identifiable individual, so they fall outside these privacy laws. Internal server IP addresses (E) are not a protected data category per se; while an IP address can be personal data when linked to an individual, internal server IPs alone are infrastructure identifiers, not a regulated category under CCPA or GDPR.

Answer

Publicly listed company stock prices

Answer

Internal server IP addresses

Question 3

Which THREE items should be included in a standard database deployment checklist to ensure consistency across environments?

Accepted Answer

Standardized service account permissions. A standardized database deployment checklist must capture the security and configuration elements that make environments behave identically. Option A (Standardized service account permissions) is correct because the database service account must have the same OS-level and DB-level privileges in every environment, otherwise the instance may fail to start or access files differently. Option C (Database instance configuration parameters) is correct because settings such as memory allocation, max_connections, and compatibility level directly affect performance and behavior, so they must be documented and applied consistently. Option E (Security access policies) is correct because authentication rules, roles, grants, and network access controls must be replicated to avoid privilege drift or exposure between environments. Option B (Application codebase version) is not a database deployment checklist item—it belongs to application release management, even though the DB may support it. Option D (The name of the database lead developer) is irrelevant operational metadata and has no bearing on environment consistency.

Answer

Application codebase version

Answer

The name of the database lead developer

Question 4

Which THREE factors are critical to consider when configuring a database for disaster recovery (DR)?

Accepted Answer

Geographic distance between primary and DR sites. Geographic distance between primary and DR sites (B) is critical because it determines the latency and bandwidth available for replication and the site's exposure to the same regional failure; too close and a single disaster can take out both sites, too far and synchronous replication may become impractical. Recovery Time Objective (C) is essential because it defines the maximum acceptable downtime and therefore dictates the DR architecture — for example, whether you need hot standby with automatic failover or can tolerate restoring from backups. Recovery Point Objective (D) is equally essential because it defines the maximum tolerable data loss and drives the replication mode and frequency, such as synchronous vs. asynchronous replication or backup intervals. The brand of the server hardware (A) is not a DR-critical factor since DR design depends on capabilities and compatibility, not vendor branding, and the database developer's IDE preference (E) is irrelevant to recovery architecture and objectives.

Answer

The brand of the server hardware

Answer

The database developer's IDE preference

Question 5

An administrator needs to restrict a user's ability to see only rows where the 'Region' column is 'North'. What feature should be used?

Accepted Answer

Row-Level Security (RLS). Row-Level Security (RLS) is a database feature that filters rows returned by queries based on the identity or context of the executing user, so a user in the 'North' region only sees rows where Region = 'North'. It enforces the restriction at the database engine level, applying transparently to all queries regardless of the application or tool used.

Answer

View creation

Answer

Stored Procedures

Answer

Column Masking

Question 6

You are deploying a database to a cloud environment and need to ensure that data at rest is encrypted according to organizational security policy. Which deployment configuration ensures transparent encryption of the database files?

Accepted Answer

Implementing Transparent Data Encryption (TDE). Transparent Data Encryption (TDE) encrypts the database files at rest — the data and log files on disk — without requiring changes to the application. It is transparent to the application because encryption and decryption happen at the storage engine level, and it satisfies organizational policies requiring encryption of data at rest. TDE is the standard mechanism for encrypting database files in SQL Server, Oracle, and Azure SQL.

Answer

Applying column-level encryption

Answer

Using a Virtual Private Cloud (VPC) endpoint

Answer

Enabling SSL/TLS on the connection string

Question 7

In PostgreSQL, you need to monitor the performance of your long-running queries. Which view is the most appropriate to inspect the query execution statistics for all active sessions?

Accepted Answer

pg_stat_activity. pg_stat_activity is the PostgreSQL system view that shows one row per server process, including the current query text, state (active, idle, idle in transaction), wait events, and timing information for every active session. It is the canonical view for monitoring long-running queries and session-level activity.

Answer

pg_stat_database

Answer

pg_locks

Answer

pg_stat_user_tables

Question 8

During a disaster recovery test, the DBA realizes the database restoration takes too long. Which action is the most effective way to improve the restore speed of a large database?

Accepted Answer

Use striped backups. Striped backups split the backup (and therefore the restore) across multiple files or devices, allowing parallel read/write streams that dramatically reduce restore time for large databases. SQL Server's BACKUP ... TO DISK='f1', DISK='f2', ... and RESTORE with multiple MOVE targets, or Oracle RMAN with multiple channels, exploit this. The bottleneck in large restores is usually I/O throughput, and striping multiplies it.

Answer

Disable antivirus

Answer

Run in single-user mode

Answer

Increase the recovery interval

Question 9

When planning a backup strategy, a company requires that the Recovery Point Objective (RPO) be set to 15 minutes. Which backup schedule meets this requirement?

Accepted Answer

Transaction Log Backups every 10 minutes. RPO defines the maximum acceptable data loss measured in time, so a 15-minute RPO requires backups no less frequent than every 15 minutes. Transaction log backups every 10 minutes capture all committed transactions within that window, guaranteeing at most 10 minutes of potential data loss — well within the 15-minute target.

Answer

Daily Full Backups

Answer

Weekly Full and Daily Differential Backups

Answer

Hourly Transaction Log Backups

Question 10

An application is experiencing deadlocks. Which strategy would effectively reduce the frequency of deadlocks?

Accepted Answer

Uniform access order. Accessing tables in the same order across different transactions is a primary method to prevent circular wait conditions that cause deadlocks.

Answer

Enabling indexing

Answer

Using read-only databases

Answer

Increasing table size

Question 11

Which TWO controls are necessary to maintain the integrity of a data warehouse?

Accepted Answer

Enforcing referential integrity constraints. Enforcing referential integrity constraints (A) is correct because it ensures that foreign keys in fact and dimension tables always reference valid primary keys, preventing orphaned or inconsistent records that would corrupt warehouse relationships and analytical results. Implementing data validation logic in ETL (C) is correct because validating, cleansing, and rejecting malformed or out-of-range data during extraction, transformation, and load stops bad data from entering the warehouse, directly preserving its accuracy and consistency. Increasing the number of database replicas (B) improves availability and read scalability, not data integrity, since replicas copy whatever data exists, including errors. Running backups every hour (D) supports recoverability and durability but does not prevent or detect inaccurate data. Using a specific naming convention for servers (E) is an administrative standardization that has no effect on the correctness or consistency of stored data.

Answer

Increasing the number of database replicas

Answer

Running backups every hour

Answer

Using a specific naming convention for servers

Question 12

Which TWO of the following are common types of NoSQL databases?

Accepted Answer

Key-Value. Document and Key-Value stores are two primary categories of NoSQL databases.

Answer

Relational

Answer

Hierarchical

Answer

Network

Question 13

Which SQL keyword is used to eliminate duplicate values from a query result set?

Accepted Answer

DISTINCT. The DISTINCT keyword is used in a SELECT statement to return only unique values.

Answer

UNIQUE

Answer

GROUP BY

Answer

FILTER

Question 14

A database administrator is planning the deployment of a new Microsoft SQL Server 2022 instance for a financial application. The application requires that committed transactions survive a power loss and that the storage subsystem provide the lowest possible write latency. The administrator must choose the appropriate recovery model and disk configuration. Which combination best meets these requirements?

Accepted Answer

Set the database to the full recovery model and place data files on RAID 10 and transaction log files on a separate RAID 10 array.. The full recovery model preserves the transaction log for point-in-time recovery, which is required for financial data. RAID 10 provides low-latency writes through mirroring and striping without parity overhead. Separating data and log files onto distinct RAID 10 arrays isolates I/O patterns and further reduces contention, meeting both durability and write latency requirements.

Answer

Set the database to the full recovery model and place data and log files on the same RAID 6 array to maximize fault tolerance.

Answer

Set the database to the simple recovery model and place data and log files on the same RAID 5 array.

Answer

Set the database to the bulk-logged recovery model and place all files on a single RAID 0 array for maximum throughput.

Question 15

A data engineer is reviewing a table that stores customer support tickets. The table has a column TicketID defined as an IDENTITY column and a column CreatedDate defined as DATETIME2 with a DEFAULT constraint of SYSDATETIME(). During a bulk insert of 500 rows, the engineer notices that all 500 rows received the exact same CreatedDate value, but each TicketID is unique and sequential. What is the most likely explanation for the identical CreatedDate values?

Accepted Answer

SYSDATETIME() is nondeterministic and is evaluated once per statement, so all rows in the same INSERT statement get the same value.. Nondeterministic functions such as SYSDATETIME() used in a DEFAULT constraint are evaluated once per INSERT statement, and the resulting value is applied to every row in that statement. This is why all 500 rows share the same CreatedDate even though they were inserted together. The IDENTITY column still generates unique values because it is a separate mechanism. To obtain distinct timestamps per row, you would need a trigger, an application-generated value, or a row-by-row insert.

Answer

The IDENTITY property forces all other columns to use the same default value for referential integrity.

Answer

DATETIME2 has a precision of one second, so all rows inserted within the same second appear identical.

Answer

The DEFAULT constraint is evaluated once per batch, not once per row.