McAfee-Secured Website

Exam Code: DP-800

Exam Name: Developing AI-Enabled Database Solutions

Certification Provider: Microsoft

Corresponding Certification: Microsoft Certified: SQL AI Developer Associate

Questions & Answers

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!

Questions & Answers

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 quarterly sales report requires a temporary named query used just once in the statement. Which acceptance check should accompany the remediation?

  1. The endpoint returns valid JSON with escaped strings and expected property names.
  2. A test hierarchy returns each expected descendant and terminates on cyclic or deep inputs as designed.
  3. The following SELECT references the CTE and returns the same rows without persistent objects.
  4. Round-trip tests preserve accents and non-Latin symbols without truncation or replacement.

Explanation

Correct Answer

C:

A CTE is a named subquery scoped to a single following DML statement, not an automatic materialized table. The observation “The following SELECT references the CTE and returns the same rows without persistent objects” directly confirms the chosen action worked for this situation: A quarterly sales report requires a temporary named query used just once in the statement.

Incorrect Answers

A:

JSON construction functions handle quoting/escaping rather than brittle string assembly. This observation would not establish that the required result occurred for A quarterly sales report requires a temporary named query used just once in the statement. The confirming test is: The following SELECT references the CTE and returns the same rows without persistent objects.

B:

Recursive CTEs repeatedly apply the recursive member and require attention to cycles and MAXRECURSION. This observation would not establish that the required result occurred for A quarterly sales report requires a temporary named query used just once in the statement. The confirming test is: The following SELECT references the CTE and returns the same rows without persistent objects.

D:

Data type and collation selection determine representability, comparisons, and index behavior. This observation would not establish that the required result occurred for A quarterly sales report requires a temporary named query used just once in the statement. The confirming test is: The following SELECT references the CTE and returns the same rows without persistent objects.

Question 171

A team wants to break a complex single SELECT into readable logical steps without creating persistent objects. Which implementation most directly satisfies the stated requirement?

  1. Use a common table expression introduced by WITH for the statement-scoped result.
  2. Aggregate at the intended grain or use a properly scoped DISTINCT key count.
  3. Use RANK over the score ordering rather than DENSE_RANK.
  4. Choose a tested uniqueness rule, such as a filtered unique index for present emails, matching SQL Server NULL semantics.

Explanation

Correct Answer

A:

A CTE is a named subquery scoped to a single following DML statement, not an automatic materialized table. For the described situation—A team wants to break a complex single SELECT into readable logical steps without creating persistent objects—the targeted fix is Use a common table expression introduced by WITH for the statement-scoped result. A successful test should show: The following SELECT references the CTE and returns the same rows without persistent objects.

Incorrect Answers

B:

Join multiplicity affects counts and sums; choose grain-aware aggregation before interpreting totals. But that technique does not solve the stated problem—A team wants to break a complex single SELECT into readable logical steps without creating persistent objects. The needed intervention is Use a common table expression introduced by WITH for the statement-scoped result.

C:

RANK leaves gaps after ties while DENSE_RANK does not. But that technique does not solve the stated problem—A team wants to break a complex single SELECT into readable logical steps without creating persistent objects. The needed intervention is Use a common table expression introduced by WITH for the statement-scoped result.

D:

SQL Server UNIQUE and filtered unique indexes differ in how missing values can be represented. But that technique does not solve the stated problem—A team wants to break a complex single SELECT into readable logical steps without creating persistent objects. The needed intervention is Use a common table expression introduced by WITH for the statement-scoped result.

Question 172

A team wants to break a complex single SELECT into readable logical steps without creating persistent objects. What measurement or test best demonstrates that the requirement is satisfied?

  1. Rows retain transaction detail and show the expected progressive totals.
  2. Valid nonmatching rows remain while genuinely matching identifiers are excluded.
  3. Round-trip storage and equality tests preserve account identifiers without unintended padding or truncation.
  4. The following SELECT references the CTE and returns the same rows without persistent objects.

Explanation

Correct Answer

D:

A CTE is a named subquery scoped to a single following DML statement, not an automatic materialized table. The observation “The following SELECT references the CTE and returns the same rows without persistent objects” directly confirms the chosen action worked for this situation: A team wants to break a complex single SELECT into readable logical steps without creating persistent objects.

Incorrect Answers

A:

Window aggregates calculate across partitions without reducing the number of input rows. This observation would not establish that the required result occurred for A team wants to break a complex single SELECT into readable logical steps without creating persistent objects. The confirming test is: The following SELECT references the CTE and returns the same rows without persistent objects.

B:

