AP22春後問6

AP22春後問6

応用情報技術者試験平成22年度春午後問6


 販売管理システムで使用する SQL 文の検証に関する次の記述を読んで,設問 1 ~ 3 に答えよ。


 X 社は,輸入インテリアの販売を行っており,全国に店舗を展開している。現在,店舗ごとの受注・出荷は販売管理システムで管理している。新たに,インターネットで販売を行う Web ショップを開設することになり,それに合わせて,Web ショップでの受注も管理できる販売管理システムを構築することにした。

 新システムの開発に当たって,システム部の Y 君が初めてデータベースの設計・開発を任され,Z 係長が指導をすることになった。


〔新システムの機能概要〕
  • Webショップで受注した商品は,顧客の住所を基に在庫のある最寄り店舗から出荷する。
  • 受注情報には受注店舗の情報をもち,受注明細には出荷店舗の情報をもつ。
  • 実店舗での販売においては,受注情報と受注明細の店舗は,販売した実店舗とする。
  • Web ショップでの販売においては,受注情報の店舗は Webショップとし,受注明細の店舗は出荷を行う最寄り店舗とする。
  • Web ショップも含めた全店舗の売上を,月次で店舗ごとに集計する。
  • Web ショップで販売した商品の売上については,出荷した実店舗に配分する。したがって,Web ショップの売上を,月次で受注明細の出荷店舗ごとに集計する。
  • Web ショップの店舗番号は"A09999"とする。

〔新システムで使用するSQL文の検証〕

(1) テーブル設計

 Y 君は,新システムのデータベースのテーブルを設計した。設計したテーブルの一部を図 1 に示す。


店舗(店舗番号,店舗名,店舗住所)

顧客(顧客コード,氏名,住所,電話番号)

受注(受注番号,受注日付,受注店舗番号,顧客コード)

受注明細(受注番号明細番号,商品番号,出荷店舗番号,受注数,受注金額)

在庫(商品番号店舗番号,在庫数)

注 下線は主キーを表す。

図 1 テーブル設計(一部)


(2) SQL 文の作成

 Y 君は,店舗ごとの売上を月次で集計する図 2 の SQL 文と,Web ショップの売上を出荷店舗ごとに月次で集計する図 3 の SQL 文を作成した。 “:指定月開始日”,“:指定月終了日”は,それぞれ集計対象月の開始日,終了日を表す埋込み変数である。


SELECT t.店舗番号,t.店舗名,SUM (m.受注金額) AS金額

FROM (店舗t INNER JOIN(SELECT j.受注店舗番号,j.受注番号FROM受注j

     MHERE j.受注日付 BETWEEN :指定月開始日 AND :指定月終了日)p

     ON t.店舗番号 = p.受注店舗番号)

     INNER JOIN 受注明細 m ON p.受注番号 = m.受注番号

GROUP BY t.店舗番号,t.店舗名

ORDER BY t.店舗番号

図 2 店舗ごとの売上を集計する SQL 文


SELECT m.出荷店舗番号,SUM(m.受注金額) AS web 売上分

FROM 受注明細m,受注 j

WHERE(j.受注店舗番号 = [ a ]) AND ([ b ],[ c ])AND

   (j,受注日付 BETWEEN :指定月開始日 AND :指定月終了日)

GROUP BY m.出荷店舗番号

ORDER BY m.出荷店舗番号

図 3 Web ショップの売上を出荷店舗ごとに集計する SQL 文


(3)テストデータの作成

 Y 君は,SQL 文の検証のためにテストデータを作成した。作成したテストデータのうち,店舗,受注,受注明細の各テーブルのデータを表 1 ~ 3 に示す。

表1 店舗テーブルのテストデータ

店舗番号店舗名店舗住所
A01001銀座店東京都中央区
A01002新宿店東京都新宿区
A01003渋谷店東京都渋谷区
A03001名古屋店名古屋市千種区
A05001難波店大阪市中央区
A09999Webショップ本社WebSystem


表 2 受注テーブルのテストデータ

受注番号受注日付受注店舗番号顧客コード
500012010-03-03A0100211001
500022010-03-08A0100111002
500032010-03-12A0100211003
500042010-03-13A0300112002
500052010-03-19A0999912003
500062010-03-21A0999913202
500072010-03-21A0500113203
500082010-03-29A0999911003


表 3 受注明細テーブルのテストデータ

受注番号明細番号商品番号出荷店舗番号受注数受注金額
50001176593301002150400
5000124328790100227200
50002110200401001150000
50003166458901002225000
50003210701301002160500
5000414328790300113600
500051168234010021113000
50006166602801001136700
50007129304105001115000
500081813556010021120000


(4) SQL 文の実行

 表 1 ~ 3 のテストデータを用いて,指定月開始日を 2010 年 3 月 1 日,指定月終了日を 2010 年 3 月 31 日として図 2 の SQL 文を実行した結果,図 4 の出力が得られた。

店舗番号店舗名金額
A01001銀座店50000
A01002新宿店143100
A03001名古屋店3600
A05001難波店15000
A09999Webショップ269700

図 4 店舗ごとの売上を集計する SQL 文の実行結果


 Z 係長は図 4 の出力された結果を見て,この SQL 文では,集計の対象となる期間に [ d ] 店舗の場合は,店舗ごとの売上の集計に出力されないことを指摘した。Y 君が SQL 文を図 5 のように修正して実行したところ,期待された結果が得られた。


SELECT t.店舗番号,t.店舗名,SUM (m.受注金額) AS 金額

FROM 店舗 t [ e ]

   (SELECT j.受注店舗番号,j.受注番号 FROM 受注 j

      WHERE j.受注日付 BETWEEN :指定月開始日 AND:指定月終了日) p

       INNER JOIN 受注明細 m 0N p.受注番号 = m.受注番号

   ON t.店舗番号 = p.受注店舗番号

GROUP BY t.店舗番号, t.店舗名

ORDER BY t.店舗番号

図 5 修正後の店舗ごとの売上を集計する SQL 文


設問1

 図 3 の SQL 文について,(1),(2) に答えよ。

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


設問2

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


設問3

 図 5 の SQL 文について,(1),(2) に答えよ。

(1) 図 5 中の[ e ]に入れる適切な字句を解答群の中から選び,記号で答えよ。

解答群
ア EXCEPT
イ INNER]JOIN
ウ INTERSECT
エ LEFT OUTER JOIN
オ UNION

(2) 図 5 の SQL 文を表 1 ~ 3 のテストデータを用いて実行したときの結果と,図 2 の SQL 文で出力した図 4 の実行結果を比較し,新たに出力される行をすべて答えよ。なお,答案用紙の行はすべて埋まるとは限らない。


問題掲載者による注意書き

  • [ a ][ b ][ c ] 等は,四角で囲まれていたが,テキスト表現をするため,[ ]で置き換えた。