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 18

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.

Question 171

A sales detail listing compares each invoice amount with its department average. The target SQL engine supports the named T-SQL feature. Which change most directly meets the requirement?

  1. Use LEAD() to inspect the next row in a series
  2. Use ROW_NUMBER() OVER with a deterministic partition and order
  3. Use RANK() when tied positions should leave ranking gaps
  4. Use AVG() OVER(PARTITION BY ...) for a group statistic on each detail row

Explanation

Correct Answer

D:

This implementation preserves each input row while exposing its peer-group average.

Incorrect Answers

A:

That technique accesses following rows inside an ordered partition. This does not meet the need for a method that preserves each input row while exposing its peer-group average.

B:

This choice assigns a distinct row position inside each group. This does not meet the need for a method that preserves each input row while exposing its peer-group average.

C:

That technique models competition-style positions with ties. This does not meet the need for a method that preserves each input row while exposing its peer-group average.

Question 172

A telemetry batch arrives as a JSON list that must be joined to relational records. What is the most appropriate next design step?

  1. Use JSON_ARRAYAGG for an array aggregated from rows
  2. Use JSON_VALUE to extract a scalar property from a JSON document
  3. Use JSON_MODIFY to change one property in JSON text
  4. Use OPENJSON to transform a JSON array into a relational rowset

Explanation

Correct Answer

D:

This implementation fits because it expands array elements into table-shaped rows. That addresses the stated technical constraint.

Incorrect Answers

A:

This option combines multiple SQL row values into one JSON array. This does not meet the need for a method that expands array elements into table-shaped rows.

B:

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

C:

That technique returns updated JSON content at a selected path. This does not meet the need for a method that expands array elements into table-shaped rows.

Question 173

An import needs to test which date strings are convertible without stopping the scan. What is the most appropriate design decision?

  1. Use TRY_CONVERT to safely handle mixed input types
  2. Use STRING_AGG to combine multiple row strings into one delimited output
  3. Use OUTER APPLY to preserve left rows without matches
  4. Use COALESCE to select the first non-null expression

Explanation

Correct Answer

A:

This choice returns NULL for failed supported conversion instead of aborting each row.

Incorrect Answers

B:

This choice aggregates text values over a group. This does not meet the need for a method that returns NULL for failed supported conversion instead of aborting each row.

C:

This alternative extends a correlated table expression while retaining unmatched outer rows. This does not meet the need for a method that returns NULL for failed supported conversion instead of aborting each row.

D:

This alternative supplies a fallback among ordered nullable values. This does not meet the need for a method that returns NULL for failed supported conversion instead of aborting each row.

Question 174

A staging job must separate non-JSON messages before extracting properties. Which solution is most directly applicable?

  1. Use JSON_VALUE to extract a scalar property from a JSON document
  2. Use JSON_ARRAYAGG for an array aggregated from rows
  3. Use JSON_MODIFY to change one property in JSON text
  4. Use ISJSON before processing untrusted JSON text

Explanation

Correct Answer

D:

This choice tests whether input has valid JSON structure.

Incorrect Answers

A:

This choice returns a single scalar SQL expression from a JSON path. This does not meet the need for a method that tests whether input has valid JSON structure.

B:

This choice combines multiple SQL row values into one JSON array. This does not meet the need for a method that tests whether input has valid JSON structure.

C:

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.

Question 175

A league table must show 1,1,3 for tied highest results. The target SQL engine supports the named T-SQL feature. What is the most appropriate design decision?

  1. Use ROW_NUMBER() OVER with a deterministic partition and order
  2. Use NTILE(4) OVER to divide ordered rows into four buckets
  3. Use LAG() to compare a row with its preceding ordered value
  4. Use RANK() when tied positions should leave ranking gaps

Explanation

Correct Answer

D:

This implementation models competition-style positions with ties.

Incorrect Answers

A:

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

B:

This choice 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 alternative accesses a prior row without a self-join. This does not meet the need for a method that models competition-style positions with ties.

Question 176

