CompTIA · Free Practice Questions · Last reviewed May 2026
30 real exam-style questions organised by domain, each with the correct answer highlighted and a plain-English explanation of why it's right — and why the others are wrong.
A data architect is designing a schema for a high-concurrency e-commerce system. They decide to use a surrogate key instead of a natural key for the Orders table to ensure join performance and data integrity. What is the primary advantage of this design choice?
It reduces the storage size of the index compared to a string-based natural key
Integer-based surrogate keys are highly efficient for indexing and foreign key joins.
It prevents the insertion of duplicate records
It ensures that the database complies with ACID requirements
It automatically creates a clustered index on the table
A database developer is implementing a partitioning strategy for a large table containing historical financial records. The requirement is to allow for efficient archival of data older than five years. Which partitioning strategy should be chosen?
Range partitioning
Range partitioning divides rows by contiguous key values such as date, so records older than five years occupy distinct partitions that can be archived or dropped as a unit. This directly satisfies the efficient archival requirement, unlike hash or list partitioning, which scatter chronological data across partitions.
Composite partitioning
List partitioning
Hash partitioning
Round-robin partitioning
During the pre-deployment phase, a team needs to estimate the hardware resources required for a new Oracle Database installation. Which tool should be used to simulate database workload and forecast resource consumption?
Automatic Workload Repository (AWR)
Oracle Database Replay
Oracle Database Replay captures a production workload and replays it against a test system, reproducing realistic concurrency and transaction mix. This satisfies the pre-deployment requirement to simulate workload and forecast hardware consumption, unlike capacity-planning spreadsheets or static benchmarks that cannot replicate actual Oracle session behaviour.
Oracle Enterprise Manager Performance Hub
SQL Tuning Advisor
You are deploying a database to a cloud environment and need to ensure that data at rest is encrypted according to organizational security policy. Which deployment configuration ensures transparent encryption of the database files?
Implementing Transparent Data Encryption (TDE)
Transparent Data Encryption encrypts database files and backups at the page level, with the Database Encryption Key protected by a certificate stored in the master database. This satisfies the policy for data-at-rest encryption without application changes, since SQL Server decrypts data transparently during queries.
Applying column-level encryption
Using a Virtual Private Cloud (VPC) endpoint
Enabling SSL/TLS on the connection string
Before finalizing a database deployment, the team must ensure high availability. Which configuration allows for automatic failover to a standby server with zero data loss in a SQL Server environment?
Database Mirroring in High Performance mode
Synchronous-commit Availability Group
Synchronous-commit Availability Group commits on the secondary before acknowledging the primary, so failover to the standby loses no committed transactions. This satisfies the stem's zero-data-loss requirement, unlike asynchronous-commit mode, which permits potential data loss during failover.
Log Shipping
Asynchronous-commit Availability Group
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?
Purging
Purging permanently removes records that have passed their retention period and hold no regulatory obligation, satisfying the policy's terminal stage. Unlike archiving, which retains data in secondary storage, or anonymisation, which preserves the dataset minus identifiers, purging eliminates the data entirely, ensuring compliance and reclaiming storage.
Encryption
Migration
Archiving
Want more Database Planning And Deployment practice?
Practice this domainA database administrator needs to store unstructured data that scales horizontally across multiple servers while maintaining high availability. Which database structure should be selected?
Relational (RDBMS)
NoSQL (Document Store)
NoSQL document stores shard data across nodes using partition keys, satisfying the horizontal scaling and high availability constraints for unstructured content. Unlike relational engines, they avoid rigid schemas and single-node write bottlenecks, letting each server hold independent document subsets with replication for failover.
Network
Hierarchical
A user wants to add a new column to an existing table named 'Employees'. Which SQL statement should be executed?
MODIFY TABLE
UPDATE TABLE
CREATE COLUMN
ALTER TABLE
ALTER TABLE modifies the structure of an existing table, so it adds the new column to Employees without dropping data. CREATE TABLE builds a new table and UPDATE changes row values, neither of which alters the schema.
To improve read performance on a table frequently queried by a specific column, which database object should be created?
Index
An index maintains a sorted structure on the queried column, letting the database locate matching rows without scanning the entire table. This directly satisfies the stem's read-performance constraint, trading additional storage and slower writes for faster lookups on that column.
View
Stored Procedure
Trigger
A database administrator is investigating performance degradation during heavy concurrent write operations. Which isolation level provides the highest level of data consistency but the lowest concurrency?
Read Committed
Repeatable Read
Serializable
Serializable is the strictest isolation level, using range locks and predicate locking to make concurrent transactions behave as if run sequentially. This guarantees the highest consistency but forces blocking, giving the lowest concurrency under heavy writes.
Read Uncommitted
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?
PRIMARY KEY
UNIQUE
CHECK
FOREIGN KEY
A FOREIGN KEY enforces referential integrity by requiring every Customer_ID value in Orders to match an existing primary key in Customers, rejecting inserts or updates that reference a non-existent customer. This directly satisfies the stem's requirement that the field always reference a valid ID.
Which SQL command is used to permanently remove all rows from a table while keeping the table definition intact?
DELETE
DROP
REMOVE
TRUNCATE
TRUNCATE removes all rows by deallocating the table's data pages, a minimally logged operation that resets the table to empty while preserving its schema, columns, and indexes. This satisfies the stem's requirement to delete every row permanently yet keep the table definition intact, unlike DROP, which eliminates the structure itself.
Want more Database Structures And SQL practice?
Practice this domainTo comply with CCPA, an organization must track the 'Right to Know' requests. Which component of a Data Governance Framework is most relevant?
Data Ingestion Pipeline
Data Cataloging
Data cataloguing inventories data assets with metadata, lineage and classification, letting staff locate every record linked to an individual. That directly satisfies the stem's CCPA constraint: responding to Right to Know requests requires knowing which datasets hold personal data and where it flows.
Load Balancing
Data Compression
Which access control model should be implemented to ensure that a user can only access files based on their specific job role?
Discretionary Access Control (DAC)
Role-Based Access Control (RBAC)
Role-Based Access Control grants permissions according to a user's job role, so access to files is determined by role membership rather than individual ownership or arbitrary attributes. This satisfies the requirement that users access only files relevant to their role.
Mandatory Access Control (MAC)
Network Access Control (NAC)
An administrator needs to restrict access to a specific S3 bucket so that only the Finance team can view files. Which configuration is the most effective approach?
Configure an S3 Bucket Policy
An S3 bucket policy is a resource-based IAM policy attached directly to the bucket, letting you grant or deny access by principal, such as the Finance team's IAM role, at the bucket level. This satisfies the constraint of restricting the specific bucket to only Finance members.
Update the VPC Route Table
Enable Public Access Block
Enable S3 Versioning
An organization is migrating sensitive financial records to a cloud environment. Which security control ensures that only authorized applications can decrypt the data?
Database Replication
Cloud Hardware Security Module (Cloud HSM)
A Cloud HSM provides tamper-resistant hardware that stores and manages encryption keys, performing cryptographic operations inside the module so plaintext keys never leave it. Only applications authorised to use the key handle can decrypt data, satisfying the authorised-decryption constraint.
Database Triggers
Shared Responsibility Model
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?
Snapshotting
Database Sharding
Cache clearing
Automated Data Lineage and Lifecycle Tracking
Automated data lineage and lifecycle tracking maps where each record resides across all distributed nodes and enforces its deletion, producing auditable evidence that erasure requests were fulfilled everywhere. GDPR Article 17 requires demonstrating deletion across the whole cluster, which lineage tracking uniquely provides.
A database administrator needs to implement column-level encryption for sensitive PII in a SQL Server environment. Which tool should be used to ensure the encryption keys are managed outside the database engine?
Database File Stream
Transparent Data Encryption (TDE)
SQL Server Data Masking
SQL Server Always Encrypted with Azure Key Vault
Always Encrypted performs cryptographic operations client-side, so the database engine never sees plaintext or keys. Storing the column master key in Azure Key Vault keeps key material outside SQL Server, satisfying the requirement that keys be managed externally.
Want more Data Governance And Security practice?
Practice this domainA DBA is configuring an Always On Availability Group in SQL Server. To ensure that the secondary node can be used for read-only reporting tasks, which setting must be correctly configured in the Availability Group replica settings?
Readable Secondary
Configuring Readable Secondary to "Read-intent only" or "Yes" lets the secondary replica accept read-only connections, satisfying the reporting requirement. Without it, the secondary remains non-readable regardless of the availability mode, so the DBA must set this replica-level option explicitly.
Automatic Failover
Synchronous Commit
Primary Role
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?
pg_stat_database
pg_locks
pg_stat_user_tables
pg_stat_activity
`pg_stat_activity` exposes one row per server process, including `state`, `query_start`, and the currently executing `query` text, so it reveals which sessions are actively running long queries. This directly satisfies the requirement to inspect execution statistics across all active sessions, unlike cumulative `pg_stat_statements` aggregates.
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?
Transaction Log Backups every 10 minutes
RPO defines the maximum tolerable data loss measured in time. Transaction log backups every 10 minutes capture changes more frequently than the 15-minute RPO, so at most 10 minutes of data is lost, meeting the requirement.
Daily Full Backups
Weekly Full and Daily Differential Backups
Hourly Transaction Log Backups
In an Oracle Database environment, a DBA identifies that specific queries are performing poorly due to fragmented tables. Which command should the DBA execute to reclaim unused space and defragment the table without dropping the table structure?
ALTER TABLE ... SHRINK SPACE
ALTER TABLE ... SHRINK SPACE reclaims fragmented space in-place, moving rows downward and resetting the high-water mark without dropping the table. It satisfies the stem's requirement to defragment while preserving the table structure, unlike MOVE (which relocates segments) or export/import. Row movement must be enabled first.
TRUNCATE TABLE
ANALYZE TABLE ... COMPUTE STATISTICS
DROP AND RECREATE
A MySQL DBA needs to diagnose a deadlock issue. Which tool or command provides the most detailed information regarding the specific InnoDB internal locks held by transactions at the time of a deadlock?
CHECK TABLE
SHOW ENGINE INNODB STATUS
SHOW ENGINE INNODB STATUS returns the LATEST DETECTED DEADLOCK section, listing each transaction's held and waiting InnoDB row and table locks plus the SQL involved. This gives the internal lock detail needed to identify the conflicting access pattern.
EXPLAIN
SHOW PROCESSLIST
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?
Disable antivirus
Use striped backups
Striped backups split the backup set across multiple files, letting SQL Server read them in parallel during restore. This directly addresses the stem's constraint: restoration of a large database takes too long. Parallel I/O streams reduce elapsed restore time proportionally to the number of stripes, unlike single-file backups, which remain bound by one device's throughput.
Run in single-user mode
Increase the recovery interval
Want more Data Systems Management practice?
Practice this domainAn 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?
Better readability and recursive capability
A CTE names the pre-aggregated result set, separating it from the join logic so the query reads top-down and is easier to maintain. It also supports recursion, unlike a plain subquery, which satisfies the readability and recursive capability requirement.
Automatic indexing of the result set
Performance is always superior to subqueries
Lower memory consumption during execution
When joining two tables in SQL where you need to return all records from the left table even if there is no match in the right table, which JOIN type is appropriate?
LEFT JOIN
LEFT JOIN returns every row from the left table and matching rows from the right, filling unmatched right-side columns with NULL. It satisfies the stem's requirement to retain all left-table records despite missing matches, unlike INNER JOIN, which discards them.
CROSS JOIN
INNER JOIN
FULL OUTER JOIN
A data analyst is writing a SQL query to retrieve unique department names from the 'Employees' table. Which keyword should be utilized to ensure the results do not contain duplicate entries?
GROUP BY
DISTINCT
DISTINCT eliminates duplicate rows from the result set by comparing all selected column values, returning each unique department name once. It directly satisfies the requirement that results contain no duplicate entries, unlike keywords that filter rows conditionally or aggregate values rather than deduplicating the projected output.
UNIQUE
FILTER
In Power BI, which visualization component is best suited to show the relationship between two numerical variables and identify potential trends or clusters?
Gauge Chart
Scatter Plot
A scatter plot places one numerical variable on each axis, so each point represents a paired observation. This reveals correlation, trends, and clusters between the two measures, which bar, line, and pie charts cannot show because they plot a single measure against categories or time.
Pie Chart
Clustered Column Chart
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?
String Normalization
Normalizing casing ensures consistency for joins.
One-hot encoding
Data Binning
Data deduplication
A database administrator needs to ensure that a query calculating running totals is performant. Which window function clause is essential to order the calculation sequence?
GROUP BY
PARTITION BY
ROWS BETWEEN
ORDER BY
The ORDER BY clause inside the OVER() specification defines the row sequence the window function processes, which is essential for cumulative running totals. Without it, the frame is unordered and the sum accumulates arbitrarily, producing incorrect totals regardless of partitioning.
Want more Data Querying And Analysis practice?
Practice this domainThe vendor does not publish an exact question count for the DS0-001; the exam lasts 90 minutes. The passing score is 700 (scale 100–900). Always confirm current details on the vendor's official exam page.
Multiple-choice and performance-based questions covering IT security, networking, and operations. Some questions are performance-based (PBQs), asking you to complete tasks in a simulated environment.
The exam covers 5 domains: Database Planning And Deployment, Database Structures And SQL, Data Governance And Security, Data Systems Management, Data Querying And Analysis. Questions are weighted by domain — higher-weight domains appear more on your actual exam.
No. These are original exam-style practice questions written against the official CompTIA DS0-001 exam objectives. They are not copied from the real exam. Courseiva focuses on genuine understanding, not memorisation of braindumps.
Courseiva tracks your accuracy per domain and routes you toward weak areas automatically. Free, no account required.