CompTIA DataSys+ (DS0-001) (DS0-001) (DS0-001) — Questions 1–75

You need to change the collation of a database. What is the impact of performing this operation?

During the testing phase of a database deployment, you discover that the database is not accepting external connections. You have verified that the database service is running. What is the most likely culprit?

A developer needs to ensure that a 'Customer_ID' field in a 'Orders' table always references a valid ID in the 'Customers' table. Which constraint should be applied?

A database architect is tasked with choosing a database model for a system that requires highly variable, semi-structured data storage (e.g., JSON documents). Which model provides the best flexibility for this requirement?

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

In PostgreSQL, you need to monitor the performance of your long-running queries. Which view is the most appropriate to inspect the query execution statistics for all active sessions?

Which SQL statement is used to remove a table entirely from the database?

An administrator is deploying a new database and needs to ensure that the backup strategy complies with the '3-2-1' backup rule. What does the '1' in this rule represent?

Which access control model should be implemented to ensure that a user can only access files based on their specific job role?

Which THREE of the following are valid methods to improve database performance for read-heavy workloads?

A transaction involves multiple updates that must either all succeed or all fail. Which command ensures this behavior?

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

An administrator needs to improve the security posture of a newly deployed PostgreSQL database. Which TWO actions should be performed to harden the environment?

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

You are performing a backup for a database that is in a 24/7 environment. Which backup type should you use if you want to avoid interfering with the existing backup sequence of full and differential backups?

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?

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

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

A company subject to GDPR needs to ensure that user data is deleted upon request across all distributed nodes in a NoSQL cluster. Which process is most critical to demonstrate compliance?

Which TWO of the following actions can a DBA take to reduce the size of a database data file?

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

Which TWO of the following are recognized components of a mature Data Governance framework? (Select TWO)

Which TWO of the following are benefits of using a data warehouse?

When preparing a database deployment plan, what THREE factors must be considered to ensure successful capacity planning?

An organization is migrating sensitive financial records to a cloud environment. Which security control ensures that only authorized applications can decrypt the data?

A database administrator is evaluating storage subsystems for a mission-critical database. The database uses random I/O patterns for most operations. Which storage performance metric is most critical for this workload?

When performing sentiment analysis on customer feedback, which preprocessing step is most critical to ensure words like 'Running' and 'run' are treated as the same base concept?

A database administrator is defining the data lifecycle policy for a new deployment. What is the final step in the data lifecycle for records that have exceeded their retention period and are no longer required for regulatory compliance?

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

Which regulatory act specifically governs the protection of healthcare-related data?

An organization is deploying a new SQL Server database. The storage administrator needs to ensure that the transaction logs are stored on a separate physical volume from the data files to improve performance and recoverability. Which configuration step accomplishes this?

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

An application requires an automatic timestamp for every row inserted into an 'Audit' table. Which feature should the developer implement?

Which TWO of the following are DDL (Data Definition Language) commands?

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

Which THREE of the following are considered ACID properties in database transactions?

A database administrator wants to move a heavily accessed table to a different physical storage tier to improve performance. In SQL Server, what is the best way to move the table data while retaining the clustered index?

You are migrating an on-premises Oracle database to Oracle Cloud Infrastructure (OCI) using a tool that requires the least amount of downtime while keeping the source and destination databases in sync. Which method should you select?

Which THREE items should be included in a standard database deployment checklist to ensure consistency across environments?

Which THREE of the following are common data types in relational databases?

You are implementing a data governance framework. Which role is primarily responsible for ensuring that the data quality, security, and lifecycle policies are defined and adhered to for a specific business domain?

Which TWO settings should be configured in a database deployment to improve performance for heavy read-intensive reporting workloads?

Which TWO of the following statements about SQL are true?

An administrator needs to restrict a user's ability to see only rows where the 'Region' column is 'North'. What feature should be used?

During a disaster recovery test, the DBA realizes the database restoration takes too long. Which action is the most effective way to improve the restore speed of a large database?

A database administrator is deploying a production environment and needs to restrict network access to the database server. Which tool or setting is the first line of defense at the network layer?

A query is slow due to excessive scanning of a large table. The execution plan shows a 'Table Scan'. What is the most likely missing element?

Which THREE of the following are examples of descriptive statistics?

You are planning the disk layout for a SQL Server production database. Which THREE best practices should you follow to optimize storage performance?

A user wants to add a new column to an existing table named 'Employees'. Which SQL statement should be executed?

Which TWO indicators should be monitored when testing a database deployment to ensure it is correctly sized for performance?

When planning a backup strategy, a company requires that the Recovery Point Objective (RPO) be set to 15 minutes. Which backup schedule meets this requirement?

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?

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?

Which THREE of the following are benefits of implementing a standardized database schema migration process?

Which THREE of the following are types of data joins in SQL?

When planning database capacity, you calculate the projected growth of a table based on transaction volume and row size. What does 'database bloat' refer to in this context?

An application is experiencing deadlocks. Which strategy would effectively reduce the frequency of deadlocks?

When working with time-series data, what does a 'lag' feature represent in an analysis?

Which SQL command is used to permanently remove all rows from a table while keeping the table definition intact?

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?

You are managing a MariaDB cluster. You notice that nodes are falling out of sync. Which status variable should you check to verify the replication lag between the primary and the replica?

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

Which THREE of the following are standard SQL aggregate functions?

Which protocol should be enforced for all data-in-transit between application servers and database instances?

Which TWO of the following are common symptoms of an undersized transaction log file?

Which DML statement is used to update existing records in a database table?

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

A company requires that all database backups be immutable for 7 years to meet regulatory audits. How can this be achieved?

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

Which operator is used to search for a pattern in a string column?

During database testing, a developer finds that a specific query is performing poorly due to a lack of statistics. What action should be taken to update the database optimizer's knowledge of the table data distribution?

To improve read performance on a table frequently queried by a specific column, which database object should be created?

A company needs to implement centralized identity management for all database administrators. Which service is appropriate?

A database server is consistently hitting high CPU usage. You suspect an unoptimized query is causing a full table scan. Which tool should you use to capture the execution plan of currently running queries?