基本情報技術者 2015年 秋期 午後 問03
電子部品の出荷データを管理する関係データベースの運用に関する次の記述を読んで、設問1～4に答えよ。
C社は、電子部品を製造販売する会社である。
ある期間に出荷した特定の電子部品について、製造装置の設定ミスによる不具合が発生しているおそれがあるので、顧客への連絡と出荷済みの電子部品の無償交換（回収及び再出荷。以下、リコールという）を実施することにした。
出荷情報は、図1に示す表で管理されている。下線付きの項目は主キーを表す。
設問1：
問題文を見る リコールの対象となる電子部品の出荷先の顧客番号、顧客名、出荷番号、出荷日、出荷数を、顧客番号の昇順に表示する。リコールの対象となる電子部品の部品番号は"007551"で、出荷日は2015年1月10日から2015年1月20日までである。次のSQL文のaに入れる正しい答えを、解答群の中から選べ。
SELECT 顧客表.顧客番号, 顧客表.顧客名, 出荷表.出荷番号, 出荷表.出荷日, 出荷表.出荷数
FROM 顧客表, 出荷表
WHERE 出荷表.顧客番号 = 顧客表.顧客番号 AND 出荷表.部品番号 = '007551' AND a
ORDER BY 顧客表.顧客番号
解答群
ア：出荷表.出荷日 = '20150110' OR 出荷表.出荷日 = '20150120'
イ：出荷表.出荷日 = ANY ('20150110', '20150120')
ウ：出荷表.出荷日 BETWEEN '20150110' AND '20150120'
エ：出荷表.出荷日 IN ('20150110', '20150120')
模範解答
a：ウ
解説
解答の導き方
まず、SQL（Structured Query Language：関係データベースを操作するための言語）の条件で、どの出荷データを取り出すのかを確認します。
本文では、対象の部品番号は「007551」、出荷日は「2015年1月10日から2015年1月20日まで」と指定されています。すでにWHERE句には、部品番号を「007551」に絞る条件があります。空欄には、出荷日をこの期間に絞る条件を入れます。
「2015年1月10日から2015年1月20日まで」は、開始日と終了日の間にある日付をすべて含む指定です。このような範囲を表すときに使うのがBETWEENです。
出荷表.出荷日 BETWEEN '20150110' AND '20150120'
BETWEENは、開始日と終了日も範囲に含みます。そのため、20150110や20150120に出荷したデータも対象になります。よって、空欄にはウが入ります。
ほかの選択肢が適さない理由も確認します。
- アは、出荷日が20150110または20150120と等しいデータだけを選びます。間の日付は対象になりません。
- イのANYは、通常、比較対象が副問合せの結果のいずれかに当てはまるかを調べる構文です。提示されたように値を直接並べて期間を指定する書き方ではありません。また、両端の日付だけを示しても、間の日付を含む範囲にはなりません。
- エのINも、指定した値のいずれかに一致するデータを選びます。両端の日付だけが対象となり、間の日付は対象になりません。
ここではWHERE句の既存条件と空欄の条件がANDで結ばれています。ANDは、結ばれた条件をすべて満たすデータだけを選ぶ指定です。したがって、部品番号が「007551」であり、かつ出荷日が指定期間内の出荷データが抽出されます。ORDER BYは抽出後の並び順を指定しており、このSQLでは顧客番号の昇順に並べます。
誤りやすいポイント
- 「2015年1月10日から2015年1月20日まで」を、両端の日付だけと読み違えないようにします。期間内のすべての日付が対象です。
- BETWEENは開始日と終了日を含みます。境界の日付を含めるかどうかも確認しましょう。
- INやORで両端の日付を指定しても、日付の範囲にはなりません。
- ORDER BYは表示順を決める条件です。どのデータを抽出するかはWHERE句で決まります。
FAQ
Q: BETWEENで指定した開始日と終了日は、対象に含まれますか？
A: はい。BETWEENの範囲には、開始日と終了日の両方が含まれます。
A: はい。BETWEENの範囲には、開始日と終了日の両方が含まれます。
Q: INで開始日と終了日を指定すれば、間の日付も選べますか？
A: いいえ。INは、指定した値のどれかと一致するデータを選びます。範囲内の値をすべて選ぶ指定ではありません。
A: いいえ。INは、指定した値のどれかと一致するデータを選びます。範囲内の値をすべて選ぶ指定ではありません。
Q: ANDはこのSQLでどのような働きをしていますか？
A: 顧客番号の一致、部品番号の一致、出荷日の範囲内という条件をすべて満たすデータに絞り込みます。
A: 顧客番号の一致、部品番号の一致、出荷日の範囲内という条件をすべて満たすデータに絞り込みます。
関連キーワード: SQL、WHERE句、BETWEEN、IN、ORDER BY
設問2：
問題文を見る C社では、電子部品を単品で出荷するだけでなく、複数の電子部品を同梱したパッケージも出荷している。このパッケージにも一意の部品番号が割り当てられている。パッケージの同梱部品の情報は、図2に示すパッケージ表で管理されている。
リコールの対象となる電子部品がパッケージにも含まれていることが判明したので、該当するパッケージの出荷情報も含めて表示するよう設問1のSQL文を変更する。次のSQL文のbに入れる正しい答えを、解答群の中から選べ。ここで、aには設問1の正しい答えが入っているものとする。また、パッケージの同梱部品にパッケージが含まれることはない。
SELECT 顧客表.顧客番号, 顧客表.顧客名, 出荷表.出荷番号, 出荷表.出荷日, 出荷表.出荷数
FROM 顧客表, 出荷表
WHERE 出荷表.顧客番号 = 顧客表.顧客番号 AND b AND a
ORDER BY 顧客表.顧客番号
解答群
ア：(出荷表.部品番号 = '007551' AND 出荷表.部品番号 = ANY (SELECT パッケージ表.同梱部品 FROM パッケージ表))
イ：(出荷表.部品番号 = '007551' AND 出荷表.部品番号 = ANY (SELECT パッケージ表.部品番号 FROM パッケージ表))
ウ：(出荷表.部品番号 = '007551' OR 出荷表.部品番号 = ANY (SELECT パッケージ表.同梱部品 FROM パッケージ表 WHERE パッケージ表.部品番号 = '007551'))
エ：(出荷表.部品番号 = '007551' OR 出荷表.部品番号 = ANY (SELECT パッケージ表.部品番号 FROM パッケージ表 WHERE パッケージ表.同梱部品 = '007551'))
模範解答
b：エ
解説
解答の導き方
求めるのは、リコール対象の部品そのものを出荷した情報だけでなく、その部品を含むパッケージを出荷した情報も表示するための条件です。
まず、図2のパッケージ表では、パッケージの部品番号を「部品番号」、パッケージに入っている部品を「同梱部品」で表しています。たとえば、部品番号が「009220」のパッケージには、同梱部品として「007551」が入っています。
したがって、出荷情報に表示すべきものは次の2種類です。
- 部品番号「007551」そのものを出荷した情報
- 「007551」を同梱部品として含むパッケージを出荷した情報
パッケージの出荷情報を探すには、まずパッケージ表から「同梱部品」が「007551」である行を探します。その行の「部品番号」が、該当するパッケージの番号です。図2の例では「009220」です。
そのうえで、出荷表の部品番号が「007551」そのもの、または見つかったパッケージの部品番号に一致するかを調べます。条件は、部品そのものとパッケージのどちらかに当てはまればよいので、両方をつなぐのは「OR」です。
選択肢エは、次のようにこの条件を表しています。
出荷表.部品番号 = '007551'
OR
出荷表.部品番号 = ANY (
SELECT パッケージ表.部品番号
FROM パッケージ表
WHERE パッケージ表.同梱部品 = '007551'
)
副問合せ（SQL文の中に含めて、先に値を取り出すためのSQL文）では、「同梱部品」が「007551」である行の「部品番号」を取り出しています。ANYは、取り出した値のいずれかと一致すればよい、という意味です。つまり、出荷表の部品番号が、対象部品「007551」か、その部品を含むパッケージの番号のいずれかなら条件に合います。
一方、選択肢アとイは、部品そのものの出荷とパッケージの出荷を「AND」でつないでいます。両方を同時に満たす必要があるため、「どちらかを出荷した情報」を表示する条件になりません。
選択肢ウは、「部品番号」が「007551」であるパッケージの「同梱部品」を調べています。これは「007551を含むパッケージ」を探すのとは逆向きです。必要なのは、「同梱部品」が「007551」である行から、そのパッケージの「部品番号」を取り出すことです。
誤りやすいポイント
- 部品そのものとパッケージのどちらかを対象にするため、条件を「AND」ではなく「OR」でつなぎます。
- 「部品番号」と「同梱部品」の役割を逆にしないようにします。パッケージを特定する番号は「部品番号」、パッケージの中身は「同梱部品」です。
- 「007551を含むパッケージ」を探すときは、パッケージ表の「同梱部品」が「007551」の行を探し、その行の「部品番号」を取り出します。
- ANYは、取り出した値のどれかと一致するかを調べるものです。副問合せでどの列を取り出しているかも確認してください。
FAQ
Q: 図2の「009220」は、なぜ出荷表の検索対象になるのですか？
A: 図2では、「009220」のパッケージに「007551」が同梱されています。そのため、「009220」が出荷表にあれば、リコール対象の部品を含むパッケージを出荷したことになります。
A: 図2では、「009220」のパッケージに「007551」が同梱されています。そのため、「009220」が出荷表にあれば、リコール対象の部品を含むパッケージを出荷したことになります。
Q: 「同梱部品 = '007551'」だけで出荷表を検索できないのですか？
A: 出荷表にはパッケージの中身ではなく、出荷した品目の部品番号が記録されています。そこで、まずパッケージ表から「007551」を含むパッケージの部品番号を調べ、その番号で出荷表を検索します。
A: 出荷表にはパッケージの中身ではなく、出荷した品目の部品番号が記録されています。そこで、まずパッケージ表から「007551」を含むパッケージの部品番号を調べ、その番号で出荷表を検索します。
Q: ANYの代わりに、どのように考えればよいですか？
A: 副問合せで取り出した部品番号の「いずれかと一致する」と考えればよいです。この問題では、出荷表の部品番号が、対象部品または該当するパッケージの番号かを調べています。
A: 副問合せで取り出した部品番号の「いずれかと一致する」と考えればよいです。この問題では、出荷表の部品番号が、対象部品または該当するパッケージの番号かを調べています。
関連キーワード: 関係データベース、SQL、副問合せ、ANY、論理演算子、主キー
設問3：
問題文を見る 今回のリコールの対象となる電子部品の出荷金額の合計を表示する。
次のSQL文のcに入れる正しい答えを、解答群の中から選べ。ここで、aとbには設問1及び設問2の正しい答えが入っているものとする。
SELECT c AS 合計出荷金額
FROM 出荷表
WHERE b AND a
解答群
ア：AVG(出荷表.出荷金額)
イ：COUNT(出荷表.出荷金額)
ウ：MAX(出荷表.出荷金額)
エ：SUM(出荷表.出荷金額)
模範解答
c：エ
解説
解答の導き方
この設問では、リコール対象となる出荷について「出荷金額の合計」を求めます。SQL（Structured Query Language：関係データベースのデータを検索・操作するための言語）では、複数の行の値を一つにまとめる処理に、集約関数（複数行の値を一つの値にまとめる関数）を使います。
まず、FROM 出荷表によって、集計するデータの表が出荷表だと分かります。続くWHERE b AND aは、bとaの両方の条件を満たす出荷だけに絞り込む指定です。つまり、リコール対象に関係する出荷の行が選ばれます。
その行に記録された「出荷金額」をすべて足し合わせるには、SUMを使います。したがって、cにはSUM(出荷表.出荷金額)が入ります。AS 合計出荷金額は、集計結果の列に「合計出荷金額」という名前を付ける指定です。よって、選択肢はエです。
ほかの選択肢は、求めるものが異なります。AVGは平均、COUNTは件数、MAXは最大値を求めます。「合計」を求めるこの設問では、SUMが適切です。
誤りやすいポイント
- COUNTは金額を足す関数ではなく、対象となる値の件数を数える関数です。
- AVGは金額の平均を求めます。合計金額とは異なります。
- MAXは最も大きい出荷金額だけを求めるため、全出荷分の合計にはなりません。
- 集計する列は「出荷数」ではなく「出荷金額」です。何を合計するかは、設問の「出荷金額の合計」という表現から判断します。
- WHERE句は集計対象の行を絞り込む指定です。条件を満たす行だけを対象にして、その後にSUMで合計します。
FAQ
Q: b AND aは、どちらか一方の条件に合えばよいという意味ですか？
A: いいえ。ANDは、両方の条件を満たすことを表します。
A: いいえ。ANDは、両方の条件を満たすことを表します。
Q: SUMは、出荷表にある全ての出荷金額を足すのですか？
A: いいえ。WHERE句の条件に合った行だけが対象です。その行の出荷金額を足し合わせます。
A: いいえ。WHERE句の条件に合った行だけが対象です。その行の出荷金額を足し合わせます。
Q: AS 合計出荷金額は、合計の計算方法を指定していますか？
A: いいえ。集計結果を表示するときの列名を指定しています。合計の計算自体はSUMが行います。
A: いいえ。集計結果を表示するときの列名を指定しています。合計の計算自体はSUMが行います。
関連キーワード: SQL、集約関数、SUM関数、WHERE句、列別名
設問4：
問題文を見る 回収の対象となった出荷の情報は残したまま、再出荷に関する情報を管理することができるように、表の構成を変更する。次の記述中のに入れる適切な答えを、解答群の中から選べ。
図1及び図2の表に回収及び再出荷の情報を追加する場合、dに回収日と再出荷番号の項目を追加し、初期値にはNULLを設定しておき、回収対象の場合には回収した日と再出荷時の出荷番号を設定すればよい。ただし、この方法では既存データへの影響が大きく、また、リコールの頻度が低い場合は効率が悪い。
そこで、既存データに影響を与えない方法として、新たに回収表を作成して、一意に割り振った回収番号、回収対象となった出荷の出荷番号、回収日、再出荷時の出荷番号を格納する方法を考えた。この方法では、例えば、ある月の出荷金額の合計を求めるとき、回収対象となった出荷の出荷金額を除いて求めたい場合は、eから集計できる。
dに関する解答群
ア：顧客表
イ：出荷表
ウ：パッケージ表
エ：部品表
eに関する解答群
ア：出荷表と回収表
イ：パッケージ表と回収表
ウ：部品表と回収表
エ：部品表と出荷表とパッケージ表
オ：部品表とパッケージ表と回収表
模範解答
d：イ、e：ア
解説
解答の導き方
まずdは、回収日と再出荷番号を、どの情報に結び付けて記録するかを考えます。
回収の対象になるのは、顧客そのものでも部品そのものでもなく、すでに行われた「出荷」です。顧客表には顧客の情報、部品表には部品の情報、パッケージ表には部品の同梱情報があります。一方、出荷表には出荷番号や出荷日、出荷金額など、出荷ごとの情報があります。
そのため、既存の表に回収日と再出荷番号を追加するなら、出荷ごとの情報を記録している出荷表が適切です。dには「イ：出荷表」が入ります。
ただし、この方法では、回収対象ではない出荷の行にも回収日や再出荷番号の欄ができます。問題文にあるように、初期値をNULLにする必要があります。NULLは、値が入っていないことを表すもので、数値の0とは異なります。リコールが少ない場合、多くの行でこれらの欄が空のままになり、既存の表にも変更を加えることになります。
そこで、回収の記録だけを別に管理する回収表を作ります。回収番号で回収の記録を識別し、回収対象となった出荷の出荷番号で元の出荷と結び付けます。回収日や再出荷時の出荷番号も、この回収表に記録できます。こうすれば、既存の出荷表の構成を変更せずに済みます。
次にeは、回収対象となった出荷の出荷金額を除いて集計するために、どの表が必要かを考えます。出荷金額は出荷表に記録されています。したがって、金額を合計する元の情報は出荷表から取ります。
一方、どの出荷が回収対象なのかは、回収表を見なければ分かりません。出荷表の出荷番号と、回収表に記録された「回収対象となった出荷の出荷番号」を照らし合わせ、該当する出荷を集計から除きます。このため、必要なのは「出荷表と回収表」です。eには「ア：出荷表と回収表」が入ります。
誤りやすいポイント
- 回収日や再出荷番号を顧客表に追加すると考えることです。リコールの記録は顧客単位ではなく、どの出荷が対象になったかに結び付く情報です。
- 出荷金額を部品表から求められると考えることです。部品表には部品の単価がありますが、出荷ごとの金額は出荷表に記録されています。
- 回収表だけで出荷金額を集計できると考えることです。回収表は回収対象を特定するために使い、金額の情報は出荷表から取得します。
- 回収表に記録された再出荷時の出荷番号を、回収対象となった元の出荷番号と混同することです。集計から除く対象を見つけるには、回収表にある「回収対象となった出荷の出荷番号」を使います。
FAQ
Q: NULLは、値が0という意味ですか？
A: いいえ。NULLは値が入っていないことを表します。出荷金額が0円であることとは異なります。
A: いいえ。NULLは値が入っていないことを表します。出荷金額が0円であることとは異なります。
Q: なぜ回収表を別に作ると、既存データへの影響が小さくなるのですか？
A: 出荷表に新しい項目を追加せず、回収が発生したときだけ回収表に記録を追加できるからです。
A: 出荷表に新しい項目を追加せず、回収が発生したときだけ回収表に記録を追加できるからです。
Q: 出荷表だけで、回収対象の出荷を集計から除けないのですか？
A: 出荷表だけでは、どの出荷が回収対象になったかを判断できません。回収表にある出荷番号と照らし合わせる必要があります。
A: 出荷表だけでは、どの出荷が回収対象になったかを判断できません。回収表にある出荷番号と照らし合わせる必要があります。
関連キーワード: 関係データベース、主キー、NULL、表の結合、集計
