I85PREP
⌘ K
DataSys+ study materials
DataSys+ study resources

DataSys+ practice questions

One question at a time. Build your DataSys+ understanding.

First 50 of 190 questions. Each one lists the choices, the correct answer, and an explanation. The checker at the top of this page saves progress in this browser when it is available.

Continue in the practice tool above to load more questions, 50 at a time.

Question 1Data, Databases, and Information Retrieval

Which type of database management system organizes data into tables with rows and columns, enforces a predefined schema, and uses SQL as its primary query language?

  1. Document database (NoSQL)
  2. Relational database (RDBMS) Correct answer.
  3. Key-value store
  4. Graph database

Explanation

A relational database management system (RDBMS) such as PostgreSQL, MySQL, Oracle, or SQL Server stores data in tables with rows (tuples) and columns (attributes), enforces a predefined schema, and uses SQL for data manipulation. Document databases like MongoDB store JSON-like documents; key-value stores like Redis pair keys with values; graph databases like Neo4j represent data as nodes and edges.

Question 2Data, Databases, and Information Retrieval

Which NoSQL database type is best suited for storing user session data that requires sub-millisecond reads with simple lookups by a unique identifier?

  1. Neo4j graph database
  2. Cassandra wide-column store
  3. Redis key-value store Correct answer.
  4. Elasticsearch search engine

Explanation

Redis is an in-memory key-value store optimized for extremely fast lookups by a single key, making it ideal for session caching. Neo4j is optimized for relationship-heavy queries; Cassandra targets high-write time-series and wide-column workloads; Elasticsearch is for full-text search and log analytics.

Question 3Data, Databases, and Information Retrieval

Which SQL statement retrieves data from one or more tables?

  1. INSERT
  2. UPDATE
  3. SELECT Correct answer.
  4. DELETE

Explanation

SELECT retrieves rows from one or more tables and is the primary Data Manipulation Language (DML) statement for querying. INSERT adds new rows, UPDATE modifies existing rows, and DELETE removes rows.

Question 4Data, Databases, and Information Retrieval

Which JOIN type returns only the rows that have matching values in both tables?

  1. LEFT JOIN
  2. RIGHT JOIN
  3. INNER JOIN Correct answer.
  4. FULL OUTER JOIN

Explanation

INNER JOIN returns only rows where the join condition is satisfied in both tables. LEFT JOIN returns all rows from the left table plus matches; RIGHT JOIN does the opposite. FULL OUTER JOIN returns all rows from both tables, with NULLs where there is no match.

Question 5Data, Databases, and Information Retrieval

A table contains repeating groups of phone numbers stored as a comma-separated list in a single column. To achieve First Normal Form (1NF), which change is required?

  1. Split the phone numbers into a separate table with one row per number Correct answer.
  2. Add a primary key constraint on the existing column
  3. Create a foreign key pointing to itself
  4. Add a CHECK constraint that validates the format

Explanation

1NF requires atomic values - each column must hold a single, indivisible value and no repeating groups. Moving the phone numbers to a related table with one row per number eliminates the repeating group. A primary key, self-referencing FK, or CHECK constraint does not resolve a 1NF violation.

Question 6Data, Databases, and Information Retrieval

Which property of ACID guarantees that once a transaction commits, its changes survive a subsequent system crash?

  1. Atomicity
  2. Consistency
  3. Isolation
  4. Durability Correct answer.

Explanation

Durability ensures committed changes are persisted to non-volatile storage (typically via the transaction log / WAL) and survive failures. Atomicity means all-or-nothing execution, Consistency means the database moves between valid states, and Isolation governs how concurrent transactions interact.

Question 7Data, Databases, and Information Retrieval

A developer wants to retrieve the top 3 highest-salaried employees per department. Which SQL feature is most appropriate?

  1. A simple ORDER BY with LIMIT
  2. A window function with ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) Correct answer.
  3. A SELF JOIN with WHERE clause
  4. A CROSS JOIN followed by GROUP BY

Explanation

Window functions like ROW_NUMBER() with PARTITION BY compute a ranking per group without collapsing rows. ORDER BY LIMIT only works on the full result set. A self join can approximate this but is harder to read and usually slower. CROSS JOIN produces a Cartesian product and is not appropriate.

