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 20

Question 190

A reporting tool must compare the current purchase against the customer's immediately preceding purchase. Which result distinguishes a successful implementation from a cosmetic change?

  1. The extracted scalar matches the expected model code on sample documents.
  2. Rows retain transaction detail and show the expected progressive totals.
  3. Invalid values are rejected consistently by interactive and bulk database writes.
  4. Each row displays the correct preceding value, and first rows produce the specified default.

Explanation

Correct Answer

D:

LAG reads a preceding row within a partition without a self-join for each row. The observation “Each row displays the correct preceding value, and first rows produce the specified default” directly confirms the chosen action worked for this situation: A reporting tool must compare the current purchase against the customer's immediately preceding purchase.

Incorrect Answers

A:

JSON_VALUE extracts scalar content; JSON_QUERY targets JSON objects or arrays. This observation would not establish that the required result occurred for A reporting tool must compare the current purchase against the customer's immediately preceding purchase. The confirming test is: Each row displays the correct preceding value, and first rows produce the specified default.

B:

Window aggregates calculate across partitions without reducing the number of input rows. This observation would not establish that the required result occurred for A reporting tool must compare the current purchase against the customer's immediately preceding purchase. The confirming test is: Each row displays the correct preceding value, and first rows produce the specified default.

C:

CHECK constraints enforce domain predicates within the database on all supported write paths. This observation would not establish that the required result occurred for A reporting tool must compare the current purchase against the customer's immediately preceding purchase. The confirming test is: Each row displays the correct preceding value, and first rows produce the specified default.

Question 191

Analysts need the prior day's measured value beside each current time-series row. Which solution is the most appropriate for the scenario?

  1. Use REGEXP_SUBSTR with the desired expression and occurrence rules.
  2. Use JSON_CONTAINS or an appropriate supported JSON-path predicate for the target version.
  3. Use LAG on an explicitly ordered partition by customer or device.
  4. Use an INSTEAD OF trigger where supported to implement the intended view-write translation.

Explanation

Correct Answer

C:

LAG reads a preceding row within a partition without a self-join for each row. For the described situation—Analysts need the prior day's measured value beside each current time-series row—the targeted fix is Use LAG on an explicitly ordered partition by customer or device. A successful test should show: Each row displays the correct preceding value, and first rows produce the specified default.

Incorrect Answers

A:

REGEXP_SUBSTR extracts matching text; REGEXP_LIKE only evaluates whether a match exists. But that technique does not solve the stated problem—Analysts need the prior day's measured value beside each current time-series row. The needed intervention is Use LAG on an explicitly ordered partition by customer or device.

B:

JSON membership functions compare structured content, not formatting-dependent text substrings. But that technique does not solve the stated problem—Analysts need the prior day's measured value beside each current time-series row. The needed intervention is Use LAG on an explicitly ordered partition by customer or device.

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—Analysts need the prior day's measured value beside each current time-series row. The needed intervention is Use LAG on an explicitly ordered partition by customer or device.

Question 192

Analysts need the prior day's measured value beside each current time-series row. What should a technical reviewer verify before approving completion?

  1. A test hierarchy returns each expected descendant and terminates on cyclic or deep inputs as designed.
  2. Known spelling variations rank closely but unrelated names remain below the review threshold.
  3. Each row displays the correct preceding value, and first rows produce the specified default.
  4. The tool can retrieve allowed schema metadata but cannot modify or expose unrelated assets.

Explanation

Correct Answer

C:

LAG reads a preceding row within a partition without a self-join for each row. The observation “Each row displays the correct preceding value, and first rows produce the specified default” directly confirms the chosen action worked for this situation: Analysts need the prior day's measured value beside each current time-series row.

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 Analysts need the prior day's measured value beside each current time-series row. The confirming test is: Each row displays the correct preceding value, and first rows produce the specified default.

B:

Fuzzy measures quantify approximate string similarity and must not automatically authorize record merges. This observation would not establish that the required result occurred for Analysts need the prior day's measured value beside each current time-series row. The confirming test is: Each row displays the correct preceding value, and first rows produce the specified default.

D:

Fabric MCP tooling is a controlled integration boundary, not blanket cross-workspace authorization. This observation would not establish that the required result occurred for Analysts need the prior day's measured value beside each current time-series row. The confirming test is: Each row displays the correct preceding value, and first rows produce the specified default.

