DS0-001 Practice Test 1 — 30 Questions

Question 1

You are reviewing a deployment plan for a multi-tenant database. To ensure security, you want to ensure that each tenant's data is logically isolated. Which strategy is most effective for this?

Accepted Answer

Using separate schemas for each tenant. Implementing separate schemas for each tenant provides a logical separation that allows for efficient management while keeping data isolated within the same instance.

Answer

Using a shared table with a TenantID column

Answer

Creating separate database instances for every tenant

Answer

Implementing row-level security on all tables

Question 2

Which TWO of the following scenarios are best suited for using a 'View'?

Accepted Answer

Simplifying complex joins. Views are best for simplifying complex queries and implementing row/column level security.

Answer

Storing large binary objects

Answer

Improving write speed

Answer

Generating unique sequences

Question 3

Which SQL keyword is used to eliminate duplicate values from a query result set?

Accepted Answer

DISTINCT. The DISTINCT keyword is used in a SELECT statement to return only unique values.

Answer

UNIQUE

Answer

GROUP BY

Answer

FILTER

Question 4

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

Question 5

Which TWO of the following are common database maintenance tasks?

Accepted Answer

Updating statistics. Updating statistics and rebuilding indexes are critical maintenance tasks to ensure optimal query performance.

Answer

Rebooting the server weekly

Answer

Renaming every table

Answer

Deleting all users

Question 6

An application is experiencing deadlocks. Which strategy would effectively reduce the frequency of deadlocks?

Accepted Answer

Uniform access order. Accessing tables in the same order across different transactions is a primary method to prevent circular wait conditions that cause deadlocks.

Answer

Enabling indexing

Answer

Using read-only databases

Answer

Increasing table size

Question 7

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 8

Which TWO of the following are common types of NoSQL databases?

Accepted Answer

Key-Value. Document and Key-Value stores are two primary categories of NoSQL databases.

Answer

Relational

Answer

Hierarchical

Answer

Network

Question 9

When performing sentiment analysis on customer feedback, which preprocessing step is most critical to ensure words like 'Running' and 'run' are treated as the same base concept?

Accepted Answer

Lemmatization. Lemmatization or stemming reduces words to their root or base form.

Answer

Stop-word removal

Answer

Tokenization

Answer

Feature scaling

Question 10

Which technology provides the best protection against data exfiltration from an on-premises database by a rogue administrator?

Accepted Answer

Database Activity Monitoring (DAM) with Firewall. A Database Firewall inspects incoming and outgoing SQL traffic to block unauthorized queries or data exports.

Answer

Database Clustering

Answer

Database Archiving

Answer

Database Sharding

Question 11

To ensure non-repudiation in a database system, what must be captured for every transaction?

Accepted Answer

Audit Logs with Unique User IDs. Detailed audit logs including a timestamp, user ID, and the executed command provide the necessary evidence for non-repudiation.

Answer

Server Uptime

Answer

Data Compression

Answer

Database Versioning

Question 12

When troubleshooting a performance bottleneck in a visualization, which action is most effective if the data source is a large SQL view?

Accepted Answer

Materializing the view. Materializing the view (creating a physical table with indexes) or optimizing the view's underlying query is the most effective way to resolve performance issues.

Answer

Changing the chart color

Answer

Increasing the number of filters

Answer

Using a different visualization software

Question 13

Which THREE of the following SQL aggregate functions are commonly supported in most relational databases?

Accepted Answer

SUM. SUM, COUNT, and AVG are fundamental aggregate functions supported across virtually all SQL standards.

Answer

CONCAT

Answer

SUBSTR

Question 14

A database is configured with a high-availability cluster. During a planned maintenance window, you need to fail over to the secondary node manually. Which command or action is used in a Windows Server Failover Cluster (WSFC) environment?

Accepted Answer

Move-ClusterGroup. In a WSFC environment, the 'Move' command in the Failover Cluster Manager is used to manually move the role (including the database resource) to another node.

Answer

SHUTDOWN WITH NOWAIT

Answer

ALTER SERVER ROLE

Answer

RESTORE DATABASE

Question 15

Which SQL command is used to add new records to an existing table?

Accepted Answer

INSERT. INSERT INTO is the standard command for adding new rows to a database table.

Answer

UPDATE

Answer

SELECT

Answer

ALTER

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

Which TWO of the following can be used to prevent unauthorized access to sensitive database tables?

Accepted Answer

Row-level security. Implementing role-based access control (RBAC) and row-level security (RLS) are effective ways to secure access to data.

Answer

Add more CPU cores

Answer

Increase the backup frequency

Answer

Use the sa account

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