Question 8Data, Databases, and Information Retrieval

Which constraint ensures that a column cannot contain duplicate values across rows but, unlike PRIMARY KEY, does allow one NULL (in most RDBMSs)?

  1. CHECK
  2. UNIQUE Correct answer.
  3. FOREIGN KEY
  4. NOT NULL

Explanation

A UNIQUE constraint prohibits duplicate values in a column but typically allows a single NULL (behavior varies by engine). PRIMARY KEY additionally disallows NULLs. CHECK enforces a Boolean expression, FOREIGN KEY references another table, and NOT NULL only prevents NULLs without enforcing uniqueness.

Question 9Data, Databases, and Information Retrieval

Which database model is OPTIMAL for representing and querying highly interconnected social-network data where friendship paths of up to six degrees must be traversed efficiently?

  1. Relational database with many-to-many junction tables
  2. Graph database such as Neo4j Correct answer.
  3. Columnstore data warehouse
  4. Time-series database

Explanation

Graph databases like Neo4j store nodes and edges natively and traverse relationships in constant time per hop, making them ideal for multi-hop social network queries. Relational many-to-many joins become exponentially expensive at deep traversal. Columnstore warehouses are for analytics, and time-series databases are for metric-style data.

Question 10Data, Databases, and Information Retrieval

In an OLTP workload of thousands of small, concurrent credit-card transactions, which characteristic is MOST important?

  1. High-throughput bulk aggregation over months of data
  2. Low-latency, high-concurrency reads and writes with strict ACID guarantees Correct answer.
  3. Full-text relevance scoring across documents
  4. Eventual consistency across global regions

Explanation

OLTP (Online Transaction Processing) systems like banking platforms require low-latency, high-concurrency ACID transactions to maintain correct balances. Bulk aggregation is an OLAP concern; relevance scoring is a search engine concern; eventual consistency is typical of globally distributed NoSQL rather than financial OLTP.

Question 11Data, Databases, and Information Retrieval

A star schema data warehouse contains a Sales fact table and Customer, Product, Date, and Store dimension tables. Which statement best describes a FACT table?

  1. It stores descriptive attributes used to filter and group queries
  2. It stores numeric measures and foreign keys to dimension tables Correct answer.
  3. It stores hierarchical relationships between entities
  4. It stores slowly changing reference data

Explanation

A fact table holds measurable, numeric business events (quantity, revenue) along with foreign keys to dimension tables. Dimension tables hold descriptive attributes and hierarchies. Slowly changing data patterns apply to dimensions, not facts.

Question 12Data, Databases, and Information Retrieval

Which SQL clause is used to filter groups after aggregation?

  1. WHERE
  2. HAVING Correct answer.
  3. GROUP BY
  4. ORDER BY

Explanation

HAVING filters rows after GROUP BY has aggregated them (for example, HAVING COUNT(*) > 10). WHERE filters rows before aggregation, GROUP BY defines the grouping, and ORDER BY sorts the final result.

Question 13Data, Databases, and Information Retrieval

A database designer is deciding between a normalized OLTP schema (3NF) and a denormalized reporting star schema. Which statement best captures the main trade-off?

  1. Normalization improves query speed at the cost of storage; denormalization is always cheaper
  2. Normalization reduces redundancy and anomalies; denormalization improves read performance at the cost of redundancy Correct answer.
  3. Normalization guarantees ACID; denormalization requires BASE
  4. Normalization only applies to NoSQL; denormalization only applies to SQL

Explanation

Normalization minimizes data redundancy and prevents update/insert/delete anomalies, which is ideal for OLTP. Denormalization deliberately introduces redundancy to reduce joins and accelerate reporting/OLAP queries. ACID and normal forms are independent concerns, and both apply to relational stores.

Question 14Data, Databases, and Information Retrieval

Which SQL JOIN returns every combination of rows from two tables (Cartesian product)?

  1. INNER JOIN
  2. LEFT JOIN
  3. CROSS JOIN Correct answer.
  4. NATURAL JOIN

Explanation

CROSS JOIN produces the Cartesian product - every row from the left table paired with every row from the right. INNER JOIN requires a matching predicate, LEFT JOIN preserves all left-side rows, and NATURAL JOIN joins on columns with matching names.

