DS0-001 Practice Test 2 — 30 Questions

Question 1

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 2

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 3

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 4

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 5

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 6

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 7

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 8

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 9

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 10

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 11

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 12

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 13

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 14

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 15

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

Question 16

A data analyst is preparing a query to compare average order values across customer segments. Before running the analysis, the analyst reviews the dataset and finds that the OrderAmount column has missing values and extreme outliers. Which TWO actions should the analyst take to improve the reliability of the comparison? (Choose two.)

Accepted Answer

Handle missing OrderAmount values explicitly, for example by excluding them or imputing them with a documented method.. Reliable segment comparisons depend on consistent handling of missing values and on measures that are not dominated by extreme outliers. Explicitly deciding how to treat NULLs keeps the denominator consistent across segments, while investigating outliers and using robust statistics such as medians or winsorized means prevents a few transactions from skewing results. Changing data types, removing segmentation, or adjusting display precision does not improve the analysis.

Answer

Convert OrderAmount to a VARCHAR data type so that non-numeric entries can be stored alongside numbers.

Answer

Remove the customer segment column and analyze the entire population as a single group.

Answer

Increase the number of decimal places displayed in the report to make the averages appear more precise.

Question 17

Which SQL clause is used to sort the result of a query by one or more columns?

Accepted Answer

ORDER BY. The ORDER BY clause in SQL is the standard, ANSI-defined mechanism for sorting the result set of a SELECT statement by one or more columns, in ascending (ASC, the default) or descending (DESC) order. It is applied after the result set is produced and can reference column names, aliases, or positional ordinals. No other clause in standard SQL performs result-set sorting.

Answer

SORT BY

Answer

ARRANGE BY

Answer

GROUP BY

Question 18

A database administrator is implementing an auditing solution for a SQL Server database that must track both successful and failed logins, as well as data modification statements on a specific table. The solution must minimize performance overhead and store audit data in a secure, tamper-evident format. (Choose two.)

Accepted Answer

Create a SQL Server Audit with an audit specification at the server level to capture failed and successful logins.. SQL Server Audit with a server-level specification for logins and a database-level specification for table modifications provides granular, low-overhead auditing. It can write to a secure target like the Windows Security log or a file with restricted access, and it is designed for tamper-evidence. C2 audit mode is heavy and deprecated, Profiler is not an auditing tool, and DDL triggers do not capture logins or DML.

Answer

Use SQL Server Profiler to trace logins and data modifications, and save the trace to a file.

Answer

Enable C2 audit mode to capture all successful and failed logins and data modifications.

Answer

Implement a DDL trigger that logs all login events and data modifications to a custom table.

Question 19

Which document outlines the 'Data Owner' responsibilities in a data governance framework?

Accepted Answer

Data Governance Charter. The Data Governance Charter is the foundational document that defines the data governance framework, including roles and responsibilities such as the Data Owner, Data Steward, and Data Custodian. It establishes accountability, decision rights, and the operating model for data management across the organization.

Answer

Server Inventory List

Answer

Database Schema

Answer

Network Topology Map

Question 20

A retail company's data governance team is defining roles for its new data stewardship program. The team needs to assign responsibility for day-to-day management of data quality, including resolving data issues and documenting business rules. Which role should be assigned this responsibility?

Accepted Answer

Data steward. The data steward role is explicitly designed for operational data management: defining business rules, monitoring quality metrics, and resolving data issues within a domain. Other roles such as the CISO, DBA, and DPO have security, platform, and privacy mandates that do not include day-to-day data quality stewardship.

Answer

Chief Information Security Officer (CISO)

Answer

Database administrator (DBA)

Answer

Data protection officer (DPO)