NOT IN with unknown NULL comparison can filter unexpectedly; NOT EXISTS tests matching row presence. This observation would not establish that the required result occurred for A team wants to break a complex single SELECT into readable logical steps without creating persistent objects. The confirming test is: The following SELECT references the CTE and returns the same rows without persistent objects.

C:

CHAR uses fixed-width padding; variable-width text types better fit identifiers whose lengths differ. This observation would not establish that the required result occurred for A team wants to break a complex single SELECT into readable logical steps without creating persistent objects. The confirming test is: The following SELECT references the CTE and returns the same rows without persistent objects.

Question 173

A hierarchy report must traverse all descendants in a manager table. What is the most defensible next technical action?

  1. Build a recursive CTE with anchor and recursive members plus a safe termination strategy.
  2. Aggregate at the intended grain or use a properly scoped DISTINCT key count.
  3. Use supported JSON_ARRAYAGG or an equivalent validated JSON aggregation mechanism for that SQL version.
  4. Evaluate SQL ledger tables and digest verification for tamper-evident data.

Explanation

Correct Answer

A:

Recursive CTEs repeatedly apply the recursive member and require attention to cycles and MAXRECURSION. For the described situation—A hierarchy report must traverse all descendants in a manager table—the targeted fix is Build a recursive CTE with anchor and recursive members plus a safe termination strategy. A successful test should show: A test hierarchy returns each expected descendant and terminates on cyclic or deep inputs as designed.

Incorrect Answers

B:

Join multiplicity affects counts and sums; choose grain-aware aggregation before interpreting totals. But that technique does not solve the stated problem—A hierarchy report must traverse all descendants in a manager table. The needed intervention is Build a recursive CTE with anchor and recursive members plus a safe termination strategy.

C:

JSON array aggregation preserves JSON structure rather than emitting unsafe concatenated text. But that technique does not solve the stated problem—A hierarchy report must traverse all descendants in a manager table. The needed intervention is Build a recursive CTE with anchor and recursive members plus a safe termination strategy.

D:

Ledger adds cryptographic tamper-evidence; temporal history alone is not cryptographic proof. But that technique does not solve the stated problem—A hierarchy report must traverse all descendants in a manager table. The needed intervention is Build a recursive CTE with anchor and recursive members plus a safe termination strategy.

Question 174

A hierarchy report must traverse all descendants in a manager table. Which result distinguishes a successful implementation from a cosmetic change?

  1. A test hierarchy returns each expected descendant and terminates on cyclic or deep inputs as designed.
  2. Valid nonmatching rows remain while genuinely matching identifiers are excluded.
  3. The sample set shows all intended token instances replaced and nonmatches preserved.
  4. The app reads approved profile fields and cannot access restricted source columns.

Explanation

Correct Answer

A:

Recursive CTEs repeatedly apply the recursive member and require attention to cycles and MAXRECURSION. The observation “A test hierarchy returns each expected descendant and terminates on cyclic or deep inputs as designed” directly confirms the chosen action worked for this situation: A hierarchy report must traverse all descendants in a manager table.

Incorrect Answers

B:

NOT IN with unknown NULL comparison can filter unexpectedly; NOT EXISTS tests matching row presence. This observation would not establish that the required result occurred for A hierarchy report must traverse all descendants in a manager table. The confirming test is: A test hierarchy returns each expected descendant and terminates on cyclic or deep inputs as designed.

C:

REGEXP_REPLACE substitutes pattern matches and requires explicit tests for greediness and escaping. This observation would not establish that the required result occurred for A hierarchy report must traverse all descendants in a manager table. The confirming test is: A test hierarchy returns each expected descendant and terminates on cyclic or deep inputs as designed.

D:

Views can provide a data-access interface when underlying object permissions are managed appropriately. This observation would not establish that the required result occurred for A hierarchy report must traverse all descendants in a manager table. The confirming test is: A test hierarchy returns each expected descendant and terminates on cyclic or deep inputs as designed.

Question 175

A tree of product categories needs a query that starts at a root and continues through parent-child links. Which solution is the most appropriate for the scenario?

  1. Build a recursive CTE with anchor and recursive members plus a safe termination strategy.
  2. Use REGEXP_REPLACE with a carefully tested replacement and pattern.
  3. Aggregate at the intended grain or use a properly scoped DISTINCT key count.
  4. Use an INSTEAD OF trigger where supported to implement the intended view-write translation.

Explanation

Correct Answer

A:

