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

 

Question 81.

A database administrator wants to identify whether a particular application account is issuing unusually expensive queries. Which source of information is MOST useful?

1. Query execution statistics associated with the application login
2. Table naming conventions
3. Backup file extensions
4. Database creation date

Correct Answer: 1. Query execution statistics associated with the application login

Explanation:

Query execution statistics can reveal which statements are consuming excessive CPU, memory, I/O, or elapsed time and can often be correlated with the user or application identity that submitted them. This allows administrators to determine whether the issue is caused by a specific workload, query pattern, or application behavior. Static metadata such as table names or database age does not provide comparable performance evidence.

Question 82.

Which SQL statement is used to remove specific rows from a table while preserving the table itself?

1. DROP
2. DELETE
3. CREATE
4. GRANT

Correct Answer: 2. DELETE

Explanation:

DELETE removes rows from a table and can use a WHERE clause to target specific records. The table definition remains in place. DROP removes the table object itself, while TRUNCATE typically removes all rows without supporting row-level filtering. Administrators should verify DELETE conditions carefully because an omitted or incorrect WHERE clause can remove more data than intended.

Question 83.

A query returns duplicate rows, but the report requires each value only once. Which SQL keyword is MOST appropriate?

1. HAVING
2. UNION ALL
3. DISTINCT
4. ORDER BY

Correct Answer: 3. DISTINCT

Explanation:

DISTINCT removes duplicate rows from a SELECT result based on the selected columns. It is useful when the report requires only unique combinations of values. DISTINCT may add processing overhead because the database must identify duplicates, so it should be used when required rather than automatically. ORDER BY sorts results, while HAVING filters aggregated groups.

Question 84.

A database administrator needs to add a new column to an existing table. Which SQL command category is MOST appropriate?

1. SELECT
2. INSERT
3. COMMIT
4. ALTER TABLE**

Correct Answer: 4. ALTER TABLE

Explanation:

ALTER TABLE changes the structure of an existing table. Depending on the database platform, it can add or remove columns, modify data types, and manage constraints. Structural changes should be tested carefully because they can affect applications, indexes, storage, and existing data. Schema changes should also follow normal change-management and rollback procedures.

Question 85.

Which database constraint is BEST suited to ensuring that a numeric Quantity column can never contain a negative value?

1. CHECK constraint
2. Foreign key constraint
3. Primary key constraint
4. Unique constraint

Correct Answer: 1. CHECK constraint

Explanation:

A CHECK constraint enforces a logical condition on values stored in a column or row. A condition such as Quantity greater than or equal to zero can prevent invalid negative values from being inserted or updated. Primary keys enforce row identity, unique constraints prevent duplicate values, and foreign keys enforce relationships between tables.

Question 86.

A database administrator needs to improve read scalability by directing reporting queries to another server that receives copies of production changes. Which solution is MOST relevant?

1. Normalization
2. Read replica
3. Data masking
4. Transaction rollback

Correct Answer: 2. Read replica

Explanation:

A read replica maintains a replicated copy of production data and can serve read-only or reporting workloads, reducing pressure on the primary database. Replication lag and consistency requirements must be considered because replica data may not always be completely current. Read replicas can improve scalability, but they are not a substitute for tested backups and disaster recovery procedures.

Question 87.

Which database architecture provides multiple nodes that can potentially accept writes, depending on the implementation?

1. Cold standby
2. Read-only replica
3. Multi-primary replication
4. Full backup chain

Correct Answer: 3. Multi-primary replication

Explanation:

Multi-primary replication allows more than one database node to accept write operations. This can improve availability or geographic distribution, but it introduces additional complexity around conflict detection, synchronization, latency, and consistency. The architecture must be selected based on business requirements and the database platform’s capabilities rather than assuming multiple writable nodes are always beneficial.

Question 88.

A company requires database service to continue if one server fails. Which design BEST supports this requirement?

1. One server with a larger disk
2. Weekly full backups only
3. More database users
4. High-availability clustering or failover architecture**

Correct Answer: 4. High-availability clustering or failover architecture

Explanation:

High-availability architectures use redundant database instances, nodes, or clusters to reduce downtime when a server fails. Failover may be automatic or manual depending on the platform and design. Backups remain necessary for recovery from corruption or accidental deletion, but they do not by themselves provide rapid service continuity after a hardware or instance failure.

Question 89.

Which metric is MOST useful for determining whether a database connection pool is exhausted?

1. Active and waiting connection counts
2. Number of table columns
3. Backup compression ratio
4. Number of foreign keys

Correct Answer: 1. Active and waiting connection counts

Explanation:

Connection-pool exhaustion occurs when all available connections are in use and additional requests must wait or fail. Monitoring active connections, idle connections, waiters, timeouts, and pool limits helps diagnose the problem. The administrator should also investigate whether connections are being held too long, leaked by applications, or limited too aggressively.

Question 90.

A database administrator sees many long-running idle sessions holding open transactions. What is the MOST important concern?

1. Increased report formatting time
2. Locks and transaction resources may remain held unnecessarily
3. Primary keys may be removed automatically
4. Backups will stop permanently

Correct Answer: 2. Locks and transaction resources may remain held unnecessarily

Explanation:

Idle sessions with open transactions can continue holding locks, retaining transaction log space, or preventing cleanup processes from completing. This can contribute to blocking, log growth, and reduced concurrency. Administrators should identify the responsible application behavior and ensure transactions are committed or rolled back promptly rather than simply terminating sessions without understanding the cause.

Question 91.

Which type of monitoring is MOST useful for detecting that database storage will run out within the next several months?

1. One-time snapshot monitoring
2. Login auditing only
3. Capacity trend analysis
4. Query syntax checking

Correct Answer: 3. Capacity trend analysis