Question 15Data, Databases, and Information Retrieval

Which of the following best describes a Common Table Expression (CTE)?

  1. A permanent view stored in the database catalog
  2. A named temporary result set defined within a single statement using WITH Correct answer.
  3. A stored procedure that returns a result set
  4. An index type optimized for range queries

Explanation

A CTE (WITH clause) defines a named, temporary result set that exists only for the duration of a single SQL statement. It improves readability and supports recursion. A view is persistent, a stored procedure is precompiled code, and indexes are storage structures.

Question 16Data, Databases, and Information Retrieval

Which statement about BASE (Basically Available, Soft state, Eventually consistent) is MOST accurate compared to ACID?

  1. BASE systems enforce strong consistency after every write
  2. BASE trades immediate consistency for availability and partition tolerance Correct answer.
  3. BASE replaces transactions with two-phase commit across nodes
  4. BASE only applies to columnstore analytics engines

Explanation

BASE is the design philosophy for many NoSQL systems: they accept eventual consistency in return for greater availability and partition tolerance (per the CAP theorem). Strong consistency after every write describes ACID. Two-phase commit is a specific protocol, and BASE applies to many NoSQL categories, not just columnstore.

Question 17Data, Databases, and Information Retrieval

Which type of NoSQL database stores data in columns grouped by column families and is designed for very high write throughput across a distributed cluster?

  1. Document database (MongoDB)
  2. Key-value store (Redis)
  3. Wide-column store (Cassandra) Correct answer.
  4. Graph database (Neo4j)

Explanation

Wide-column stores like Apache Cassandra group data into column families and distribute writes across many nodes using consistent hashing, delivering very high write throughput. MongoDB stores JSON-like documents, Redis is a key-value cache, and Neo4j is relationship-focused.

Question 18Data, Databases, and Information Retrieval

Given tables Orders(customer_id, total) and Customers(id, name), which query returns each customer's name with their total order amount, including customers who have no orders?

  1. SELECT c.name, SUM(o.total) FROM Customers c INNER JOIN Orders o ON o.customer_id = c.id GROUP BY c.name
  2. SELECT c.name, SUM(o.total) FROM Customers c LEFT JOIN Orders o ON o.customer_id = c.id GROUP BY c.name Correct answer.
  3. SELECT c.name, SUM(o.total) FROM Customers c CROSS JOIN Orders o GROUP BY c.name
  4. SELECT c.name FROM Customers c WHERE c.id NOT IN (SELECT customer_id FROM Orders)

Explanation

A LEFT JOIN preserves every row from Customers even if Orders has no match - matching rows sum normally, unmatched customers return NULL (which SUM treats as 0 rows). INNER JOIN would drop customers without orders. CROSS JOIN produces a Cartesian product, and the NOT IN query only returns customers with no orders.

Question 19Data, Databases, and Information Retrieval

Which data type is MOST appropriate for storing exact monetary values in a financial application?

  1. FLOAT or DOUBLE (binary floating point)
  2. DECIMAL / NUMERIC with defined precision and scale Correct answer.
  3. VARCHAR representation of the number
  4. INT multiplied by 100

Explanation

DECIMAL/NUMERIC stores exact fixed-point values without the rounding error inherent in IEEE-754 floating point, making it the standard for currency. FLOAT/DOUBLE can produce off-by-a-penny errors. Storing currency as VARCHAR loses arithmetic capability, and INT-times-100 is a workaround but loses clarity and scale flexibility.

Question 20Data, Databases, and Information Retrieval

A BCNF violation most commonly indicates which underlying issue in a schema?

  1. Repeating groups in a single column
  2. A non-trivial functional dependency on a non-superkey determinant Correct answer.
  3. Missing foreign keys
  4. Lack of a primary key

Explanation

Boyce-Codd Normal Form requires every non-trivial functional dependency X -> Y to have X be a superkey. A BCNF violation means some non-superkey attribute determines another attribute, producing redundancy. Repeating groups violate 1NF; missing keys are separate integrity issues.

Question 21Data, Databases, and Information Retrieval

