DS0-001 Practice Test 5 — 25 Questions

Question 1

An analyst is using a CTE (Common Table Expression) to pre-aggregate sales data before performing a join. What is the primary benefit of using a CTE instead of a subquery in this scenario?

Accepted Answer

Better readability and recursive capability. CTEs improve readability and maintainability of complex queries by allowing the logic to be defined in a named temporary result set.

Answer

Automatic indexing of the result set

Answer

Performance is always superior to subqueries

Answer

Lower memory consumption during execution

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. WORM (Write Once, Read Many) storage policies prevent files from being altered or deleted until the retention period expires.

Answer

Database Replication

Answer

Regular manual deletion

Answer

Standard RAID 10

Question 3

Which of the following is a limitation of the 'Star Schema' design compared to a fully normalized schema?

Accepted Answer

Increased data redundancy. Star schemas increase redundancy by duplicating attributes in dimension tables, which can lead to larger storage footprints.

Answer

Inability to store large data volumes

Answer

Slower query performance

Answer

Difficulty in writing joins

Question 4

Which THREE of the following represent common data transformation tasks in a preparation workflow?

Accepted Answer

Data cleaning. Data cleaning, data filtering, and column renaming are standard tasks to ready data for analysis.

Answer

Database deletion

Answer

Hardware installation

Question 5

Which TWO of the following are key considerations when configuring a database for high availability?

Accepted Answer

Network latency between nodes. Network latency between nodes and the speed of the storage subsystem are critical to ensure synchronization is efficient and failover is reliable.

Answer

The color of the server chassis

Answer

Number of database users

Answer

The database name

Question 6

A DBA needs to implement transparent data encryption (TDE) for an entire database. Which component is required to protect the Database Encryption Key (DEK) in a SQL Server environment?

Accepted Answer

Certificate. The Certificate or Asymmetric Key is required to protect the DEK, which in turn is protected by the Master Key in the master database.

Answer

Active Directory group

Answer

Service Master Key

Answer

SSL/TLS certificate

Question 7

A DBA needs to automate the maintenance of database statistics in SQL Server. Which tool is the standard approach to manage this across all user databases?

Accepted Answer

SQL Server Agent Maintenance Plans. Maintenance Plans allow for the automation of tasks like updating statistics, reorganizing indexes, and backing up databases.

Answer

Active Directory Users and Computers

Answer

SQL Server Configuration Manager

Answer

Windows Task Scheduler

Question 8

Which THREE of the following are standard SQL aggregate functions?

Accepted Answer

AVG. SUM, AVG, and COUNT are standard aggregate functions used in SQL.

Answer

SELECT

Answer

DELETE

Question 9

When using Python's 'Matplotlib' library, which function is used to add a new figure to the canvas?

Accepted Answer

plt.figure(). The plt.figure() function initializes a new figure, allowing multiple plots to be managed.

Answer

plt.axis()

Answer

plt.plot()

Answer

plt.show()

Question 10

Which THREE of the following are recognized data governance best practices?

Accepted Answer

Defining clear data ownership roles. Effective governance includes defining data ownership, maintaining a metadata catalog, and ensuring data quality.

Answer

Using a single database vendor

Answer

Increasing database storage capacity

Question 11

Which THREE of the following are valid methods for securing cloud-based data storage?

Accepted Answer

Configuring Virtual Private Cloud (VPC) endpoints. Securing cloud storage requires a combination of IAM, encryption, and secure network configuration.

Answer

Sharing storage URLs publicly

Answer

Using a password for the root cloud account

Question 12

An organization must ensure that PII in a legacy SQL Server database is masked before being accessed by the HR analytics team. Which SQL Server feature should the DBA implement to ensure the data is obscured at the query level without changing the underlying storage?

Accepted Answer

Dynamic Data Masking (DDM). Dynamic Data Masking (DDM) limits sensitive data exposure by masking it to non-privileged users in the result set of a query.

Answer

Transparent Data Encryption (TDE)

Answer

Row-Level Security (RLS)

Answer

Always Encrypted

Question 13

Which THREE actions should be performed during a data incident response procedure involving a potential PII leak?

Accepted Answer

Analyze access logs for suspicious activity. Incident response requires containment, investigation, and reporting to meet legal obligations like GDPR/CCPA.

Answer

Update the database schema

Answer

Delete all historical backups

Question 14

Which TWO of the following actions are considered best practices for maintaining data integrity in a data pipeline?

Accepted Answer

Automated schema validation. Implementing automated schema validation and logging data quality metrics are critical for identifying and fixing issues early.

Answer

Using only manual entry

Answer

Hardcoding credentials in scripts

Answer

Ignoring error logs

Question 15

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 command used to shrink the size of data or log files in SQL Server.

Answer

DBCC SHRINKDATABASE

Answer

BACKUP LOG ... WITH TRUNCATE

Answer

ALTER DATABASE ... SET LOGFILE

Question 16

Which THREE of the following are necessary prerequisites for implementing database mirroring in a SQL Server environment?

Accepted Answer

Database must be in Full recovery model. Mirroring requires the database to be in Full recovery mode, a full backup/restore for seeding, and the correct account permissions for network communication.

Answer

The server must be a domain controller

Answer

Must be running in Read-only mode

Question 17

In an Oracle Database environment, a DBA identifies that specific queries are performing poorly due to fragmented tables. Which command should the DBA execute to reclaim unused space and defragment the table without dropping the table structure?

Accepted Answer

ALTER TABLE ... SHRINK SPACE. The ALTER TABLE ... SHRINK SPACE command is the standard way to defragment and reclaim space in an Oracle table while maintaining the table's structure and indexes.

Answer

TRUNCATE TABLE

Answer

ANALYZE TABLE ... COMPUTE STATISTICS

Answer

DROP AND RECREATE

Question 18

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 returns all rows from the left table, and matching rows from the right table; if no match exists, NULL values are returned for right-side columns.

Answer

INNER JOIN

Answer

RIGHT JOIN

Answer

FULL JOIN

Question 19

Which THREE database configuration settings help ensure data security and regulatory compliance?

Accepted Answer

Role-Based Access Control (RBAC). Auditing, encryption, and granular access control (roles) are cornerstones of data security compliance.

Answer

Disabling transaction log backups

Answer

Automatic plan cache clearing

Question 20

Your organization uses Azure Active Directory for access management. You need to ensure that database administrators can only access a specific production Azure SQL instance during a scheduled 4-hour maintenance window. Which control is most appropriate?

Accepted Answer

Azure AD Privileged Identity Management (PIM). Privileged Identity Management (PIM) provides just-in-time access, which is ideal for time-bound administrative access.

Answer

IP Firewall rules on the SQL Server

Answer

Service Principals with managed identity

Answer

Azure RBAC with Conditional Access policies

Answer

SQL Server Audit logs