CertArea
All CompTIA exams

Free DS0-002 practice test: 30 questions with answers

30 original questions for CompTIA DataSys+ (V2), drawn from every topic area. Answer each one to see whether you got it right and why. No sign-up.

Free questions
30
Full bank
100 questions
Real exam
Maximum of 90 questions, 90 min
Passing score
700 (scale 100-900)

DS0-002 exam guide: topics, cost and format

All 30 DS0-002 questions with answers

The same questions as the test above, grouped by exam topic. Open any question to see the answer and explanation.

Database Fundamentals (5 questions)

  1. Question 1

    A table stores customer orders, and each order must reference exactly one existing customer. Which constraint enforces this relationship?

    1. AA check constraint on the orders table that limits the order total to a fixed amount
    2. BA default value on the customer name column that fills in a placeholder name for new rows
    3. CA unique index on the order date to prevent duplicate dates
    4. DA foreign key from the orders table to the customers table's primary key
    Show answer and explanation
    Correct answer: D

    A foreign key ensures each order references an existing customer through the primary key. A unique date index, a total check and a default name do not enforce the relationship.

  2. Question 2

    Which SQL statement returns each customer's name with the total of their orders, including customers with no orders?

    1. AAn INNER JOIN with a WHERE clause that removes customers with no matching orders
    2. BA DELETE statement that removes customers with no orders from the customers table
    3. CA LEFT OUTER JOIN from customers to orders with GROUP BY on the customer and COUNT or SUM of orders
    4. DAn UPDATE that sets the total to zero for every customer row in the customers table
    Show answer and explanation
    Correct answer: C

    A left outer join keeps customers without orders, and aggregation over the grouped results returns totals. An inner join removes customers without orders, and DELETE or UPDATE do not return a query result.

  3. Question 3

    A developer writes a query that concatenates user input directly into a SQL string. What vulnerability does this create, and what is the fix?

    1. ADenial of service from full table scans, fixed by adding an index
    2. BSQL injection, fixed by parameterized queries or prepared statements
    3. CReplication lag on the standby, fixed by reducing the batch size
    4. DIndex fragmentation in a heap, fixed by rebuilding the table
    Show answer and explanation
    Correct answer: B

    Concatenated input can change the query's meaning, which is SQL injection. Parameterized queries separate data from SQL. Indexes, batch sizes and rebuilds do not address injection.

  4. Question 4

    Which data type is most appropriate for storing monetary values in a relational database to avoid rounding errors?

    1. ADECIMAL or NUMERIC with a defined precision and scale
    2. BBOOLEAN with a yes or no flag per account record
    3. CFLOAT for exact currency amounts across reports
    4. DVARCHAR storing the amount as text for display
    Show answer and explanation
    Correct answer: A

    DECIMAL and NUMERIC store exact values with defined precision and scale, which suits currency. FLOAT stores approximations, text complicates arithmetic, and booleans cannot hold amounts.

  5. Question 5

    A stored procedure must be reused by several applications to insert orders consistently. Which benefit is most relevant?

    1. AReplacing the need for user permissions on tables when applications connect through a single account
    2. BCentralized logic and fewer round trips when several statements run together on the server
    3. CAutomatic backup of every table on a nightly schedule with point-in-time recovery enabled by default
    4. DRemoving the need for primary keys in normalized tables when stored procedures run
    Show answer and explanation
    Correct answer: B

    Stored procedures centralize logic and can reduce network round trips. They do not perform backups, remove keys or replace permissions.