Which of the following is TRUE about a surrogate primary key compared to a natural key?

  1. A surrogate key is derived from business data and changes when the entity changes
  2. A surrogate key is a system-generated identifier that is stable and has no business meaning Correct answer.
  3. A surrogate key is always a composite of multiple columns
  4. A surrogate key eliminates the need for any indexes

Explanation

Surrogate keys (for example, auto-incrementing IDs or UUIDs) are system-generated, stable identifiers with no business meaning, which insulates relationships from changes in business attributes. Natural keys come from business data. Surrogate keys are typically single-column and are themselves indexed (usually via the PK).

Question 22Data, Databases, and Information Retrieval

Which SQL statement is classified as Data Definition Language (DDL)?

  1. SELECT
  2. INSERT
  3. CREATE TABLE Correct answer.
  4. COMMIT

Explanation

DDL statements define or modify schema objects: CREATE, ALTER, DROP, TRUNCATE. SELECT/INSERT are DML (Data Manipulation Language), and COMMIT is TCL (Transaction Control Language).

Question 23Data, Databases, and Information Retrieval

A snowflake schema differs from a star schema primarily in that it:

  1. Stores facts and dimensions in the same table
  2. Normalizes dimension tables into sub-dimensions Correct answer.
  3. Uses only columnar storage
  4. Requires eventual consistency

Explanation

A snowflake schema normalizes dimension tables further - a dimension like Product might be split into Product, Category, Subcategory tables. This reduces storage but requires more joins. A star schema keeps dimensions denormalized. Storage format and consistency are unrelated to star vs snowflake.

Question 24Data, Databases, and Information Retrieval

Which SQL feature groups multiple statements so that they either ALL succeed or ALL fail?

  1. A transaction (BEGIN ... COMMIT/ROLLBACK) Correct answer.
  2. A view
  3. An index
  4. A schema

Explanation

Transactions provide atomicity across multiple statements via BEGIN ... COMMIT or ROLLBACK. Views are named queries, indexes speed lookups, and schemas are logical containers.

Question 25Data, Databases, and Information Retrieval

Which SQL feature allows a transaction to mark an intermediate point to which a partial ROLLBACK can return?

  1. SAVEPOINT Correct answer.
  2. CHECKPOINT
  3. DUMP
  4. SNAPSHOT

Explanation

SAVEPOINT marks a location inside a transaction so ROLLBACK TO SAVEPOINT can undo work done after that point without rolling back the whole transaction. CHECKPOINT is a storage/recovery mechanism, DUMP is vendor-specific, and SNAPSHOT refers to a database snapshot, not a mid-transaction marker.

Question 26Database deployment, performance and recovery lifecycle

Among these choices, which concerns actual PostgreSQL memory and connection capacity planning for the supplied workload, rather than a desktop or editor presentation preference?

  1. Default desktop wallpaper for the server
  2. The shared_buffers, work_mem, and max_connections parameters Correct answer.
  3. A custom font for pgAdmin
  4. The timezone of the operating-system user profile

Explanation

Memory and connection settings affect resource planning under the actual PostgreSQL workload. Per-operation allocations can multiply under concurrency, so measure and test rather than assume that increasing every value helps. Production readiness also requires justified access, integrity, backup, monitoring and recovery controls; parameter tuning alone is not sufficient.

Question 27Database deployment, performance and recovery lifecycle

During a migration from SQL Server on-premises to Azure SQL Database, which tool is specifically designed by Microsoft to assess schema and code compatibility?

  1. SQL Server Management Studio only
  2. Data Migration Assistant (DMA) Correct answer.
  3. Windows Performance Monitor
  4. Notepad++ with a SQL plugin

Explanation

Microsoft's Data Migration Assistant (DMA) assesses schema/code compatibility and identifies breaking changes, deprecated features, and blocking issues before migration to Azure SQL. SSMS alone does not produce a structured compatibility assessment; PerfMon measures Windows performance; Notepad++ does not analyze schemas.

Question 28Database deployment, performance and recovery lifecycle

Which ETL step transforms raw source data into a format aligned with the target schema before loading?

  1. Extract
  2. Transform Correct answer.
  3. Load
  4. Index

Explanation

