DS0-001 Practice Exam 3 — 20 Questions

Question 1

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

Accepted Answer

Creating indexes on frequently filtered columns. Creating appropriate indexes and avoiding redundant subqueries significantly improve query efficiency.

Answer

Using a single large table

Answer

Selecting all columns (SELECT *)

Answer

Deleting all system statistics

Question 2

When reviewing a database security configuration, which finding poses the highest risk?

Accepted Answer

Enabled default accounts with default passwords. Default accounts (like 'admin' or 'sa') with default passwords are the most common entry points for attackers.

Answer

Old server hardware

Answer

Lack of database documentation

Answer

Redundant indexes

Question 3

A company is migrating to a cloud data warehouse and must comply with CCPA requirements for data portability. Which action is necessary to ensure compliance regarding user access requests?

Accepted Answer

Implement automated data export routines for individual user requests. Data portability under CCPA requires that the organization can export user data in a readily usable format.

Answer

Enable multi-factor authentication for all warehouse users

Answer

Disable all logging of user activity to prevent PII leakage

Answer

Increase the frequency of full database backups

Question 4

What is the primary purpose of a Data Protection Impact Assessment (DPIA) under GDPR?

Accepted Answer

Identify and mitigate data processing risks. A DPIA identifies and minimizes data protection risks associated with new data processing activities.

Answer

Backup the production database

Answer

Configure firewall rules

Answer

Accelerate database performance

Question 5

An administrator needs to enforce password complexity on the database level. Where should this policy be configured?

Accepted Answer

Database Authentication Policy/IAM. Password policies for database users are typically managed via the database's built-in authentication provider or integrated identity management system.

Answer

Database Trigger

Answer

Backup Schedule

Answer

Stored Procedure

Question 6

What is the first step in a Data Governance program?

Accepted Answer

Data Classification and Inventory. Identifying and classifying data assets is the prerequisite to applying any security controls.

Answer

Hiring a network engineer

Answer

Writing SQL queries

Answer

Purchasing storage hardware

Question 7

Which SQL function is used to convert a NULL value in a query result into a default value, such as 0?

Accepted Answer

COALESCE. COALESCE is a standard function used to return the first non-null value in a list, often used to replace NULLs.

Answer

NVL

Answer

IFNULL

Answer

ISNULL

Question 8

When cleaning a dataset, you notice that column names contain inconsistent casing (e.g., 'Name', 'NAME', 'name'). Which transformation step should be prioritized to facilitate join operations?

Accepted Answer

String Normalization. Standardizing string casing (normalization) is a fundamental prerequisite for accurate merging and joining.

Answer

One-hot encoding

Answer

Data Binning

Answer

Data deduplication

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 (or TOP/FETCH FIRST depending on dialect) restricts the result set to the specified number of rows.

Answer

WHERE

Answer

OFFSET

Answer

HAVING

Question 10

A company is conducting a data governance audit. Which TWO of the following items should be verified to ensure proper data security?

Accepted Answer

User access rights are reviewed periodically. Audits verify the implementation of technical controls like encryption and the existence of administrative processes like access reviews.

Answer

The breakroom refrigerator is energy efficient

Answer

The database server has a secondary monitor

Answer

The office printer is connected to a UPS

Question 11

When assessing data privacy compliance, which THREE categories of data are typically protected under laws like CCPA or GDPR?

Accepted Answer

Biometric data. PII, sensitive biometric data, and financial account information are primary targets for privacy regulation.

Answer

Publicly listed company stock prices

Answer

Internal server IP addresses

Question 12

A developer wants to use a subset of production data for testing. Which method ensures the test data retains its structure while protecting privacy?

Accepted Answer

Data Masking/De-identification. Data masking or de-identification replaces real values with realistic but fake values, maintaining schema integrity.

Answer

Database Snapshotting

Answer

Database Migration

Answer

Full Database Clone

Question 13

A database administrator needs to perform a schema-only export of a database. Which tool parameter is typically used for this purpose in mysqldump?

Accepted Answer

--no-data. The '--no-data' flag instructs mysqldump to export the schema (CREATE statements) without including the table content.

Answer

--quick

Answer

--add-drop-table

Answer

--all-databases

Question 14

Which THREE of the following are indicators of significant index fragmentation in a database?

Accepted Answer

Low page density. High scan latency, decreased query performance, and low page density are all common signs that indexes need maintenance.

Answer

Increased memory usage

Answer

Increased server uptime

Question 15

Which database backup strategy is recommended to ensure that you can restore a database to a specific point in time?

Accepted Answer

Full and Transaction Log backups. Transaction log backups, when used in conjunction with full backups and the Full recovery model, allow for point-in-time recovery.

Answer

Differential backups only

Answer

Full backups only

Answer

Copy-only backups

Question 16

Which TWO of the following are potential issues when denormalizing a database?

Accepted Answer

Data anomalies. Denormalization can lead to data anomalies (inconsistencies) and increased storage usage due to redundancy.

Answer

Simplified data entry

Answer

Improved schema flexibility

Answer

Slower join performance

Question 17

Which THREE factors should be considered when choosing between a traditional RDBMS and a NoSQL database for a new project?

Accepted Answer

Consistency requirements (ACID vs BASE). Data structure, scalability requirements, and ACID transaction needs are the primary drivers in this architectural decision.

Answer

The developer's preferred operating system

Answer

The color of the database console UI

Question 18

Which THREE factors are critical to consider when configuring a database for disaster recovery (DR)?

Accepted Answer

Geographic distance between primary and DR sites. RPO, RTO, and geographic location are the three pillars of a comprehensive disaster recovery strategy.

Answer

The brand of the server hardware

Answer

The database developer's IDE preference

Question 19

In Power BI, which visualization component is best suited to show the relationship between two numerical variables and identify potential trends or clusters?

Accepted Answer

Scatter Plot. A Scatter Plot is specifically designed to plot two numerical variables to show correlations.

Answer

Gauge Chart

Answer

Pie Chart

Answer

Clustered Column Chart

Question 20

You are debugging a SQL query that uses an aggregate function. You receive an error stating that a column in your SELECT list is not part of an aggregate or a GROUP BY clause. What is the cause?

Accepted Answer

Missing column in GROUP BY. Standard SQL requires every non-aggregated column in the SELECT clause to be included in the GROUP BY clause to ensure deterministic results.

Answer

Missing WHERE clause

Answer

Incorrect join type

Answer

Using an alias in WHERE