DS0-001 Practice Test 2 — 25 Questions

Question 1

A company is planning to migrate a legacy on-premises database to the cloud. Which assessment should be conducted to ensure the application compatibility with the target managed database service?

Accepted Answer

Database compatibility assessment. A database compatibility assessment evaluates whether the legacy database's schema, data types, stored procedures, triggers, and application dependencies will work on the target managed service. Managed cloud databases often lack certain features (e.g., SQL Server Agent, cross-database queries, CLR, or specific system stored procedures), so this assessment identifies incompatibilities before migration and informs remediation planning.

Answer

Network throughput analysis

Answer

Capacity planning report

Answer

Data integrity audit

Question 2

You are deploying a database that requires compliance with FIPS 140-2. What must be configured to ensure the database meets this security standard?

Accepted Answer

FIPS-mode encryption. FIPS 140-2 is a U.S. government standard for cryptographic modules, so compliance requires that the database use FIPS-validated cryptographic modules — typically enabled via a FIPS mode setting that forces all encryption operations through validated algorithms and libraries. Without FIPS-mode encryption, the database may use non-validated ciphers even if encryption is otherwise enabled.

Answer

Database hardening scripts

Answer

User password rotation policies

Answer

Multi-factor authentication (MFA)

Question 3

Which database backup strategy is recommended to ensure that you can restore a database to a specific point in time?

Accepted Answer

Full and Transaction Log backups. Point-in-time recovery requires a full backup as the base plus a continuous chain of transaction log backups. Restoring the full backup and then replaying transaction logs up to a specific timestamp (STOPAT) reconstructs the database to any moment within the log chain's coverage.

Answer

Differential backups only

Answer

Full backups only

Answer

Copy-only backups

Question 4

An administrator needs to restrict access to a specific S3 bucket so that only the Finance team can view files. Which configuration is the most effective approach?

Accepted Answer

Configure an S3 Bucket Policy. An S3 Bucket Policy is a resource-based IAM policy attached directly to the bucket that defines who can access it and under what conditions. By specifying the Finance team's IAM principals and granting only them s3:GetObject permissions, the administrator can effectively restrict access to the bucket.

Answer

Update the VPC Route Table

Answer

Enable Public Access Block

Answer

Enable S3 Versioning

Question 5

A cloud data platform team must prove to auditors that only approved personnel accessed a regulated dataset during a specific incident window. The platform uses role-based access control and stores query history in the warehouse. Which evidence source most directly demonstrates authorized access during that window?

Accepted Answer

The warehouse access history and query logs filtered to the dataset and incident window, correlated with role grants.. Auditors need object-level access evidence tied to identities and time. Warehouse access history and query logs filtered to the dataset and incident window, correlated with the role grants in effect, show exactly which principals read the regulated data and whether each read was authorized, which is the most direct and defensible proof.

Answer

The change management tickets approved during the incident window for the platform team.

Answer

The data catalog's lineage graph showing which downstream reports consume the regulated dataset.

Answer

The identity provider's sign-in logs showing which users authenticated and from which IP addresses.

Question 6

A security team is implementing an access management strategy. Which THREE of the following are considered essential components of an effective Identity and Access Management (IAM) framework?

Accepted Answer

Authentication mechanisms. Authentication mechanisms (A) are essential because IAM must first verify a subject's claimed identity through credentials such as passwords, MFA, certificates, or biometrics before any access decision can be made. Authorization policies (D) are equally essential, as they define what an authenticated identity is permitted to do, typically expressed through role-based access control (RBAC), attribute-based access control (ABAC), or least-privilege rules. Centralized logging and auditing (C) are also core to IAM because they provide the accountability trail—recording authentication events, privilege changes, and access attempts—needed for compliance, forensic investigation, and continuous monitoring. Network cabling standards (B) belong to physical infrastructure design (e.g., Cat6, fiber) and are unrelated to identity or access management. Database partitioning (E) is a data storage and performance technique for splitting tables or schemas, not an IAM component.

