2010年 春期 応用情報技術者試験 問6

販売管理システムで使用するSQL文の検証

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

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

[新システムの機能概要]

・Webショップで受注した商品は，顧客の住所を基に在庫のある最寄り店舗から出荷する。

・受注情報には受注店舗の情報をもち，受注明細には出荷店舗の情報をもつ。

・実店舗での販売においては，受注情報と受注明細の店舗は，販売した実店舗とする。

・Webショップでの販売においては，受注情報の店舗はWebショップとし，受注明細の店舗は出荷を行う最寄り店舗とする。

・Webショップも含めた全店舗の売上を，月次で店舗ごとに集計する。

・Webショップで販売した商品の売上については，出荷した実店舗に配分する。したがって，Webショップの売上を，月次で受注明細の出荷店舗ごとに集計する。

・Webショップの店舗番号は"A09999"とする。

[新システムで使用するSQL文の検証]

(1) テーブル設計

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

(2) SQL文の作成

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

SELECT t.店舗番号, t.店舗名, SUM(m.受注金額) AS 金額
FROM (店舗 t INNER JOIN (SELECT j.受注店舗番号, j.受注番号 FROM 受注 j
    WHERE j.受注日付 BETWEEN :指定月開始日 AND :指定月終了日) p
    ON t.店舗番号 = p.受注店舗番号)
    INNER JOIN 受注明細 m ON p.受注番号 = m.受注番号
GROUP BY t.店舗番号, t.店舗名
ORDER BY t.店舗番号

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) テストデータの作成

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

(4) SQL文の実行

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

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 ON p.受注番号 = m.受注番号
ON t.店舗番号 = p.受注店舗番号
GROUP BY t.店舗番号, t.店舗名
ORDER BY t.店舗番号