Using DISTINCT and ALL: Assess the limitation.\n“DISTINCT and its counterpart ALL can be used more than once in a single SELECT statement.”

Introduction / Context:
DISTINCT removes duplicate rows from a SELECT result, while ALL (the default) retains all rows. Understanding where and how often DISTINCT/ALL can appear prevents syntax errors and unintended results. This question tests whether you can apply DISTINCT multiple times in one SELECT clause.

Given Data / Assumptions:

• ANSI SQL behavior is assumed.
• DISTINCT or ALL applies to the entire SELECT list at the query block level.
• We are not discussing set operators like UNION, INTERSECT, EXCEPT which have their own DISTINCT/ALL options.

Concept / Approach:
Within a single SELECT query block, DISTINCT (or ALL) can be specified at most once and it applies to the whole projection list taken together. You cannot use DISTINCT per column or repeat it multiple times. You may, however, use DISTINCT in separate query blocks (for example, subqueries) or specify DISTINCT/ALL for set operators such as UNION DISTINCT or UNION ALL independently of the inner SELECT’s DISTINCT.

Step-by-Step Solution:

Verification / Alternative check:
Vendor references show DISTINCT as a single keyword per query block; repeating it triggers a parse error.

Why Other Options Are Wrong:

• “Correct” conflicts with the grammar.
• “Correct only with UNION” is misleading: UNION itself can be ALL or DISTINCT, but not multiple times per block.
• “Per column” support does not exist in standard SQL.
• Optimizer behavior does not change syntax rules.

Common Pitfalls:
Misusing DISTINCT to mask joins that duplicate rows; placing DISTINCT where GROUP BY is more appropriate.

Final Answer:
Incorrect