View the Exhibit and examine the structure of the PROMOTIONS table. Evaluate the following SQL statement: Which statement is true regarding the outcome of the above query?

Question

Exhibits

Options

• AIt produces an error because subqueries cannot be used with the CASE expression.
• BIt shows COST_REMARK for all the promos in the promo category `TV'.
• CIt shows COST_REMARK for all the promos in the table.
• DIt produces an error because the subquery gives an error.

• A
  17% (3)
• B
  6% (1)
• C
  72% (13)
• D
  6% (1)

Explanation

Option C is correct because the SQL query contains no WHERE clause on the outer SELECT statement, meaning every row in the PROMOTIONS table is returned and receives a COST_REMARK value - the subquery inside the CASE expression only affects the comparison value, not which rows are included in the result.

Option B is the primary distractor: the subquery likely references the 'TV' category (e.g., SELECT AVG(promo_cost) FROM promotions WHERE promo_category = 'TV'), which tricks you into thinking only TV promos are shown - but a subquery inside CASE influences the logic, not the filtering of the outer query.

Option A is wrong because subqueries are perfectly valid inside CASE expressions in Oracle SQL, as long as they return a single value (scalar subquery).

Option D is wrong because a correlated or scalar subquery filtering by promo category is syntactically and logically valid - it returns one value and causes no error.

Memory tip: Always distinguish between where data comes from (the outer FROM/WHERE) and how data is labeled (the CASE expression). A subquery inside CASE is just a value lookup - it never filters the outer result set.

No community discussion yet for this question.