Microsoft DP-800 (Developing AI-Enabled Database Solutions) Practice Test 36

Question 350

A sales report needs several grouping levels in one aggregate query, then must retain only those grouped results whose totals exceed a threshold. Which TWO constructs address this? Choose TWO.

  1. SUM(...) OVER
  2. HAVING
  3. COUNT(DISTINCT CustomerId)
  4. TRY_CONVERT
  5. GROUPING SETS

Explanation

Correct Answers

B:

HAVING filters aggregated groups after grouping, unlike a row-level WHERE filter; used in combination with GROUPING SETS, it handles a separate requirement of this combined workflow.

E:

GROUPING SETS explicitly requests several grouping levels in a single grouped SELECT; used in combination with HAVING, it handles a separate requirement of this combined workflow.

Incorrect Answers

A:

A window aggregate computes over partitions while maintaining input-row granularity rather than grouping detail away; this differs from the HAVING and GROUPING SETS requirement described in the scenario.

C:

COUNT(DISTINCT ...) counts distinct non-NULL values in a group or qualifying result set; the alternative cannot substitute for HAVING and GROUPING SETS under the given constraints.

D:

TRY_CONVERT attempts type conversion and returns NULL for many invalid values where a regular CONVERT would fail; the condition in the stem is resolved by HAVING and GROUPING SETS, not by this feature alone.

Question 351

An assistant will access a production SQL environment, but only audited schema-read operations may be invoked. Which option satisfies the desired behavior most directly?

  1. Allowlisted MCP tools
  2. Stored procedure execution boundary
  3. Restricted DAB entity permissions
  4. Parameterized SQL statements

Explanation

Correct Answer

A:

An allowlisted set of restricted MCP tools reduces agent capabilities; prompt instructions cannot substitute for backend enforcement.

Incorrect Answers

B:

Exposing a reviewed procedure with EXECUTE permission provides a constrained interface compared with granting direct table writes; for the stated conditions, the deciding feature is Allowlisted MCP tools rather than this behavior.

C:

DAB entity permissions can limit operations exposed to each role, complementing database-side permissions; it addresses a different step; the question calls for Allowlisted MCP tools.

D:

SQL parameters separate supplied values from executable query syntax and are a core mitigation against injection; this differs from the Allowlisted MCP tools requirement described in the scenario.

Question 352

A SQL-backed RAG service starts with a user question and must convert it into a vector, retrieve relevant source passages, and assemble those passages before contacting the generator. Which THREE stages are required? Choose THREE.

  1. Question embedding
  2. Answer generation
  3. Context assembly
  4. Full-text document retrieval
  5. Vector retrieval
  6. Retrieval-augmented generation

Explanation

Correct Answers

A:

Embedding the user query with a compatible model creates the numeric representation needed for vector similarity search; used in combination with Context assembly, Vector retrieval, it handles a separate requirement of this combined workflow.

C:

Context assembly selects and formats trusted retrieved passages within the token budget before model invocation; used in combination with Question embedding, Vector retrieval, it handles a separate requirement of this combined workflow.

E:

Vector-neighbor search ranks stored embeddings by proximity to the query representation and returns source identifiers; used in combination with Context assembly, Question embedding, it handles a separate requirement of this combined workflow.

Incorrect Answers

B:

An LLM can generate a response conditioned on retrieved evidence and the user's question, with grounding controls applied; the condition in the stem is resolved by Question embedding and Context assembly and Vector retrieval, not by this feature alone.

D:

Full-text search can locate matching passages directly; generation adds complexity when the task is simply retrieval; that function is not interchangeable with Question embedding and Context assembly and Vector retrieval for the requested result.

F:

RAG retrieves relevant current documents and provides them as context to the generator, keeping knowledge retrieval distinct from model weights; it addresses a different step; the question calls for Question embedding and Context assembly and Vector retrieval.

Question 353

A DBA is choosing an Azure SQL compute tier and needs historical CPU and storage utilization trends to establish whether the workload is regularly capacity-constrained. Which technique applies to this scenario?

  1. Azure Monitor metrics
  2. Log Analytics diagnostic queries
  3. Query Store runtime report
  4. Azure SQL Query Performance Insight

Explanation

