Oracle Database Sql Certified Associate · Free Practice Question Hard
Question 10
View and examine the following available responses.
Examine this statement.
- MERGE INTO products p
- USING promotions pr
- ON (p.promo_id = pr.promo_id)
- WHEN MATCHED THEN
- UPDATE SET p.price = p.price * 0.9
- WHEN NOT MATCHED THEN
- INSERT (product_id, promo_id, price)
- VALUES (pr.promo_product_id, pr.promo_id, pr.discount_price);
Which of the following is the outcome of the above MERGE statement?
-
A
Updates unmatched promotions and inserts matched ones
-
B
Requires a DELETE clause for unmatched rows
-
C
Updates products that match, inserts unmatched promotions
-
D
Results in an error if promo_id is null
Reveal correct answer
Correct answer: C
Explanation
Correct Answer: Updates products that match, inserts unmatched promotions
Break down the MERGE statement and evaluate each option.
Given SQL:
- MERGE INTO products p
- USING promotions pr
- ON (p.promo_id = pr.promo_id)
- WHEN MATCHED THEN
- UPDATE SET p.price = p.price * 0.9
- WHEN NOT MATCHED THEN
- INSERT (product_id, promo_id, price)
- VALUES (pr.promo_product_id, pr.promo_id, pr.discount_price);
What this MERGE does:
MERGE INTO → target table is
products.USING → source is
promotions.ON → condition: match on
promo_id.
Behaviour:
If a row in
promotionsmatches a row inproducts, it updates the product price (10% discount).If there's no match, it inserts a new row into
productsusing values frompromotions.
Option Analysis:
1. "Updates products that match, inserts unmatched promotions" — Correct
This is exactly what the MERGE does.
2. "Updates unmatched promotions and inserts matched ones" — Incorrect
It's the opposite of what MERGE does.
Updates occur on matches, inserts on non-matches.
3. "Results in an error if promo_id is null" — Incorrect
NULLvalues in the join condition (ON) don't cause an error.However, nulls won't match, so they'll go into the NOT MATCHED path (insertion).
An error only occurs if constraints (e.g., NOT NULL) are violated — not from the join itself.
4. "Requires a DELETE clause for unmatched rows" — Incorrect
A
DELETEclause is optional in aMERGEand not required.
A. This statement does not update unmatched promotions or insert matched ones. The UPDATE SET clause only applies to matched rows, where the price of the product is updated. The INSERT clause is executed only for rows in the promotions table that do not have a corresponding promo_id in the products table.
B. The MERGE statement does not require a DELETE clause for unmatched rows in this scenario. The WHEN NOT MATCHED THEN INSERT clause handles the insertion of new records from the promotions table into the products table when there is no match based on the promo_id column. The DELETE operation is not needed for this specific statement.
C. The MERGE statement is used to perform a combination of INSERT, UPDATE, and DELETE operations based on a condition. In this specific statement, when a match is found between the products and promotions tables, the price of the product is updated by reducing it by 10%. If no match is found, a new record is inserted into the products table with the product_id, promo_id, and discount_price from the promotions table.
D. The statement will not result in an error if promo_id is null. The ON clause specifies the condition for matching rows between the products and promotions tables based on the promo_id column. If a promo_id is null in either table, it will not match with any rows in the other table, and the appropriate action (UPDATE or INSERT) will be taken accordingly.
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
