DS0-001 Practice Test 20 — 15 Questions

Question 1

A healthcare analytics team stores patient records in a PostgreSQL database. A new analyst joins and must be able to query only the columns patient_id, visit_date, and diagnosis_code from the encounters table, but must be blocked from viewing patient_name and ssn. Which mechanism should the database administrator implement to meet this requirement?

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

Accepted Answer

Create a view that selects only the permitted columns and grant SELECT on the view to the analyst while revoking table-level access.. Restricting an analyst to specific columns is achieved by creating a view that projects only the approved fields and granting SELECT on that view, while revoking direct table access. This enforces least privilege at the database layer and prevents the analyst from querying sensitive columns such as patient_name or ssn through the base table.

Answer

Enable row-level security (RLS) policies on the encounters table for the analyst role.

Answer

Apply Transparent Data Encryption (TDE) to the encounters table and share the encryption key with the analyst.

Answer

Grant SELECT on all columns of the encounters table and rely on the analyst to avoid selecting sensitive fields.

Question 2

A developer needs to retrieve a list of all customers who have placed at least one order, along with the total amount spent by each. The 'Customers' table has one row per customer, and the 'Orders' table has one row per order with a 'CustomerID' foreign key. Which SQL construct should the developer use?

Accepted Answer

SELECT with an INNER JOIN between Customers and Orders, grouped by customer, with SUM on order amount. An INNER JOIN between Customers and Orders returns only customers with matching orders, and grouping by customer with SUM on the order amount yields the total spent per customer. The other joins either include unmatched rows, create a Cartesian product, or group at the wrong level.

Answer

SELECT with a CROSS JOIN between Customers and Orders and a HAVING clause on total amount

Answer

SELECT with a RIGHT JOIN from Customers to Orders and a GROUP BY on order ID

Answer

SELECT with a LEFT JOIN from Customers to Orders and a WHERE clause excluding NULL order amounts

Question 3

A database administrator is troubleshooting a query that occasionally runs slowly. The query uses a parameterized stored procedure. The execution plan shows that sometimes the query uses an index seek and sometimes a clustered index scan. The DBA suspects parameter sniffing. Which action should the DBA take to mitigate the issue while preserving the ability to use the optimal plan for each execution?

Accepted Answer

Add OPTION (RECOMPILE) to the query inside the stored procedure.. Parameter sniffing causes the optimizer to reuse a plan compiled for a specific parameter value, which may be suboptimal for other values. Adding OPTION (RECOMPILE) forces a new plan for each execution, allowing the optimizer to use the actual parameter values and choose the best plan per execution, directly mitigating the issue while retaining flexibility.

Answer

Enable Trace Flag 2371 at the server level.

Answer

Use the OPTIMIZE FOR UNKNOWN query hint.

Answer

Create a plan guide to force the index seek plan.

Question 4

A retail company is designing a data warehouse star schema for sales analysis. The team must choose attributes for the fact table and the dimension tables. Which TWO of the following should be placed in the fact table rather than a dimension table? (Choose two.)

Accepted Answer

Quantity of units sold in each transaction line. Fact tables hold numeric, additive measures that analysts aggregate, such as quantity sold and extended sales amount. Dimension tables hold descriptive attributes that provide context for those measures, including product category, store city, and customer loyalty tier. Separating measures from descriptors keeps the star schema efficient and supports flexible slicing across dimensions.

Answer

Store city where each sale occurred

Answer

Customer loyalty tier at time of purchase

Answer

Product category name assigned to each item

Question 5

You are planning the disk layout for a SQL Server production database. Which THREE best practices should you follow to optimize storage performance?

Accepted Answer

Place transaction log files on high-speed, low-latency storage.. Option B is correct because transaction log files (.ldf) are written sequentially and are highly latency-sensitive, so placing them on high-speed, low-latency storage (e.g., SSD/NVMe or a dedicated RAID 10 array) minimizes log write waits and improves transaction throughput. Option D is correct because keeping backups on separate physical volumes from the data files prevents a single disk failure from destroying both the database and its backups, and it avoids backup I/O competing with production data I/O. Option E is correct because isolating data files from the OS volume prevents OS paging, updates, and system I/O from contending with database reads/writes, and it also protects the database from OS volume corruption or reinstallation. Option A is incorrect because co-locating .mdf and .ldf on the same physical drive creates I/O contention between random data access and sequential log writes and removes fault isolation. Option C is incorrect because storing backups on the same drive as the data files means a single drive failure can destroy both the live database and its backups, and backup writes will degrade production performance.

Answer

Place data files (.mdf) and transaction log files (.ldf) on the same physical drive.

Answer

Place backup files on the same drive as the database data files.

Question 6

A database administrator is deploying a PostgreSQL 15 cluster that will serve a global application with users in North America, Europe, and Asia. The requirement is that reads be served with low latency from each region while writes are accepted in only one region. Which deployment architecture should the administrator choose?

Accepted Answer

A primary in North America with physical streaming replicas in Europe and Asia, plus a connection router that directs reads to the nearest replica.. Streaming physical replicas in each region place a full copy of the data close to local users, and a connection router sends read queries to the nearest replica, minimizing read latency. Writes continue to flow to the single primary, so the single-write-region constraint is preserved while read performance improves globally.

Answer

A single primary in North America with cascading streaming replicas in Europe and Asia.

Answer

A single primary in North America with synchronous replication to a standby in Europe.

Answer

Multi-master logical replication with writable nodes in all three regions.

Question 7

A user wants to add a new column to an existing table named 'Employees'. Which SQL statement should be executed?

Accepted Answer

