McAfee Secured

Exam Code: DP-800

Exam Name: Developing AI-Enabled Database Solutions

Certification Provider: Microsoft

Corresponding Certification: Microsoft Certified: SQL AI Developer Associate

100% Microsoft DP-800 Guaranteed Success With Testking Training Materials

Get Real DP-800 Exam Questions & Expert Verified Answers as Experienced on the Real Exam!

101 Questions & Answers with Testing Engine

"Developing AI-Enabled Database Solutions Exam", also known as DP-800 exam, is a Microsoft certification exam.

Pass your tests with the always up-to-date DP-800 Exam Engine. Your DP-800 training materials keep you at the head of the pack!

Satisfaction Guaranteed

Money Back Guarantee

Testking provides hassle-free money back guarantee with our products. That is because we have 100% trust in the abilities of our professional and experience product team, and our record is a proof of that.

99.6% PASS RATE
Was: $137.49
Now: $124.99

Microsoft DP-800 Practice Test 17

Question 160

A rate report needs to avoid runtime failure for branches with zero orders. The target SQL engine supports the named T-SQL feature. Which technical choice addresses this need?

  1. Use COALESCE to select the first non-null expression
  2. Use OUTER APPLY to preserve left rows without matches
  3. Use NULLIF to avoid division by zero for an explicitly optional denominator
  4. Use EXISTS for a correlated existence test

Explanation

Correct Answer

C:

This implementation converts a matching zero denominator into NULL.

Incorrect Answers

A:

This alternative supplies a fallback among ordered nullable values. This does not meet the need for a method that converts a matching zero denominator into NULL.

B:

This choice extends a correlated table expression while retaining unmatched outer rows. This does not meet the need for a method that converts a matching zero denominator into NULL.

D:

That technique filters rows by presence without returning or duplicating matching children. This does not meet the need for a method that converts a matching zero denominator into NULL.

Question 161

A sensor report needs the preceding reading for each device. Which solution best satisfies the stated constraint?

  1. Use LEAD() to inspect the next row in a series
  2. Use AVG() OVER(PARTITION BY ...) for a group statistic on each detail row
  3. Use SUM(amount) OVER(PARTITION BY ... ORDER BY ...) with an appropriate frame
  4. Use LAG() to compare a row with its preceding ordered value

Explanation

Correct Answer

D:

This approach accesses a prior row without a self-join.

Incorrect Answers

A:

This option accesses following rows inside an ordered partition. This does not meet the need for a method that accesses a prior row without a self-join.

B:

This option preserves each input row while exposing its peer-group average. This does not meet the need for a method that accesses a prior row without a self-join.

C:

This alternative calculates a running total while retaining detail rows. This does not meet the need for a method that accesses a prior row without a self-join.

Question 162

A dashboard displays active tag names for each ticket in one comma-separated cell. Which solution best satisfies the stated constraint?

  1. Use NOT EXISTS for an anti-join condition
  2. Use EXISTS for a correlated existence test
  3. Use NULLIF to avoid division by zero for an explicitly optional denominator
  4. Use STRING_AGG to combine multiple row strings into one delimited output

Explanation

Correct Answer

D:

This choice aggregates text values over a group.

Incorrect Answers

A:

This option keeps rows with no matching counterpart. This does not meet the need for a method that aggregates text values over a group.

B:

That technique filters rows by presence without returning or duplicating matching children. This does not meet the need for a method that aggregates text values over a group.

C:

This option converts a matching zero denominator into NULL. This does not meet the need for a method that aggregates text values over a group.

Question 163

A support app must change just the notificationLevel property in a stored JSON document. Which change most directly meets the requirement?

  1. Use JSON_MODIFY to change one property in JSON text
  2. Use JSON_ARRAYAGG for an array aggregated from rows
  3. Use JSON_OBJECT to construct a JSON object from relational values
  4. Use JSON_VALUE to extract a scalar property from a JSON document

Explanation

Correct Answer

A:

This implementation fits because it returns updated JSON content at a selected path. That addresses the stated technical constraint.

Incorrect Answers

B:

This option combines multiple SQL row values into one JSON array. This does not meet the need for a method that returns updated JSON content at a selected path.

C:

This choice serializes named SQL expressions into a single JSON object. This does not meet the need for a method that returns updated JSON content at a selected path.

D:

This choice returns a single scalar SQL expression from a JSON path. This does not meet the need for a method that returns updated JSON content at a selected path.

Question 164

An ingest endpoint receives arbitrary payload strings and needs to reject malformed JSON. The target SQL engine supports the named T-SQL feature. Which solution best satisfies the stated constraint?

  1. Use JSON_MODIFY to change one property in JSON text
  2. Use JSON_QUERY to extract an object or array fragment
  3. Use ISJSON before processing untrusted JSON text
  4. Use JSON_OBJECT to construct a JSON object from relational values

Explanation

Correct Answer

C:

This implementation tests whether input has valid JSON structure.

Incorrect Answers

A:

This option returns updated JSON content at a selected path. This does not meet the need for a method that tests whether input has valid JSON structure.

B:

This choice returns JSON-shaped nested content rather than a scalar. This does not meet the need for a method that tests whether input has valid JSON structure.

D:

This alternative serializes named SQL expressions into a single JSON object. This does not meet the need for a method that tests whether input has valid JSON structure.

Question 165

