基本情報技術者 2016年 秋期 午後 問03

解答の導き方

誤りやすいポイント

• 通勤手当の合計では、従業員番号でまとめるだけでなく、交通費を合計する必要があります。件数を数えるCOUNTでは、金額は求められません。
• 交通機関ごとの利用者数では、交通機関コードでまとめます。従業員番号でまとめると、交通機関ごとの人数にはなりません。
• 交通機関表には交通機関名が載っていますが、従業員の利用記録はありません。利用者数を求めるときは、利用記録がある通勤費表を選びます。
• AVG（average：平均）は平均値、MAX（maximum：最大値）を求める関数です。交通費の合計にも、利用者数にも使いません。

FAQ

解答の導き方

• アのIN ('B00', 'B99') は、指定した2種類のコードだけに一致します。Bで始まるすべてのコードを選べるわけではありません。
• イのLIKE 'B%' は、先頭がBであるコードを選べます。
• ウのLIKE 'B_' は、_を文字として扱うなら「Bの次にアンダースコアがある文字列」を表し、任意の2文字を表すものではありません。
• エのLIKE '_B%' は、_を文字として扱うならアンダースコアで始まる文字列を表します。Bで始まるコードを選ぶ条件ではありません。

誤りやすいポイント

• %と_の意味を混同しないようにしましょう。%は0文字以上、_は1文字を表します。
• INは、指定した値の中から一致するものを探す条件です。値をすべて列挙しているので、指定されていないB始まりのコードは選べません。
• 交通機関コードを交通機関名に結び付ける条件と、バスのコードを選ぶ条件は役割が違います。前者で表同士を対応付け、後者で必要な行に絞り込みます。

FAQ

解答の導き方

1. 対象となる交通機関コードを調べます。
   問題文では、影響する交通機関名が「情報都市線」と「駒込バス」とされています。図2では、交通機関名と交通機関コードは交通機関表で対応付けられています。
   そこで、交通機関名がこの二つのどちらかに当てはまる行から、交通機関コードを取り出します。このように、SQL（Structured Query Language：データベースに検索や操作を指示する言語）の命令の中に、別の検索命令を置くものをサブクエリといいます。
2. 取り出したコードに当てはまる通勤費表の行を探します。
   通勤費表の交通機関コードが、サブクエリで得たコードのいずれかと一致する行を選びます。候補のアの「=」は、比較する値が一つの場合に使います。しかし、今回は交通機関名が二つあるため、対応するコードも複数になる可能性があります。
   複数の候補の中に一致する値があるかを調べるには、INを使います。したがって、交通機関コードの条件は候補のイ・ウ・エの形になります。
3. 従業員番号が重複しないようにします。
   問題文には「同じ従業員番号は一つだけ表示する」とあります。図2の通勤費表では、同じ従業員番号が異なる交通機関コードの行に現れることがあります。そのため、条件に当てはまる行をそのまま表示すると、同じ従業員番号が複数回出る場合があります。
   GROUP BYは、指定した項目の値が同じ行をまとめる句です。従業員番号でまとめれば、同じ従業員番号は一つにできます。したがって、GROUP BYの対象は候補のエにある「通勤費表.従業員番号」です。

誤りやすいポイント

• 「交通機関名が二つある」ことを見落とし、「=」を選ばないようにします。サブクエリの結果が複数になる可能性があるときは、複数の候補との一致を調べるINが適しています。
• 通勤費表の行数と、表示したい従業員の人数は同じとは限りません。同じ従業員番号が複数の行に現れる可能性があるため、重複をなくす処理が必要です。
• GROUP BYで何をまとめるかは、何を重複させたくないかで決めます。この設問で一つにしたいのは交通機関コードではなく、従業員番号です。

FAQ

解答の導き方

1. 自家用車を利用している従業員に絞ります。
   問題文では、自家用車の交通機関コードは「C01」とされています。SQL（Structured Query Language：関係データベースのデータを検索・操作するための言語）の条件「通勤費表.交通機関コード = 'C01'」によって、通勤費表から自家用車を利用している従業員を選べます。
2. 指定日に受講した従業員の一覧を作ります。
   講習会受講表には、受講した従業員の情報が記録されています。そこで、受講日が「20160420」の行から、従業員番号を取り出します。このように、SQL文の中で別の検索を行う部分をサブクエリといいます。
3. 受講者の一覧に含まれない従業員を選びます。
   問題文には「受講していない従業員のレコードは存在しない」とあります。つまり、受講していない人を講習会受講表の中から探すのではなく、指定日に受講した人の一覧から除く必要があります。
   INは、ある値が一覧に含まれるかを調べる条件です。その反対に、NOT INは一覧に含まれない値を選びます。したがって、サブクエリで指定日に受講した従業員番号を取り出し、通勤費表の従業員番号がその一覧に含まれないことを条件にします。

誤りやすいポイント

• 「受講していない人」を「受講日がNULLの人」と考える
  NULLは、データが入っていないことを表す値です。しかし、問題文では受講していない従業員のレコード自体がありません。存在しない行の中から、受講日がNULLの行を探すことはできません。
• INとNOT INを逆にする
  指定日の受講者を選ぶだけならINですが、今回はその人たちを除く必要があります。そのため、NOT INを使います。
• 受講日を指定し忘れる
  受講者の中でも、対象となるのは2016年4月20日の講習会に出席した人です。サブクエリには、受講日が「20160420」という条件が必要です。
• アのように、従業員番号を1件だけ返す前提で比較する
  指定日に受講した従業員は複数いる可能性があります。従業員番号の一覧との比較には、複数の値を扱えるINやNOT INを使います。

FAQ