Question 220 An API response needs named properties composed from projected SQL values. What is the most appropriate next design step? Use ISJSON before processing untrusted JSON text Use JSON_MODIFY to change one property in JSON text Use JSON_OBJECT to construct a JSON object from relational values Use JSON_ARRAYAGG for an array aggregated from rows Explanation Correct Answer C:This implementation serializes named SQL expressions into a single JSON object. Incorrect Answers A:This alternative tests whether input has valid JSON structure. This does not meet the need for a method that serializes named SQL expressions into a single JSON object. B:This alternative returns updated JSON content at a selected path. This does not meet the need for a method that serializes named SQL expressions into a single JSON object. D:This choice combines multiple SQL row values into one JSON array. This does not meet the need for a method that serializes named SQL expressions into a single JSON object.
Question 221 A data quality report isolates malformed numeric identifiers for later correction. What should the SQL developer implement? Use TRY_CONVERT to safely handle mixed input types Use EXISTS for a correlated existence test Use CROSS APPLY for a correlated table expression that must match Use STRING_AGG to combine multiple row strings into one delimited output Explanation Correct Answer A:This choice returns NULL for failed supported conversion instead of aborting each row. Incorrect Answers B:This alternative filters rows by presence without returning or duplicating matching children. This does not meet the need for a method that returns NULL for failed supported conversion instead of aborting each row. C:This alternative evaluates a right-hand rowset per left row and excludes unmatched rows. This does not meet the need for a method that returns NULL for failed supported conversion instead of aborting each row. D:This alternative aggregates text values over a group. This does not meet the need for a method that returns NULL for failed supported conversion instead of aborting each row.
Question 222 A product popularity report needs dense ranks by equal order counts. The target SQL engine supports the named T-SQL feature. What is the most appropriate design decision? Use DENSE_RANK() for tied rankings without numeric gaps Use ROW_NUMBER() OVER with a deterministic partition and order Use AVG() OVER(PARTITION BY ...) for a group statistic on each detail row Use SUM(amount) OVER(PARTITION BY ... ORDER BY ...) with an appropriate frame Explanation Correct Answer A:This implementation assigns equal ranks for ties and consecutive distinct ranks. Incorrect Answers B:That technique assigns a distinct row position inside each group. This does not meet the need for a method that assigns equal ranks for ties and consecutive distinct ranks. C:That technique 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. D:This choice calculates a running total while retaining detail rows. This does not meet the need for a method that assigns equal ranks for ties and consecutive distinct ranks.
Question 223 A configuration JSON contains many allowed codes to inspect as rows. Which solution is most directly applicable? Use JSON_VALUE to extract a scalar property from a JSON document Use JSON_ARRAY to build an array from explicit expressions Use JSON_QUERY to extract an object or array fragment Use OPENJSON to transform a JSON array into a relational rowset Explanation Correct Answer D:This implementation expands array elements into table-shaped rows. Incorrect Answers A:This option returns a single scalar SQL expression from a JSON path. This does not meet the need for a method that expands array elements into table-shaped rows. B:This choice creates JSON array values from supplied arguments. This does not meet the need for a method that expands array elements into table-shaped rows. C:This choice returns JSON-shaped nested content rather than a scalar. This does not meet the need for a method that expands array elements into table-shaped rows.
Question 224 A maintenance report finds the next inspection date after each completed visit. Which solution is most directly applicable? Use LEAD() to inspect the next row in a series Use ROW_NUMBER() OVER with a deterministic partition and order Use LAG() to compare a row with its preceding ordered value Use DENSE_RANK() for tied rankings without numeric gaps Explanation Correct Answer A:This approach accesses following rows inside an ordered partition. Incorrect Answers B:This option assigns a distinct row position inside each group. This does not meet the need for a method that accesses following rows inside an ordered partition. C:That technique accesses a prior row without a self-join. This does not meet the need for a method that accesses following rows inside an ordered partition. D:This choice assigns equal ranks for ties and consecutive distinct ranks. This does not meet the need for a method that accesses following rows inside an ordered partition.
Question 225 An average-per-customer expression has to tolerate empty customer counts. Which approach would you recommend? Use TRY_CONVERT to safely handle mixed input types Use NULLIF to avoid division by zero for an explicitly optional denominator Use NOT EXISTS for an anti-join condition Use OUTER APPLY to preserve left rows without matches Explanation Correct Answer B:This implementation converts a matching zero denominator into NULL. Incorrect Answers A:This option returns NULL for failed supported conversion instead of aborting each row. This does not meet the need for a method that converts a matching zero denominator into NULL. C:This alternative keeps rows with no matching counterpart. This does not meet the need for a method that converts a matching zero denominator into NULL. D:That technique extends a correlated table expression while retaining unmatched outer rows. This does not meet the need for a method that converts a matching zero denominator into NULL.
Question 226 A catalog report lists products that have no active supplier relationship. The target SQL engine supports the named T-SQL feature. What should the engineer choose? Use NULLIF to avoid division by zero for an explicitly optional denominator Use EXISTS for a correlated existence test Use NOT EXISTS for an anti-join condition Use TRY_CONVERT to safely handle mixed input types Explanation Correct Answer C:This approach keeps rows with no matching counterpart. Incorrect Answers A:That technique converts a matching zero denominator into NULL. This does not meet the need for a method that keeps rows with no matching counterpart. B:That technique filters rows by presence without returning or duplicating matching children. This does not meet the need for a method that keeps rows with no matching counterpart. D:This option returns NULL for failed supported conversion instead of aborting each row. This does not meet the need for a method that keeps rows with no matching counterpart.
Question 227 Copilot drafts a script changing hundreds of rows for a schema migration. What should the engineer choose? Validate AI-generated SQL in a nonproduction test database Limit MCP tool credentials to least privilege Keep secret values out of prompts and checked-in instruction files Use grounded workspace context before relying on Copilot schema suggestions Explanation Correct Answer A:Validate AI-generated SQL in a nonproduction test database fits because it detects incorrect joins, DDL and resource usage before deployment. That addresses the stated technical constraint. Incorrect Answers B:That technique confines damage from malformed agent calls or prompt injection. This does not meet the need for a method that detects incorrect joins, DDL and resource usage before deployment. C:This option prevents credential disclosure and repository leakage. This does not meet the need for a method that detects incorrect joins, DDL and resource usage before deployment. D:That technique reduces hallucinated tables and obsolete column names. This does not meet the need for a method that detects incorrect joins, DDL and resource usage before deployment.
Question 228 A developer wants an AI assistant to inspect a repository containing production credentials. Which solution best satisfies the stated constraint? Verify the selected AI model and connector permissions in chat configuration Use approved GitHub Copilot organization policies for developer access Treat suggested AI-generated DDL as untrusted until validated Review sensitive schema and data before allowing an AI coding assistant to access repository context Explanation Correct Answer D:Review sensitive schema and data before allowing an AI coding assistant to access repository context fits because it reduces accidental exposure of regulated information to an external assistant. That addresses the stated technical constraint. Incorrect Answers A:This alternative prevents unapproved model choice or excessive tool access. This does not meet the need for a method that reduces accidental exposure of regulated information to an external assistant. B:This option enforces centralized enablement and allowed models/features. This does not meet the need for a method that reduces accidental exposure of regulated information to an external assistant. C:This option requires human review, tests and security checks before use. This does not meet the need for a method that reduces accidental exposure of regulated information to an external assistant.
Question 229 An assistant invents a column that is absent from the current database project. Which solution is most directly applicable? Validate AI-generated SQL in a nonproduction test database Limit MCP tool credentials to least privilege Use pull-request review for AI-assisted schema changes Use grounded workspace context before relying on Copilot schema suggestions Explanation Correct Answer D:This approach reduces hallucinated tables and obsolete column names. Incorrect Answers A:This option detects incorrect joins, DDL and resource usage before deployment. This does not meet the need for a method that reduces hallucinated tables and obsolete column names. B:This option confines damage from malformed agent calls or prompt injection. This does not meet the need for a method that reduces hallucinated tables and obsolete column names. C:That technique obtains tracked review and branch protection before integration. This does not meet the need for a method that reduces hallucinated tables and obsolete column names.
Question 230 Generated T-SQL repeatedly violates the team's naming and transaction-handling conventions. Which solution best satisfies the stated constraint? Review sensitive schema and data before allowing an AI coding assistant to access repository context Use repository-level Copilot instruction files to encode project conventions Treat suggested AI-generated DDL as untrusted until validated Verify the selected AI model and connector permissions in chat configuration Explanation Correct Answer B:This implementation fits because it gives code generation stable repository-specific context. That addresses the stated technical constraint. Incorrect Answers A:This choice reduces accidental exposure of regulated information to an external assistant. This does not meet the need for a method that gives code generation stable repository-specific context. C:That technique requires human review, tests and security checks before use. This does not meet the need for a method that gives code generation stable repository-specific context. D:That technique prevents unapproved model choice or excessive tool access. This does not meet the need for a method that gives code generation stable repository-specific context.