Question 160 A rate report needs to avoid runtime failure for branches with zero orders. The target SQL engine supports the named T-SQL feature. Which technical choice addresses this need? Use COALESCE to select the first non-null expression Use OUTER APPLY to preserve left rows without matches Use NULLIF to avoid division by zero for an explicitly optional denominator Use EXISTS for a correlated existence test Explanation Correct Answer C:This implementation converts a matching zero denominator into NULL. Incorrect Answers A:This alternative supplies a fallback among ordered nullable values. This does not meet the need for a method that converts a matching zero denominator into NULL. B:This choice 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. D:That technique filters rows by presence without returning or duplicating matching children. This does not meet the need for a method that converts a matching zero denominator into NULL.
Question 161 A sensor report needs the preceding reading for each device. Which solution best satisfies the stated constraint? Use LEAD() to inspect the next row in a series 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 Use LAG() to compare a row with its preceding ordered value Explanation Correct Answer D:This approach accesses a prior row without a self-join. Incorrect Answers A:This option accesses following rows inside an ordered partition. This does not meet the need for a method that accesses a prior row without a self-join. B:This option preserves each input row while exposing its peer-group average. This does not meet the need for a method that accesses a prior row without a self-join. C:This alternative calculates a running total while retaining detail rows. This does not meet the need for a method that accesses a prior row without a self-join.
Question 162 A dashboard displays active tag names for each ticket in one comma-separated cell. Which solution best satisfies the stated constraint? Use NOT EXISTS for an anti-join condition Use EXISTS for a correlated existence test Use NULLIF to avoid division by zero for an explicitly optional denominator Use STRING_AGG to combine multiple row strings into one delimited output Explanation Correct Answer D:This choice aggregates text values over a group. Incorrect Answers A:This option keeps rows with no matching counterpart. This does not meet the need for a method that aggregates text values over a group. B:That technique filters rows by presence without returning or duplicating matching children. This does not meet the need for a method that aggregates text values over a group. C:This option converts a matching zero denominator into NULL. This does not meet the need for a method that aggregates text values over a group.
Question 163 A support app must change just the notificationLevel property in a stored JSON document. Which change most directly meets the requirement? Use JSON_MODIFY to change one property in JSON text Use JSON_ARRAYAGG for an array aggregated from rows Use JSON_OBJECT to construct a JSON object from relational values Use JSON_VALUE to extract a scalar property from a JSON document Explanation Correct Answer A:This implementation fits because it returns updated JSON content at a selected path. That addresses the stated technical constraint. Incorrect Answers B:This option combines multiple SQL row values into one JSON array. This does not meet the need for a method that returns updated JSON content at a selected path. C:This choice serializes named SQL expressions into a single JSON object. This does not meet the need for a method that returns updated JSON content at a selected path. D:This choice returns a single scalar SQL expression from a JSON path. This does not meet the need for a method that returns updated JSON content at a selected path.
Question 164 An ingest endpoint receives arbitrary payload strings and needs to reject malformed JSON. The target SQL engine supports the named T-SQL feature. Which solution best satisfies the stated constraint? Use JSON_MODIFY to change one property in JSON text Use JSON_QUERY to extract an object or array fragment Use ISJSON before processing untrusted JSON text Use JSON_OBJECT to construct a JSON object from relational values Explanation Correct Answer C:This implementation tests whether input has valid JSON structure. Incorrect Answers A:This option returns updated JSON content at a selected path. This does not meet the need for a method that tests whether input has valid JSON structure. B:This choice returns JSON-shaped nested content rather than a scalar. This does not meet the need for a method that tests whether input has valid JSON structure. D:This alternative serializes named SQL expressions into a single JSON object. This does not meet the need for a method that tests whether input has valid JSON structure.
Question 165 An organization chart query returns all descendants under one manager. Which solution is most directly applicable? Use a graph MATCH query over node and edge tables Use a recursive CTE for a bounded hierarchy traversal Use REGEXP_LIKE for a regular-expression predicate on supported engines Use REGEXP_REPLACE to replace portions matching a regular expression Explanation Correct Answer B:This implementation repeatedly joins a parent-child relationship across levels. Incorrect Answers A:This choice follows graph-pattern relationships stored in graph tables. This does not meet the need for a method that repeatedly joins a parent-child relationship across levels. C:This choice evaluates whether a string matches a regex expression. This does not meet the need for a method that repeatedly joins a parent-child relationship across levels. D:This alternative transforms substrings identified by a regex. This does not meet the need for a method that repeatedly joins a parent-child relationship across levels.
Question 166 Two locations sharing first place should make the next location third, not second. Which solution is most directly applicable? Use DENSE_RANK() for tied rankings without numeric gaps Use NTILE(4) OVER to divide ordered rows into four buckets Use ROW_NUMBER() OVER with a deterministic partition and order Use RANK() when tied positions should leave ranking gaps Explanation Correct Answer D:This choice models competition-style positions with ties. Incorrect Answers A:That technique assigns equal ranks for ties and consecutive distinct ranks. This does not meet the need for a method that models competition-style positions with ties. B:That technique assigns approximate quantile groups by ordered position. This does not meet the need for a method that models competition-style positions with ties. C:This choice assigns a distinct row position inside each group. This does not meet the need for a method that models competition-style positions with ties.
Question 167 A network investigation traverses device-to-account edge relationships. Which solution is most directly applicable? Use REGEXP_REPLACE to replace portions matching a regular expression Use a graph MATCH query over node and edge tables Use JARO_WINKLER_DISTANCE for name-oriented fuzzy comparison Use EDIT_DISTANCE for character-edit similarity analysis Explanation Correct Answer B:This approach follows graph-pattern relationships stored in graph tables. Incorrect Answers A:This option transforms substrings identified by a regex. This does not meet the need for a method that follows graph-pattern relationships stored in graph tables. C:That technique measures likeness of string pairs especially near their prefixes. This does not meet the need for a method that follows graph-pattern relationships stored in graph tables. D:This choice measures edit operations needed between two strings. This does not meet the need for a method that follows graph-pattern relationships stored in graph tables.
Question 168 An import must check whether invoice codes fit an anchored alphanumeric pattern. The target SQL engine supports the named T-SQL feature. What should the engineer choose? Use REGEXP_SUBSTR to extract a regex-matched fragment Use a recursive CTE for a bounded hierarchy traversal Use JARO_WINKLER_DISTANCE for name-oriented fuzzy comparison Use REGEXP_LIKE for a regular-expression predicate on supported engines Explanation Correct Answer D:This implementation fits because it evaluates whether a string matches a regex expression. That addresses the stated technical constraint. Incorrect Answers A:That technique returns a portion of text matching a pattern. This does not meet the need for a method that evaluates whether a string matches a regex expression. B:This choice repeatedly joins a parent-child relationship across levels. This does not meet the need for a method that evaluates whether a string matches a regex expression. C:This choice measures likeness of string pairs especially near their prefixes. This does not meet the need for a method that evaluates whether a string matches a regex expression.
Question 169 A payment payload stores one currencyCode string that must be compared in a WHERE clause. What should the engineer choose? Use JSON_VALUE to extract a scalar property from a JSON document Use OPENJSON to transform a JSON array into a relational rowset Use JSON_QUERY to extract an object or array fragment Use JSON_OBJECT to construct a JSON object from relational values Explanation Correct Answer A:This choice returns a single scalar SQL expression from a JSON path. Incorrect Answers B:That technique expands array elements into table-shaped rows. This does not meet the need for a method that returns a single scalar SQL expression from a JSON path. C:This alternative returns JSON-shaped nested content rather than a scalar. This does not meet the need for a method that returns a single scalar SQL expression from a JSON path. D:That technique serializes named SQL expressions into a single JSON object. This does not meet the need for a method that returns a single scalar SQL expression from a JSON path.
Question 170 A report expands each parent row into matching parameterized child results only. What should the engineer choose? Use COALESCE to select the first non-null expression Use EXISTS for a correlated existence test Use CROSS APPLY for a correlated table expression that must match Use TRY_CONVERT to safely handle mixed input types Explanation Correct Answer C:This approach evaluates a right-hand rowset per left row and excludes unmatched rows. Incorrect Answers A:This choice supplies a fallback among ordered nullable values. This does not meet the need for a method that evaluates a right-hand rowset per left row and excludes unmatched rows. B:That technique filters rows by presence without returning or duplicating matching children. This does not meet the need for a method that evaluates a right-hand rowset per left row and excludes unmatched rows. D:This option returns NULL for failed supported conversion instead of aborting each row. This does not meet the need for a method that evaluates a right-hand rowset per left row and excludes unmatched rows.