This question's explanation and your answers so far.
次の“商品”表から，仕入先が“A社”で価格が140円以上の商品，及び仕入先が“B社”で価格が170円以下の商品を抽出した。得られた商品名だけを全て挙げたものはどれか。 商品
| 商品コード | 商品名 | 価格（円） | 仕入先 |
|---|---|---|---|
| S001 | チョコレート | 150 | A社 |
| S002 | ケーキ | 260 | B社 |
| S003 | カステラ | 290 | C社 |
| S004 | リンゴ | 190 | A社 |
| S005 | イチゴ | 220 | B社 |
| S006 | メロン | 280 | C社 |
| S007 | ジュース | 120 | A社 |
| S008 | コーヒー | 160 | B社 |
| S009 | ビール | 220 | C社 |
“Cake, Strawberry, Coffee” lists all three of Company B's products. Cake is 260 yen and Strawberry is 220 yen, so neither satisfies the “170 yen or less” of condition 2 and neither is extracted. The only Company B product that meets the condition is Coffee at 160 yen. Furthermore, Chocolate and Apple from Company A, which condition 1 extracts, are missing. This is an easy mistake to make if you look only at the supplier and do not check the price inequality.
“Cake, Strawberry, Juice” is the option produced by reversing the direction of the price in the two conditions. Cake at 260 yen and Strawberry at 220 yen are from Company B, but they are not 170 yen or less; Juice at 120 yen is from Company A, but it is not 140 yen or more. In other words, all three are rows that fail the conditions, which is the result of misreading the conditions as “under 140 yen for Company A, over 170 yen for Company B”. Note that in the extraction conditions the Company A side is 『or more』 and the Company B side is 『or less』.
正解 The correct answer is ウ “Chocolate, Apple, Coffee”. This question has you carry out by hand the relational database operations of “selection (narrowing down rows)” and “projection (taking out columns)”. The key point is that there are two extraction conditions, joined by OR (a row is extracted if it satisfies either one). First, apply condition 1, “the supplier is Company A and the price is 140 yen or more”, to the Products table: Company A has three products, S001 Chocolate (150 yen), S004 Apple (190 yen), and S007 Juice (120 yen). Of these, Chocolate (150 ≧ 140) and Apple (190 ≧ 140) are 140 yen or more, while Juice is 120 yen and so does not meet the condition. Next, apply condition 2, “the supplier is Company B and the price is 170 yen or less”: Company B has three products, S002 Cake (260 yen), S005 Strawberry (220 yen), and S008 Coffee (160 yen). Of these, only Coffee (160 ≦ 170) is 170 yen or less; Cake and Strawberry are both far above 170 yen, so they are excluded. The Company C products (Castella, Melon, Beer) match the supplier in neither condition, so they are out of scope from the start. Combining the rows selected by the two conditions gives the three rows S001, S004, and S008, and projecting only the product name column from them yields Chocolate, Apple, Coffee. The option that lists exactly these and nothing more is ウ.
“Chocolate, Apple, Juice” simply lists Company A's three products as they are. Chocolate at 150 yen and Apple at 190 yen do satisfy condition 1, but Juice is 120 yen, which is not 140 yen or more, so it is not extracted. Coffee (160 yen) from Company B, which condition 2 extracts, is also missing. This is the error that arises when you divide the table by the single condition that the supplier is Company A, overlooking the lower price limit and the other condition (Company B and 170 yen or less).