DS0-001 Practice Test 22 — 15 Questions

Question 1

A database team is designing a new table to store employee data. They need to ensure that the table structure supports efficient queries and maintains data integrity. Which two of the following statements about primary keys and indexes are true? (Choose two.)

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

Accepted Answer

A unique index can be created on a column that allows NULL values, and multiple NULLs are permitted in some DBMSs.. The true statements are that a primary key automatically creates a unique index, and that a unique index can allow multiple NULLs in some DBMSs. These reflect standard behaviors that affect both data integrity and query performance. The other statements are false because a table can have only one primary key, indexes can slow down data modifications, and foreign key indexing is not automatic in all systems.

Answer

Indexes always improve the performance of INSERT, UPDATE, and DELETE operations.

Answer

A foreign key constraint automatically creates an index on the foreign key column(s) in all DBMSs.

Answer

A table can have multiple primary keys to improve query performance.

Question 2

A data analyst needs to retrieve the average order value for each region from a sales table. The table has columns region, order_id, and order_total. Which SQL statement correctly returns one row per region with its average order value?

Accepted Answer

SELECT region, AVG(order_total) FROM sales GROUP BY region;. Per-region averages require grouping rows by region and applying AVG to order_total within each group. The GROUP BY clause creates one result row per distinct region, and AVG computes the mean for that region. Omitting GROUP BY yields a single overall aggregate, and using DISTINCT does not substitute for grouping because it operates on the final result set rather than partitioning rows for aggregation.

Answer

SELECT region, SUM(order_total)/COUNT(*) FROM sales;

Answer

SELECT region, AVG(order_total) FROM sales;

Answer

SELECT DISTINCT region, AVG(order_total) FROM sales;

Question 3

A database administrator is planning the deployment of a new database server. The organization requires that the database be able to recover from a failure with minimal data loss, and the Recovery Point Objective (RPO) is 5 minutes. Which backup strategy should the administrator implement?

Accepted Answer

Full backups taken once per day plus transaction log backups every 5 minutes.. To achieve an RPO of 5 minutes, transaction log backups must be taken at intervals no greater than 5 minutes, combined with periodic full backups. This allows recovery to a point in time just before the failure, minimizing data loss. Daily full backups alone are insufficient, and differential or snapshot backups at longer intervals do not meet the requirement.

Answer

Differential backups taken every 6 hours.

Answer

Full backups taken once per day.

Answer

Snapshot backups taken every hour.

Question 4

Which THREE of the following are standard database maintenance tasks?

Accepted Answer

Updating statistics. Updating statistics (B) is a standard maintenance task because the query optimizer relies on accurate statistics to generate efficient execution plans, and stale statistics lead to poor cardinality estimates and slow queries. Rebuilding indexes (D) is standard maintenance because index fragmentation degrades read performance and wastes space, so indexes are periodically rebuilt (or reorganized) to restore efficiency. Full database backups (E) are a core maintenance task since they provide the recoverability baseline required for disaster recovery and point-in-time restores. Creating new database servers (A) is a provisioning/deployment activity, not routine maintenance of an existing database, and changing the server IP address (C) is a network/infrastructure change unrelated to database upkeep.

Answer

Creating new database servers

Answer

Changing the server IP address

Question 5

A multinational bank must demonstrate that its data processing activities are lawful and documented. An auditor requests evidence that each processing purpose has a documented legal basis, defined retention, and identified data subjects. Which artifact should the data governance team produce?

Accepted Answer

A Record of Processing Activities (ROPA) maintained under GDPR Article 30.. The auditor wants per-purpose evidence of lawful basis, retention, and data-subject categories. That is the definition of a Record of Processing Activities, which governance teams maintain as the authoritative register for accountability. Diagrams, classification matrices, and continuity plans support related controls but do not contain the specific legal and retention attributes the auditor requested.

Answer

A network diagram showing where personal data flows between systems.

Answer

A data classification matrix that labels each column as public, internal, or confidential.

Answer