Correct Answer

A:

Azure Monitor resource metrics expose time-series consumption and pressure signals for capacity planning, but do not replace Query Store for statement-level plan diagnosis.

Incorrect Answers

B:

A Log Analytics workspace stores forwarded log records and supports cross-resource KQL analysis where diagnostics are configured; this differs from the Azure Monitor metrics requirement described in the scenario.

C:

Query Store retains statement-level plan and runtime aggregates suitable for comparing the same query across intervals; the required capability here is Azure Monitor metrics, which this operation does not supply.

D:

Query Performance Insight provides a focused view of captured SQL query performance metrics and historical trends; the alternative cannot substitute for Azure Monitor metrics under the given constraints.

Question 354

A stored procedure emits query results with nested property paths and must serialize the entire relational result set as JSON. Which option would you recommend?

  1. JSON_ARRAYAGG
  2. JSON_OBJECT
  3. FOR JSON PATH
  4. JSON_QUERY over one column

Explanation

Correct Answer

C:

FOR JSON PATH converts SELECT output into a path-controlled JSON representation, often suitable for API responses.

Incorrect Answers

A:

JSON_ARRAYAGG constructs an aggregate JSON array from group members rather than returning multiple relational rows; the missing behavior is FOR JSON PATH; this mechanism does something else.

B:

JSON_OBJECT creates a named-property JSON structure from scalar values and other JSON expressions; that function is not interchangeable with FOR JSON PATH for the requested result.

D:

JSON_QUERY extracts a JSON object or array fragment at a path; it does not serialize every selected relational row with nested property paths.

Question 355

A legacy matching process deliberately groups names by approximate phonetic pronunciation rather than exact letter substitutions. Which option would you recommend?

  1. EDIT_DISTANCE_SIMILARITY
  2. EDIT_DISTANCE
  3. SOUNDEX
  4. REGEXP_LIKE

Explanation

Correct Answer

C:

SOUNDEX converts a string into a phonetic code for approximate pronunciation comparison, with limits across languages.

Incorrect Answers

A:

EDIT_DISTANCE_SIMILARITY converts an edit-distance relationship into a similarity measure, making threshold comparisons easier; the required capability here is SOUNDEX, which this operation does not supply.

B:

EDIT_DISTANCE measures Levenshtein-style edit operations between two strings and returns a distance rather than a similarity label; the alternative cannot substitute for SOUNDEX under the given constraints.

D:

REGEXP_LIKE checks a string against a regular-expression pattern; it does not map similarly pronounced names to shared phonetic codes.

Question 356

A transaction must prevent rows it already read from being modified by other sessions before its commit, but phantom protection is not required. Which option would you recommend?

  1. REPEATABLE READ
  2. SNAPSHOT isolation
  3. SERIALIZABLE
  4. READ COMMITTED SNAPSHOT

Explanation

Correct Answer

A:

REPEATABLE READ holds appropriate locks to protect previously read rows, but qualifying new rows may still appear on subsequent range queries.

Incorrect Answers

B:

SNAPSHOT delivers transaction-consistent versioned reads while maintaining update conflict considerations; it addresses a different step; the question calls for REPEATABLE READ.

C:

SERIALIZABLE applies range semantics that protect qualifying predicate ranges against conflicting concurrent insertions; this differs from the REPEATABLE READ requirement described in the scenario.

D:

Statement-level row versioning under RCSI can allow committed reads without ordinary shared read locks; the required capability here is REPEATABLE READ, which this operation does not supply.

Question 357

The query embedding is available and the pipeline must find the top relevant document chunks from an indexed SQL corpus. Which approach should the team select?

  1. Vector retrieval
  2. Full-text document retrieval
  3. Context assembly
  4. Retrieval-augmented generation
  5. Question embedding
  6. Answer generation

Explanation

Correct Answer

A:

Vector-neighbor search ranks stored embeddings by proximity to the query representation and returns source identifiers.

Incorrect Answers

B:

Full-text search can locate matching passages directly; generation adds complexity when the task is simply retrieval; the missing behavior is Vector retrieval; this mechanism does something else.

C:

Context assembly selects and formats trusted retrieved passages within the token budget before model invocation; that function is not interchangeable with Vector retrieval for the requested result.