Question 193

A business wants only the latest support ticket per customer from millions of records. What should the SQL developer implement after verifying the conditions?

  1. Use ROW_NUMBER partitioned by customer ordered by time descending and stable event ID, then filter row number 1.
  2. Use JSON_VALUE with the correct property path and appropriate type conversion.
  3. Build a recursive CTE with anchor and recursive members plus a safe termination strategy.
  4. Prefer set-based expressions or an appropriate inline TVF after measuring query plans.

Explanation

Correct Answer

A:

A ranking window supports groupwise top-1 selection when order and tie-breakers are explicit. For the described situation—A business wants only the latest support ticket per customer from millions of records—the targeted fix is Use ROW_NUMBER partitioned by customer ordered by time descending and stable event ID, then filter row number 1. A successful test should show: Exactly one latest ticket is selected per customer, including tie cases.

Incorrect Answers

B:

JSON_VALUE extracts scalar content; JSON_QUERY targets JSON objects or arrays. But that technique does not solve the stated problem—A business wants only the latest support ticket per customer from millions of records. The needed intervention is Use ROW_NUMBER partitioned by customer ordered by time descending and stable event ID, then filter row number 1.

C:

Recursive CTEs repeatedly apply the recursive member and require attention to cycles and MAXRECURSION. But that technique does not solve the stated problem—A business wants only the latest support ticket per customer from millions of records. The needed intervention is Use ROW_NUMBER partitioned by customer ordered by time descending and stable event ID, then filter row number 1.

D:

Row-by-row function evaluation can inhibit query optimization; set-based evaluation often scales better. But that technique does not solve the stated problem—A business wants only the latest support ticket per customer from millions of records. The needed intervention is Use ROW_NUMBER partitioned by customer ordered by time descending and stable event ID, then filter row number 1.

Question 194

A business wants only the latest support ticket per customer from millions of records. Which verification finding is most specific to this scenario?

  1. Each customer response contains a syntactically valid array with the right members.
  2. Valid samples pass and malformed strings are rejected on the target SQL version.
  3. Exactly one latest ticket is selected per customer, including tie cases.
  4. Group-by report throughput and storage use improve on representative workloads.

Explanation

Correct Answer

C:

A ranking window supports groupwise top-1 selection when order and tie-breakers are explicit. The observation “Exactly one latest ticket is selected per customer, including tie cases” directly confirms the chosen action worked for this situation: A business wants only the latest support ticket per customer from millions of records.

Incorrect Answers

A:

JSON array aggregation preserves JSON structure rather than emitting unsafe concatenated text. This observation would not establish that the required result occurred for A business wants only the latest support ticket per customer from millions of records. The confirming test is: Exactly one latest ticket is selected per customer, including tie cases.

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 business wants only the latest support ticket per customer from millions of records. The confirming test is: Exactly one latest ticket is selected per customer, including tie cases.

D:

Columnstore organizes and compresses columns for analytic scans but can cost more for some OLTP mutations. This observation would not establish that the required result occurred for A business wants only the latest support ticket per customer from millions of records. The confirming test is: Exactly one latest ticket is selected per customer, including tie cases.

Question 195

An API must pick one deterministic newest event in each partition even when timestamps tie. Which approach achieves the intended result without bypassing the stated controls?

  1. Evaluate a suitable fuzzy text similarity function such as JARO_WINKLER_DISTANCE with a threshold validated on labeled examples.
  2. Use ROW_NUMBER partitioned by customer ordered by time descending and stable event ID, then filter row number 1.
  3. Use JSON_CONTAINS or an appropriate supported JSON-path predicate for the target version.
  4. Create a view with the required SELECT and grant access appropriately.

Explanation

Correct Answer

B:

A ranking window supports groupwise top-1 selection when order and tie-breakers are explicit. For the described situation—An API must pick one deterministic newest event in each partition even when timestamps tie—the targeted fix is Use ROW_NUMBER partitioned by customer ordered by time descending and stable event ID, then filter row number 1. A successful test should show: Exactly one latest ticket is selected per customer, including tie cases.

Incorrect Answers

A:

Fuzzy measures quantify approximate string similarity and must not automatically authorize record merges. But that technique does not solve the stated problem—An API must pick one deterministic newest event in each partition even when timestamps tie. The needed intervention is Use ROW_NUMBER partitioned by customer ordered by time descending and stable event ID, then filter row number 1.

