IPA FE Subject A 2026 Question 7

Topic: Databases

A question IPA Japan published in 令和８年度 基本情報技術者試験 科目A 公開問題 問7, translated into English. ITPEC is based on the same exams.

For the “Product” table that is defined by the SQL statement below and has values stored in it, which of the following SQL statements results in an execution error due to a constraint violation?

[SQL statement]
CREATE TABLE Product
(ProductCode CHAR(4) PRIMARY KEY, ProductName VARCHAR(21),
SupplierCode CHAR(4), PurchaseUnitPrice INT, StockQuantity INT)

Product

ProductCodeProductNameSupplierCodePurchaseUnitPriceStockQuantity
A111TelevisionS00175,0000
A222Digital cameraS00250,00050
A333DVD playerNULLNULLNULL
A444Washing machineS00445,00020

Explanation

The UPDATE that sets ProductCode to A777 where StockQuantity >= 20 — it would give two rows the same primary key.

ProductCode is the PRIMARY KEY, so its values must be unique and not NULL. The other columns have no constraint, so NULL is allowed in them.

In (d), StockQuantity >= 20 is true for A222 (50) and A444 (20); A111 (0) is false and A333 (NULL) is unknown, so those two are not updated. Both matching rows would get the ProductCode A777, a duplicate primary key, so the statement fails with a constraint violation.

Answer (d)

Why not others:

  • •(a) — deletes row A333, whose SupplierCode is NULL; deleting a row cannot break the primary key, and no other table refers to Product
  • •(b) — inserts a row with the new key A555, and every value fits its column (12 characters for VARCHAR(21))
  • •(c) — changes A444 to A666, a key not used yet, in one row only, so the key stays unique

Key rule: An UPDATE that sets a primary key to a constant fails as soon as its WHERE clause matches more than one row; count the matching rows, remembering that a comparison with NULL is never true.

Databases · AI-written, may contain errors

Practice this exam

This question comes from the past questions IPA Japan publishes, translated into English by ITPEC Practice. ITPEC Practice is an independent study tool and is not affiliated with IPA or ITPEC. See the original paper (PDF) or Report an issue.