DS0-001 Practice Test 9 — 10 Questions

Question 1

Which type of join should be used to return all records from the 'Left' table and only matching records from the 'Right' table?

Accepted Answer

LEFT JOIN. A LEFT JOIN (LEFT OUTER JOIN) returns every row from the left table regardless of whether a match exists in the right table, and for unmatched rows it fills right-table columns with NULLs. This is exactly the behavior described: all left records plus only matching right records.

Answer

INNER JOIN

Answer

RIGHT JOIN

Answer

FULL JOIN

Question 2

A company requires that all database backups be immutable for 7 years to meet regulatory audits. How can this be achieved?

Accepted Answer

Immutable WORM Storage Policies. Immutable WORM (Write Once, Read Many) storage policies enforce retention at the storage layer, preventing modification or deletion of backup objects for the configured period — commonly 7 years for regulatory compliance. This satisfies audit requirements because even administrators cannot alter or delete the data before the retention period expires.

Answer

Database Replication

Answer

Regular manual deletion

Answer

Standard RAID 10

Question 3

Which dashboarding practice helps prevent users from being overwhelmed by too much data at once?

Accepted Answer

Using interactive filters. Interactive filters let users narrow the dataset to what is relevant to them, reducing cognitive overload and preventing the dashboard from displaying every metric at once. This is a core dashboard design principle: progressive disclosure through filtering, slicing, and drill-down. It keeps the initial view clean while still giving access to detail on demand.

Answer

Removing all tooltips

Answer

Adding more charts per page

Answer

Increasing font size

Question 4

A database administrator notices that the transaction log file is nearly full. Which operation will shrink the file to free up space?

Accepted Answer

DBCC SHRINKFILE. DBCC SHRINKFILE is the targeted command that reduces the size of a specific database file — including a transaction log file — by moving the file's end marker inward and releasing unused pages back to the OS. It is the correct tool when a DBA needs to reclaim space from one named log file without affecting other files in the database.

Answer

DBCC SHRINKDATABASE

Answer

BACKUP LOG ... WITH TRUNCATE

Answer

ALTER DATABASE ... SET LOGFILE

Question 5

A data analyst is using a window function to rank products by sales within each region. They write: RANK() OVER (PARTITION BY region ORDER BY sales DESC). They notice that products with identical sales receive the same rank, but the next rank is skipped (e.g., 1,2,2,4). They want to assign consecutive ranks without gaps. Which function should they use instead?

Accepted Answer

DENSE_RANK(). DENSE_RANK() assigns the same rank to tied values and does not skip ranks afterward, producing consecutive integers. RANK() skips ranks after ties, ROW_NUMBER() ignores ties, and NTILE and PERCENT_RANK serve different purposes. DENSE_RANK is the correct function for consecutive ranks without gaps.

Answer

ROW_NUMBER()

Answer

PERCENT_RANK()

Answer

NTILE(4)

Question 6

A database architect is tasked with choosing a database model for a system that requires highly variable, semi-structured data storage (e.g., JSON documents). Which model provides the best flexibility for this requirement?

Accepted Answer

Document-oriented. Document-oriented databases store data in flexible, semi-structured formats like JSON or XML, allowing for schema-less design and easy evolution of data structures. This makes them ideal for highly variable data such as JSON documents.

Answer

Key-Value

Answer

Relational (RDBMS)

Answer

Graph

Question 7

A database administrator wants to check the current connection count. Which system catalog or dynamic management view would typically provide this information?

Accepted Answer

Dynamic Management Views. Dynamic Management Views (DMVs) in SQL Server, such as sys.dm_exec_connections and sys.dm_exec_sessions, expose real-time server state including current connection counts, session details, and resource usage. They are the standard mechanism for monitoring live connection activity because they query the running instance's internal state rather than static metadata. This makes DMVs the correct choice for checking current connection count.

Answer

Information Schema

Answer

System Tables

Answer

Audit Logs

Question 8

An analyst notices that a database query is performing full table scans even though a WHERE clause is present on a indexed column. What is a likely reason for this?

Accepted Answer

Using a function on the indexed column. When a function is applied to an indexed column in a WHERE clause (e.g., WHERE UPPER(name) = 'SMITH'), the optimizer cannot use the index because the index stores the raw column values, not the function results. This forces a full table scan. The fix is to rewrite the predicate to avoid wrapping the column in a function or to create a function-based/computed-column index.

Answer

Too many rows match the criteria

Answer

The table is too small

Answer

The index is fragmented

Question 9

To implement a secure 'Multi-Tenant' architecture, which design approach is most effective for data separation?

Accepted Answer

Separate schemas. Separate schemas provide strong logical isolation by giving each tenant its own namespace of objects, enabling independent permission management and reducing cross-tenant data exposure. This approach balances security and manageability better than sharing columns, which risks accidental data leakage between tenants.

Answer

Shared columns

Answer

Indexing

Answer

Stored procedures

Question 10

Which operator is used to search for a pattern in a string column?

Accepted Answer

LIKE. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

Answer

IN

Answer

CONTAINS

Answer

MATCH