Transform reshapes, cleans, enriches, and conforms data (type conversion, deduplication, business rules) before it is Loaded into the target. Extract reads from source, Load writes to target, and indexing happens inside the target database.

Question 29Database deployment, performance and recovery lifecycle

A modern cloud data warehouse like Snowflake or BigQuery often uses ELT instead of ETL. Which is the MAIN advantage?

  1. ELT performs no transformations, so data stays raw
  2. ELT leverages the warehouse's massive compute to transform data after loading, enabling faster ingestion and reusable raw data Correct answer.
  3. ELT does not require a schema
  4. ELT is only for unstructured data

Explanation

ELT loads raw data into a scalable cloud warehouse and transforms it in-place using the warehouse's MPP compute. This accelerates ingestion, preserves raw data for re-processing, and simplifies pipelines. Transformations are still applied - just after load. ELT still uses schemas and works for structured and semi-structured data.

Question 30Database deployment, performance and recovery lifecycle

What is the PRIMARY purpose of a schema in a relational database?

  1. To encrypt the data at rest
  2. To act as a logical container that groups related objects (tables, views, procedures) Correct answer.
  3. To guarantee high availability
  4. To perform query optimization

Explanation

A schema is a logical namespace/container that groups related database objects and supports permission boundaries. Encryption, availability, and query optimization are separate concerns handled by TDE, replication/clustering, and the query optimizer respectively.

Question 31Database deployment, performance and recovery lifecycle

A DBA must install MySQL 8 on a new Ubuntu server. Which sequence represents the CORRECT deployment order?

  1. Start service, install package, configure, secure installation
  2. Install package, start service, run mysql_secure_installation, configure parameters Correct answer.
  3. Configure parameters, install package, start service
  4. Run mysql_secure_installation, install package, start service, configure

Explanation

The standard deployment flow is: install the package (apt install mysql-server), start/enable the service, run mysql_secure_installation to set the root password and remove test data, then tune my.cnf for workload-specific parameters. The other sequences violate dependency order.

Question 32Database deployment, performance and recovery lifecycle

In a containerized deployment of PostgreSQL, which Kubernetes object is MOST appropriate to provide stable network identity and persistent storage per pod?

  1. Deployment
  2. DaemonSet
  3. StatefulSet with PersistentVolumeClaims Correct answer.
  4. Job

Explanation

StatefulSets give each pod a stable network identity (pod-0, pod-1, ...) and allow each to bind to its own PersistentVolumeClaim, which is what databases require. Deployments are for stateless workloads, DaemonSets run one pod per node for node-level agents, and Jobs run to completion.

Question 33Database deployment, performance and recovery lifecycle

Which migration strategy moves data continuously while keeping the source and target databases in sync to minimize cutover downtime?

  1. Big-bang migration
  2. Parallel (dual-write) migration
  3. Change Data Capture (CDC) based migration Correct answer.
  4. Restore-from-backup only

Explanation

CDC captures row-level changes from the source's transaction log and replays them on the target, allowing near-zero-downtime cutover. Big-bang migrations require a full downtime window. Dual-write is a related pattern but requires application changes. A backup/restore alone produces a stale copy.

Question 34Database deployment, performance and recovery lifecycle

What is the primary purpose of a staging environment in the database deployment lifecycle?

  1. To serve production traffic during peak load
  2. To provide a production-like environment for final testing before release Correct answer.
  3. To replace backups
  4. To permanently store archived data

Explanation

Staging mirrors production configuration and data volume so that releases, migrations, and performance changes can be validated before production deployment. It does not serve real users, replace backups, or act as an archive.

Question 35Database deployment, performance and recovery lifecycle

Which of the following is an example of Infrastructure as Code for deploying a database instance?

  1. Clicking through the AWS Console to create an RDS instance
  2. Writing a Terraform aws_db_instance resource in version control Correct answer.
  3. Emailing the DBA a deployment request
  4. Opening a ticket in a help desk system

Explanation

Infrastructure as Code means defining infrastructure declaratively in version-controlled files (Terraform, CloudFormation, Bicep) so deployments are repeatable and auditable. Console clicks, emails, and tickets are manual processes.

Question 36Database deployment, performance and recovery lifecycle

