View Full CompTIA DataSys+ DS0-001 Exam Dumps and Practice Test Dumps

 

Question 161.

A database administrator wants to determine whether a slow application is waiting primarily on database locks rather than CPU or storage. Which information is MOST useful?

1. Lock waits and blocking-session details
2. Backup retention settings
3. Number of database schemas
4. Password expiration dates

Correct Answer: 1. Lock waits and blocking-session details

Explanation:

Lock and blocking information shows whether sessions are waiting for resources held by other transactions. This can reveal long-running transactions, conflicting updates, or inefficient access patterns that reduce concurrency. Administrators should correlate wait information with active queries and transaction duration. CPU and storage metrics remain important, but they do not directly identify lock contention.

Question 162.

Which SQL clause is used to sort query results by one or more columns?

1. HAVING
2. ORDER BY
3. WHERE
4. GROUP BY

Correct Answer: 2. ORDER BY

Explanation:

ORDER BY sorts the result set according to one or more columns or expressions. ASC normally specifies ascending order and DESC specifies descending order. WHERE filters individual rows, GROUP BY creates groups for aggregation, and HAVING filters grouped results. ORDER BY affects presentation order rather than which rows qualify for the query.

Question 163.

A database administrator wants to return employees whose salaries fall between two specified values. Which SQL operator is MOST appropriate?

1. LIKE
2. EXISTS
3. BETWEEN
4. DISTINCT

Correct Answer: 3. BETWEEN

Explanation:

BETWEEN is commonly used to test whether a value falls within an inclusive range. It can be applied to numbers, dates, and other comparable data types depending on the database platform. LIKE performs pattern matching, EXISTS tests whether a subquery returns rows, and DISTINCT removes duplicate rows from a result.

Question 164.

A table column should automatically generate a unique numeric value for each newly inserted row. Which feature is MOST appropriate when supported by the platform?

1. View
2. Foreign key
3. Trigger-only auditing
4. Identity or auto-increment column**

Correct Answer: 4. Identity or auto-increment column

Explanation:

Identity or auto-increment functionality generates numeric values automatically as rows are inserted. It is commonly used for surrogate primary keys. The exact implementation differs among database systems, but the concept reduces the need for applications to manually calculate identifiers. Business uniqueness may still need separate constraints when the generated key has no business meaning.

Question 165.

Which database design BEST represents a many-to-many relationship between Students and Courses?

1. Use a junction table containing StudentID and CourseID
2. Store all course IDs in one Student column
3. Duplicate student details in the Courses table
4. Remove primary keys from both tables

Correct Answer: 1. Use a junction table containing StudentID and CourseID

Explanation:

A junction or associative table resolves a many-to-many relationship by storing references to both related entities. Each row represents one student-course association. The pair of foreign keys may form a composite primary key or be protected by a unique constraint. This normalized design avoids storing repeating lists or duplicating entity information.

Question 166.

A database administrator wants a query to return rows only when a related record exists in another table, without necessarily returning columns from that table. Which SQL construct is MOST appropriate?

1. ORDER BY
2. EXISTS
3. TRUNCATE
4. DEFAULT

Correct Answer: 2. EXISTS

Explanation:

EXISTS tests whether a subquery returns at least one matching row. It is useful when the query only needs to verify the existence of related data rather than retrieve columns from the related table. Depending on the optimizer and query, EXISTS can also provide a clear expression of semi-join logic.

Question 167.

A table contains millions of rows and a frequently used query filters by CustomerID and DateCreated. What should the administrator evaluate FIRST for performance improvement?

1. Whether to disable transaction logging
2. Whether to drop the primary key
3. Whether an appropriate composite index matches the query pattern
4. Whether to grant the application more privileges

Correct Answer: 3. Whether an appropriate composite index matches the query pattern

Explanation:

A composite index may help a query that commonly filters by multiple columns, but its effectiveness depends on column order, selectivity, sorting requirements, and the overall workload. The administrator should examine the execution plan and existing indexes before making changes. Indexes improve reads at the cost of storage and additional write maintenance.

Question 168.

A query uses a function on an indexed column inside the WHERE clause and no longer uses the index efficiently. What is the BEST next step?

1. Add more unrelated indexes
2. Restart the database
3. Remove all query filters
4. Review whether the predicate can be rewritten to preserve index usability**

Correct Answer: 4. Review whether the predicate can be rewritten to preserve index usability

Explanation:

Applying functions to indexed columns can sometimes prevent efficient index seeks because the database must transform values before comparing them. Rewriting the predicate so the indexed column remains directly searchable may allow better index use. The execution plan should confirm whether the change improves performance before it is deployed.

Question 169.

Which metric is MOST useful for identifying whether a database server is experiencing sustained storage pressure?

1. Disk latency, queue depth, and I/O throughput
2. Number of user roles
3. Number of views
4. Password age

Correct Answer: 1. Disk latency, queue depth, and I/O throughput

Explanation:

Storage pressure is best evaluated through I/O-focused metrics such as latency, queue depth, operations per second, and throughput. These measurements should be correlated with database waits and active workload. High disk utilization alone may not provide enough context. Persistent latency increases can directly affect query and transaction response time.

Question 170.

A database server has sufficient CPU but repeatedly performs physical reads for the same frequently accessed pages. Which resource should the administrator evaluate?

1. User account count
2. Buffer cache or available memory
3. Number of schemas
4. Backup encryption method

Correct Answer: 2. Buffer cache or available memory

Explanation:

Database buffer caches keep commonly accessed data pages in memory. If frequently requested pages must repeatedly be read from disk, memory pressure or insufficient cache allocation may be contributing to poor performance. Administrators should review cache hit behavior, paging, operating-system memory, and workload demand before adjusting memory settings.

Question 171.