ALTER TABLE. The ALTER TABLE statement is the standard SQL DDL command used to modify the structure of an existing table, including adding, dropping, or modifying columns. To add a new column to 'Employees', the correct syntax is ALTER TABLE Employees ADD column_name data_type.

Answer

MODIFY TABLE

Answer

UPDATE TABLE

Answer

CREATE COLUMN

Question 8

Which TWO indicators should be monitored when testing a database deployment to ensure it is correctly sized for performance?

Accepted Answer

Disk I/O latency. Disk I/O latency (B) is a correct indicator because database performance is heavily bound by storage throughput and response time; high read/write latency directly signals that the deployment's disk subsystem is undersized for the workload. CPU utilization (C) is also correct because sustained high processor usage indicates the database engine is CPU-bound, meaning the instance is not correctly sized in terms of compute capacity. Together these two metrics reveal whether the storage and compute tiers meet the performance demands of the database. The total number of network users (A) is not a direct measure of database sizing, since user count alone says nothing about query load or resource consumption. The number of open database connections (D) reflects concurrency but not whether the underlying resources are adequately provisioned. Server uptime (E) only indicates availability over time and has no bearing on performance sizing.

Answer

Total number of users on the network

Answer

Number of open database connections

Answer

Server uptime

Question 9

A report needs to identify the 'top 10' customers by total spend. In SQL, which clause would you use to return only the first 10 results after sorting by spend descending?

Accepted Answer

LIMIT. The LIMIT clause restricts the number of rows returned by a query after the result set has been sorted. Combined with ORDER BY spend DESC, LIMIT 10 returns only the top 10 highest-spending customers. WHERE filters rows before aggregation/sorting, OFFSET skips rows, and HAVING filters grouped results — none of these cap the row count.

Answer

WHERE

Answer

OFFSET

Answer

HAVING

Question 10

Which SQL command is used to permanently remove all rows from a table while keeping the table definition intact?

Accepted Answer

TRUNCATE. TRUNCATE is a DDL operation that removes all rows from a table in a single transaction-efficient operation while preserving the table's structure, columns, constraints, and indexes. It deallocates the data pages rather than logging each row deletion, making it far faster than DELETE for clearing a table. The table definition remains intact and ready for new inserts.

Answer

DELETE

Answer

DROP

Answer

REMOVE

Question 11

Which TWO of the following are common symptoms of an undersized transaction log file?

Accepted Answer

Transaction log full errors. An undersized transaction log file commonly manifests as transaction log full errors (Option C), because the log cannot grow fast enough or is capped, so SQL Server raises error 9002 ("The transaction log for database is full") when it cannot accommodate new log records. It also causes frequent auto-growth events (Option D), since the log repeatedly hits its size limit and must expand in small increments, producing many VLFs and I/O stalls. Options A, B, and E are not typical symptoms of an undersized log: memory fragmentation relates to buffer pool or OS memory allocation, excessive CPU usage stems from query or workload inefficiency rather than log sizing, and slow index reading is an I/O or indexing issue unrelated to transaction log capacity.

Answer

Memory fragmentation

Answer

Excessive CPU usage

Answer

Slow index reading

Question 12

Which TWO of the following are legitimate ways to optimize SQL query performance?

Accepted Answer

Creating indexes on frequently filtered columns. Option C is correct because creating indexes on frequently filtered columns (those used in WHERE, JOIN, and ORDER BY clauses) allows the database engine to locate rows via B-tree or hash lookups instead of full table scans, dramatically reducing I/O and query time. Option D is correct because replacing subqueries with JOINs where possible lets the optimizer build a single, more efficient execution plan, often avoiding repeated subquery evaluation (especially correlated subqueries) and enabling better use of indexes and join algorithms. Option A is wrong because consolidating data into a single large table increases scan costs, worsens contention, and prevents proper normalization and partitioning. Option B is wrong because SELECT * retrieves unnecessary columns, increasing I/O, memory, and network overhead and preventing covering-index optimizations. Option E is wrong because deleting system statistics removes the optimizer's cardinality and distribution data, leading to poor execution plans and degraded performance.

Answer

Using a single large table

Answer

Selecting all columns (SELECT *)

Answer

Deleting all system statistics

Question 13

During database testing, a developer finds that a specific query is performing poorly due to a lack of statistics. What action should be taken to update the database optimizer's knowledge of the table data distribution?

Accepted Answer

Update statistics. When a query performs poorly due to outdated or missing statistics, the optimizer lacks accurate information about data distribution and may choose a suboptimal execution plan. Running UPDATE STATISTICS refreshes the optimizer's knowledge of column value distributions, enabling better cardinality estimates and plan selection.

Answer

Run a database consistency check (DBCC)

Answer

Clear the plan cache

Answer

Rebuild the table indexes

Question 14

To improve read performance on a table frequently queried by a specific column, which database object should be created?

Accepted Answer

Index. An index on the frequently queried column allows the database engine to locate rows without scanning the entire table, dramatically improving read performance for SELECT queries filtering or joining on that column. Indexes create a sorted structure (B-tree) that enables fast seeks and range scans.

Answer

View

Answer

Stored Procedure

Answer

Trigger

Question 15

A database server is consistently hitting high CPU usage. You suspect an unoptimized query is causing a full table scan. Which tool should you use to capture the execution plan of currently running queries?

Accepted Answer

Extended Events. Extended Events is a lightweight, highly scalable event-handling system in SQL Server that can capture execution plans of currently running queries. It provides detailed diagnostic information, including query plans, waits, and resource usage, making it the ideal tool for identifying full table scans and other performance issues.

Answer

Performance Monitor

Answer

SQL Server Configuration Manager

Answer

Windows Event Viewer