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

 

Question 41.

A database administrator wants to ensure that child rows cannot reference nonexistent parent rows. Which database feature BEST enforces this requirement?

1. Referential integrity through foreign key constraints
2. Table partitioning
3. Data compression
4. Query caching

Correct Answer: 1. Referential integrity through foreign key constraints

Explanation:

Foreign key constraints enforce relationships between child and parent tables by requiring referenced values to exist in the parent table. This helps prevent orphaned records and preserves relational consistency. Referential actions may also control what happens when parent rows are updated or deleted. Compression, caching, and partitioning address other database concerns and do not directly enforce parent-child validity.

Question 42.

Which SQL statement is used to add a new row to a relational table?

1. ALTER
2. INSERT
3. GRANT
4. COMMIT

Correct Answer: 2. INSERT

Explanation:

INSERT adds new rows to a database table. It specifies the target table and values, either directly or through a query result. ALTER changes database object definitions, GRANT assigns permissions, and COMMIT makes the changes of a completed transaction permanent. INSERT is therefore the standard data manipulation operation for creating new records.

Question 43.

A database administrator needs to modify existing salary values for selected employees. Which SQL command is MOST appropriate?

1. SELECT
2. CREATE
3. UPDATE
4. DROP

Correct Answer: 3. UPDATE

Explanation:

UPDATE modifies existing rows in a table and can use a WHERE clause to target specific records. Without a proper filter, an UPDATE may affect every row, so administrators should verify conditions carefully before running it in production. SELECT retrieves data, CREATE defines new objects, and DROP removes database objects.

Question 44.

Which SQL statement permanently removes a table definition and its associated data?

1. DELETE
2. TRUNCATE
3. UPDATE
4. DROP**

Correct Answer: 4. DROP

Explanation:

DROP removes a database object such as a table, including its definition and normally its contained data. DELETE removes rows while retaining the table structure, and TRUNCATE empties a table while also preserving its definition. Because DROP can be highly destructive, permissions should be restricted and changes should be carefully controlled.

Question 45.

A database contains a table where every non-key column depends on the primary key, the whole primary key, and nothing but the primary key. Which normal form is MOST closely associated with removing transitive dependencies?

1. Third normal form
2. First normal form
3. Second normal form
4. Unnormalized form

Correct Answer: 1. Third normal form

Explanation:

Third normal form builds on first and second normal form by removing transitive dependencies, where a non-key attribute depends on another non-key attribute rather than directly on the key. This reduces redundancy and update anomalies. The common phrase that non-key attributes should depend on the key, the whole key, and nothing but the key summarizes the goal of well-normalized relational design.

Question 46.

Which database object can execute predefined SQL logic and accept input parameters?

1. Index
2. Stored procedure
3. Foreign key
4. Tablespace

Correct Answer: 2. Stored procedure

Explanation:

A stored procedure is database-resident program logic that can execute one or more SQL statements and often accept parameters. It can centralize data operations, enforce consistent logic, and reduce repeated SQL in applications. Indexes optimize access paths, foreign keys enforce relationships, and tablespaces or similar storage structures organize physical database storage.

Question 47.

A database administrator wants an action to execute automatically whenever a row is inserted into a particular table. Which database object is MOST appropriate?

1. View
2. Index
3. Trigger
4. Primary key

Correct Answer: 3. Trigger

Explanation:

A trigger executes automatically in response to specified database events, such as INSERT, UPDATE, or DELETE operations. Triggers can enforce business logic, maintain audit records, or synchronize related information. They should be used carefully because hidden automatic behavior can complicate troubleshooting and performance if triggers become excessive or poorly documented.

Question 48.

A company needs reports to show all customers, including customers who have never placed an order. Which join should be used when Customers is the left table and Orders is the right table?

1. INNER JOIN
2. CROSS JOIN
3. SELF JOIN
4. LEFT JOIN**

Correct Answer: 4. LEFT JOIN

Explanation:

A LEFT JOIN returns all rows from the left table and matching rows from the right table. Customers without orders will still appear, with NULL values for columns from the Orders table. An INNER JOIN would exclude customers with no matching order. This makes LEFT JOIN appropriate when the complete customer list must be preserved.

Question 49.

Which SQL aggregate function returns the number of rows or matching values in a result set?

1. COUNT
2. AVG
3. MAX
4. SUM

Correct Answer: 1. COUNT

Explanation:

COUNT is used to determine how many rows or non-null values satisfy a query. COUNT(*) counts rows, while COUNT(column) generally counts non-null values in the specified column. AVG calculates averages, MAX returns a maximum, and SUM adds numeric values. Aggregate functions are often combined with GROUP BY for summary reporting.

Question 50.

Which SQL clause is used to group rows with common values before applying aggregate functions?

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

Correct Answer: 2. GROUP BY

Explanation:

GROUP BY organizes rows into groups based on one or more columns so aggregate functions such as COUNT, SUM, AVG, MIN, and MAX can calculate results for each group. WHERE filters individual rows before grouping, while HAVING filters grouped results afterward. ORDER BY controls the presentation order of the final result set.

Question 51.

A report must return only departments whose total salary exceeds a specified amount. Which SQL clause is MOST appropriate for filtering the aggregated results?

1. SELECT
2. WHERE
3. HAVING
4. JOIN