A parent report needs optional child details while preserving parents with no children. What should the engineer choose?

  1. Use COALESCE to select the first non-null expression
  2. Use STRING_AGG to combine multiple row strings into one delimited output
  3. Use CROSS APPLY for a correlated table expression that must match
  4. Use OUTER APPLY to preserve left rows without matches

Explanation

Correct Answer

D:

This implementation fits because it extends a correlated table expression while retaining unmatched outer rows. That addresses the stated technical constraint.

Incorrect Answers

A:

This alternative supplies a fallback among ordered nullable values. This does not meet the need for a method that extends a correlated table expression while retaining unmatched outer rows.

B:

That technique aggregates text values over a group. This does not meet the need for a method that extends a correlated table expression while retaining unmatched outer rows.

C:

This option evaluates a right-hand rowset per left row and excludes unmatched rows. This does not meet the need for a method that extends a correlated table expression while retaining unmatched outer rows.

Question 177

An equipment hierarchy must enumerate nested components beneath a root assembly. Which approach would you recommend?

  1. Use REGEXP_REPLACE to replace portions matching a regular expression
  2. Use a recursive CTE for a bounded hierarchy traversal
  3. Use REGEXP_COUNT to count matches of a pattern
  4. Use REGEXP_SUBSTR to extract a regex-matched fragment

Explanation

Correct Answer

B:

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

Incorrect Answers

A:

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

C:

That technique returns the number of regex occurrences. This does not meet the need for a method that repeatedly joins a parent-child relationship across levels.

D:

That technique returns a portion of text matching a pattern. This does not meet the need for a method that repeatedly joins a parent-child relationship across levels.

Question 178

A compliance query finds suppliers connected to a flagged entity through approval edges. What should the engineer choose?

  1. Use REGEXP_SUBSTR to extract a regex-matched fragment
  2. Use EDIT_DISTANCE for character-edit similarity analysis
  3. Use REGEXP_COUNT to count matches of a pattern
  4. Use a graph MATCH query over node and edge tables

Explanation

Correct Answer

D:

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

Incorrect Answers

A:

This option returns a portion of text matching a pattern. This does not meet the need for a method that follows graph-pattern relationships stored in graph tables.

B:

This option 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.

C:

This choice returns the number of regex occurrences. This does not meet the need for a method that follows graph-pattern relationships stored in graph tables.

Question 179

A preferences document includes an array of notification channels to return intact. The target SQL engine supports the named T-SQL feature. Which solution is most directly applicable?

  1. Use JSON_OBJECT to construct a JSON object from relational values
  2. Use JSON_VALUE to extract a scalar property from a JSON document
  3. Use JSON_QUERY to extract an object or array fragment
  4. Use JSON_ARRAYAGG for an array aggregated from rows

Explanation

Correct Answer

C:

This choice returns JSON-shaped nested content rather than a scalar.

Incorrect Answers

A:

This alternative serializes named SQL expressions into a single JSON object. This does not meet the need for a method that returns JSON-shaped nested content rather than a scalar.

B:

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

D:

That technique combines multiple SQL row values into one JSON array. This does not meet the need for a method that returns JSON-shaped nested content rather than a scalar.

Question 180

A report needs a display name using nickname first then legal name. Which approach would you recommend?

  1. Use COALESCE to select the first non-null expression
  2. Use EXISTS for a correlated existence test
  3. Use TRY_CONVERT to safely handle mixed input types
  4. Use STRING_AGG to combine multiple row strings into one delimited output

Explanation

Correct Answer

A:

This approach supplies a fallback among ordered nullable values.

Incorrect Answers

B:

This option filters rows by presence without returning or duplicating matching children. This does not meet the need for a method that supplies a fallback among ordered nullable values.

C:

This alternative returns NULL for failed supported conversion instead of aborting each row. This does not meet the need for a method that supplies a fallback among ordered nullable values.

D:

This option aggregates text values over a group. This does not meet the need for a method that supplies a fallback among ordered nullable values.

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