A business continuity plan describing recovery time objectives for core systems.

Question 6

During the pre-deployment phase, a team needs to estimate the hardware resources required for a new Oracle Database installation. Which tool should be used to simulate database workload and forecast resource consumption?

Accepted Answer

Oracle Database Replay. Oracle Database Replay captures a production workload and replays it on a test system, allowing DBAs to measure resource consumption and forecast hardware requirements before deployment. It is specifically designed for workload simulation and capacity planning. This makes it the correct tool for pre-deployment resource estimation.

Answer

Automatic Workload Repository (AWR)

Answer

Oracle Enterprise Manager Performance Hub

Answer

SQL Tuning Advisor

Question 7

A financial services firm must prove to regulators that its nightly ETL job loaded the same source data that was archived for a given business date, and that no record was altered after extraction. Which combination of practices best provides this non-repudiation evidence?

Accepted Answer

Compute a cryptographic hash of the extracted dataset, store it with the archive, and re-verify the hash before and after loading.. Non-repudiation of an ETL load requires content-level integrity evidence, not just operational success signals or access controls. Hashing the extract, storing the digest with the archive, and re-verifying before and after load creates a verifiable chain proving the loaded data matches the archived source and was not altered. Exit codes, TLS, and certificate authentication cannot demonstrate content equality.

Answer

Enable TLS on the connection between the ETL server and the source database.

Answer

Require the ETL service account to authenticate with a client certificate and rotate it quarterly.

Answer

Store the ETL job's exit code and row counts in a control table after each run.

Question 8

A financial services company is building a data governance program and must classify data assets so that protection controls map correctly to sensitivity. Which two actions are essential first steps in establishing data classification? (Choose two.)

Accepted Answer

Inventory data assets and assign an owner and a classification label to each identified asset.. Classification begins with agreed sensitivity tiers and a complete asset inventory that assigns owners and labels. Those two actions create the vocabulary and the record of what is sensitive, enabling controls, retention, and access decisions to be tied to data value and risk rather than applied blindly across all systems.

Answer

Deploy a data loss prevention tool that blocks outbound transfers of records matching regular expressions for account numbers.

Answer

Require multifactor authentication for all employees accessing any corporate system.

Answer

Encrypt every database at rest using the vendor's transparent data encryption feature.

Question 9

A data analyst is querying a sales table with columns order_id, order_date, customer_id, and order_total. They need to return each order along with the running total of order_total for each customer, ordered by order_date. Which SQL feature should they use?

Accepted Answer

A window function with PARTITION BY customer_id ORDER BY order_date and a SUM() aggregate. The running total per customer requires preserving each order row while accumulating order_total in date order within each customer. A window function with PARTITION BY customer_id and ORDER BY order_date, using SUM() as the window aggregate, achieves this in one pass and returns the detail rows plus the cumulative value. Grouping, self-joins, or correlated subqueries either collapse rows or perform poorly and ambiguously.

Answer

A GROUP BY customer_id with SUM(order_total) and ORDER BY order_date

Answer

A self-join on customer_id with a condition that the joined order_date is less than or equal to the outer order_date, then SUM the joined order_total

Answer

A correlated subquery that selects SUM(order_total) from the same table where customer_id matches and order_date is less than the outer order_date

Question 10

During a database deployment, the administrator needs to ensure that audit logs are captured for all privileged user actions. Which component should be configured?

Accepted Answer

Database Audit Specification. To ensure audit logs are captured for all privileged user actions, the administrator should configure a Database Audit Specification. This feature in SQL Server allows auditing of specific database-level actions, such as SELECT, INSERT, UPDATE, DELETE, and EXECUTE by privileged users. It captures these actions to the audit log for compliance and monitoring.

Answer

Row-Level Security

Answer

Data Masking

Answer

Query Store

Question 11

A database administrator is preparing the deployment plan for a new transactional database that must support point-in-time recovery and minimize data loss after a storage failure. The organization has approved additional storage for this purpose. Which TWO deployment measures best meet these requirements? (Choose two.)