D:

RAG retrieves relevant current documents and provides them as context to the generator, keeping knowledge retrieval distinct from model weights; for the stated conditions, the deciding feature is Vector retrieval rather than this behavior.

E:

Embedding the user query with a compatible model creates the numeric representation needed for vector similarity search; it addresses a different step; the question calls for Vector retrieval.

F:

An LLM can generate a response conditioned on retrieved evidence and the user's question, with grounding controls applied; this differs from the Vector retrieval requirement described in the scenario.

Question 358

An engineer working in a supported Fabric experience wants contextual assistance generating and explaining SQL against a Fabric SQL database. Which choice best meets these constraints?

  1. SQL endpoint authorization
  2. Execute and inspect results
  3. Copilot in Fabric chat
  4. Fabric tenant feature settings
  5. Authorization policy on tool calls

Explanation

Correct Answer

C:

Microsoft Copilot in Fabric offers AI-assisted authoring in supported Fabric workloads and remains subject to tenant and capacity configuration.

Incorrect Answers

A:

Database authorization still applies when queries are proposed by Copilot, so appropriate grants are required separately; this differs from the Copilot in Fabric chat requirement described in the scenario.

B:

Running the query against representative data and reviewing results validates behavior; an AI suggestion is not a substitute for execution; the required capability here is Copilot in Fabric chat, which this operation does not supply.

D:

Tenant-level Fabric settings can govern Copilot availability, with scope and capacity prerequisites depending on the specific feature; the condition in the stem is resolved by Copilot in Fabric chat, not by this feature alone.

E:

Tool-side authorization and command restrictions should enforce permitted operations independently of natural-language prompt instructions; the missing behavior is Copilot in Fabric chat; this mechanism does something else.

Question 359

A matching investigation focuses on short person names where character transpositions and shared prefixes affect similarity. Which technique applies to this scenario?

  1. SOUNDEX
  2. EDIT_DISTANCE
  3. REGEXP_MATCHES
  4. JARO_WINKLER_DISTANCE
  5. EDIT_DISTANCE_SIMILARITY

Explanation

Correct Answer

D:

JARO_WINKLER_DISTANCE applies Jaro-Winkler name-oriented matching behavior rather than simple adjacent-edit counting.

Incorrect Answers

A:

SOUNDEX converts a string into a phonetic code for approximate pronunciation comparison, with limits across languages; that function is not interchangeable with JARO_WINKLER_DISTANCE for the requested result.

B:

EDIT_DISTANCE measures Levenshtein-style edit operations between two strings and returns a distance rather than a similarity label; for the stated conditions, the deciding feature is JARO_WINKLER_DISTANCE rather than this behavior.

C:

REGEXP_MATCHES returns matching occurrences as relational rows on supporting versions, unlike a single scalar extraction; it addresses a different step; the question calls for JARO_WINKLER_DISTANCE.

E:

EDIT_DISTANCE_SIMILARITY converts an edit-distance relationship into a similarity measure, making threshold comparisons easier; the required capability here is JARO_WINKLER_DISTANCE, which this operation does not supply.

Question 360

Removing a parent shopping cart must also remove its related cart lines in the same enforced referential action. Which choice best meets these constraints?

  1. CHECK with IN predicate
  2. NOT NULL constraint
  3. UNIQUE constraint
  4. ON DELETE CASCADE foreign key action

Explanation

Correct Answer

D:

A cascading DELETE relationship automatically deletes referencing child rows when the referenced parent is deleted.

Incorrect Answers

A:

A CHECK expression can restrict a column to an enumerated set during writes, without requiring every client to implement the same validation; the alternative cannot substitute for ON DELETE CASCADE foreign key action under the given constraints.

B:

NOT NULL forbids the NULL marker on the designated column, regardless of whether a default exists; the condition in the stem is resolved by ON DELETE CASCADE foreign key action, not by this feature alone.

C:

UNIQUE enforces a candidate key distinct from the primary key and prevents duplicate non-NULL key combinations according to platform semantics; the missing behavior is ON DELETE CASCADE foreign key action; this mechanism does something else.

Developing AI-Enabled Database Solutions (DP-800) Practice Tests