Explanation:

Capacity trend analysis examines storage usage over time and helps administrators forecast when available space may be exhausted. Trends can account for data growth, indexes, transaction logs, backups, and retention requirements. This allows storage to be expanded or data lifecycle policies to be adjusted before users experience an outage caused by insufficient capacity.

Question 92.

A database administrator wants to ensure database changes can be traced back to an approved request. Which operational process is MOST relevant?

1. Normalization
2. Replication
3. Query optimization
4. Change management**

Correct Answer: 4. Change management

Explanation:

Change management provides documentation, approval, testing, scheduling, implementation, and validation for database modifications. Linking changes to approved requests supports accountability and reduces unplanned outages. Emergency changes may use an expedited process, but they should still be documented and reviewed afterward. Change management also supports rollback planning and coordination with application teams.

Question 93.

Which action should be performed BEFORE applying a major database engine upgrade in production?

1. Test compatibility, backups, rollback procedures, and application behavior in a non-production environment
2. Upgrade production first to save time
3. Disable all monitoring
4. Delete previous installation media

Correct Answer: 1. Test compatibility, backups, rollback procedures, and application behavior in a non-production environment

Explanation:

Major database upgrades can affect drivers, extensions, SQL behavior, performance, security settings, and application compatibility. Testing in a representative non-production environment helps identify problems before production impact occurs. A verified backup and rollback plan should also be available in case the upgrade fails or introduces unacceptable behavior.

Question 94.

A database administrator must apply a security patch with minimal service interruption. Which approach is BEST when supported by the architecture?

1. Ignore the patch until the next major release
2. Patch redundant nodes sequentially while maintaining service availability
3. Shut down all database nodes simultaneously
4. Disable redundancy before patching

Correct Answer: 2. Patch redundant nodes sequentially while maintaining service availability

Explanation:

In a redundant high-availability architecture, administrators may patch one node at a time while other nodes continue serving the workload. This rolling maintenance approach can reduce downtime. Compatibility, failover behavior, replication health, and vendor guidance should be validated first. Security patches should still follow change-management procedures and include post-patch verification.

Question 95.

A user has read access to payroll data even though the employee changed departments six months ago. What security process has MOST likely failed?

1. Index maintenance
2. Backup verification
3. Periodic access review
4. Query optimization

Correct Answer: 3. Periodic access review

Explanation:

Periodic access reviews help confirm that users still require the permissions they currently hold. When employees change roles or departments, outdated privileges should be removed promptly. Failure to review access can lead to privilege accumulation and unnecessary exposure of sensitive information. Role-based access control and automated provisioning can help reduce this risk.

Question 96.

Which control BEST protects highly privileged database credentials from being used continuously for routine work?

1. Shared administrator passwords
2. Permanent administrator sessions
3. Anonymous database access
4. Privileged access management with controlled elevation**

Correct Answer: 4. Privileged access management with controlled elevation

Explanation:

Privileged access management can restrict high-impact credentials, provide time-limited elevation, require approvals, and record privileged sessions. Administrators can use lower-privilege identities for routine work and elevate only when necessary. This supports least privilege and accountability while reducing the risk associated with permanent use of broad administrative permissions.

Question 97.

Which database security activity is MOST effective for detecting repeated failed login attempts against administrative accounts?

1. Monitor authentication logs and configure appropriate alerts
2. Rebuild indexes nightly
3. Normalize authentication tables
4. Increase query cache size

Correct Answer: 1. Monitor authentication logs and configure appropriate alerts

Explanation:

Repeated failed logins can indicate password guessing, misconfigured applications, or attempted unauthorized access. Authentication logs provide evidence of these events, while alerts can notify administrators when thresholds are exceeded. Monitoring should include source information, account names, timing, and other relevant context while avoiding unnecessary exposure of credentials.

Question 98.

A database contains sensitive information that must be retained for seven years and then securely removed. Which practice BEST supports this requirement?

1. Keep all data indefinitely
2. Implement and enforce a documented data retention and disposal policy
3. Disable all backups
4. Store duplicate copies in every environment

Correct Answer: 2. Implement and enforce a documented data retention and disposal policy

Explanation:

A retention policy defines how long data must be kept, where it may be stored, and how it should be disposed of when retention requirements expire. The policy should address primary databases, archives, replicas, and backups where applicable. Secure disposal reduces unnecessary exposure while ensuring legal, regulatory, and business obligations are met.

Question 99.

A database administrator needs to reduce the risk of SQL injection in an application that submits user input to the database. Which approach is BEST?

1. Concatenate user input directly into SQL statements
2. Grant the application database-owner permissions
3. Use parameterized queries or prepared statements
4. Disable database logging

Correct Answer: 3. Use parameterized queries or prepared statements

Explanation:

Parameterized queries separate executable SQL structure from user-supplied data, greatly reducing the risk that malicious input will be interpreted as SQL commands. Input validation and least-privilege application accounts provide additional defense. Simply escaping strings manually is more error-prone than using supported parameterization mechanisms consistently throughout the application.

Question 100.

Which statement BEST describes effective database lifecycle management?

1. Focus only on initial installation
2. Perform maintenance only when users report problems
3. Keep all databases and data forever
4. Manage databases continuously through planning, deployment, security, monitoring, maintenance, backup, recovery, upgrades, and retirement**

Correct Answer: 4. Manage databases continuously through planning, deployment, security, monitoring, maintenance, backup, recovery, upgrades, and retirement

Explanation:

Database administration extends across the full system lifecycle. Administrators must plan capacity and architecture, deploy securely, monitor health and performance, manage access, test backups, apply patches, perform upgrades, and eventually retire systems and data safely. Treating database operations as an ongoing lifecycle process improves reliability, security, recoverability, and alignment with organizational requirements.