Correct Answer: 3. HAVING

Explanation:

HAVING filters groups after aggregation has occurred. For example, a query can GROUP BY department and use HAVING SUM(Salary) greater than a threshold. WHERE normally filters individual rows before grouping and cannot always be used to filter aggregate results directly. HAVING is therefore designed for conditions involving aggregated group values.

Question 52.

A database administrator needs to prevent users from seeing sensitive columns while still allowing access to selected data from a table. Which object can BEST support this requirement?

1. Full backup
2. Tablespace
3. Transaction log
4. View**

Correct Answer: 4. View

Explanation:

A view can expose only selected columns or rows from underlying tables. Combined with appropriate permissions, it can provide users with access to the data they need while hiding sensitive fields. Views are useful for simplifying complex queries and supporting logical security boundaries, though access controls on underlying objects must still be configured correctly.

Question 53.

Which practice BEST protects database credentials used by an application?

1. Store them in a protected secrets-management system
2. Hard-code them in source code
3. Put them in public documentation
4. Use the database administrator password for all applications

Correct Answer: 1. Store them in a protected secrets-management system

Explanation:

Application database credentials should be protected from exposure through source repositories, configuration files, and logs. A secrets-management system can store and retrieve credentials securely and support rotation. Service accounts should also follow least privilege so a compromised application identity cannot perform unnecessary administrative actions.

Question 54.

A database administrator wants users to authenticate with an additional factor beyond a password before receiving privileged access. Which control is being implemented?

1. Data masking
2. Multifactor authentication
3. Normalization
4. Replication

Correct Answer: 2. Multifactor authentication

Explanation:

Multifactor authentication requires users to present evidence from more than one authentication factor, such as something they know and something they possess. It reduces the likelihood that a stolen password alone can provide privileged access. MFA is especially valuable for database administrators and other high-impact accounts when supported by the surrounding identity infrastructure.

Question 55.

Which database security control replaces sensitive data with realistic but non-sensitive values in a test environment?

1. Replication
2. Indexing
3. Data masking
4. Partitioning

Correct Answer: 3. Data masking

Explanation:

Data masking obscures sensitive information while preserving enough structure for testing or development. Examples include replacing names, account numbers, or personal identifiers with realistic substitutes. Masking reduces privacy and security risks associated with using production-like data in lower environments. The masking method should prevent practical recovery of the original sensitive values when required.

Question 56.

A database administrator wants to identify unauthorized changes to database objects and permissions. Which source of information is MOST useful?

1. Query cache only
2. Data dictionary only
3. Index statistics only
4. Audit logs**

Correct Answer: 4. Audit logs

Explanation:

Audit logs can record administrative activity, permission changes, schema modifications, login events, and access to sensitive objects. Reviewing audit records helps identify unauthorized or unexpected activity and supports investigations and compliance. Audit policies should focus on meaningful events and ensure logs are protected from unauthorized modification or deletion.

Question 57.

Which backup strategy generally minimizes the amount of data backed up after each successful backup by storing only changes since the most recent backup of any type?

1. Incremental backup
2. Full backup
3. Differential backup
4. Snapshot-only backup

Correct Answer: 1. Incremental backup

Explanation:

An incremental backup typically captures changes since the most recent successful backup, whether that backup was full or incremental. This can reduce backup duration and storage usage compared with repeated full backups. Restoration may require the last full backup plus a sequence of incremental backups, depending on the technology. Backup design should balance backup efficiency with recovery complexity and objectives.

Question 58.

Which backup type normally contains changes made since the most recent full backup?

1. Transaction rollback
2. Differential backup
3. Database view
4. Replication snapshot

Correct Answer: 2. Differential backup

Explanation:

A differential backup contains changes made since the last full backup. As time passes, differential backups may grow because each one includes all changes since that baseline. Recovery is often simpler than with a long incremental chain because administrators generally need the full backup and the latest differential backup, subject to the capabilities of the database platform.

Question 59.

A storage device containing a database fails completely. Which database planning activity MOST directly helps administrators restore service without exceeding business downtime requirements?

1. Table normalization
2. Query formatting
3. Disaster recovery planning based on documented RTO and RPO targets
4. Adding comments to stored procedures

Correct Answer: 3. Disaster recovery planning based on documented RTO and RPO targets

Explanation:

Recovery objectives define how quickly service must return and how much data loss is acceptable. Disaster recovery planning uses those targets to determine backup frequency, replication, infrastructure, recovery procedures, staffing, and testing requirements. Without documented and tested recovery plans, having backup files alone may not be sufficient to restore service within business expectations.

Question 60.

Which statement BEST describes a sound database change-management process?

1. Make production changes whenever an administrator finds them convenient
2. Allow undocumented emergency changes to remain permanently
3. Skip testing for small schema changes
4. Document, review, test, approve, deploy, and validate database changes through controlled procedures**

Correct Answer: 4. Document, review, test, approve, deploy, and validate database changes through controlled procedures

Explanation:

Controlled change management reduces the risk of outages, data loss, performance regressions, and security problems. Database changes should be documented, reviewed, tested in appropriate environments, approved according to organizational processes, and validated after deployment. Rollback or recovery considerations should also be included. Emergency changes may require expedited handling, but they should still be documented and reviewed afterward.