Answer

Network cabling standards

Answer

Database partitioning

Question 7

A data analyst is reviewing a query that joins three tables: orders, customers, and products. The query returns more rows than expected. Which TWO of the following are likely causes of the row multiplication? (Choose two.)

Accepted Answer

The query includes a many-to-many relationship between orders and products without an intermediate bridge table. Row multiplication in multi-table joins typically stems from join cardinality mismatches. A non-unique key on the dimension side causes one fact row to match multiple dimension rows, and a many-to-many relationship without a bridge table causes combinatorial expansion. Join type changes, NULL filters, and sorting affect inclusion or order but not the number of matched rows, so they do not explain the inflated result set.

Answer

The query filters on a column that contains NULL values in the products table

Answer

The query orders the result by a column with duplicate values

Answer

The query uses an INNER JOIN instead of a LEFT JOIN between orders and customers

Question 8

A database administrator is asked to create a new database that will store historical data and will not be updated frequently. The database will be read-only most of the time, and the administrator wants to minimize storage space and improve query performance for large scans. Which SQL Server feature should the administrator implement?

Accepted Answer

Columnstore index. Columnstore indexes use columnar storage and compression, which significantly reduces storage space and improves performance for large scans and analytical queries. They are optimized for read-only or infrequently updated data, making them ideal for historical data. Clustered, nonclustered, and filtered indexes are row-based and do not offer the same compression and scan performance benefits.

Answer

Nonclustered index

Answer

Filtered index

Answer

Clustered index

Question 9

A DBA is configuring an Always On Availability Group in SQL Server. To ensure that the secondary node can be used for read-only reporting tasks, which setting must be correctly configured in the Availability Group replica settings?

Accepted Answer

Readable Secondary. The Readable Secondary setting in the Availability Group replica configuration determines whether a secondary replica can accept read-only connections. Setting it to 'Read-intent only' or 'Yes' allows reporting queries to be routed to the secondary, offloading the primary. Without this setting, the secondary is not accessible for read-only workloads even if it is synchronized.

Answer

Automatic Failover

Answer

Synchronous Commit

Answer

Primary Role

Question 10

An organization uses a 'bring your own key' (BYOK) model for database encryption. What is the primary benefit?

Accepted Answer

Increased control over encryption keys. The primary benefit of BYOK is increased control over encryption keys: the organization generates and manages its own keys, which are used by the cloud provider for encryption at rest. This gives the organization greater security oversight, the ability to revoke access, and compliance with data sovereignty requirements.

Answer

Easier database installation

Answer

Improved query performance

Answer

Lower storage costs

Question 11

Which DML statement is used to update existing records in a database table?

Accepted Answer

UPDATE. The UPDATE statement modifies the data of existing rows in a table.

Answer

ALTER

Answer

MODIFY

Answer

INSERT

Question 12

You are migrating an on-premises Oracle database to Oracle Cloud Infrastructure (OCI) using a tool that requires the least amount of downtime while keeping the source and destination databases in sync. Which method should you select?

Accepted Answer

Implement Oracle GoldenGate for continuous logical replication.. Oracle GoldenGate performs continuous logical replication by mining redo/archive logs on the source and applying changes to the target, keeping both databases in sync with minimal downtime. This makes it the standard choice for near-zero-downtime migrations where the source must remain available until cutover.

Answer

Perform a cold backup and restore to an OCI block storage volume.

Answer

Use Data Pump Export/Import with manual application of incremental backups.

Answer

Use SQL Developer to manually generate and execute insert scripts for all tables.

Question 13

Which THREE of the following are common SQL constraints?

Accepted Answer

