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.
Question 181 A change log creates a small JSON envelope from T-SQL columns. Which approach would you recommend? Use JSON_ARRAYAGG for an array aggregated from rows Use JSON_OBJECT to construct a JSON object from relational values Use JSON_MODIFY to change one property in JSON text Use JSON_ARRAY to build an array from explicit expressions Explanation Correct Answer B:This implementation serializes named SQL expressions into a single JSON object. 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. C:This choice 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 creates JSON array values from supplied arguments. This does not meet the need for a method that serializes named SQL expressions into a single JSON object.
Question 182 A data pipeline removes disallowed punctuation sequences from imported reference codes. Which approach would you recommend? Use JARO_WINKLER_DISTANCE for name-oriented fuzzy comparison Use REGEXP_LIKE for a regular-expression predicate on supported engines Use REGEXP_REPLACE to replace portions matching a regular expression Use EDIT_DISTANCE for character-edit similarity analysis Explanation Correct Answer C:This approach transforms substrings identified by a regex. Incorrect Answers A:This choice measures likeness of string pairs especially near their prefixes. This does not meet the need for a method that transforms substrings identified by a regex. B:This choice evaluates whether a string matches a regex expression. This does not meet the need for a method that transforms substrings identified by a regex. D:That technique measures edit operations needed between two strings. This does not meet the need for a method that transforms substrings identified by a regex.
Question 183 A stored procedure returns an approved set of literal workflow stages as one JSON list. The target SQL engine supports the named T-SQL feature. What should the SQL developer implement? 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_OBJECT to construct a JSON object from relational values Explanation Correct Answer A:This implementation fits because it creates JSON array values from supplied arguments. 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 creates JSON array values from supplied arguments. C:That technique 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 option serializes named SQL expressions into a single JSON object. This does not meet the need for a method that creates JSON array values from supplied arguments.
Question 184 A billing run finds subscriptions with no payment this month. What should the SQL developer implement? Use EXISTS for a correlated existence test Use NOT EXISTS for an anti-join condition Use STRING_AGG to combine multiple row strings into one delimited output Use CROSS APPLY for a correlated table expression that must match Explanation Correct Answer B:This choice keeps rows with no matching counterpart. Incorrect Answers A:This choice 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:This choice aggregates text values over a group. This does not meet the need for a method that keeps rows with no matching counterpart. D:This alternative 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 185 A utilization calculation must show NULL when its planned capacity is zero. Which change most directly meets the requirement? Use STRING_AGG to combine multiple row strings into one delimited output Use NOT EXISTS for an anti-join condition Use CROSS APPLY for a correlated table expression that must match Use NULLIF to avoid division by zero for an explicitly optional denominator Explanation Correct Answer D:This approach converts a matching zero denominator into NULL. Incorrect Answers A:That technique aggregates text values over a group. This does not meet the need for a method that converts a matching zero denominator into NULL. B:This option keeps rows with no matching counterpart. This does not meet the need for a method that converts a matching zero denominator into NULL. 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 converts a matching zero denominator into NULL.
Question 186 A subscription log needs the next status-change timestamp for each customer. Which implementation is the best fit? Use SUM(amount) OVER(PARTITION BY ... ORDER BY ...) with an appropriate frame Use DENSE_RANK() for tied rankings without numeric gaps Use ROW_NUMBER() OVER with a deterministic partition and order Use LEAD() to inspect the next row in a series Explanation Correct Answer D:This choice accesses following rows inside an ordered partition. Incorrect Answers A:This option 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. B: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. C:This choice 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.
Question 187 A validation rule searches for a specific multi-part identifier shape in text. The target SQL engine supports the named T-SQL feature. Which change most directly meets the requirement? 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 EDIT_DISTANCE for character-edit similarity analysis Explanation Correct Answer C:This choice evaluates whether a string matches a regex expression. Incorrect Answers A:This alternative follows graph-pattern relationships stored in graph tables. This does not meet the need for a method that evaluates whether a string matches a regex expression. B:That technique 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. D:This choice measures edit operations needed between two strings. This does not meet the need for a method that evaluates whether a string matches a regex expression.
Question 188 An importer extracts a tagged sequence from a mixed-format comment. What should the SQL developer implement? Use a graph MATCH query over node and edge tables Use JARO_WINKLER_DISTANCE for name-oriented fuzzy comparison Use REGEXP_COUNT to count matches of a pattern Use REGEXP_SUBSTR to extract a regex-matched fragment Explanation Correct Answer D:This implementation fits because it returns a portion of text matching a pattern. That addresses the stated technical constraint. Incorrect Answers A:That technique follows graph-pattern relationships stored in graph tables. This does not meet the need for a method that returns a portion of text matching a pattern. B:That technique measures likeness of string pairs especially near their prefixes. This does not meet the need for a method that returns a portion of text matching a pattern. C:This alternative returns the number of regex occurrences. This does not meet the need for a method that returns a portion of text matching a pattern.
Question 189 A supplier matching report looks for near names with short transpositions. Which technical choice addresses this need? Use REGEXP_COUNT to count matches of a pattern Use JARO_WINKLER_DISTANCE for name-oriented fuzzy comparison Use REGEXP_REPLACE to replace portions matching a regular expression Use REGEXP_SUBSTR to extract a regex-matched fragment Explanation Correct Answer B:This implementation measures likeness of string pairs especially near their prefixes. Incorrect Answers A:This option returns the number of regex occurrences. This does not meet the need for a method that measures likeness of string pairs especially near their prefixes. C:This option transforms substrings identified by a regex. This does not meet the need for a method that measures likeness of string pairs especially near their prefixes. D:That technique returns a portion of text matching a pattern. This does not meet the need for a method that measures likeness of string pairs especially near their prefixes.
Question 190 A marketing report selects products that have any qualifying review. Which approach would you recommend? Use OUTER APPLY to preserve left rows without matches Use CROSS APPLY for a correlated table expression that must match Use TRY_CONVERT to safely handle mixed input types Use EXISTS for a correlated existence test Explanation Correct Answer D:This choice filters rows by presence without returning or duplicating matching children. Incorrect Answers A:This option extends a correlated table expression while retaining unmatched outer rows. This does not meet the need for a method that filters rows by presence without returning or duplicating matching children. B:This alternative evaluates a right-hand rowset per left row and excludes unmatched rows. This does not meet the need for a method that filters rows by presence without returning or duplicating matching children. C:This choice returns NULL for failed supported conversion instead of aborting each row. This does not meet the need for a method that filters rows by presence without returning or duplicating matching children.