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.
Question 171 A sales detail listing compares each invoice amount with its department average. The target SQL engine supports the named T-SQL feature. Which change most directly meets the requirement? Use LEAD() to inspect the next row in a series Use ROW_NUMBER() OVER with a deterministic partition and order Use RANK() when tied positions should leave ranking gaps Use AVG() OVER(PARTITION BY ...) for a group statistic on each detail row Explanation Correct Answer D:This implementation preserves each input row while exposing its peer-group average. Incorrect Answers A:That technique accesses following rows inside an ordered partition. This does not meet the need for a method that preserves each input row while exposing its peer-group average. B:This choice assigns a distinct row position inside each group. This does not meet the need for a method that preserves each input row while exposing its peer-group average. C:That technique models competition-style positions with ties. This does not meet the need for a method that preserves each input row while exposing its peer-group average.
Question 172 A telemetry batch arrives as a JSON list that must be joined to relational records. What is the most appropriate next design step? Use JSON_ARRAYAGG for an array aggregated from rows Use JSON_VALUE to extract a scalar property from a JSON document Use JSON_MODIFY to change one property in JSON text Use OPENJSON to transform a JSON array into a relational rowset Explanation Correct Answer D:This implementation fits because it expands array elements into table-shaped rows. That addresses the stated technical constraint. Incorrect Answers A:This option combines multiple SQL row values into one JSON array. This does not meet the need for a method that expands array elements into table-shaped rows. B:That technique 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. C:That technique returns updated JSON content at a selected path. This does not meet the need for a method that expands array elements into table-shaped rows.
Question 173 An import needs to test which date strings are convertible without stopping the scan. What is the most appropriate design decision? Use TRY_CONVERT to safely handle mixed input types Use STRING_AGG to combine multiple row strings into one delimited output Use OUTER APPLY to preserve left rows without matches Use COALESCE to select the first non-null expression Explanation Correct Answer A:This choice returns NULL for failed supported conversion instead of aborting each row. Incorrect Answers B:This choice 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. C:This alternative extends a correlated table expression while retaining unmatched outer 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 supplies a fallback among ordered nullable values. This does not meet the need for a method that returns NULL for failed supported conversion instead of aborting each row.
Question 174 A staging job must separate non-JSON messages before extracting properties. Which solution is most directly applicable? Use JSON_VALUE to extract a scalar property from a JSON document Use JSON_ARRAYAGG for an array aggregated from rows Use JSON_MODIFY to change one property in JSON text Use ISJSON before processing untrusted JSON text Explanation Correct Answer D:This choice tests whether input has valid JSON structure. Incorrect Answers A:This choice returns a single scalar SQL expression from a JSON path. This does not meet the need for a method that tests whether input has valid JSON structure. B:This choice combines multiple SQL row values into one JSON array. This does not meet the need for a method that tests whether input has valid JSON structure. C: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.
Question 175 A league table must show 1,1,3 for tied highest results. The target SQL engine supports the named T-SQL feature. What is the most appropriate design decision? Use ROW_NUMBER() OVER with a deterministic partition and order Use NTILE(4) OVER to divide ordered rows into four buckets Use LAG() to compare a row with its preceding ordered value Use RANK() when tied positions should leave ranking gaps Explanation Correct Answer D:This implementation models competition-style positions with ties. Incorrect Answers A:That technique assigns a distinct row position inside each group. This does not meet the need for a method that models competition-style positions with ties. B:This choice 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 alternative accesses a prior row without a self-join. This does not meet the need for a method that models competition-style positions with ties.
Question 176 A parent report needs optional child details while preserving parents with no children. What should the engineer choose? Use COALESCE to select the first non-null expression Use STRING_AGG to combine multiple row strings into one delimited output Use CROSS APPLY for a correlated table expression that must match Use OUTER APPLY to preserve left rows without matches Explanation Correct Answer D:This implementation fits because it extends a correlated table expression while retaining unmatched outer rows. That addresses the stated technical constraint. Incorrect Answers A:This alternative supplies a fallback among ordered nullable values. This does not meet the need for a method that extends a correlated table expression while retaining unmatched outer rows. B:That technique aggregates text values over a group. This does not meet the need for a method that extends a correlated table expression while retaining unmatched outer rows. C:This option evaluates a right-hand rowset per left row and excludes unmatched rows. This does not meet the need for a method that extends a correlated table expression while retaining unmatched outer rows.
Question 177 An equipment hierarchy must enumerate nested components beneath a root assembly. Which approach would you recommend? Use REGEXP_REPLACE to replace portions matching a regular expression Use a recursive CTE for a bounded hierarchy traversal Use REGEXP_COUNT to count matches of a pattern Use REGEXP_SUBSTR to extract a regex-matched fragment Explanation Correct Answer B:This implementation repeatedly joins a parent-child relationship across levels. Incorrect Answers A:That technique transforms substrings identified by a regex. This does not meet the need for a method that repeatedly joins a parent-child relationship across levels. C:That technique returns the number of regex occurrences. This does not meet the need for a method that repeatedly joins a parent-child relationship across levels. D:That technique returns a portion of text matching a pattern. This does not meet the need for a method that repeatedly joins a parent-child relationship across levels.
Question 178 A compliance query finds suppliers connected to a flagged entity through approval edges. What should the engineer choose? Use REGEXP_SUBSTR to extract a regex-matched fragment Use EDIT_DISTANCE for character-edit similarity analysis Use REGEXP_COUNT to count matches of a pattern Use a graph MATCH query over node and edge tables Explanation Correct Answer D:This choice follows graph-pattern relationships stored in graph tables. Incorrect Answers A:This option returns a portion of text matching a pattern. This does not meet the need for a method that follows graph-pattern relationships stored in graph tables. B:This option 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. C:This choice returns the number of regex occurrences. This does not meet the need for a method that follows graph-pattern relationships stored in graph tables.
Question 179 A preferences document includes an array of notification channels to return intact. The target SQL engine supports the named T-SQL feature. Which solution is most directly applicable? Use JSON_OBJECT to construct a JSON object from relational values Use JSON_VALUE to extract a scalar property from a JSON document Use JSON_QUERY to extract an object or array fragment Use JSON_ARRAYAGG for an array aggregated from rows Explanation Correct Answer C:This choice returns JSON-shaped nested content rather than a scalar. Incorrect Answers A:This alternative serializes named SQL expressions into a single JSON object. This does not meet the need for a method that returns JSON-shaped nested content rather than a scalar. B:This alternative returns a single scalar SQL expression from a JSON path. This does not meet the need for a method that returns JSON-shaped nested content rather than a scalar. D:That technique combines multiple SQL row values into one JSON array. This does not meet the need for a method that returns JSON-shaped nested content rather than a scalar.
Question 180 A report needs a display name using nickname first then legal name. Which approach would you recommend? Use COALESCE to select the first non-null expression Use EXISTS for a correlated existence test Use TRY_CONVERT to safely handle mixed input types Use STRING_AGG to combine multiple row strings into one delimited output Explanation Correct Answer A:This approach supplies a fallback among ordered nullable values. Incorrect Answers B:This option filters rows by presence without returning or duplicating matching children. This does not meet the need for a method that supplies a fallback among ordered nullable values. C:This alternative returns NULL for failed supported conversion instead of aborting each row. This does not meet the need for a method that supplies a fallback among ordered nullable values. D:This option aggregates text values over a group. This does not meet the need for a method that supplies a fallback among ordered nullable values.