An organization chart query returns all descendants under one manager. Which solution is most directly applicable?

  1. Use a graph MATCH query over node and edge tables
  2. Use a recursive CTE for a bounded hierarchy traversal
  3. Use REGEXP_LIKE for a regular-expression predicate on supported engines
  4. Use REGEXP_REPLACE to replace portions matching a regular expression

Explanation

Correct Answer

B:

This implementation repeatedly joins a parent-child relationship across levels.

Incorrect Answers

A:

This choice follows graph-pattern relationships stored in graph tables. This does not meet the need for a method that repeatedly joins a parent-child relationship across levels.

C:

This choice evaluates whether a string matches a regex expression. This does not meet the need for a method that repeatedly joins a parent-child relationship across levels.

D:

This alternative transforms substrings identified by a regex. This does not meet the need for a method that repeatedly joins a parent-child relationship across levels.

Question 166

Two locations sharing first place should make the next location third, not second. Which solution is most directly applicable?

  1. Use DENSE_RANK() for tied rankings without numeric gaps
  2. Use NTILE(4) OVER to divide ordered rows into four buckets
  3. Use ROW_NUMBER() OVER with a deterministic partition and order
  4. Use RANK() when tied positions should leave ranking gaps

Explanation

Correct Answer

D:

This choice models competition-style positions with ties.

Incorrect Answers

A:

That technique assigns equal ranks for ties and consecutive distinct ranks. This does not meet the need for a method that models competition-style positions with ties.

B:

That technique assigns approximate quantile groups by ordered position. This does not meet the need for a method that models competition-style positions with ties.

C:

This choice assigns a distinct row position inside each group. This does not meet the need for a method that models competition-style positions with ties.

Question 167

A network investigation traverses device-to-account edge relationships. Which solution is most directly applicable?

  1. Use REGEXP_REPLACE to replace portions matching a regular expression
  2. Use a graph MATCH query over node and edge tables
  3. Use JARO_WINKLER_DISTANCE for name-oriented fuzzy comparison
  4. Use EDIT_DISTANCE for character-edit similarity analysis

Explanation

Correct Answer

B:

This approach follows graph-pattern relationships stored in graph tables.

Incorrect Answers

A:

This option transforms substrings identified by a regex. This does not meet the need for a method that follows graph-pattern relationships stored in graph tables.

C:

That technique measures likeness of string pairs especially near their prefixes. This does not meet the need for a method that follows graph-pattern relationships stored in graph tables.

D:

This choice measures edit operations needed between two strings. This does not meet the need for a method that follows graph-pattern relationships stored in graph tables.

Question 168

An import must check whether invoice codes fit an anchored alphanumeric pattern. The target SQL engine supports the named T-SQL feature. What should the engineer choose?

  1. Use REGEXP_SUBSTR to extract a regex-matched fragment
  2. Use a recursive CTE for a bounded hierarchy traversal
  3. Use JARO_WINKLER_DISTANCE for name-oriented fuzzy comparison
  4. Use REGEXP_LIKE for a regular-expression predicate on supported engines

Explanation

Correct Answer

D:

This implementation fits because it evaluates whether a string matches a regex expression. That addresses the stated technical constraint.

Incorrect Answers

A:

That technique returns a portion of text matching a pattern. This does not meet the need for a method that evaluates whether a string matches a regex expression.

B:

This choice repeatedly joins a parent-child relationship across levels. This does not meet the need for a method that evaluates whether a string matches a regex expression.

C:

This choice measures likeness of string pairs especially near their prefixes. This does not meet the need for a method that evaluates whether a string matches a regex expression.

Question 169

A payment payload stores one currencyCode string that must be compared in a WHERE clause. What should the engineer choose?

  1. Use JSON_VALUE to extract a scalar property from a JSON document
  2. Use OPENJSON to transform a JSON array into a relational rowset
  3. Use JSON_QUERY to extract an object or array fragment
  4. Use JSON_OBJECT to construct a JSON object from relational values

Explanation

Correct Answer

A:

This choice returns a single scalar SQL expression from a JSON path.

Incorrect Answers

B:

That technique expands array elements into table-shaped rows. This does not meet the need for a method that returns a single scalar SQL expression from a JSON path.

C:

This alternative returns JSON-shaped nested content rather than a scalar. This does not meet the need for a method that returns a single scalar SQL expression from a JSON path.

D:

That technique serializes named SQL expressions into a single JSON object. This does not meet the need for a method that returns a single scalar SQL expression from a JSON path.

Question 170

A report expands each parent row into matching parameterized child results only. What should the engineer choose?

  1. Use COALESCE to select the first non-null expression
  2. Use EXISTS for a correlated existence test
  3. Use CROSS APPLY for a correlated table expression that must match
  4. Use TRY_CONVERT to safely handle mixed input types

Explanation

Correct Answer

C:

This approach evaluates a right-hand rowset per left row and excludes unmatched rows.

Incorrect Answers

A:

This choice supplies a fallback among ordered nullable values. This does not meet the need for a method that evaluates a right-hand rowset per left row and excludes unmatched rows.

B:

That technique filters rows by presence without returning or duplicating matching children. This does not meet the need for a method that evaluates a right-hand rowset per left row and excludes unmatched rows.

D:

This option returns NULL for failed supported conversion instead of aborting each row. This does not meet the need for a method that evaluates a right-hand rowset per left row and excludes unmatched rows.

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