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 20

Question 190

A marketing report selects products that have any qualifying review. Which approach would you recommend?

  1. Use OUTER APPLY to preserve left rows without matches
  2. Use CROSS APPLY for a correlated table expression that must match
  3. Use TRY_CONVERT to safely handle mixed input types
  4. Use EXISTS for a correlated existence test

Explanation

Correct Answer

D:

This choice filters rows by presence without returning or duplicating matching children.

Incorrect Answers

A:

This option extends a correlated table expression while retaining unmatched outer rows. This does not meet the need for a method that filters rows by presence without returning or duplicating matching children.

B:

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

C:

This choice returns NULL for failed supported conversion instead of aborting each row. This does not meet the need for a method that filters rows by presence without returning or duplicating matching children.

Question 191

A report combines assigned technician initials per incident. The target SQL engine supports the named T-SQL feature. What should the SQL developer implement?

  1. Use STRING_AGG to combine multiple row strings into one delimited output
  2. Use OUTER APPLY to preserve left rows without matches
  3. Use NOT EXISTS for an anti-join condition
  4. Use TRY_CONVERT to safely handle mixed input types

Explanation

Correct Answer

A:

This implementation fits because it aggregates text values over a group. That addresses the stated technical constraint.

Incorrect Answers

B:

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

C:

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

D:

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

Question 192

An inventory log shows a running on-hand quantity after each movement. What is the most appropriate design decision?

  1. Use AVG() OVER(PARTITION BY ...) for a group statistic on each detail row
  2. Use ROW_NUMBER() OVER with a deterministic partition and order
  3. Use SUM(amount) OVER(PARTITION BY ... ORDER BY ...) with an appropriate frame
  4. Use NTILE(4) OVER to divide ordered rows into four buckets

Explanation

Correct Answer

C:

This implementation calculates a running total while retaining detail rows.

Incorrect Answers

A:

That technique preserves each input row while exposing its peer-group average. This does not meet the need for a method that calculates a running total while retaining detail rows.

B:

This option assigns a distinct row position inside each group. This does not meet the need for a method that calculates a running total while retaining detail rows.

D:

That technique assigns approximate quantile groups by ordered position. This does not meet the need for a method that calculates a running total while retaining detail rows.

Question 193

A report counts repeated diagnostic tags inside one string field. What is the most appropriate next design step?

  1. Use REGEXP_COUNT to count matches of a pattern
  2. Use EDIT_DISTANCE for character-edit similarity analysis
  3. Use JARO_WINKLER_DISTANCE for name-oriented fuzzy comparison
  4. Use a recursive CTE for a bounded hierarchy traversal

Explanation

Correct Answer

A:

This choice returns the number of regex occurrences.

Incorrect Answers

B:

This choice measures edit operations needed between two strings. This does not meet the need for a method that returns the number of regex occurrences.

C:

This option measures likeness of string pairs especially near their prefixes. This does not meet the need for a method that returns the number of regex occurrences.

D:

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

Question 194

A support service needs one most recent status update from each ticket. Which change most directly meets the requirement?

  1. Use SUM(amount) OVER(PARTITION BY ... ORDER BY ...) with an appropriate frame
  2. Use RANK() when tied positions should leave ranking gaps
  3. Use ROW_NUMBER() OVER with a deterministic partition and order
  4. Use NTILE(4) OVER to divide ordered rows into four buckets

Explanation

Correct Answer

C:

This approach assigns a distinct row position inside each group.

Incorrect Answers

A:

This option calculates a running total while retaining detail rows. This does not meet the need for a method that assigns a distinct row position inside each group.

B:

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

D:

This option assigns approximate quantile groups by ordered position. This does not meet the need for a method that assigns a distinct row position inside each group.

Question 195

An order metadata object includes one priority number needed as a scalar filter. The target SQL engine supports the named T-SQL feature. What should the SQL developer implement?

  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 JSON_ARRAY to build an array from explicit expressions

Explanation

Correct Answer

A:

This implementation fits because it returns a single scalar SQL expression from a JSON 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 a single scalar SQL expression from a JSON path.

C:

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

D:

This alternative creates JSON array values from supplied arguments. This does not meet the need for a method that returns a single scalar SQL expression from a JSON path.

Question 196

A subscriptions report compares this month's balance to the previous month for each account. What is the most appropriate design decision?

  1. Use DENSE_RANK() for tied rankings without numeric gaps
  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 AVG() OVER(PARTITION BY ...) for a group statistic on each detail row

Explanation

Correct Answer

C:

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

Incorrect Answers

A:

This option assigns equal ranks for ties and consecutive distinct ranks. This does not meet the need for a method that accesses a prior row without a self-join.

B:

This option assigns approximate quantile groups by ordered position. This does not meet the need for a method that accesses a prior row without a self-join.

D:

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

Question 197

A contest report groups identical scores together and numbers the next score consecutively. Which solution best satisfies the stated constraint?

  1. Use AVG() OVER(PARTITION BY ...) for a group statistic on each detail row
  2. Use LAG() to compare a row with its preceding ordered value
  3. Use NTILE(4) OVER to divide ordered rows into four buckets
  4. Use DENSE_RANK() for tied rankings without numeric gaps

Explanation

Correct Answer

D:

This approach assigns equal ranks for ties and consecutive distinct ranks.

Incorrect Answers

A:

This choice preserves each input row while exposing its peer-group average. This does not meet the need for a method that assigns equal ranks for ties and consecutive distinct ranks.

B:

That technique accesses a prior row without a self-join. This does not meet the need for a method that assigns equal ranks for ties and consecutive distinct ranks.

C:

That technique assigns approximate quantile groups by ordered position. This does not meet the need for a method that assigns equal ranks for ties and consecutive distinct ranks.

Question 198

A configuration update edits the nested timezone without reconstructing the entire payload. What is the most appropriate next design step?

  1. Use JSON_MODIFY to change one property in JSON text
  2. Use OPENJSON to transform a JSON array into a relational rowset
  3. Use JSON_ARRAY to build an array from explicit expressions
  4. Use JSON_ARRAYAGG for an array aggregated from rows

Explanation

Correct Answer

A:

This implementation returns updated JSON content at a selected path.

Incorrect Answers

B:

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

C:

This choice creates JSON array values from supplied arguments. This does not meet the need for a method that returns updated JSON content at a selected path.

D:

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

Question 199

A department report aggregates its employee IDs into a JSON collection. The target SQL engine supports the named T-SQL feature. Which solution best satisfies the stated constraint?

  1. Use JSON_ARRAYAGG for an array aggregated from rows
  2. Use JSON_MODIFY to change one property in JSON text
  3. Use JSON_ARRAY to build an array from explicit expressions
  4. Use ISJSON before processing untrusted JSON text

Explanation

Correct Answer

A:

This implementation fits because it combines multiple SQL row values into one JSON array. That addresses the stated technical constraint.

Incorrect Answers

B:

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

C:

This option creates JSON array values from supplied arguments. This does not meet the need for a method that combines multiple SQL row values into one JSON array.

D:

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

Question 200

An address matching prototype compares candidate names by substitution and insertion cost. Which solution is most directly applicable?

  1. Use EDIT_DISTANCE for character-edit similarity analysis
  2. Use REGEXP_REPLACE to replace portions matching a regular expression
  3. Use REGEXP_LIKE for a regular-expression predicate on supported engines
  4. Use a recursive CTE for a bounded hierarchy traversal

Explanation

Correct Answer

A:

This approach measures edit operations needed between two strings.

Incorrect Answers

B:

That technique transforms substrings identified by a regex. This does not meet the need for a method that measures edit operations needed between two strings.

C:

That technique evaluates whether a string matches a regex expression. This does not meet the need for a method that measures edit operations needed between two strings.

D:

This alternative repeatedly joins a parent-child relationship across levels. This does not meet the need for a method that measures edit operations needed between two strings.

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