Question 140 A customer matching study handles swapped or slightly misspelled surnames. The target SQL engine supports the named T-SQL feature. Which implementation is the best fit? Use a graph MATCH query over node and edge tables Use REGEXP_REPLACE to replace portions matching a regular expression Use JARO_WINKLER_DISTANCE for name-oriented fuzzy comparison Use REGEXP_SUBSTR to extract a regex-matched fragment Explanation Correct Answer C:This implementation measures likeness of string pairs especially near their prefixes. Incorrect Answers A:That technique follows graph-pattern relationships stored in graph tables. This does not meet the need for a method that measures likeness of string pairs especially near their prefixes. B:That technique 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:This alternative 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 141 On a SQL platform that supports the listed regular-expression functions, a support ingestion query must extract the first incident identifier from a mixed free-text message. Which function directly returns a matching fragment? 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 Use REGEXP_SUBSTR to extract a regex-matched fragment Explanation Correct Answer D:This approach returns a portion of text matching a pattern. Incorrect Answers A:This alternative evaluates whether a string matches a regex expression. This does not meet the need for a method that returns a portion of text matching a pattern. B: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. C:This alternative 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.
Question 142 An invoice query invokes an inline TVF for each account but must omit accounts with no matching line. What is the most appropriate next design step? Use CROSS APPLY for a correlated table expression that must match Use OUTER APPLY to preserve left rows without matches Use NOT EXISTS for an anti-join condition Use TRY_CONVERT to safely handle mixed input types Explanation Correct Answer A:This implementation evaluates a right-hand rowset per left row and excludes unmatched rows. Incorrect Answers B:This alternative extends a correlated table expression while retaining unmatched outer rows. This does not meet the need for a method that evaluates a right-hand rowset per left row and excludes unmatched rows. C:That technique keeps rows with no matching counterpart. 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 143 A cleanup tool wants a distance measure between typo-prone product labels. What should the engineer choose? Use EDIT_DISTANCE for character-edit similarity analysis Use a recursive CTE for a bounded hierarchy traversal Use a graph MATCH query over node and edge tables Use REGEXP_SUBSTR to extract a regex-matched fragment Explanation Correct Answer A:This implementation fits because it measures edit operations needed between two strings. That addresses the stated technical constraint. Incorrect Answers B:That technique repeatedly joins a parent-child relationship across levels. This does not meet the need for a method that measures edit operations needed between two strings. C:This choice follows graph-pattern relationships stored in graph tables. This does not meet the need for a method that measures edit operations needed between two strings. D:This option returns a portion of text matching a pattern. This does not meet the need for a method that measures edit operations needed between two strings.
Question 144 A bank statement displays each transaction and the account's cumulative balance. The target SQL engine supports the named T-SQL feature. Which technical choice addresses this need? Use AVG() OVER(PARTITION BY ...) for a group statistic on each detail row Use DENSE_RANK() for tied rankings without numeric 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 C:This implementation calculates a running total while retaining detail rows. Incorrect Answers A:That technique preserves each input row while exposing its peer-group average. This does not meet the need for a method that calculates a running total while retaining detail rows. B:This alternative assigns equal ranks for ties and consecutive distinct ranks. This does not meet the need for a method that calculates a running total while retaining detail rows. D:That technique assigns approximate quantile groups by ordered position. This does not meet the need for a method that calculates a running total while retaining detail rows.
Question 145 On a supported Azure SQL deployment, an API posts a JSON lineItems array whose entries must be validated as separate SQL rows. Which function should parse the array into a relational rowset? Use OPENJSON to transform a JSON array into a relational rowset Use JSON_OBJECT to construct a JSON object from relational values Use JSON_ARRAYAGG for an array aggregated from rows Use JSON_ARRAY to build an array from explicit expressions Explanation Correct Answer A:This choice expands array elements into table-shaped rows. Incorrect Answers B:This option serializes named SQL expressions into a single JSON object. This does not meet the need for a method that expands array elements into table-shaped rows. C:This choice 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. D: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.
Question 146 An app must find customers who have at least one unpaid invoice. Which approach would you recommend? Use NOT EXISTS for an anti-join condition Use CROSS APPLY for a correlated table expression that must match Use EXISTS for a correlated existence test Use STRING_AGG to combine multiple row strings into one delimited output Explanation Correct Answer C:This approach filters rows by presence without returning or duplicating matching children. Incorrect Answers A:This option keeps rows with no matching counterpart. 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. D:That technique aggregates text values over a group. This does not meet the need for a method that filters rows by presence without returning or duplicating matching children.
Question 147 A staging table has amounts in text with occasional invalid characters. What is the most appropriate next design step? Use OUTER APPLY to preserve left rows without matches Use TRY_CONVERT to safely handle mixed input types Use NOT EXISTS for an anti-join condition Use EXISTS for a correlated existence test Explanation Correct Answer B:This approach returns NULL for failed supported conversion instead of aborting each row. Incorrect Answers A: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. C:This choice keeps rows with no matching counterpart. This does not meet the need for a method that returns NULL for failed supported conversion instead of aborting each row. D: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.
Question 148 An ETL query must normalize repeated whitespace within free-text labels. The target SQL engine supports the named T-SQL feature. Which approach would you recommend? Use REGEXP_LIKE for a regular-expression predicate on supported engines Use REGEXP_REPLACE to replace portions matching a regular expression Use a recursive CTE for a bounded hierarchy traversal Use a graph MATCH query over node and edge tables Explanation Correct Answer B:This choice transforms substrings identified by a regex. Incorrect Answers A:This option evaluates whether a string matches a regex expression. This does not meet the need for a method that transforms substrings identified by a regex. C:This option repeatedly joins a parent-child relationship across levels. This does not meet the need for a method that transforms substrings identified by a regex. D:This alternative follows graph-pattern relationships stored in graph tables. This does not meet the need for a method that transforms substrings identified by a regex.
Question 149 A sales leaderboard must give tied regions the same position without skipping the next rank. Which solution is most directly applicable? Use DENSE_RANK() for tied rankings without numeric gaps 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 A:This choice assigns equal ranks for ties and consecutive distinct ranks. Incorrect Answers B:This alternative 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:This option models competition-style positions with ties. This does not meet the need for a method that assigns equal ranks for ties and consecutive distinct ranks. D: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.
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.