A DBA needs to import a 500 GB CSV file into PostgreSQL as quickly as possible. Which approach is MOST efficient?

  1. Run individual INSERT statements from a script
  2. Use COPY (or \copy) to bulk-load the file Correct answer.
  3. Open the file in a text editor and paste it into psql
  4. Create 500 million single-row transactions

Explanation

COPY bypasses row-by-row parsing overhead and writes directly into the table at near disk-throughput speed. Individual INSERTs or one-row transactions generate massive WAL and per-row overhead. Pasting is impractical and slower.

Question 37Database deployment, performance and recovery lifecycle

Which cloud database service is a fully managed relational offering from AWS that supports engines like MySQL, PostgreSQL, Oracle, and SQL Server?

  1. Amazon S3
  2. Amazon RDS Correct answer.
  3. Amazon DynamoDB
  4. Amazon Redshift

Explanation

Amazon RDS is AWS's managed relational database service supporting MySQL, PostgreSQL, MariaDB, Oracle, SQL Server, and Aurora. S3 is object storage, DynamoDB is NoSQL key-value/document, and Redshift is a columnar data warehouse.

Question 38Database deployment, performance and recovery lifecycle

Which deployment pattern uses two identical environments (Blue and Green) to enable near-zero-downtime releases and instant rollback?

  1. Canary deployment
  2. Blue-green deployment Correct answer.
  3. Rolling deployment
  4. Recreate deployment

Explanation

In blue-green, the idle environment is upgraded and tested; once validated, traffic is switched over (often at the DNS/load-balancer layer). Rollback is an instantaneous switch back. Canary gradually shifts a percentage of traffic; rolling replaces instances incrementally; recreate tears down and rebuilds with downtime.

Question 39Database deployment, performance and recovery lifecycle

When deploying a database in a Virtual Private Cloud (VPC), which control limits which IP addresses can initiate connections to the database instance?

  1. A security group or network ACL Correct answer.
  2. The database collation setting
  3. The backup retention period
  4. The DB instance's storage type (gp3 vs io2)

Explanation

Security groups (stateful) and network ACLs (stateless) at the VPC level restrict inbound/outbound IP/port access. Collation affects string comparison; backup retention governs recoverability; storage type affects performance - none of those filter network traffic.

Question 40Database deployment, performance and recovery lifecycle

Which file format is BEST suited for loading semi-structured, nested event data into a data warehouse while preserving schema flexibility?

  1. JSON or Parquet Correct answer.
  2. Fixed-width text
  3. Binary executable
  4. Proprietary screen-capture video

Explanation

JSON preserves nested structures, and Parquet stores semi-structured data in a columnar, schema-evolving format ideal for warehouse ingestion. Fixed-width text is rigid, and executables or videos are not tabular data formats.

Question 41Database deployment, performance and recovery lifecycle

What is the role of a change management process in database deployment?

  1. To replace automated testing
  2. To formally review, approve, and track changes to reduce risk and support auditability Correct answer.
  3. To prevent all schema changes indefinitely
  4. To encrypt the database files

Explanation

Change management provides a formal workflow (request, review, approval, implementation, post-review) so database changes are traceable, risk-assessed, and auditable. It complements rather than replaces testing, does not block changes, and has nothing to do with encryption.

Question 42Database deployment, performance and recovery lifecycle

Which Azure database service is a globally distributed, multi-model NoSQL offering that supports document, key-value, graph, and column-family APIs?

  1. Azure SQL Database
  2. Azure Cosmos DB Correct answer.
  3. Azure Data Lake Storage
  4. Azure Synapse Analytics

Explanation

Azure Cosmos DB is a globally distributed NoSQL database that supports multiple APIs (SQL/Core, MongoDB, Cassandra, Gremlin graph, and Table). Azure SQL is a managed RDBMS, Data Lake is object/HDFS-like storage, and Synapse is an analytics/warehouse platform.

Question 43Database deployment, performance and recovery lifecycle

Which scripting / automation approach is MOST appropriate for ensuring schema changes are reproducible across dev, staging, and production?

  1. Manually editing tables in GUI tools
  2. Using a database migration tool (Flyway, Liquibase, Alembic) with version-controlled scripts Correct answer.
  3. Copy-pasting DDL into ad-hoc SSMS windows
  4. Running DDL only in production with no lower environments