Accepted Answer

Enable archive logging so completed transaction log files are copied to a separate volume.. Point-in-time recovery depends on a continuous log chain, which requires the full recovery model plus scheduled log backups, and on protecting those logs from the same failure that destroys the data. Archiving completed log files to a separate volume keeps the chain available after a media failure. Weekly full backups alone, co-located logs, and autogrowth do not provide the ability to restore to a chosen moment.

Answer

Enable automatic growth on the data files with a small increment so the database never runs out of space.

Answer

Place the transaction log on the same volume as the data files to reduce write latency.

Answer

Schedule a full backup once per week and rely on the database's automatic crash recovery for the intervening days.

Question 12

A data analyst is writing a query to find the average order value (AOV) per customer from an orders table that includes cancelled and returned orders. They want to exclude those orders from the calculation but still show all customers, including those with no valid orders. Which SQL construct should they use to ensure customers with no valid orders appear with a NULL or zero AOV?

Accepted Answer

LEFT JOIN between customers and orders with the order status filter in the ON clause. To include all customers while excluding certain orders from the aggregation, the filter must be applied in the ON clause of a LEFT JOIN. This keeps all left-table rows and only joins matching right-table rows that satisfy the condition. Customers without valid orders retain NULLs, which can be handled in the SELECT list to display zero or NULL AOV.

Answer

LEFT JOIN between customers and orders with a WHERE clause filtering out cancelled and returned orders

Answer

FULL OUTER JOIN between customers and orders

Answer

INNER JOIN between customers and orders

Question 13

Which TWO actions constitute good data lifecycle management?

Accepted Answer

Defining data retention policies. Option B is correct because defining data retention policies is a core element of data lifecycle management: it specifies how long each data category must be kept to satisfy legal, regulatory, and business requirements, and when it should be securely disposed of. Option E is correct because automated archiving of stale data moves infrequently accessed records to lower-cost storage tiers while preserving them for retention or compliance needs, reducing primary storage load and risk. The unmarked options do not belong: A violates least-privilege and confidentiality principles by exposing all data to all users, C merely raises logging verbosity (debug level) and increases log volume without governing data lifecycle, and D is a destructive blanket reformatting of drives that ignores retention requirements and would cause data loss.

Answer

Allowing all users access to all data

Answer

Increasing database logging level to debug

Answer

Reformatting all drives annually

Question 14

A data governance committee is defining the requirements for a new master data management program covering customer records across four business units. Which two practices are essential to maintain trustworthy golden records? (Choose two.)

Accepted Answer

Define and enforce data stewardship roles with clear accountability for each customer data domain.. Trustworthy golden records require both accountability and mechanics. Stewardship roles ensure someone owns decisions about customer data, while match, merge, and survivorship rules determine how duplicates are consolidated and which values prevail. Open editing, inheriting one unit's system, or collapsing to a single application do not supply the governance and quality logic that make master data reliable across business units.

Answer

Store the golden records only in the source system of the business unit with the largest customer base.

Answer

Replace all source systems with a single application so that only one copy of customer data exists.

Answer

Grant every business unit write access to the golden record so they can correct errors immediately.

Question 15

Which THREE of the following items should be monitored to identify performance bottlenecks in a database?

Accepted Answer

Disk latency (I/O wait). Disk latency (I/O wait) (A) is correct because high I/O wait times directly indicate storage bottlenecks where the database engine is waiting on reads/writes, a primary cause of slow query performance. Batch requests per second (B) is correct because it measures the throughput of SQL batches processed by the instance, revealing whether the workload is saturating the server or degrading under load. CPU utilization (E) is correct because sustained high CPU usage shows the database engine is compute-bound, often from expensive queries, missing indexes, or inefficient plans. The database name length (C) is irrelevant since naming conventions have no effect on query execution or resource consumption, and server room temperature (D) is a facilities/environmental metric, not a database performance counter, though extreme heat could indirectly affect hardware.

Answer

The database name length

Answer

Server room temperature