This question's explanation and your answers so far.
関係データベースで管理している“商品”表及び“売上”表を結合して商品の売上集計を行う。5月の売上合計金額が最も大きい商品はどれか。 商品
| 商品コード | 商品名 | 価格 |
|---|---|---|
| 0001 | 商品A | 2,000 |
| 0002 | 商品B | 4,000 |
| 0003 | 商品C | 7,000 |
| 0004 | 商品D | 10,000 |
売上
| 売上番号 | 商品コード | 数量 | 売上日 | 配達日 |
|---|---|---|---|---|
| Z00001 | 0004 | 2 | 4/30 | 5/2 |
| Z00002 | 0001 | 1 | 5/1 | 5/3 |
| Z00003 | 0003 | 2 | 5/15 | 5/17 |
| Z00004 | 0001 | 3 | 5/15 | 5/18 |
| Z00005 | 0002 | 3 | 5/5 | 5/18 |
| Z00006 | 0001 | 2 | 5/10 | 5/20 |
| Z00007 | 0002 | 1 | 5/30 | 6/2 |
| Z00008 | 0001 | 2 | 6/1 | 6/9 |
| Z00009 | 0003 | 1 | 6/8 | 6/10 |
Product A has the most sales records in May, three of them, but its price is the lowest at 2,000 yen, so its total stops at 2,000×6 units=12,000 yen and does not reach Product B's 16,000 yen. A large number of sales records or a large quantity is a different thing from a large total amount; you must always convert to an amount with price × quantity before comparing.
正解 The correct answer is イ (Product B). First, join the “Sales” table to the “Product” table using the product code as the key; the amount of each sales row can then be found as price × quantity. Next, what the question asks about is “sales in May”, so take only the rows whose sales date falls in May (note that it is not the delivery date). Totaling by product: Product A has 1 unit in Z00002, 2 units in Z00006 and 3 units in Z00004, for 6 units in all, so 2,000×6=12,000 yen. Product B has 3 units in Z00005 (5/5) and 1 unit in Z00007 (5/30), for 4 units in all, so 4,000×4=16,000 yen. Product C has only the 2 units in Z00003 (5/15), so 7,000×2=14,000 yen. Product D has no sales rows in May, so 0 yen. Therefore the largest total amount is Product B with 16,000 yen.
Product C's sales in May are only the 2 units of Z00003 (sales date 5/15), that is 7,000×2=14,000 yen, which is smaller than Product B's 16,000 yen. The other record, Z00009, has a sales date of 6/8, so it is a June sale and must not be included in the May aggregation.
Product D has the highest price at 10,000 yen, but the sales date of the corresponding record Z00001 is 4/30, which makes it an April sale, so its May total is 0 yen. It is a mistake to be pulled along by the delivery date of 5/2 and count 10,000×2=20,000 yen. The date that decides what is aggregated is the date of the “sale” specified by the question, namely the sales date.