Explanation

Migration tools apply ordered, version-controlled DDL/DML scripts identically across environments and track which have been applied, ensuring reproducibility. GUI edits, ad-hoc scripts, and production-only runs create drift and lose traceability.

Question 44Database deployment, performance and recovery lifecycle

A DBA wants to identify which long-running queries are consuming the most CPU on SQL Server. Which tool is MOST appropriate?

  1. Windows Task Manager
  2. Query Store and Dynamic Management Views (DMVs) Correct answer.
  3. The Event Viewer's System log
  4. ipconfig

Explanation

Query Store captures execution plans and runtime stats for historical analysis, and DMVs (like sys.dm_exec_query_stats) expose live CPU, reads, and wait information per query. Task Manager shows process-level CPU but not query-level detail. Event Viewer and ipconfig are unrelated.

Question 45Database deployment, performance and recovery lifecycle

Which index type is typically the DEFAULT and best for range scans and equality lookups on sorted data in most RDBMSs?

  1. B-tree index Correct answer.
  2. Hash index
  3. Bitmap index
  4. Full-text index

Explanation

B-tree indexes maintain sorted order, so they support both equality and range queries (BETWEEN, >, <). Hash indexes are equality-only, bitmap indexes suit low-cardinality columns in data warehouses, and full-text indexes power keyword search.

Question 46Database deployment, performance and recovery lifecycle

A query is running slowly. The DBA views the execution plan and sees a FULL TABLE SCAN on a 200-million-row table filtered by a non-indexed column. What is the MOST appropriate first action?

  1. Reboot the server
  2. Add an index on the filter column and re-evaluate the execution plan Correct answer.
  3. Delete random rows from the table
  4. Change the table's collation

Explanation

A full scan on a large filtered column is a prime candidate for an index that supports the WHERE predicate. After adding the index, re-check the plan to confirm the optimizer uses it. Rebooting, deleting data, or changing collation would not address the missing access path.

Question 47Database deployment, performance and recovery lifecycle

Which SQL command is used in PostgreSQL to reclaim space from dead tuples and update planner statistics?

  1. VACUUM (ANALYZE) Correct answer.
  2. TRUNCATE
  3. REINDEX only
  4. DROP DATABASE

Explanation

VACUUM removes dead tuples left by MVCC, preventing bloat; VACUUM ANALYZE also updates planner statistics. TRUNCATE empties a table. REINDEX rebuilds indexes but does not update table statistics. DROP DATABASE deletes everything.

Question 48Database deployment, performance and recovery lifecycle

Two sessions each hold a lock the other needs and neither can proceed. This is known as:

  1. Thrashing
  2. A deadlock Correct answer.
  3. Starvation
  4. A dirty read

Explanation

A deadlock occurs when two or more sessions hold resources that each other need, creating a cycle. Most RDBMSs detect deadlocks and abort one transaction. Thrashing is excessive paging, starvation is indefinite denial of a resource, and a dirty read is reading uncommitted data.

Question 49Database deployment, performance and recovery lifecycle

Which transaction isolation level prevents dirty reads, non-repeatable reads, AND phantom reads?

  1. READ UNCOMMITTED
  2. READ COMMITTED
  3. REPEATABLE READ
  4. SERIALIZABLE Correct answer.

Explanation

SERIALIZABLE is the strictest level and prevents dirty reads, non-repeatable reads, and phantom reads. READ UNCOMMITTED prevents none. READ COMMITTED prevents only dirty reads. REPEATABLE READ prevents dirty and non-repeatable reads but still allows phantoms in many engines.

Question 50Database deployment, performance and recovery lifecycle

What is the PRIMARY benefit of a covering index?

  1. It eliminates the need for a primary key
  2. It stores the data file itself
  3. It contains all columns needed by a query, so the database can serve the query from the index alone (index-only scan) Correct answer.
  4. It forces every query to use a full scan

Explanation

A covering index includes (via included columns or composite keys) all columns a query needs, so the engine avoids going back to the heap/clustered index - an index-only scan. It does not replace a PK or the data file and does not force full scans.