Question 440
An application team must address both issues before release. A report must count each customer's invoices and show zero for customers with none, but an INNER JOIN drops those customers. A developer writes a correlated scalar subquery that unexpectedly returns multiple rows for one customer and the query fails at runtime. Which course of action addresses the stated issues?
- Use an appropriate LEFT JOIN with grouped counts that distinguish missing rows from actual invoices; enforce a single-row selection with an appropriate aggregate or deterministic TOP condition after clarifying the business rule.
- Use an appropriate LEFT JOIN with grouped counts that distinguish missing rows from actual invoices; use the supported MATCH graph traversal syntax and verify edge direction and path semantics against test data.
- Use a correlated subquery or a suitable window comparison keyed by supplier and verify exclusion rules; enforce a single-row selection with an appropriate aggregate or deterministic TOP condition after clarifying the business rule.
- Enforce a single-row selection with an appropriate aggregate or deterministic TOP condition after clarifying the business rule; do not use an appropriate LEFT JOIN with grouped counts that distinguish missing rows from actual invoices.
Explanation
Correct Answer
A:First, an outer join preserves unmatched customers while aggregate logic accounts for zero related records. Second, scalar subqueries must return no more than one value for each outer row.
Incorrect Answers
B:Use an appropriate LEFT JOIN with grouped counts that distinguish missing rows from actual invoices; use the supported MATCH graph traversal syntax and verify edge direction and path semantics against test data: scalar subqueries must return no more than one value for each outer row.
C:Use a correlated subquery or a suitable window comparison keyed by supplier and verify exclusion rules; enforce a single-row selection with an appropriate aggregate or deterministic TOP condition after clarifying the business rule: an outer join preserves unmatched customers while aggregate logic accounts for zero related records.
D:Enforce a single-row selection with an appropriate aggregate or deterministic TOP condition after clarifying the business rule; do not use an appropriate LEFT JOIN with grouped counts that distinguish missing rows from actual invoices: use an appropriate LEFT JOIN with grouped counts that distinguish missing rows from actual invoices.
Question 441
An application team must address both issues before release. An API must produce one JSON array of product codes per invoice group rather than comma-joined text that fails on embedded commas. A developer needs to check whether a nested JSON payload contains an approved value under an array path. Which response best addresses these needs?
- Use a supported JSON path or containment predicate with explicit validation of path and value semantics; do not use the platform's JSON_ARRAYAGG capability and validate output types and ordering expectations.
- Use the platform's JSON_ARRAYAGG capability and validate output types and ordering expectations; Parse the array with OPENJSON and an explicit expected schema before applying policy checks.
- Use the platform's JSON_ARRAYAGG capability and validate output types and ordering expectations; use a supported JSON path or containment predicate with explicit validation of path and value semantics.
- Extract the scalar with an appropriate JSON function and cast or validate its numeric type before filtering; use a supported JSON path or containment predicate with explicit validation of path and value semantics.
Explanation
Correct Answer
C:First, JSON array construction represents elements as data rather than ambiguous delimiter-separated text. Second, structured containment checks avoid treating arbitrary text resemblance as proof of a matching JSON element.
Incorrect Answers
A:Use a supported JSON path or containment predicate with explicit validation of path and value semantics; do not use the platform's JSON_ARRAYAGG capability and validate output types and ordering expectations.
B:Use the platform's JSON_ARRAYAGG capability and validate output types and ordering expectations; Parse the array with OPENJSON and an explicit expected schema before applying policy checks: structured containment checks avoid treating arbitrary text resemblance as proof of a matching JSON element.
D:Extract the scalar with an appropriate JSON function and cast or validate its numeric type before filtering; use a supported JSON path or containment predicate with explicit validation of path and value semantics: jSON array construction represents elements as data rather than ambiguous delimiter-separated text.
Question 442
An application team must address both issues before release. A GitHub Actions deployment workflow embeds the production SQL administrator password directly in its YAML file. A release pipeline authenticates as a personal developer account that will be disabled when the developer leaves. What is the most appropriate plan for this situation?
- Configure an approved deployment service principal or workload identity with restricted environment permissions; do not remove and rotate the exposed password and use an approved workload identity or protected secret for deployment.
- Remove and rotate the exposed password and use an approved workload identity or protected secret for deployment; Fail the deployment and verify artifact provenance and integrity before executing the package.
- Mask sensitive variables and replace secret-bearing diagnostic output with sanitized structured error logging; configure an approved deployment service principal or workload identity with restricted environment permissions.
- Remove and rotate the exposed password and use an approved workload identity or protected secret for deployment; configure an approved deployment service principal or workload identity with restricted environment permissions.
Explanation
Correct Answer
D:First, secrets committed in pipeline source can leak through history and logs and should not be hardcoded. Second, production releases require accountable non-personal credentials with lifecycle and least-privilege controls.
Incorrect Answers
A:Configure an approved deployment service principal or workload identity with restricted environment permissions; do not remove and rotate the exposed password and use an approved workload identity or protected secret for deployment: remove and rotate the exposed password and use an approved workload identity or protected secret for deployment.
B:Remove and rotate the exposed password and use an approved workload identity or protected secret for deployment; Fail the deployment and verify artifact provenance and integrity before executing the package: production releases require accountable non-personal credentials with lifecycle and least-privilege controls.
C:Mask sensitive variables and replace secret-bearing diagnostic output with sanitized structured error logging; configure an approved deployment service principal or workload identity with restricted environment permissions: secrets committed in pipeline source can leak through history and logs and should not be hardcoded.
Question 443
An SQL-to-LLM pipeline sends every column of a health table even though the request concerns only anonymous appointment counts. Which action best addresses the database requirement?
- Normalize numeric types and include explicit units and currency in structured model context.
- Convert authorized relational results to a typed, labeled JSON structure with clear keys and row identities.
- Filter and aggregate source results to fit context limits, retaining references needed for verification.
- Project and aggregate only the authorized minimum columns before serializing context for the model.
Explanation
Correct Answer
D:Data minimization should occur before data crosses the external model boundary.
Incorrect Answers
A:Normalize numeric types and include explicit units and currency in structured model context: normalize numeric types and include explicit units and currency in structured model context: ambiguous number formatting can produce incorrect calculations or misleading business narratives.
B:Convert authorized relational results to a typed, labeled JSON structure with clear keys and row identities: structured fields preserve semantic roles better than unlabeled positional text.
C:Filter and aggregate source results to fit context limits, retaining references needed for verification: filter and aggregate source results to fit context limits, retaining references needed for verification: context budgets are finite and require intentional evidence selection rather than uncontrolled dumping.
Question 444
An API supports filtering but clients submit arbitrary SQL fragments rather than the documented query syntax. A customer-facing GraphQL resolver returns all users across tenants when called by a valid account because its database identity bypasses RLS. Which response best addresses these needs?
- Enforce supported REST or GraphQL filter inputs and keep dynamic SQL construction out of untrusted client parameters; configure supported DAB entity relationships and GraphQL navigation with restrictive permissions.
- Enforce supported REST or GraphQL filter inputs and keep dynamic SQL construction out of untrusted client parameters; Rework the effective identity or tenant predicate and test GraphQL results for each isolated caller.
- Rework the effective identity or tenant predicate and test GraphQL results for each isolated caller; do not enforce supported REST or GraphQL filter inputs and keep dynamic SQL construction out of untrusted client parameters.
- Expose the appropriate stored procedure through supported DAB configuration and grant restricted execution rights; Rework the effective identity or tenant predicate and test GraphQL results for each isolated caller.
Explanation
Correct Answer
B:First, structured API filters constrain requests without granting clients direct SQL execution. Second, API authentication is insufficient if underlying database authorization returns data outside the caller's scope.
Incorrect Answers
A:Enforce supported REST or GraphQL filter inputs and keep dynamic SQL construction out of untrusted client parameters; configure supported DAB entity relationships and GraphQL navigation with restrictive permissions: aPI authentication is insufficient if underlying database authorization returns data outside the caller's scope.
C:Enforce supported REST or GraphQL filter inputs and keep dynamic SQL construction out of untrusted client parameters.
D:Expose the appropriate stored procedure through supported DAB configuration and grant restricted execution rights; Rework the effective identity or tenant predicate and test GraphQL results for each isolated caller: structured API filters constrain requests without granting clients direct SQL execution.
Question 445
A shared lookup function returns different results because it uses an unstated session setting and the client pool has mixed defaults. Which action best addresses the database requirement?
- Move external side effects to a reliable asynchronous integration path and keep the trigger transaction short.
- Make the calculation's assumptions explicit and validate session-sensitive behavior in repeatable integration tests.
- Use TRY/CATCH with transaction rollback and consistent failure signaling so partial business changes are not reported as committed.
- Review whether controlled procedures or a suitable INSTEAD OF trigger provide a supported update interface.
Explanation
Correct Answer
B:Reusable database logic should not silently depend on varying client session state.
Incorrect Answers
A:Move external side effects to a reliable asynchronous integration path and keep the trigger transaction short: long-running work inside write triggers extends transaction duration and raises contention and failure risk.
C:Use TRY/CATCH with transaction rollback and consistent failure signaling so partial business changes are not reported as committed: use TRY/CATCH with transaction rollback and consistent failure signaling so partial business changes are not reported as committed: transactional failure handling must preserve atomicity and communicate that the operation did not complete.
D:Review whether controlled procedures or a suitable INSTEAD OF trigger provide a supported update interface: review whether controlled procedures or a suitable INSTEAD OF trigger provide a supported update interface: complex views can lose straightforward update semantics and need explicit mapping of writes.
Question 446
Issue 1: A database import accepts product IDs only when they follow two uppercase letters and six digits, but a LIKE check misses invalid punctuation. Issue 2: A support team must remove a known prefix from varied incoming ticket identifiers without deleting similar letters elsewhere. Issue 3: A reconciliation process needs the approximate number of single-character edits between two normalized part descriptions. Which complete response addresses the three issues in order?
- Use a supported fuzzy similarity measure such as JARO_WINKLER_DISTANCE in candidate scoring and retain manual review thresholds; use REGEXP_REPLACE with an anchored prefix expression and validate unmatched values before updating stored IDs; and evaluate EDIT_DISTANCE with domain-appropriate preprocessing and a tested acceptance threshold.
- Apply a supported REGEXP_LIKE pattern with explicit anchors and test representative valid and invalid identifiers; use REGEXP_REPLACE with an anchored prefix expression and validate unmatched values before updating stored IDs; and use a supported fuzzy similarity measure such as JARO_WINKLER_DISTANCE in candidate scoring and retain manual review thresholds.
- Apply a supported REGEXP_LIKE pattern with explicit anchors and test representative valid and invalid identifiers; use REGEXP_REPLACE with an anchored prefix expression and validate unmatched values before updating stored IDs; and evaluate EDIT_DISTANCE with domain-appropriate preprocessing and a tested acceptance threshold.
- Apply a supported REGEXP_LIKE pattern with explicit anchors and test representative valid and invalid identifiers; use a supported fuzzy similarity measure such as JARO_WINKLER_DISTANCE in candidate scoring and retain manual review thresholds; and evaluate EDIT_DISTANCE with domain-appropriate preprocessing and a tested acceptance threshold.
Explanation
Correct Answer
C:For issue 1, anchored regular expressions validate the full identifier shape instead of only a loose substring. For issue 2, an anchored replacement targets the intended prefix location rather than every matching character sequence. For issue 3, edit distance quantifies text changes; similarity cutoffs must be calibrated against real business examples.
Incorrect Answers
A:Use a supported fuzzy similarity measure such as JARO_WINKLER_DISTANCE in candidate scoring and retain manual review thresholds; use REGEXP_REPLACE with an anchored prefix expression and validate unmatched values before updating stored IDs; and evaluate EDIT_DISTANCE with domain-appropriate preprocessing and a tested acceptance threshold: anchored regular expressions validate the full identifier shape instead of only a loose substring.
B:Apply a supported REGEXP_LIKE pattern with explicit anchors and test representative valid and invalid identifiers; use REGEXP_REPLACE with an anchored prefix expression and validate unmatched values before updating stored IDs; and use a supported fuzzy similarity measure such as JARO_WINKLER_DISTANCE in candidate scoring and retain manual review thresholds: edit distance quantifies text changes; similarity cutoffs must be calibrated against real business examples.
D:Apply a supported REGEXP_LIKE pattern with explicit anchors and test representative valid and invalid identifiers; use a supported fuzzy similarity measure such as JARO_WINKLER_DISTANCE in candidate scoring and retain manual review thresholds; and evaluate EDIT_DISTANCE with domain-appropriate preprocessing and a tested acceptance threshold: an anchored replacement targets the intended prefix location rather than every matching character sequence.
Question 447
A database architect reviews two implementation risks. A reporting table needs sequential business reference numbers independent of any particular table's identity column. A purchase-order table has duplicate invoice numbers per supplier, but the same number is legitimately used by different suppliers. Which response best addresses these needs?
- Create and use a SEQUENCE with defined allocation and concurrency expectations for the external reference numbers; configure an appropriate DEFAULT expression on the created-at column and test insert behavior when the field is omitted.
- Define the SKU column as NOT NULL and validate existing null records before applying the change; enforce a composite UNIQUE constraint on supplier ID and invoice number after resolving same-supplier duplicates.
- Create and use a SEQUENCE with defined allocation and concurrency expectations for the external reference numbers; enforce a composite UNIQUE constraint on supplier ID and invoice number after resolving same-supplier duplicates.
- Enforce a composite UNIQUE constraint on supplier ID and invoice number after resolving same-supplier duplicates; do not create and use a SEQUENCE with defined allocation and concurrency expectations for the external reference numbers.
Explanation
Correct Answer
C:First, a sequence generates numbers independently of table identity insertion, with gaps possible under concurrency or rollback. Second, composite uniqueness protects the intended business key without falsely requiring global uniqueness of an invoice label.
Incorrect Answers
A:Create and use a SEQUENCE with defined allocation and concurrency expectations for the external reference numbers; configure an appropriate DEFAULT expression on the created-at column and test insert behavior when the field is omitted: composite uniqueness protects the intended business key without falsely requiring global uniqueness of an invoice label.
B:Define the SKU column as NOT NULL and validate existing null records before applying the change; enforce a composite UNIQUE constraint on supplier ID and invoice number after resolving same-supplier duplicates: a sequence generates numbers independently of table identity insertion, with gaps possible under concurrency or rollback.
D:Create and use a SEQUENCE with defined allocation and concurrency expectations for the external reference numbers.
Question 448
A generated SQL script works in a development lakehouse but fails in a production SQL endpoint with a different feature set. A model proposes changing a schema to satisfy a prompt, but the Fabric project's pull-request policy requires two reviewers. What is the most appropriate plan for this situation?
- Submit the change through the required pull request and validation process rather than applying the model output directly; do not validate the target engine's supported SQL surface and deploy only code tested against that platform.
- Validate the target engine's supported SQL surface and deploy only code tested against that platform; verify workspace, lakehouse and identity entitlements before expecting source-grounded assistance.
- Attach authorized plan and workload context and treat the suggested tuning changes as hypotheses to test; Submit the change through the required pull request and validation process rather than applying the model output directly.
- Validate the target engine's supported SQL surface and deploy only code tested against that platform; Submit the change through the required pull request and validation process rather than applying the model output directly.
Explanation
Correct Answer
D:First, Microsoft SQL experiences share language concepts but can differ in supported database capabilities. Second, AI-assisted authoring does not supersede development governance and approval requirements.
Incorrect Answers
A:Validate the target engine's supported SQL surface and deploy only code tested against that platform.
B:Validate the target engine's supported SQL surface and deploy only code tested against that platform; verify workspace, lakehouse and identity entitlements before expecting source-grounded assistance: aI-assisted authoring does not supersede development governance and approval requirements.
C:Attach authorized plan and workload context and treat the suggested tuning changes as hypotheses to test; Submit the change through the required pull request and validation process rather than applying the model output directly: microsoft SQL experiences share language concepts but can differ in supported database capabilities.
Question 449
An application team must address both issues before release. A team turns on auditing but sends the logs to a destination writable by the same administrators whose actions are being reviewed. A security reviewer wants evidence of failed login attempts but a report shows only successful query calls. Which course of action addresses the stated issues?
- Enable and validate relevant authentication audit event coverage and correlate it with centralized security monitoring; do not Protect the audit sink with appropriate retention and independent access controls to reduce unauthorized tampering.
- Protect the audit sink with appropriate retention and independent access controls to reduce unauthorized tampering; enable and validate relevant authentication audit event coverage and correlate it with centralized security monitoring.
- Protect the audit sink with appropriate retention and independent access controls to reduce unauthorized tampering; configure appropriate SQL auditing and retain identity, action and timestamp evidence with protected access.
- Review captured fields and apply data minimization, access restrictions and retention controls before continuing collection; enable and validate relevant authentication audit event coverage and correlate it with centralized security monitoring.
Explanation
Correct Answer
B:First, audit evidence is less trustworthy when subjects of the audit can alter or delete it unchecked. Second, query activity alone cannot reveal credential guessing or other failed authentication events.
Incorrect Answers
A:Protect the audit sink with appropriate retention and independent access controls to reduce unauthorized tampering.
C:Protect the audit sink with appropriate retention and independent access controls to reduce unauthorized tampering; configure appropriate SQL auditing and retain identity, action and timestamp evidence with protected access: query activity alone cannot reveal credential guessing or other failed authentication events.
D:Review captured fields and apply data minimization, access restrictions and retention controls before continuing collection; enable and validate relevant authentication audit event coverage and correlate it with centralized security monitoring: audit evidence is less trustworthy when subjects of the audit can alter or delete it unchecked.
Question 450
A database architect reviews two implementation risks. A database pipeline invokes an external model using a long-lived administrative API key stored in a publicly readable table. A GraphQL endpoint allows arbitrary deeply nested queries and one client request consumes all database worker resources. Which response best addresses these needs?
- Constrain the MCP tool to approved operations and identities and treat retrieved instructions as untrusted data; apply supported query depth, complexity, rate and resource controls alongside entity permissions.
- Move model credentials to an approved secret or managed identity boundary and restrict network and caller access; require validated authentication and per-entity authorization before exposing the API to clients.
- Apply supported query depth, complexity, rate and resource controls alongside entity permissions; do not Move model credentials to an approved secret or managed identity boundary and restrict network and caller access.
- Move model credentials to an approved secret or managed identity boundary and restrict network and caller access; apply supported query depth, complexity, rate and resource controls alongside entity permissions.
Explanation
Correct Answer
D:First, external model connections need credential isolation and least privilege like any other remote service. Second, GraphQL authorization alone does not prevent denial of service from unbounded query execution.
Incorrect Answers
A:Constrain the MCP tool to approved operations and identities and treat retrieved instructions as untrusted data; apply supported query depth, complexity, rate and resource controls alongside entity permissions: external model connections need credential isolation and least privilege like any other remote service.
B:Move model credentials to an approved secret or managed identity boundary and restrict network and caller access; require validated authentication and per-entity authorization before exposing the API to clients: graphQL authorization alone does not prevent denial of service from unbounded query execution.
C:Move model credentials to an approved secret or managed identity boundary and restrict network and caller access.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests