問60
頻出度★★☆
関係データベースで管理している「販売明細」表と「商品」表がある。ノートの売上数量の合計は幾らか。 販売明細
| 伝票番号 | 商品コード | 売上数量 |
|---|---|---|
| H001 | S001 | 20 |
| H001 | S003 | 40 |
| H002 | S002 | 60 |
| H002 | S003 | 80 |
商品
| 商品コード | 商品名 |
|---|---|
| S001 | 鉛筆 |
| S002 | 消しゴム |
| S003 | ノート |
平成29年度 秋期 ITパスポート試験 問60
テクノロジ系データベース › データ操作
This question's explanation and your answers so far.
解説
問 60
Incorrectア
“40” counts only the single row for slip H001 among the S003 rows (sales quantity 40) and misses the S003 row on slip H002 (80). Since notebooks were sold across multiple slips, all S003 rows must be summed together.
Incorrectイ
“80” counts only the single row for slip H002 among the S003 rows (sales quantity 80) and misses the S003 row on slip H001 (40). This too is the mistake of looking at only one of the matching S003 rows.
- ウ
正解 First, from the “Product” table, confirm that the product code for a notebook is S003. Next, in the “Sales Detail” table, extract only the rows whose product code is S003; two rows match — sales quantity 40 (slip H001) and 80 (slip H002). The total sales quantity of notebooks is these two rows added together, 40 + 80 = 120, so the correct answer is ウ, “120”. The key point is that the slip number is irrelevant to the aggregation; you narrow down the target rows by product code.
Incorrectエ
“200” sums all sales quantities in the “Sales Detail” table without distinguishing products (20 + 40 + 60 + 80 = 200). The question asks only for the total of notebooks (S003), so the quantities of pencils (S001) and erasers (S002) must not be included.
ここがポイント
- A basic relational-database operation: link the two tables by product code (join), identify the matching product code from the target product name, then filter out only those rows by condition (selection).
- For aggregation, select the target rows by “Product Code” and sum up all sales quantities that share the same product code. Note that as long as it is the same product, rows are included in the total even if their slip numbers differ.
用語