Recursive CTEs repeatedly apply the recursive member and require attention to cycles and MAXRECURSION. For the described situation—A tree of product categories needs a query that starts at a root and continues through parent-child links—the targeted fix is Build a recursive CTE with anchor and recursive members plus a safe termination strategy. A successful test should show: A test hierarchy returns each expected descendant and terminates on cyclic or deep inputs as designed.

Incorrect Answers

B:

REGEXP_REPLACE substitutes pattern matches and requires explicit tests for greediness and escaping. But that technique does not solve the stated problem—A tree of product categories needs a query that starts at a root and continues through parent-child links. The needed intervention is Build a recursive CTE with anchor and recursive members plus a safe termination strategy.

C:

Join multiplicity affects counts and sums; choose grain-aware aggregation before interpreting totals. But that technique does not solve the stated problem—A tree of product categories needs a query that starts at a root and continues through parent-child links. The needed intervention is Build a recursive CTE with anchor and recursive members plus a safe termination strategy.

D:

INSTEAD OF logic replaces the direct DML action; AFTER logic runs after a supported modification. But that technique does not solve the stated problem—A tree of product categories needs a query that starts at a root and continues through parent-child links. The needed intervention is Build a recursive CTE with anchor and recursive members plus a safe termination strategy.

Question 176

A tree of product categories needs a query that starts at a root and continues through parent-child links. What should a technical reviewer verify before approving completion?

  1. The query returns the expected extracted token and handles missing matches correctly.
  2. The sample set shows all intended token instances replaced and nonmatches preserved.
  3. Duplicate non-null emails fail while multiple missing values behave per the documented rule.
  4. A test hierarchy returns each expected descendant and terminates on cyclic or deep inputs as designed.

Explanation

Correct Answer

D:

Recursive CTEs repeatedly apply the recursive member and require attention to cycles and MAXRECURSION. The observation “A test hierarchy returns each expected descendant and terminates on cyclic or deep inputs as designed” directly confirms the chosen action worked for this situation: A tree of product categories needs a query that starts at a root and continues through parent-child links.

Incorrect Answers

A:

REGEXP_SUBSTR extracts matching text; REGEXP_LIKE only evaluates whether a match exists. This observation would not establish that the required result occurred for A tree of product categories needs a query that starts at a root and continues through parent-child links. The confirming test is: A test hierarchy returns each expected descendant and terminates on cyclic or deep inputs as designed.

B:

REGEXP_REPLACE substitutes pattern matches and requires explicit tests for greediness and escaping. This observation would not establish that the required result occurred for A tree of product categories needs a query that starts at a root and continues through parent-child links. The confirming test is: A test hierarchy returns each expected descendant and terminates on cyclic or deep inputs as designed.

C:

SQL Server UNIQUE and filtered unique indexes differ in how missing values can be represented. This observation would not establish that the required result occurred for A tree of product categories needs a query that starts at a root and continues through parent-child links. The confirming test is: A test hierarchy returns each expected descendant and terminates on cyclic or deep inputs as designed.

Question 177

An accountant wants each transaction with its running account balance in date order. What should the SQL developer implement after verifying the conditions?

  1. Use SUM(amount) OVER with PARTITION BY account and an explicit ordered window frame.
  2. Use explicit TRY...CATCH with transaction control and THROW or XACT_STATE-aware rollback.
  3. Use LEFT JOIN from customers to invoices and manage NULL totals appropriately.
  4. Use a scalar function when a single return value and performance constraints fit.

Explanation

Correct Answer

A:

Window aggregates calculate across partitions without reducing the number of input rows. For the described situation—An accountant wants each transaction with its running account balance in date order—the targeted fix is Use SUM(amount) OVER with PARTITION BY account and an explicit ordered window frame. A successful test should show: Rows retain transaction detail and show the expected progressive totals.

Incorrect Answers

B:

Error handling and atomic transactions prevent partial multi-step business operations. But that technique does not solve the stated problem—An accountant wants each transaction with its running account balance in date order. The needed intervention is Use SUM(amount) OVER with PARTITION BY account and an explicit ordered window frame.

C:

Outer joins preserve unmatched left rows; aggregate semantics require choosing COUNT expressions carefully. But that technique does not solve the stated problem—An accountant wants each transaction with its running account balance in date order. The needed intervention is Use SUM(amount) OVER with PARTITION BY account and an explicit ordered window frame.

D:

A scalar function encapsulates a single-result expression, not a full transaction workflow. But that technique does not solve the stated problem—An accountant wants each transaction with its running account balance in date order. The needed intervention is Use SUM(amount) OVER with PARTITION BY account and an explicit ordered window frame.

Question 178

