Oracle Database MySQL 5.6 Developer — Question 2

A table (t1) contains 1000 random integer values in the first column (col1). The random values range from 1 to 1000.
You execute this query:
SELECT col1 FROM t1 WHERE col1< 100

UNION -
SELECT col1 FROM t1 WHERE col1 BETWEEN 100 and 200

UNION ALL -
SELECT col1 FROM t1 WHERE col 1 >=900
What is the output?

Answer options

• A. A list of unique values within the ranges of 1-200 and 900-1000
• B. A list of unique values within the range of 1-200 and a list of all values, including duplicates, on the table within the range of 900-1000
• C. A list of all values , including duplicates, in the range of 1-200 and a list of unique values in the range of 900-1000
• D. A list of all values, including duplicates, in the ranges of 1-200 and 900-1000
• E. An error, because mixing UNION and UNION ALL in the same query is not permitted

Correct answer: C

Explanation

The correct answer is C because the first two parts of the query use UNION, which eliminates duplicates, resulting in unique values in the range of 1-200. Meanwhile, the third part uses UNION ALL, which includes all values (including duplicates) in the range of 900-1000. The other options incorrectly describe the output by misrepresenting the uniqueness or inclusion of duplicates.