基本情報技術者過去問題 平成28年秋期 午後問3

問題文解答用紙を別タブで開けます(印刷可能)。

問3 データベース

従業員の通勤情報を管理する関係データベースに関する次の記述を読んで,設問1〜4に答えよ。

 Y社では,従業員の1か月分の交通費の合計を通勤手当として支給している。交通費は,通勤に公共の交通機関を利用している場合は通勤経路の各区間運賃であり,自家用車を利用している場合は燃料費などの諸経費である。
 通勤手当は図1に示す表で管理していたが,より詳細に情報を管理するために,図2のとおり変更した。下線付きの項目は,主キーを表す。

設問1

変更後の表を用いて通勤手当に関するデータを集計する。次の記述中の に入れる正しい答えを,解答群の中から選べ。

 従業員ごとの通勤手当を求めるには,aグループ化して,集合関数bを用いればよい。また,交通機関ごとの利用者数を求めるには,cグループ化して,集合関数dを用いればよい。
a,c に関する解答群
  • 交通機関表を交通機関コードで
  • 従業員表を従業員番号で
  • 通勤費表を交通機関コードで
  • 通勤費表を従業員番号で
b,d に関する解答群
  • AVG
  • COUNT
  • MAX
  • SUM

解答選択欄

  • a:
  • b:
  • c:
  • d:

解答

  • a=
  • b=
  • c=
  • d=

解説

変更前の表では従業員ごとに支給する通勤手当額が格納されているだけでしたが、変更後はより詳細になり、通勤費表には従業員と交通機関ごとにその通勤費が格納されてるようになっています。図2でも例が示されているように、1人の従業員が「電車とバス」または「電車と自家用車」などのように複数の交通機関を通勤に使用している場合、通勤費表には1人の従業員に対する複数のレコードが存在することになります。

abについて〕
変更後の表では各交通費は通勤費表に格納されています。したがって通勤費表を従業員番号ごとにグループ化し、グループ(従業員番号)ごとに交通費の値を合計することになります。グループごとの合計は集計関数のSUM()で計算するので、従業員ごとの通勤手当を得るには、通勤費表を従業員番号でグループ化し、SUMで交通費の値を合計する方法が適切です。

a=通勤費表を従業員番号で
 b=SUM
SELECT 従業員番号,SUM(交通費) FROM 通勤費表
 GROUP BY 従業員番号
pm03_8.gif/image-size:440×231
cdについて〕
通勤費表には、従業員と交通機関コードの組合せごとに1つのレコードが作成されます。これは、ある交通機関の利用者数と、その交通機関を示す交通機関コードをもつ通勤費表内のレコードが同数であることを意味しています。つまり利用者数を得るには交通機関ごとに、その交通機関コードをもつレコード数を数えることになります。レコード数を求める集計関数はCOUNT()なので、交通機関ごとの利用者数を得るには、通勤費表を交通機関コードでグループ化し、COUNTでレコード数を数える方法が適切です。

c=通勤費表を交通機関コードで
 d=COUNT
SELECT 交通機関コード,COUNT(*) FROM 通勤費表
 GROUP BY 交通機関コード
pm03_9.gif/image-size:434×231

設問2

通勤にバスを利用している従業員の従業員番号と交通機関名を表示する。ここで,交通機関コードは3文字の固定長文字列であり,バスの交通機関コードだけが文字"B"で始まる。次のSQL文の に入れる正しい答えを,解答群の中から選べ。
pm03_2.gif/image-size:418×79
e に関する解答群
  • 通勤費表.交通機関コード IN ('B00','B99')
  • 通勤費表.交通機関コード LIKE 'B%'
  • 通勤費表.交通機関コード LIKE 'B_'
  • 通勤費表.交通機関コード LIKE '_B%'

解答選択欄

  • e:

解答

  • e=

解説

LIKE句は、文字列が指定したパターンに一致する場合に真を返す演算子です、LIKE句で使用される"_"と"%"はワイルドカードと呼ばれ、次の意味をもつ特殊文字として扱われます。
_
任意の1文字にマッチする
%
0文字以上の任意の文字列にマッチする
選択対象となるバスの交通機関コードは"B"から始まる3文字の文字列です。このため"B"から始まる任意の文字列を表す「B%」や、"B"から始まる3文字を表す「B__」などのパターンをLIKE句に指定することで条件に合致するレコードを選択できます。なお「B%」には3文字でない文字列もマッチしますが、設問にて交通機関コードは3文字の「固定長」という条件があるため「B%」でも期待する結果が得られます。

したがって「イ」が適切です。
  • IN句は、それに続く"()"内の集合に値があれば真を返す演算子です。この条件では"B00"または"B99"のどちらかの値をもつレコードにしかマッチしません。
  • 正しい。
  • 「B_」は"B"から始まる2文字を表すため誤りです。
  • 「_B%」は2文字目が"B"である文字列を表すため誤りです。

設問3

従業員の通勤圏内に新しい路線が開通することになった。この影響を確認するために,通勤経路が変わる可能性がある従業員の従業員番号を抽出することにした。同じ従業員番号は一つだけ表示する。新しい路線が影響する交通機関名は"情報都市線"と"駒込バス"であり,いずれも同名の交通機関名は他にないものとする。次のSQL文の に入れる正しい答えを,解答群の中から選ベ。
pm03_3.gif/image-size:220×59
f に関する解答群
  • pm03_4a.gif/image-size:404×51
  • pm03_4i.gif/image-size:404×50
  • pm03_4u.gif/image-size:404×68
  • pm03_4e.gif/image-size:404×68

解答選択欄

  • f:

解答

  • f=

解説

まず、どの副問合せにも共通して現れる以下のSQL文の結果について考えます。
SELECT 交通機関表.交通機関コード FROM 交通機関表
 WHERE 交通機関表.交通機関名 IN ('情報都市線', '駒込バス')
このSQLを実行すると、交通機関表から交通機関名が"情報都市線"または"駒込バス"のレコードが選択され、その交通機関コード列が抜き出されます。その結果、"情報都市線"と"駒込バス"の交通機関コードのリストが結果として返されます。
pm03_10.gif/image-size:522×223
上記の副問合せの結果セットと通勤費表の交通機関コードの値をIN句で比較することで、通勤費表から、交通機関コードが"情報都市線"または"駒込バス"であるレコードが選択できます。このとき1人の従業員が"情報都市線"と"駒込バス"の両方を利用しているケースでは1人に対して2つのレコードが抽出されることになります。設問には「同じ従業員番号は一つだけ表示する」という条件があるため、従業員番号でのグループ化を行い、従業員番号が重複して表示されないようにする必要があります。

IN句での比較と、グループ化が適切に指定されている「エ」が正解です。
pm03_11.gif/image-size:509×523
  • 副問合せ結果との比較に"="を使用する場合は、結果セットが1行である必要があります。このケースでは副問合せから2行が返されるため実行時エラーになります。
  • 副問合せの結果セットとIN句で比較するところまでは適切ですが、従業員番号でのグループ化が行われていません。このため1人の従業員が"情報都市線"と"駒込バス"の両方を利用している場合、従業員番号が重複して表示されてしまいます。
  • GROUP BY句を使用する場合、SELECTで指定できる列はグループ化した列または集計関数に限定されます。このSQL文ではグループ化されていない"従業員番号"列をSELECTで指定しているため構文エラーになります。
  • 正しい。

設問4

Y社では,毎年4月に交通安全講習会を開催しており,通勤に自家用車(交通機関コード"C01")を利用している従業員には受講を義務付けている。通勤に自家用車を利用していない従業員の受講は任意である。交通安全講習会を受講した従業員は,図3に示す講習会受講表で管理している。受講していない従業員のレコードは存在しない。
 通勤に自家用車を利用している従業員のうち,2016年4月20日に開催された交通安全講習会を受講していない従業員の従業員番号を表示する。次のSQL文の に入れる正しい答えを,解答群の中から選べ。
pm03_5.gif/image-size:204×107
pm03_6.gif/image-size:284×79
g に関する解答群
  • pm03_7a.gif/image-size:453×32
  • pm03_7i.gif/image-size:453×31
  • pm03_7u.gif/image-size:453×31
  • pm03_7e.gif/image-size:453×31

解答選択欄

  • g:

解答

  • g=

解説

表示対象は「通勤に自家用車を利用している」および「2016年4月20日の講習会を受講していない」という2つの条件を満たす従業員番号です。通勤に自家用車を使用しているレコードは「通勤費表.交通機関コード = 'C01'」で絞れるため、ANDで結合されているgにはもう一つの条件である「2016年4月20日の講習会を受講していない」を判定するSQL文が入ります。

まず選択肢中の2つの副問合せが返す結果を考えます。

1つ目は「受講日 IS NULL」を使用した以下のSQL文です。
SELECT 講習会受講表.従業員番号
 FROM 講習会受講表 WHERE 受講日 IS NULL
講習会受講表には「受講していない従業員のレコードは存在しない」と説明されています。つまり受講日には必ず何らかの日付が格納されていて、受講日が"空値"になっている行は存在しません。「IS NULL」は、比較対象の値が'空値'の場合に真を返す演算子なので、副問合せの結果は常に0行になります。この時点で「ア」「ウ」は誤りであると判断できます。

2つ目は「受講日 = '20160420'」を使用した以下のSQL文です。
SELECT 講習会受講表.従業員番号
 FROM 講習会受講表 WHERE 受講日 = '20160420'
このSQL文は講習会受講表の中から受講日が'20160420'であるレコードを選択し、その従業員番号のリストを返します。このリストと従業員番号を比較し、リストに含まれていれば「受講済み」、含まれていなければ「受講していない」と判断できます。今回は「受講していない」従業員を列挙したいので、比較対象がリストに含まれていない場合に真を返すNOT IN句を使用する必要があります。

したがって「エ」が正解です。

平成28年秋期 午後問題一覧

問1 問2 問3 問4 問5 問6 問7 問8 問9 問10 問11 問12 問13 採点講評
© 2010-2020 基本情報技術者試験ドットコム All Rights Reserved.

Pagetop