A database administrator wants to determine whether a scheduled maintenance job is causing nightly performance degradation. Which approach is BEST?

1. Disable all monitoring
2. Delete the job immediately
3. Correlate job execution times with resource and query-performance metrics
4. Change every index at once

Correct Answer: 3. Correlate job execution times with resource and query-performance metrics

Explanation:

Time correlation helps determine whether a backup, integrity check, index operation, ETL task, or other scheduled job coincides with performance degradation. Administrators should compare CPU, memory, storage, locks, and query response times during the same period. This evidence allows maintenance schedules or job configurations to be adjusted without guessing.

Question 172.

A database backup is stored in a cloud object store. Which control BEST protects it from unauthorized modification or deletion?

1. Public write access
2. Shared credentials
3. Disabling versioning
4. Restrictive permissions with immutability or protected retention where supported**

Correct Answer: 4. Restrictive permissions with immutability or protected retention where supported

Explanation:

Backup storage should be protected with strong access controls and, where available, immutable or retention-locked storage. These controls reduce the risk that compromised production credentials or ransomware can alter or delete recovery copies. Encryption and separate administrative access further strengthen backup security. Recovery procedures should still be tested regularly.

Question 173.

Which practice BEST verifies that a replicated database can actually support disaster recovery?

1. Perform planned failover and recovery testing
2. Assume replication guarantees recovery
3. Review only the replica hostname
4. Disable monitoring during failover

Correct Answer: 1. Perform planned failover and recovery testing

Explanation:

Replication alone does not prove that applications, DNS, credentials, network paths, dependencies, and operational procedures will work during a real outage. Planned failover exercises validate the complete recovery process and help determine whether RTO and RPO targets can be met. Testing also reveals documentation gaps and operational dependencies before an actual disaster.

Question 174.

A database administrator needs to ensure that backup jobs are not silently failing. Which operational control is MOST appropriate?

1. Review backups only during disasters
2. Monitor backup-job status and configure failure alerts
3. Disable backup logs
4. Store backup files without verification

Correct Answer: 2. Monitor backup-job status and configure failure alerts

Explanation:

Backup failures should be detected quickly rather than discovered during recovery. Automated monitoring and alerting can identify missed schedules, storage failures, permission problems, or job errors. Administrators should also perform restore tests because a successful job status alone does not prove that backup data is complete or usable.

Question 175.

A database account used by an application has not been rotated in several years. Which security practice is MOST relevant?

1. Data normalization
2. Index fragmentation management
3. Credential lifecycle management
4. Query caching

Correct Answer: 3. Credential lifecycle management

Explanation:

Credential lifecycle management covers secure creation, storage, use, rotation, revocation, and replacement of database credentials. Long-lived credentials increase exposure if they are leaked or copied. Automated secret rotation and managed identities can reduce this risk when supported. Applications should also use least-privilege accounts rather than broad administrative credentials.

Question 176.

A database administrator wants to prevent one compromised application account from accessing databases used by unrelated applications. Which design BEST supports this?

1. Use one shared account for every application
2. Grant every application administrator rights
3. Disable auditing
4. Use separate service identities with scoped permissions**

Correct Answer: 4. Use separate service identities with scoped permissions

Explanation:

Separate application identities limit the impact of a credential compromise and improve accountability. Each account should receive access only to the databases, schemas, tables, or procedures required by that application. Shared service accounts create unnecessary trust relationships and make it harder to determine which application performed an action.

Question 177.

Which practice BEST protects database administrator accounts against password-only compromise?

1. Require multifactor authentication where supported
2. Use shorter passwords for convenience
3. Share administrator credentials
4. Disable authentication logging

Correct Answer: 1. Require multifactor authentication where supported

Explanation:

Multifactor authentication requires an additional factor beyond the password, reducing the risk that stolen credentials alone can provide privileged access. It is particularly valuable for administrators because their accounts can make high-impact changes. MFA should be combined with least privilege, auditing, secure credential storage, and controlled elevation where practical.

Question 178.

A company wants to know which users actually access a sensitive database rather than relying only on assigned permissions. Which control provides the BEST evidence?

1. Index statistics
2. Audit logging
3. Table partitioning
4. Data compression

Correct Answer: 2. Audit logging

Explanation:

Permission reviews show who can access data, while audit logs can show who actually did access it and what actions were performed. This supports investigations, compliance, and detection of unusual behavior. Audit events should be carefully selected and protected against modification so the records remain useful and trustworthy.

Question 179.

A database administrator discovers that developers are using real production customer data in a training environment. What is the BEST response?

1. Ignore the issue because the environment is internal
2. Give more developers access for consistency
3. Replace sensitive data with masked, synthetic, or appropriately anonymized data
4. Disable all training systems permanently

Correct Answer: 3. Replace sensitive data with masked, synthetic, or appropriately anonymized data

Explanation:

Production data may contain personal, financial, or confidential information that should not be unnecessarily exposed in lower environments. Masked, synthetic, or anonymized datasets can preserve useful characteristics while reducing privacy and security risk. Access controls and retention requirements should also apply to test and training environments.

Question 180.

Which statement BEST describes an effective database security baseline?

1. It contains only password-length requirements
2. It is optional when databases are internal
3. It should never change after installation
4. It defines approved security settings, permissions, encryption, auditing, network exposure, and related controls for comparison over time**

Correct Answer: 4. It defines approved security settings, permissions, encryption, auditing, network exposure, and related controls for comparison over time

Explanation:

A security baseline establishes the expected secure configuration for database systems. It can include authentication, roles, privileges, encryption, auditing, network settings, patch levels, and prohibited defaults. Administrators can compare actual systems with the baseline to identify drift or unauthorized changes. Baselines should evolve as technology, threats, and organizational requirements change.