DS0-001 Practice Test 5 — 25 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.

Question 16

A database administrator is designing a schema for a financial application that must enforce that every account balance is never negative. The application inserts and updates balances frequently, and the DBA wants the rule enforced at the database level regardless of which application or script performs the change. Which database object should the DBA create to enforce this rule?

Accepted Answer

A CHECK constraint on the Balance column that verifies Balance >= 0.. A CHECK constraint is the appropriate database object because it declaratively enforces a Boolean condition on column values during every INSERT and UPDATE. It cannot be bypassed by applications or ad-hoc scripts, and the database engine optimizes its evaluation. Stored procedures and triggers can enforce rules procedurally but are more complex and, in the case of stored procedures, easily bypassed. A foreign key addresses referential integrity, not numeric ranges.

Answer

A stored procedure that all applications must call to modify balances.

Answer

A foreign key constraint referencing a Balances lookup table that contains only non-negative values.

Answer

A trigger on the table that rolls back any transaction resulting in a negative balance.

Question 17

A data engineer is designing a table in a relational database to store product inventory records. The business requires that the combination of 'ProductCode' and 'WarehouseID' uniquely identifies each row, and that no duplicate pairs are allowed. Which constraint should be applied to enforce this requirement?

Accepted Answer

PRIMARY KEY on ProductCode and WarehouseID combined. A composite PRIMARY KEY on ProductCode and WarehouseID is the correct choice because it enforces uniqueness of the combination and disallows NULLs, exactly matching the requirement that each pair uniquely identifies an inventory row. Other constraints either enforce referential integrity, validate row-level conditions, or apply uniqueness too narrowly.

Answer

FOREIGN KEY referencing the Product and Warehouse tables

Answer

UNIQUE constraint on ProductCode only

Answer

CHECK constraint comparing ProductCode and WarehouseID

Question 18

A database administrator is deploying a new PostgreSQL 15 cluster on Linux and wants to protect against data loss if a single disk in the data volume fails. The server has four identical 1 TB SSDs available. Which RAID level should the administrator configure for the PostgreSQL data directory?

Accepted Answer

RAID 10. RAID 10 mirrors data across pairs of disks and then stripes across those pairs, delivering redundancy plus the high random I/O throughput a PostgreSQL data directory requires. It tolerates at least one disk failure and often more, depending on which disks fail, while avoiding the parity write penalty of RAID 5 and the total lack of protection in RAID 0.

Answer

RAID 0

Answer

RAID 5

Answer

RAID 1

Question 19

A financial analyst runs a query that joins a 500-million-row transactions table to a 200-row currency table on a non-indexed currency code column. The optimizer chooses a nested loop join and the query runs for hours. Which execution strategy should the analyst encourage to improve performance most effectively?

Accepted Answer

Hash join, building a hash table on the small currency table. When one input is very small and the join column on the large input is unindexed, a hash join avoids repeated scans by building an in-memory hash table from the small table and probing it once per large-table row. Merge joins need sorted inputs, nested loops repeat inner scans, and cross joins explode the result set, so the hash join is the most effective strategy here.

Answer

Cross join followed by a WHERE filter on matching currency codes

Answer

Merge join, requiring both inputs to be sorted on currency code

Answer

Nested loop join with a larger sort area allocated to the session

Question 20

A junior DBA runs the following statement against a production table that contains millions of rows with an active workload:

TRUNCATE TABLE Sales.Transactions;

What is the most important characteristic of this operation that the junior DBA should have understood before executing it?

Accepted Answer

It removes all rows without firing DELETE triggers or logging individual row deletions, and the removal cannot normally be rolled back row by row.. TRUNCATE TABLE removes all rows by deallocating data pages rather than deleting rows individually. This means it is minimally logged, does not fire DELETE triggers, and cannot be rolled back row by row. For a production table with millions of rows and concurrent activity, the DBA must understand that recovery depends on backups or the surrounding transaction, not on per-row undo. The other options describe behaviors that belong to DROP TABLE, foreign key enforcement, or backup automation, none of which apply to TRUNCATE.

Answer

It automatically creates a full backup of the table before removing the rows, allowing point-in-time recovery of the truncated data.

Answer

It releases the table's clustered index and all nonclustered indexes, so any index must be recreated afterward.

Answer

It enforces all FOREIGN KEY constraints referencing the table, so it will always fail if child rows exist.