An accountant wants each transaction with its running account balance in date order. Which verification finding is most specific to this scenario?

  1. A test hierarchy returns each expected descendant and terminates on cyclic or deep inputs as designed.
  2. Only relationships present in the graph edge table appear in query output.
  3. An insert against the view updates the expected base tables and rejects invalid mappings.
  4. Rows retain transaction detail and show the expected progressive totals.

Explanation

Correct Answer

D:

Window aggregates calculate across partitions without reducing the number of input rows. The observation “Rows retain transaction detail and show the expected progressive totals” directly confirms the chosen action worked for this situation: An accountant wants each transaction with its running account balance in date order.

Incorrect Answers

A:

Recursive CTEs repeatedly apply the recursive member and require attention to cycles and MAXRECURSION. This observation would not establish that the required result occurred for An accountant wants each transaction with its running account balance in date order. The confirming test is: Rows retain transaction detail and show the expected progressive totals.

B:

MATCH expresses graph patterns over node and edge relationships in SQL graph queries. This observation would not establish that the required result occurred for An accountant wants each transaction with its running account balance in date order. The confirming test is: Rows retain transaction detail and show the expected progressive totals.

C:

INSTEAD OF logic replaces the direct DML action; AFTER logic runs after a supported modification. This observation would not establish that the required result occurred for An accountant wants each transaction with its running account balance in date order. The confirming test is: Rows retain transaction detail and show the expected progressive totals.

Question 179

A dashboard should show cumulative sales by store and day without collapsing detail rows. Which approach achieves the intended result without bypassing the stated controls?

  1. Use SUM(amount) OVER with PARTITION BY account and an explicit ordered window frame.
  2. Use NOT EXISTS with a proper correlated predicate to avoid NOT IN NULL three-valued pitfalls.
  3. Use GROUP BY with HAVING on the aggregated measure.
  4. Design a nonclustered index with appropriate leading tenant and timestamp keys and useful INCLUDE columns.

Explanation

Correct Answer

A:

Window aggregates calculate across partitions without reducing the number of input rows. For the described situation—A dashboard should show cumulative sales by store and day without collapsing detail rows—the targeted fix is Use SUM(amount) OVER with PARTITION BY account and an explicit ordered window frame. A successful test should show: Rows retain transaction detail and show the expected progressive totals.

Incorrect Answers

B:

NOT IN with unknown NULL comparison can filter unexpectedly; NOT EXISTS tests matching row presence. But that technique does not solve the stated problem—A dashboard should show cumulative sales by store and day without collapsing detail rows. The needed intervention is Use SUM(amount) OVER with PARTITION BY account and an explicit ordered window frame.

C:

HAVING filters groups after aggregation; WHERE filters source rows before aggregation. But that technique does not solve the stated problem—A dashboard should show cumulative sales by store and day without collapsing detail rows. The needed intervention is Use SUM(amount) OVER with PARTITION BY account and an explicit ordered window frame.

D:

An index keyed by selective predicates improves retrieval when its order follows workload filters and sorts. But that technique does not solve the stated problem—A dashboard should show cumulative sales by store and day without collapsing detail rows. The needed intervention is Use SUM(amount) OVER with PARTITION BY account and an explicit ordered window frame.

Question 180

A dashboard should show cumulative sales by store and day without collapsing detail rows. How should the team validate the selected design?

  1. Rows retain transaction detail and show the expected progressive totals.
  2. Valid samples pass and malformed strings are rejected on the target SQL version.
  3. The inserted rows map each JSON element into typed fields with validation.
  4. New rows omitting status receive the approved initial value automatically.

Explanation

Correct Answer

A:

Window aggregates calculate across partitions without reducing the number of input rows. The observation “Rows retain transaction detail and show the expected progressive totals” directly confirms the chosen action worked for this situation: A dashboard should show cumulative sales by store and day without collapsing detail rows.

Incorrect Answers

B:

Regular-expression matching expresses pattern validation more precisely than broad LIKE wildcards. This observation would not establish that the required result occurred for A dashboard should show cumulative sales by store and day without collapsing detail rows. The confirming test is: Rows retain transaction detail and show the expected progressive totals.

C:

OPENJSON exposes elements as rows and explicit schema maps document fields into SQL types. This observation would not establish that the required result occurred for A dashboard should show cumulative sales by store and day without collapsing detail rows. The confirming test is: Rows retain transaction detail and show the expected progressive totals.

D:

DEFAULT supplies a value only when a column is omitted or DEFAULT is requested; it is not a replacement for CHECK validation. This observation would not establish that the required result occurred for A dashboard should show cumulative sales by store and day without collapsing detail rows. The confirming test is: Rows retain transaction detail and show the expected progressive totals.

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