Database Deployment (5 questions)

  1. Question 6

    A team is designing a new database schema and wants a shared reference of tables, columns, data types, and their meaning. Which artifact should be created?

    1. AA wallpaper for the database server
    2. BA data dictionary
    3. CA marketing brochure
    4. DA customer invoice template
    Show answer and explanation
    Correct answer: B

    A data dictionary documents tables, columns, types and definitions. The other artifacts are unrelated to schema documentation.

  2. Question 7

    A database must be deployed in a cloud environment with high availability across two zones. Which configuration is most appropriate?

    1. AA managed database with zone-redundant or multi-zone deployment
    2. BManual copies of the database files every month to an external drive
    3. CA single instance in one zone with no backups and no automated failover
    4. DA database on a laptop for production use with a local USB disk as storage
    Show answer and explanation
    Correct answer: A

    Multi-zone or zone-redundant deployments provide high availability across zones. Single-zone deployments, laptops and monthly manual copies do not meet the requirement.

  3. Question 8

    An application connects to a database through a load balancer that distributes connections across read replicas. Which requirement must the application follow for this to work safely?

    1. AWrites must be sent to random replicas in round-robin order by connection
    2. BReplicas must be shut down during business hours while the primary handles every read and write
    3. CWrites must go to the primary and reads can go to replicas that may lag slightly
    4. DThe load balancer must cache query results forever in memory for every session without invalidation
    Show answer and explanation
    Correct answer: C

    Writes belong on the primary, while replicas serve reads that may be slightly behind. Random writes, shutdowns and permanent caching are not safe designs.

  4. Question 9

    Before deploying a schema change to production, the team wants to confirm that the change works and that application queries still perform as expected. Which step should come first?

    1. AApplying the change directly to production during peak hours without a rollback plan or a backup taken first
    2. BTesting the change in a staging environment with representative data and workloads
    3. CSkipping testing because the change is small and the rename looks trivial
    4. DDeleting the old schema before testing the new one, along with the legacy columns used by reports
    Show answer and explanation
    Correct answer: B

    Staging tests with representative data and workloads reveal failures and performance regressions before production. Direct production changes, skipping tests and deleting schemas first are risky.

  5. Question 10

    A database administrator must document the standard steps for applying patches to production databases. Which document should be created?

    1. AA wallpaper design guide for the office lobby color scheme
    2. BA holiday schedule for staff vacation days
    3. CA standard operating procedure (SOP) for patching
    4. DA marketing campaign plan for spring promotions
    Show answer and explanation
    Correct answer: C

    An SOP documents repeatable steps, roles and checks for patching. The other documents are not operational procedures for databases.

Data and Database Security (5 questions)

  1. Question 11

    What does data masking primarily protect?

    1. ANetwork cable length between the application tier and the storage array
    2. BDisk partition labels assigned during the operating system installation
    3. CSensitive values from being seen by unauthorized users in non-production or reports
    4. DThe server's power supply unit rated for 1200 watts with dual hot-swap modules
    Show answer and explanation
    Correct answer: C

    Masking hides sensitive values from users who should not see them, especially outside production. The other items are unrelated.

  2. Question 12

    Which two practices help protect database credentials? (Choose two.)

    1. AHardcoding credentials in source code
    2. BReusing one password for all systems
    3. CStoring secrets in a secrets manager
    4. DRotating passwords and using unique accounts
    5. EWriting passwords in a shared spreadsheet
    Show answer and explanation
    Correct answer: C, D

    Secrets managers and rotation with unique accounts reduce credential exposure. Shared spreadsheets, hardcoded credentials and reused passwords expose them.

  3. Question 13

    A database contains personal data, and the security team wants developers to see realistic-looking values without seeing real personal information. Which technique fits best?

    1. AGiving developers full production access with a note asking them to be careful
    2. BStoring personal data in comments
    3. CCopying production data to personal laptops
    4. DData masking of sensitive columns for non-production use
    Show answer and explanation
    Correct answer: D

    Masking produces realistic but non-identifying values for development and testing. Full access, personal copies and comments expose personal data.

  4. Question 14

    Sensitive columns must be encrypted so that database administrators cannot read the plaintext values. Which approach best meets this requirement?

    1. AEncrypting the entire server volume with a password written on a sticky note taped beside the rack
    2. BStoring the keys in the same table as the encrypted data columns for speed
    3. CUsing base64 encoding as protection for the stored values in production
    4. DColumn-level encryption with keys managed separately from the database administrators
    Show answer and explanation
    Correct answer: D

    Column encryption with separately managed keys prevents administrators from reading plaintext. Keys stored with data, sticky notes and base64 encoding do not provide that protection.

  5. Question 15

    Which access control approach gives each application account only the permissions it needs on specific tables?

    1. ALeast-privilege database roles granted per application, with specific object permissions
    2. BGiving every application the db_owner role on the production database server
    3. COne shared administrator account for every application using one password
    4. DGranting public access to every table in the reporting schema for convenience
    Show answer and explanation
    Correct answer: A

    Least-privilege roles with specific object permissions limit each application's access. Shared admin accounts, public access and db_owner for all applications violate least privilege.