PRIMARY KEY. PRIMARY KEY (C) is a common SQL constraint that uniquely identifies each row in a table and implicitly enforces NOT NULL and uniqueness. UNIQUE (D) is a common SQL constraint that ensures all values in a column or set of columns are distinct, though it typically permits NULL values. CHECK (E) is a common SQL constraint that restricts the values a column can accept based on a Boolean expression, such as CHECK (age >= 18). GROUP BY (A) is a SELECT clause used for aggregating rows into groups, not a constraint, and JOIN (B) is a query operation that combines rows from two or more tables, also not a constraint.

Answer

GROUP BY

Answer

JOIN

Question 14

A healthcare analytics team stores patient records in a PostgreSQL database. Auditors require that any change to the 'diagnosis' column be traceable to a specific database user and timestamp, without relying on application-level logging. Which mechanism should the data engineer implement to meet this requirement?

Accepted Answer

Create an AFTER UPDATE trigger that writes old and new values plus current_user and now() into an audit table.. Database-level auditing is required when changes must be attributable to a specific user and timestamp independent of the application. An UPDATE trigger writing old and new values along with current_user and now() captures this at the engine, covering both application and direct SQL changes. Access controls and statement logging do not produce the per-change, queryable evidence the auditors demand.

Answer

Grant UPDATE on the diagnosis column only to a dedicated service role and revoke it from others.

Answer

Enable PostgreSQL row-level security policies on the patient table.

Answer

Configure PostgreSQL to write all statements to the server log with log_statement = 'all'.

Question 15

A database administrator needs to ensure that a query calculating running totals is performant. Which window function clause is essential to order the calculation sequence?

Accepted Answer

ORDER BY. The ORDER BY clause inside the OVER() specification defines the logical ordering of rows for the window function, which is essential for running totals because the cumulative sum must be computed in a defined sequence. Without ORDER BY, the window frame defaults to the entire partition and the running total is meaningless. ORDER BY establishes the row sequence that makes the cumulative calculation deterministic.

Answer

GROUP BY

Answer

PARTITION BY

Answer

ROWS BETWEEN

Question 16

Which database object is specifically designed to enforce referential integrity between two tables?

Accepted Answer

Foreign Key. A Foreign Key constraint is designed to ensure that data in one table matches valid entries in another table, maintaining referential integrity.

Answer

Index

Answer

Primary Key

Answer

Unique Constraint

Question 17

Which tool provides a graphical interface for managing SQL Server instances, including configuring security and creating maintenance plans?

Accepted Answer

SQL Server Management Studio. SQL Server Management Studio (SSMS) is the standard GUI tool for SQL Server management.

Answer

Performance Monitor

Answer

SQL Server Profiler

Answer

SQL Server Configuration Manager

Question 18

A healthcare analytics team stores patient records in a PostgreSQL database. An auditor requires that any change to a patient's diagnosis code be permanently recorded with the identity of the user who made the change, and that this record cannot be altered or deleted by that same user. Which mechanism should the database administrator implement?

Accepted Answer

An append-only audit table populated by triggers. Auditability of modifications requires capturing the old and new values plus the acting user in a store that ordinary users cannot rewrite. Database triggers on the diagnosis table can write such records to a separate append-only audit table, and revoking UPDATE and DELETE on that table preserves integrity. Encryption and privilege restrictions limit access but generate no durable change history for the auditor.

Answer

Row-level security policies on the diagnosis table

Answer

Transparent data encryption on the tablespace

Answer

A column-level GRANT restricting diagnosis updates to the clinical role

Question 19

What is the primary function of an index in a database?

Accepted Answer

To speed up data retrieval. Indexes are designed to speed up data retrieval by providing a faster lookup mechanism than scanning every row in a table.

Answer

To enforce data encryption

Answer

To reduce disk space usage

Answer

To manage user permissions

Question 20

Which THREE of the following are common types of database backups?

Accepted Answer

Full. Full, differential, and transaction log backups are the standard backup types used in professional database environments.

Answer

Snapshot

Answer

Hardware copy