100% Latest & Updated Microsoft DP-800 Practice Test Questions, Exam Dumps & Verified Answers!
30 Days Free Updates, Instant Download!
Question 340
A text cleansing query replaces matching substrings using regex. A test instead finds: Only literal substring is replaced. Which underlying issue best explains this finding?
- Pattern matching lacks required regex structure
- CROSS APPLY used where OUTER APPLY was required
- JSON path or case-sensitive property names do not match
- REPLACE used where a pattern was required
Explanation
Correct Answer
D:REPLACE used where a pattern was required. It accounts for the specific observed failure: only literal substring is replaced. The intended solution is use regexp_replace on supported engine with the required setup.
Incorrect Answers
A:Pattern matching lacks required regex structure. This concerns ordinary like incorrectly accepts malformed ids. The relevant SQL or system operation is REPLACE used where a pattern was required.
B:CROSS APPLY used where OUTER APPLY was required. This concerns orders vanish when payment row is absent. The relevant SQL or system operation is REPLACE used where a pattern was required.
C:JSON path or case-sensitive property names do not match. This concerns nested array values are null in projection. The relevant SQL or system operation is REPLACE used where a pattern was required.
Question 341
A string comparison requires the minimum insert/delete/substitute character count rather than a percentage. Which database design or engineering approach most directly meets this requirement?
- Use COUNT(*) OVER(PARTITION BY TeamID)
- Use ROW_NUMBER by customer and descending OrderDate
- Use EDIT_DISTANCE for character-change count
- Use EDIT_DISTANCE_SIMILARITY if supported
Explanation
Correct Answer
C:Use EDIT_DISTANCE for character-change count. The implementation is compute edit_distance(a,b) on normalized text and compare integer edit operations; success can be checked by check known one-insertion and substitution cases. The method addresses the stated data and workload constraint.
Incorrect Answers
A:Use COUNT(*) OVER(PARTITION BY TeamID). This concerns counting rows per team while retaining row detail. Instead, use EDIT_DISTANCE for character-change count.
B:Use ROW_NUMBER by customer and descending OrderDate. This concerns pick latest order per customer. Instead, use EDIT_DISTANCE for character-change count.
D:Use EDIT_DISTANCE_SIMILARITY if supported. This concerns an app scores the similarity of two misspelled names. Instead, use EDIT_DISTANCE for character-change count.
Question 342
A string comparison requires the minimum insert/delete/substitute character count rather than a percentage. The team selects: Use EDIT_DISTANCE for character-change count. Which implementation action puts this design into practice?
- Project COUNT window alongside source rows
- Compute EDIT_DISTANCE(a,b) on normalized text and compare integer edit operations
- Specify tested pattern and replacement with REGEXP_REPLACE
- WHERE NOT EXISTS(SELECT 1 FROM Orders WHERE CustomerID=c.ID)
Explanation
Correct Answer
B:Compute EDIT_DISTANCE(a,b) on normalized text and compare integer edit operations. This is the concrete setup for use edit_distance for character-change count, and check known one-insertion and substitution cases supplies a direct confirmation.
Incorrect Answers
A:Project COUNT window alongside source rows. This concerns counting rows per team while retaining row detail. Here, compute EDIT_DISTANCE(a,b) on normalized text and compare integer edit operations.
C:Specify tested pattern and replacement with REGEXP_REPLACE. This concerns a text cleansing query replaces matching substrings using regex. Here, compute EDIT_DISTANCE(a,b) on normalized text and compare integer edit operations.
D:WHERE NOT EXISTS(SELECT 1 FROM Orders WHERE CustomerID=c.ID). This concerns finding customers with no orders. Here, compute EDIT_DISTANCE(a,b) on normalized text and compare integer edit operations.
Question 343
A string comparison requires the minimum insert/delete/substitute character count rather than a percentage. After implementing the design, which check most directly validates the expected behavior?
- Check known one-insertion and substitution cases
- Test expected two-hop results
- Verify each row shows its team's total
- Check each group has row numbers 1 to 3
Explanation
Correct Answer
A:Check known one-insertion and substitution cases. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that raw edit distance confused with normalized similarity.
Incorrect Answers
B:Test expected two-hop results. This concerns a graph database query asks for friends of friends. Instead, check known one-insertion and substitution cases.
C:Verify each row shows its team's total. This concerns counting rows per team while retaining row detail. Instead, check known one-insertion and substitution cases.
D:Check each group has row numbers 1 to 3. This concerns top three sales orders per salesperson. Instead, check known one-insertion and substitution cases.
Question 344
A string comparison requires the minimum insert/delete/substitute character count rather than a percentage. A test instead finds: Distance scale mistakenly treated as 0 to 100 score. Which underlying issue best explains this finding?
- JSON path is wrong or payload shape differs
- GROUP BY collapsed the original rows
- Raw edit distance confused with normalized similarity
- Unnecessary string conversion altered JSON types
Explanation
Correct Answer
C:Raw edit distance confused with normalized similarity. It accounts for the specific observed failure: distance scale mistakenly treated as 0 to 100 score. The intended solution is use edit_distance for character-change count with the required setup.
Incorrect Answers
A:JSON path is wrong or payload shape differs. This concerns returned value is null for present nested field. Here, raw edit distance confused with normalized similarity.
B:GROUP BY collapsed the original rows. This concerns group detail disappears from report. Here, raw edit distance confused with normalized similarity.
D:Unnecessary string conversion altered JSON types. This concerns generated output contains string-encoded numbers. Here, raw edit distance confused with normalized similarity.
Question 345
A code assistant proposes SQL access beyond the developer's privileges. Which database design or engineering approach most directly meets this requirement?
- Restrict MCP tools and endpoints to trusted approved servers
- Keep human review of AI-generated DDL and its data impact
- Use the documented Fabric MCP server endpoint
- Apply least-privilege security review to AI-assisted output
Explanation
Correct Answer
D:Apply least-privilege security review to AI-assisted output. The implementation is review permissions and parameterize generated queries; success can be checked by run with a restricted test principal and inspect access. The method addresses the stated data and workload constraint.
Incorrect Answers
A:Restrict MCP tools and endpoints to trusted approved servers. This concerns a developer invokes a third-party mcp server from the SQL code assistant. Instead, apply least-privilege security review to AI-assisted output.
B:Keep human review of AI-generated DDL and its data impact. This concerns explain generated SQL before approving deployment. Instead, apply least-privilege security review to AI-assisted output.
C:Use the documented Fabric MCP server endpoint. This concerns connection to a fabric lakehouse through mcp. Instead, apply least-privilege security review to AI-assisted output.
Question 346
A code assistant proposes SQL access beyond the developer's privileges. The team selects: Apply least-privilege security review to AI-assisted output. Which implementation action puts this design into practice?
- Check tenant settings, capacity and access prerequisites
- Allowlist endpoints, scope credentials and tool permissions
- Require a pull request with explain plans, tests and reviewers
- Review permissions and parameterize generated queries
Explanation
Correct Answer
D:Review permissions and parameterize generated queries. This is the concrete setup for apply least-privilege security review to ai-assisted output, and run with a restricted test principal and inspect access supplies a direct confirmation.
Incorrect Answers
A:Check tenant settings, capacity and access prerequisites. This concerns copilot in fabric for SQL development assistance. Instead, review permissions and parameterize generated queries.
B:Allowlist endpoints, scope credentials and tool permissions. This concerns a developer invokes a third-party mcp server from the SQL code assistant. Instead, review permissions and parameterize generated queries.
C:Require a pull request with explain plans, tests and reviewers. This concerns explain generated SQL before approving deployment. Instead, review permissions and parameterize generated queries.
Question 347
A code assistant proposes SQL access beyond the developer's privileges. After implementing the design, which check most directly validates the expected behavior?
- Validate access to allowed lakehouse metadata only
- Review invoked tool calls and returned resources
- Run with a restricted test principal and inspect access
- Verify approved users can invoke assistant in the intended workspace
Explanation
Correct Answer
C:Run with a restricted test principal and inspect access. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that generated sql was trusted without privilege checks.
Incorrect Answers
A:Validate access to allowed lakehouse metadata only. This concerns connection to a fabric lakehouse through mcp. Instead, run with a restricted test principal and inspect access.
B:Review invoked tool calls and returned resources. This concerns a developer invokes a third-party mcp server from the SQL code assistant. Instead, run with a restricted test principal and inspect access.
D:Verify approved users can invoke assistant in the intended workspace. This concerns copilot in fabric for SQL development assistance. Instead, run with a restricted test principal and inspect access.
Question 348
A code assistant proposes SQL access beyond the developer's privileges. A test instead finds: Copilot-generated query reads a restricted table. Which underlying issue best explains this finding?
- Generated SQL was trusted without privilege checks
- AI output bypassed the normal code-review gate
- Untrusted MCP endpoint was enabled with broad access
- Unsafe examples were committed to accessible context
Explanation
Correct Answer
A:Generated SQL was trusted without privilege checks. It accounts for the specific observed failure: copilot-generated query reads a restricted table. The intended solution is apply least-privilege security review to ai-assisted output with the required setup.
Incorrect Answers
B:AI output bypassed the normal code-review gate. This concerns unreviewed ddl drops a dependent object. Here, generated SQL was trusted without privilege checks.
C:Untrusted MCP endpoint was enabled with broad access. This concerns an unexpected tool exfiltrates schema information. Here, generated SQL was trusted without privilege checks.
D:Unsafe examples were committed to accessible context. This concerns assistant repeats a credential from an example file. Here, generated SQL was trusted without privilege checks.
Question 349
A team uses GitHub Copilot across its SQL Database Project repository. Which database design or engineering approach most directly meets this requirement?
- Define repository instructions for schema and test conventions
- Remove sensitive data and constrain repository access
- Keep human review of AI-generated DDL and its data impact
- Enable it under tenant-approved Fabric governance
Explanation
Correct Answer
A:Define repository instructions for schema and test conventions. The implementation is create .github/copilot-instructions.md with approved coding policy; success can be checked by assess suggested migrations against the repository rules. The method addresses the stated data and workload constraint.
Incorrect Answers
B:Remove sensitive data and constrain repository access. This concerns a repository contains examples that copilot may use in suggestions. Instead, define repository instructions for schema and test conventions.
C:Keep human review of AI-generated DDL and its data impact. This concerns explain generated SQL before approving deployment. Instead, define repository instructions for schema and test conventions.
D:Enable it under tenant-approved Fabric governance. This concerns copilot in fabric for SQL development assistance. Instead, define repository instructions for schema and test conventions.
Question 350
A team uses GitHub Copilot across its SQL Database Project repository. The team selects: Define repository instructions for schema and test conventions. Which implementation action puts this design into practice?
- Create .github/copilot-instructions.md with approved coding policy
- Check tenant settings, capacity and access prerequisites
- Create dedicated least-privileged database principal for tool
- Scan repository examples and apply secret-detection hooks
Explanation
Correct Answer
A:Create .github/copilot-instructions.md with approved coding policy. This is the concrete setup for define repository instructions for schema and test conventions, and assess suggested migrations against the repository rules supplies a direct confirmation.
Incorrect Answers
B:Check tenant settings, capacity and access prerequisites. This concerns copilot in fabric for SQL development assistance. Instead, create .github/copilot-instructions.md with approved coding policy.
C:Create dedicated least-privileged database principal for tool. This is relevant when an ai assistant uses a database connection during debugging. Instead, create .github/copilot-instructions.md with approved coding policy.
D:Scan repository examples and apply secret-detection hooks. This concerns a repository contains examples that copilot may use in suggestions. Instead, create .github/copilot-instructions.md with approved coding policy.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests