平成22年春期試験午後問題　問6

問6　データベース

⇱問題PDF
販売管理システムで使用するSQL文の検証に関する次の記述を読んで，設問1～3に答えよ。
広告
　X社は，輸入インテリアの販売を行っており，全国に店舗を展開している。現在，店舗ごとの受注・出荷は販売管理システムで管理している。新たに，インターネットで販売を行うWebショップを開設することになり，それに合わせて，Webショップでの受注も管理できる販売管理システムを構築することにした。
　新システムの開発に当たって，システム部のY君が初めてデータベースの設計・開発を任され，Z係長が指導をすることになった。

〔新システムの機能概要〕
  • Webショップで受注した商品は，顧客の住所を基に在庫のある最寄り店舗から出荷する。
  • 受注情報には受注店舗の情報をもち，受注明細には出荷店舗の情報をもつ。
  • 実店舗での販売においては，受注情報と受注明細の店舗は，販売した実店舗とする。
  • Webショップでの販売においては，受注情報の店舗はWebショップとし，受注明細の店舗は出荷を行う最寄り店舗とする。
  • Webショップも含めた全店舗の売上を，月次で店舗ごとに集計する。
  • Webショップで販売した商品の売上については，出荷した実店舗に配分する。したがって，Webショップの売上を，月次で受注明細の出荷店舗ごとに集計する。
  • Webショップの店舗番号は"A09999"とする。
〔新システムで使用するSQL文の検証〕
  • テーブル設計
    Y君は，新システムのデータベースのテーブルを設計した。設計したテーブルの一部を図1に示す。
    pm06_1.png
  • SQL文の作成
    Y君は，店舗ごとの売上を月次で集計する図2のSQL文と，Webショップの売上を出荷店舗ごとに月次で集計する図3のSQL文を作成した。":指定月開始日"，":指定月終了日"は，それぞれ集計対象月の開始日終了日を表す埋込み変数である。
    pm06_2.png
  • テストデータの作成
    Y君は，SQL文の検証のためにテストデータを作成した。作成したテストデータのうち，店舗，受注，受注明細の各テーブルのデータを表1～3に示す。
    pm06_3.png
  • SQL文の実行
    表1～3のテストデータを用いて，指定月開始日を2010年3月1日，指定月終了日を2010年3月31日として図2のSQL文を実行した結果，図4の出力が得られた。
    pm06_4.png
　Z係長は図4の出力された結果を見て，このSQL文では，集計の対象となる期間にd店舗の場合は，店舗ごとの売上の集計に出力されないことを指摘した。Y君がSQL文を図5のように修正して実行したところ，期待された結果が得られた。
pm06_5.png
広告

設問1

図3のSQL文について，(1)，(2)に答えよ。
  • 図3中のa～cに入れる適切な字句を答えよ。
  • 図3のSQL文を，指定月開始日を2010年3月1日，指定月終了日を2010年3月31日として表1～3のテストデータを用いて実行した結果のデータ項目名とデータの値を答えよ。

解答例・解答の要点

  • a：'A09999'
    b：j.受注番号 ※順不同
    c：m.受注番号 ※順不同
  • pm06_6.png

解説

まず、図1のテーブル設計を確認してみましょう。

受注情報は、受注テーブルと受注明細テーブルの2つのテーブルに格納されます。受注テーブルと受注明細テーブルはお互いの主キーの"受注番号"で関連付けられています。そして、実店舗とWebショップの受注情報は共通で管理する設計になっており、受注テーブルの"受注店舗番号"により受注した店舗を特定することができます。また、受注明細テーブルの"出荷店舗番号"により出荷した店舗（売上を計上する店舗）を特定することができます。

〔新システムの機能概要〕を確認するとWebショップにも店舗番号"A09999"が発番されていますので、受注テーブルから"受注店舗番号"が"A09999"の受注情報だけを抽出することにより、Webショップの売上を計算できることがわかります。Webショップの売上は、出荷した店舗の売上に振り分けることになっています。
  • 〔aについて〕
    図3のSQL文は、Webショップの売上を集計するSQLです。図3のSQLを確認するとWHERE句に3つの条件が記述されており、そのうちの2つが設問部分となっています。

    このSQLの目的は、①受注テーブルの出荷店舗番号がWebショップの売上を、②受注テーブルと結合した受注明細テーブルの出荷店舗番号の実店舗ごとに、③集計対象月の期間を開始日と終了日で指定して集計を行うことですから、まずは①の条件を満たすため、受注テーブル（j）の受注番号が"A09999"であるレコードに絞り込む必要があります。したがって、[a]には「'A09999'」が当てはまります。

    ∴a＝'A09999'

    〔b、cについて〕
    FROM句に2つのテーブルが記載されているにもかかわらず、結合を指定している文がないので、ここにはテーブルの結合を指定する条件式が入ることがわかります。
    テーブルの結合は、2つのテーブルに共通する属性を"="でつなぐことで行います。受注明細テーブル（m）と受注テーブル（j）は共通の属性"受注番号"で関連付けられていますから、空欄には「j.受注番号」及び「m.受注番号」が当てはまります。

    ∴b、c＝j.受注番号、m.受注番号（順不同）

  • [a]～[c]を埋めて完成させた図3のSQL文は以下の通りです。
    pm06_8.png
    開始日を2010年3月1日、終了日を2010年3月31日としてこのSQL文を実行すると、まずWHERE句に記述された3つの条件によってレコードの絞り込みが行われます。

    受注番号が"A09999"である3つのレコードが抽出されます。
    pm06_9.png
    この3つの受注番号レコードを表3の受注明細テーブルと結合すると、以下のようになります。
    pm06_10.png
    受注日付はすべて開始日と終了日の間に入っているので、上記の全行が条件を満たしています。

    この中間表に対して、①出荷店舗番号でグループ化（GROUP BY m.出荷番号）する、②グループごとに受注明細の受注金額の合計（ SUM(m.受注金額) ）を出す、③"出荷店舗番号"列と"Web売上分"列を抜き出す、④出荷店舗番号で昇順に整列する、という操作を行うと、以下の結果が得られます。
    pm06_11.png
広告

設問2

本文中のdに入れる適切な字句を15字以内で答えよ。

解答例・解答の要点

d：・受注情報が存在しない (10文字)
・売上がない (5文字)

解説

〔dについて〕
図2のSQL文は、店舗テーブルと受注情報との結合にINNER JOINが使われており、店舗テーブルと受注テーブルの間で結合条件に合致したものだけが抽出されます。このため、その月（開始日から終了日の間）に受注が1件もない店舗の行は結合対象から外れ、集計対象から抜けてしまうことになります。これにより月次売上として出力されない店舗が発生する可能性があります。表1～3のテストデータで言うと、表1の店舗のうち、表2に受注情報が存在しない店舗番号"A01003"の渋谷店が出力されません。

「d店舗」に当てはまる字句ですので、「受注がない」「売上がない」「受注情報が記録されていない」などの解答が適切となります。

∴d＝受注情報が存在しない 又は 売上がない
広告

設問3

図5のSQL文について，(1)，(2)に答えよ。
  • 図5中のeに入れる適切な字句を解答群の中から選び，記号で答えよ。
  • 図5のSQL文を表1～3のテストデータを用いて実行したときの結果と，図2のSQL文で出力した図4の実行結果を比較し，新たに出力される行をすべて答えよ。なお，答案用紙の行はすべて埋まるとは限らない。
e に関する解答群
  • EXCEPT
  • INNER JOIN
  • INTERSECT
  • LEFT OUTER JOIN
  • UNION

解答例・解答の要点

  • e：エ
  • pm06_7.png

解説

  • 〔eについて〕
    空欄には2つの表を繋げるための演算子が入ります。
    設問2で解説した通り、期間内の受注の有無にかかわらず全ての店舗を一覧化したい場合には、店舗テーブルの行が全て残るように結合する必要があります。このためには店舗テーブルと受注情報を左外部結合することになります。したがって、[e]には「LEFT OUTER JOIN」が当てはまります。
    • EXCEPTは、2つの関係の差集合を得る演算なので誤りです。
    • INNER JOINは内部結合演算です。従前の"="による結合と同じく、出力されない店舗が出るため誤りです。
    • INTERSECTは、2つの関係の共通集合を得る演算なので誤りです。
    • 正しい。
    • UNIONは、2つの関係の和集合を得る演算なので誤りです。
    ∴e＝エ：LEFT OUTER JOIN

  • LEFT OUTER JOIN（左外部結合）を行うと、基準となる左表の行を全て抽出し、右表からは左表の行と結合できる行のみが抽出されます。対応する右表の行が存在しない場合には、右表の属性はnullで埋められます。

    LEFT OUTER JOINで結合したときに新たに現れるのはINNER JOINでは欠落した、すなわち表2に受注情報が存在しなかった、受注番号"A010003"の渋谷店の情報です。結合される行がない場合、右表側の属性は全てnullになりますから、渋谷店の金額はnulとして出力されることとなります。他の店舗は図2のSQLでも出力されるので、図5のSQL文で新たに出力されるのはこの1行のみとなります。

    ∴pm06_7.png
広告
模範解答
広告

前の問題次の問題

▲
Pagetop