C:

JSON membership functions compare structured content, not formatting-dependent text substrings. But that technique does not solve the stated problem—An API must pick one deterministic newest event in each partition even when timestamps tie. The needed intervention is Use ROW_NUMBER partitioned by customer ordered by time descending and stable event ID, then filter row number 1.

D:

A standard view stores query definition rather than a separately refreshed result set. But that technique does not solve the stated problem—An API must pick one deterministic newest event in each partition even when timestamps tie. The needed intervention is Use ROW_NUMBER partitioned by customer ordered by time descending and stable event ID, then filter row number 1.

Question 196

An API must pick one deterministic newest event in each partition even when timestamps tie. How should the team validate the selected design?

  1. The total equals the number of unique business entities independent of payment line multiplicity.
  2. True matches are found in the intended array or object path while nonmatches are excluded.
  3. Exactly one latest ticket is selected per customer, including tie cases.
  4. A multi-hop traversal returns the expected connected entities under controlled samples.

Explanation

Correct Answer

C:

A ranking window supports groupwise top-1 selection when order and tie-breakers are explicit. The observation “Exactly one latest ticket is selected per customer, including tie cases” directly confirms the chosen action worked for this situation: An API must pick one deterministic newest event in each partition even when timestamps tie.

Incorrect Answers

A:

Join multiplicity affects counts and sums; choose grain-aware aggregation before interpreting totals. This observation would not establish that the required result occurred for An API must pick one deterministic newest event in each partition even when timestamps tie. The confirming test is: Exactly one latest ticket is selected per customer, including tie cases.

B:

JSON membership functions compare structured content, not formatting-dependent text substrings. This observation would not establish that the required result occurred for An API must pick one deterministic newest event in each partition even when timestamps tie. The confirming test is: Exactly one latest ticket is selected per customer, including tie cases.

D:

Graph tables express nodes and directed edges for relationships; they do not eliminate security constraints. This observation would not establish that the required result occurred for An API must pick one deterministic newest event in each partition even when timestamps tie. The confirming test is: Exactly one latest ticket is selected per customer, including tie cases.

Question 197

A query needs to filter customers whose total paid orders exceed a threshold, but retain one row per customer. What intervention should the responsible engineer choose?

  1. Use GROUP BY with HAVING on the aggregated measure.
  2. Use explicit TRY...CATCH with transaction control and THROW or XACT_STATE-aware rollback.
  3. Use SUM(amount) OVER with PARTITION BY account and an explicit ordered window frame.
  4. Validate generated SQL against actual engine version, feature availability, and representative tests.

Explanation

Correct Answer

A:

HAVING filters groups after aggregation; WHERE filters source rows before aggregation. For the described situation—A query needs to filter customers whose total paid orders exceed a threshold, but retain one row per customer—the targeted fix is Use GROUP BY with HAVING on the aggregated measure. A successful test should show: Only customers meeting the aggregate threshold appear at the requested group granularity.

Incorrect Answers

B:

Error handling and atomic transactions prevent partial multi-step business operations. But that technique does not solve the stated problem—A query needs to filter customers whose total paid orders exceed a threshold, but retain one row per customer. The needed intervention is Use GROUP BY with HAVING on the aggregated measure.

C:

Window aggregates calculate across partitions without reducing the number of input rows. But that technique does not solve the stated problem—A query needs to filter customers whose total paid orders exceed a threshold, but retain one row per customer. The needed intervention is Use GROUP BY with HAVING on the aggregated measure.

D:

AI suggestions can hallucinate features or ignore version differences; execution verification is essential. But that technique does not solve the stated problem—A query needs to filter customers whose total paid orders exceed a threshold, but retain one row per customer. The needed intervention is Use GROUP BY with HAVING on the aggregated measure.

Question 198

A query needs to filter customers whose total paid orders exceed a threshold, but retain one row per customer. What observable outcome proves the recommendation worked?

  1. Only customers meeting the aggregate threshold appear at the requested group granularity.
  2. An order is flagged relative to its actual customer's average rather than a global average.
  3. The extracted scalar matches the expected model code on sample documents.
  4. Assistant-access logs show only approved scope and no high-privilege keys enter its context.

Explanation

Correct Answer

A:

HAVING filters groups after aggregation; WHERE filters source rows before aggregation. The observation “Only customers meeting the aggregate threshold appear at the requested group granularity” directly confirms the chosen action worked for this situation: A query needs to filter customers whose total paid orders exceed a threshold, but retain one row per customer.

Incorrect Answers

B:

Correlated predicates are evaluated in row context; the scope of aggregation determines correctness. This observation would not establish that the required result occurred for A query needs to filter customers whose total paid orders exceed a threshold, but retain one row per customer. The confirming test is: Only customers meeting the aggregate threshold appear at the requested group granularity.

C:

JSON_VALUE extracts scalar content; JSON_QUERY targets JSON objects or arrays. This observation would not establish that the required result occurred for A query needs to filter customers whose total paid orders exceed a threshold, but retain one row per customer. The confirming test is: Only customers meeting the aggregate threshold appear at the requested group granularity.

D:

An AI coding tool cannot make elevated secrets safe; supplied context and tool privileges must be governed. This observation would not establish that the required result occurred for A query needs to filter customers whose total paid orders exceed a threshold, but retain one row per customer. The confirming test is: Only customers meeting the aggregate threshold appear at the requested group granularity.

Question 199

Developers misuse WHERE SUM(Amount) over individual order rows in a grouped report. Which design choice BEST addresses the root issue?

  1. Use REGEXP_REPLACE with a carefully tested replacement and pattern.
  2. Use GROUP BY with HAVING on the aggregated measure.
  3. Aggregate at the intended grain or use a properly scoped DISTINCT key count.
  4. Select a length-appropriate VARCHAR or NVARCHAR type with explicit normalization requirements.

Explanation

Correct Answer

B:

HAVING filters groups after aggregation; WHERE filters source rows before aggregation. For the described situation—Developers misuse WHERE SUM(Amount) over individual order rows in a grouped report—the targeted fix is Use GROUP BY with HAVING on the aggregated measure. A successful test should show: Only customers meeting the aggregate threshold appear at the requested group granularity.

Incorrect Answers

A:

REGEXP_REPLACE substitutes pattern matches and requires explicit tests for greediness and escaping. But that technique does not solve the stated problem—Developers misuse WHERE SUM(Amount) over individual order rows in a grouped report. The needed intervention is Use GROUP BY with HAVING on the aggregated measure.

C:

Join multiplicity affects counts and sums; choose grain-aware aggregation before interpreting totals. But that technique does not solve the stated problem—Developers misuse WHERE SUM(Amount) over individual order rows in a grouped report. The needed intervention is Use GROUP BY with HAVING on the aggregated measure.

D:

CHAR uses fixed-width padding; variable-width text types better fit identifiers whose lengths differ. But that technique does not solve the stated problem—Developers misuse WHERE SUM(Amount) over individual order rows in a grouped report. The needed intervention is Use GROUP BY with HAVING on the aggregated measure.

Question 200

Developers misuse WHERE SUM(Amount) over individual order rows in a grouped report. Which result would provide the strongest acceptance evidence?

  1. Each customer response contains a syntactically valid array with the right members.
  2. Only customers meeting the aggregate threshold appear at the requested group granularity.
  3. True matches are found in the intended array or object path while nonmatches are excluded.
  4. Duplicate non-null emails fail while multiple missing values behave per the documented rule.

Explanation

Correct Answer

B:

HAVING filters groups after aggregation; WHERE filters source rows before aggregation. The observation “Only customers meeting the aggregate threshold appear at the requested group granularity” directly confirms the chosen action worked for this situation: Developers misuse WHERE SUM(Amount) over individual order rows in a grouped report.

Incorrect Answers

A:

JSON array aggregation preserves JSON structure rather than emitting unsafe concatenated text. This observation would not establish that the required result occurred for Developers misuse WHERE SUM(Amount) over individual order rows in a grouped report. The confirming test is: Only customers meeting the aggregate threshold appear at the requested group granularity.

C:

JSON membership functions compare structured content, not formatting-dependent text substrings. This observation would not establish that the required result occurred for Developers misuse WHERE SUM(Amount) over individual order rows in a grouped report. The confirming test is: Only customers meeting the aggregate threshold appear at the requested group granularity.

D:

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 Developers misuse WHERE SUM(Amount) over individual order rows in a grouped report. The confirming test is: Only customers meeting the aggregate threshold appear at the requested group granularity.

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