Question 150 An order report must select the newest order per customer with a tie-breaker. Which change most directly meets the requirement? Use ROW_NUMBER() OVER with a deterministic partition and order Use LAG() to compare a row with its preceding ordered value Use RANK() when tied positions should leave ranking gaps Use SUM(amount) OVER(PARTITION BY ... ORDER BY ...) with an appropriate frame Explanation Correct Answer A:This implementation assigns a distinct row position inside each group. Incorrect Answers B:This choice accesses a prior row without a self-join. This does not meet the need for a method that assigns a distinct row position inside each group. C:This alternative models competition-style positions with ties. This does not meet the need for a method that assigns a distinct row position inside each group. D:This choice calculates a running total while retaining detail rows. This does not meet the need for a method that assigns a distinct row position inside each group.
Question 151 A schedule audit calculates the time until the next appointment. What is the most appropriate design decision? 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 LEAD() to inspect the next row in a series Use LAG() to compare a row with its preceding ordered value Explanation Correct Answer C:This choice accesses following rows inside an ordered partition. Incorrect Answers A:This alternative preserves each input row while exposing its peer-group average. This does not meet the need for a method that accesses following rows inside an ordered partition. B:That technique calculates a running total while retaining detail rows. This does not meet the need for a method that accesses following rows inside an ordered partition. D: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.
Question 152 An analyst needs quartile labels for a ranked satisfaction score list. 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 RANK() when tied positions should leave ranking gaps Use SUM(amount) OVER(PARTITION BY ... ORDER BY ...) with an appropriate frame Use NTILE(4) OVER to divide ordered rows into four buckets Explanation Correct Answer D:This implementation fits because it assigns approximate quantile groups by ordered position. That addresses the stated technical constraint. Incorrect Answers A:This choice accesses following rows inside an ordered partition. This does not meet the need for a method that assigns approximate quantile groups by ordered position. B:That technique models competition-style positions with ties. This does not meet the need for a method that assigns approximate quantile groups by ordered position. C:This option calculates a running total while retaining detail rows. This does not meet the need for a method that assigns approximate quantile groups by ordered position.
Question 153 A service must return an id/name/status object for each order. Which solution best satisfies the stated constraint? Use JSON_ARRAYAGG for an array aggregated from rows Use OPENJSON to transform a JSON array into a relational rowset Use JSON_OBJECT to construct a JSON object from relational values Use JSON_MODIFY to change one property in JSON text Explanation Correct Answer C:This implementation fits because it serializes named SQL expressions into a single JSON object. That addresses the stated technical constraint. Incorrect Answers A:This alternative 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. B:That technique expands array elements into table-shaped rows. This does not meet the need for a method that serializes named SQL expressions into a single JSON object. D:This option 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.
Question 154 A support audit identifies devices with no linked inventory assignment. What is the most appropriate next design step? Use NULLIF to avoid division by zero for an explicitly optional denominator Use EXISTS for a correlated existence test Use CROSS APPLY for a correlated table expression that must match Use NOT EXISTS for an anti-join condition Explanation Correct Answer D:This choice 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. C:That technique evaluates a right-hand rowset per left row and excludes unmatched rows. This does not meet the need for a method that keeps rows with no matching counterpart.
Question 155 An audit checks how many identifiers of a given format occur in a message. What should the SQL developer implement? Use JARO_WINKLER_DISTANCE for name-oriented fuzzy comparison Use REGEXP_LIKE for a regular-expression predicate on supported engines Use REGEXP_COUNT to count matches of a pattern Use a graph MATCH query over node and edge tables Explanation Correct Answer C:This choice returns the number of regex occurrences. Incorrect Answers A:This option measures likeness of string pairs especially near their prefixes. This does not meet the need for a method that returns the number of regex occurrences. B:This option evaluates whether a string matches a regex expression. This does not meet the need for a method that returns the number of regex occurrences. D:This option follows graph-pattern relationships stored in graph tables. This does not meet the need for a method that returns the number of regex occurrences.
Question 156 An event payload contains a nested shipping object needed as JSON output. The target SQL engine supports the named T-SQL feature. Which technical choice addresses this need? Use JSON_ARRAY to build an array from explicit expressions Use JSON_OBJECT to construct a JSON object from relational values Use JSON_QUERY to extract an object or array fragment Use JSON_VALUE to extract a scalar property from a JSON document Explanation Correct Answer C:This implementation returns JSON-shaped nested content rather than a scalar. Incorrect Answers A:This choice creates JSON array values from supplied arguments. This does not meet the need for a method that returns JSON-shaped nested content rather than a scalar. B:This choice 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. D:This option 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.
Question 157 A settings response needs a short ordered list of three configured labels. What should the engineer choose? Use JSON_ARRAYAGG for an array aggregated from rows Use JSON_ARRAY to build an array from explicit expressions Use JSON_VALUE to extract a scalar property from a JSON document Use ISJSON before processing untrusted JSON text Explanation Correct Answer B:This implementation creates JSON array values from supplied arguments. Incorrect Answers A:This alternative combines multiple SQL row values into one JSON array. This does not meet the need for a method that creates JSON array values from supplied arguments. C:This option returns a single scalar SQL expression from a JSON path. This does not meet the need for a method that creates JSON array values from supplied arguments. D:This alternative tests whether input has valid JSON structure. This does not meet the need for a method that creates JSON array values from supplied arguments.
Question 158 A customer response needs all associated product codes in one JSON array. What should the engineer choose? Use JSON_ARRAY to build an array from explicit expressions 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 Explanation Correct Answer B:This implementation fits because it combines multiple SQL row values into one JSON array. That addresses the stated technical constraint. Incorrect Answers A:This alternative creates JSON array values from supplied arguments. This does not meet the need for a method that combines multiple SQL row values into one JSON array. C:This alternative returns a single scalar SQL expression from a JSON path. This does not meet the need for a method that combines multiple SQL row values into one JSON array. D:This alternative returns updated JSON content at a selected path. This does not meet the need for a method that combines multiple SQL row values into one JSON array.
Question 159 A contact list prefers an approved mobile phone number then office contact. Which technical choice addresses this need? Use CROSS APPLY for a correlated table expression that must match Use EXISTS for a correlated existence test Use COALESCE to select the first non-null expression Use OUTER APPLY to preserve left rows without matches Explanation Correct Answer C:This choice supplies a fallback among ordered nullable values. Incorrect Answers A:This alternative evaluates a right-hand rowset per left row and excludes unmatched rows. This does not meet the need for a method that supplies a fallback among ordered nullable values. B:This choice 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. D:This option extends a correlated table expression while retaining unmatched outer rows. This does not meet the need for a method that supplies a fallback among ordered nullable values.
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.