Question 230
Issue 1: A report needs distinct employee IDs from two sources, but the developer uses UNION ALL and counts duplicates as separate people. Issue 2: A product search assumes ORDER BY inside a subquery guarantees the final result order without an outer ORDER BY. Issue 3: A report compares timestamp values across regions but mixes UTC storage with local-wall-time strings before filtering by day. Which complete response addresses the three issues in order?
Explanation
Correct Answer
For issue 1, uNION removes duplicate result rows whereas UNION ALL preserves them. For issue 2, relational result order is not guaranteed without ordering at the final query boundary. For issue 3, time-zone ambiguity can shift records across reporting boundaries even when timestamps look similar.
Incorrect Answers
Choose UNION or explicit deduplication for the requested unique population after checking key equivalence; Preaggregate the relevant child data or use correct join keys and cardinality checks before computing totals; and Normalize timestamps to a common time basis and apply explicit business time-zone conversion for the report: relational result order is not guaranteed without ordering at the final query boundary.
Preaggregate the relevant child data or use correct join keys and cardinality checks before computing totals; apply ORDER BY to the final result query with a stable tie-breaker when output ordering matters; and Normalize timestamps to a common time basis and apply explicit business time-zone conversion for the report: uNION removes duplicate result rows whereas UNION ALL preserves them.
Choose UNION or explicit deduplication for the requested unique population after checking key equivalence; apply ORDER BY to the final result query with a stable tie-breaker when output ordering matters; and Preaggregate the relevant child data or use correct join keys and cardinality checks before computing totals: time-zone ambiguity can shift records across reporting boundaries even when timestamps look similar.



Developing AI-Enabled Database Solutions (DP-800) Practice Tests