Database Management and Maintenance (5 questions)

  1. Question 16

    A monitoring alert shows that a database's disk usage will reach capacity in two weeks. What is the most appropriate first action?

    1. ADeleting the transaction logs to free space immediately on the production server
    2. BReviewing growth trends and planning storage expansion or data archiving before capacity is reached
    3. CIgnoring the alert until the disk is full and assuming autogrowth will keep working on the current volume
    4. DRebooting the database server every night as a scheduled task to keep temp space available
    Show answer and explanation
    Correct answer: B

    Trend review and planned expansion or archiving prevents an outage before it happens. Deleting transaction logs can break recovery, ignoring alerts causes outages, and rebooting does not free disk.

  2. Question 17

    A query that used to run quickly now takes minutes after a data load. Which step is the most appropriate first troubleshooting action?

    1. AAdding more users to the database connection pool to spread the query load across sessions and tenants
    2. BDropping every index on the affected tables to free space and then rebuilding them later during business hours
    3. CRestarting the database server without analysis during the load window, which may clear cached plans and hide the root cause
    4. DReviewing the query execution plan and checking whether statistics and indexes are current
    Show answer and explanation
    Correct answer: D

    Execution plans and up-to-date statistics and indexes reveal why performance changed after the load. Restarting, adding users and dropping indexes are unfocused or harmful.

  3. Question 18

    A database index is slowing writes heavily and is rarely used by queries. What is the most appropriate action?

    1. AReviewing index usage statistics and removing or consolidating the unused index after testing
    2. BAdding more indexes on the same columns with different sort orders and included columns for reporting
    3. CDisabling statistics collection permanently on every table so the optimizer relies on outdated row estimates
    4. DStoring the index keys and values in a plain text file on disk for quick reads
    Show answer and explanation
    Correct answer: A

    Reviewing usage and removing unused indexes after testing reduces write overhead without harming queries. Adding indexes increases writes, disabling statistics harms the optimizer, and text files are not indexes.

  4. Question 19

    Database integrity checks report corruption on a table. What should the administrator do first?

    1. ARunning an in-place repair command such as DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS on the production database during peak hours without a backup
    2. BDeleting the corrupted table outright with DROP TABLE and recreating it empty from the application's schema scripts before anyone checks the data
    3. CIsolating the affected table, verifying the most recent good backup, and planning a restore or repair under change control
    4. DIgnoring the integrity check report because the application still returns results for most queries and the errors appear in the nightly job logs
    Show answer and explanation
    Correct answer: C

    Isolation, verifying a good backup and planned repair protect data while fixing corruption. Unplanned repairs without backups, deletion and ignoring reports risk permanent loss.

  5. Question 20

    A database log file grows continuously and fills the disk. The database uses full recovery mode and no log backups are taken. What is the root cause?

    1. AThe server's time zone is incorrect for the host clock
    2. BThe application uses too many tables across the schema
    3. CLog backups are not being taken, so the log cannot be truncated
    4. DThe database has too many users connected at one time
    Show answer and explanation
    Correct answer: C

    In full recovery mode, the transaction log is truncated only after log backups. Without them, the log keeps growing. User counts, time zones and table counts do not cause this behavior.

Business Continuity (5 questions)

  1. Question 21

    Which backup type copies only the data that changed since the last full backup?

    1. AOS disk snapshot
    2. BPrinted report backup
    3. CFull database backup
    4. DDifferential backup
    Show answer and explanation
    Correct answer: D

    A differential backup captures changes since the last full backup. A full backup copies everything, OS snapshots are not database backups, and print backups are not backup types.

  2. Question 22

    Which two factors affect the choice of backup frequency? (Choose two.)

    1. AThe rate at which data changes
    2. BThe brand of the backup software
    3. CThe color of the backup report
    4. DThe number of users in the marketing team
    5. EThe acceptable data loss for the business
    Show answer and explanation
    Correct answer: A, E

    Acceptable data loss and the rate of change determine how often backups must run. Software brands, report colors and team sizes do not drive frequency.

  3. Question 23

    A company has a recovery time objective of one hour and a recovery point objective of 15 minutes for a critical database. Which backup strategy best meets these targets?

    1. ANo backups because the system is redundant across two data centers and clusters
    2. BFrequent transaction log backups combined with a replicated standby that can be promoted quickly
    3. CA weekly full backup stored on the same server as the production database, with daily differential backups kept on that host
    4. DA monthly tape archive stored offsite with a courier pickup every thirty days and a restore lag of several days
    Show answer and explanation
    Correct answer: B

    Log backups every few minutes and a standby that can be promoted meet a 15-minute recovery point and a one-hour recovery time. Weekly, monthly and no backups cannot meet those objectives.

  4. Question 24

    A disaster recovery site must be ready to take over within minutes with current data. Which DR strategy fits this requirement?

    1. ACold site with no hardware or data
    2. BHot standby with continuous replication
    3. CBackup tapes shipped after the disaster
    4. DRestoring from a paper record
    Show answer and explanation
    Correct answer: B

    A hot standby with continuous replication is ready for rapid failover with current data. Cold sites, shipped tapes and paper records take much longer and lose more data.

  5. Question 25

    A database backup completed successfully, but a restore test fails. What is the most important lesson for the disaster recovery plan?

    1. ABackups are unnecessary once the nightly job log reports success
    2. BBackups must be validated through regular restore tests, not assumed to work
    3. CThe restore test can be skipped because the backup job succeeded
    4. DThe backup file size alone determines recoverability in practice
    Show answer and explanation
    Correct answer: B

    A completed backup does not prove it can be restored, so regular restore tests are required. The other statements assume recoverability without evidence.

Data Integration (5 questions)

  1. Question 26

    A nightly ETL job loads data from several source systems into a warehouse, and the load fails when a source adds a new column. Which practice reduces this risk?

    1. AIgnoring schema changes because the warehouse data is read-only and loads continue to run regardless
    2. BValidating source schemas and handling schema changes with versioned mappings and alerts
    3. CRenaming the warehouse tables every night with a date suffix so downstream reports reference the new name
    4. DRemoving the ETL job entirely and loading each source by hand through a spreadsheet export each morning
    Show answer and explanation
    Correct answer: B

    Schema validation, versioned mappings and alerts catch and manage source changes before they break loads. Ignoring changes, renaming tables and removing jobs do not solve the problem.

  2. Question 27

    A data quality check finds that some customer records have invalid postal codes. Which action should be taken first?

    1. AIgnoring the issue because invalid codes affect a small percentage of rows in the extract
    2. BChanging every postal code in the customer table to the same placeholder value
    3. CDeleting every customer with unusual postal codes without review from the CRM
    4. DProfiling the data to identify the cause and applying validation rules at the source where possible
    Show answer and explanation
    Correct answer: D

    Profiling identifies the root cause and validation at the source prevents recurrence. Mass deletion, ignoring issues and overwriting values damage data quality.

  3. Question 28

    An ELT process loads raw data into a cloud warehouse, then transforms it inside the warehouse. What is the main advantage of this pattern?

    1. AIt skips the need for data quality checks since loads are trusted
    2. BIt removes every need for a data model, so raw tables can be queried directly by analysts
    3. CIt uses the warehouse's compute to transform data at scale after loading raw data
    4. DIt stores data in plain text files exported from the source systems on a schedule
    Show answer and explanation
    Correct answer: C

    ELT uses scalable warehouse compute after loading raw data, which supports flexible transformations. It still requires quality checks and a data model, and it is not limited to text files.

  4. Question 29

    A connection between an integration tool and a database fails intermittently with timeouts. Which troubleshooting step is most useful first?

    1. AChanging the database name and the connection string so that the integration tool points to a renamed instance on another host
    2. BRestarting the integration tool's logo and splash screen so the user interface reloads its branding assets before the job runs
    3. CChecking network connectivity, firewall rules, and connection pool settings between the tool and the database
    4. DDeleting the integration job and its checkpoint table so the next run rebuilds the target from scratch without first testing the link
    Show answer and explanation
    Correct answer: C

    Network paths, firewall rules and connection pools are the common causes of intermittent timeouts. Renaming databases, logos and deleting jobs do not address connectivity.

  5. Question 30

    Which two practices improve data quality in a pipeline? (Choose two.)

    1. ALogging rejected records with reasons for review
    2. BLoading all records even when they fail validation without review
    3. CChanging source data without notice
    4. DIgnoring duplicate records
    5. EValidating records against rules at load time
    Show answer and explanation
    Correct answer: A, E

    Validation at load and logging rejected records with reasons improve quality. Unreviewed loads, silent source changes and ignored duplicates degrade data.