会員制通信販売事業者における会員販売データ管理に関する次の記述を読んで、設問1～4に答えよ。

清涼飲料水の会員制通信販売事業を運営するD社では、販売促進と商品管理の効率化を目的に会員情報や販売情報を管理するシステム(以下、販売管理システムという)を、事業開始当初から導入している。注文の受付は電話応対で行い、電話の受付時間は8時から20時までである。

販売管理システムで利用するデータベースの表構成とデータ格納例を、図1に示す。下線付きの項目は主キーである。

会員表

販売表

販売明細表

商品表

設問1

販売促進のために、コーヒーの新商品案内のはがきを送ることになった。その際、購入しそうな会員に効率よく案内するために、2016年の1月1日から12月31日までの1年間において、分類がコーヒーである商品を5回以上購入し、かつ、その購入額の合計が10,000円以上である会員の氏名、郵便番号、住所を抽出することにした。ここで、1回の購入は販売明細表の1行に該当するものとする。次のSQL文の　に入れる正しい答えを、解答群の中から選べ。

SELECT
      会員表.氏名
    , 会員表.郵便番号
    , 会員表.住所
FROM 会員表
WHERE 会員表.会員番号 IN 
(
SELECT 
    販売表.会員番号
FROM 販売表, 販売明細表, 商品表
WHERE 販売表.伝票番号 = 販売明細表.伝票番号
AND   商品表.商品番号 = 販売明細表.商品番号 a
)

a に関する解答群

ア

AND 販売表.販売日 >= '2016-01-01' AND 販売表.販売日 <= '2016-12-31'
AND 商品表.分類 = 'コーヒー'
AND 商品表.単価 * 販売明細表.個数 >= 10000
GROUP BY 販売表.会員番号
HAVING COUNT(*) >= 5

イ

AND 販売表.販売日 >= '2016-01-01' AND 販売表.販売日 <= '2016-12-31'
AND 商品表.分類 = 'コーヒー'
GROUP BY 販売表.会員番号
HAVING SUM(商品表.単価 * 販売明細表.個数) >= 10000
AND COUNT(*) >= 5

ウ

AND 販売表.販売日 >= '2016-01-01' AND 販売表.販売日 <= '2016-12-31'
GROUP BY 販売表.会員番号
HAVING 商品表.分類 = 'コーヒー'
AND SUM(商品表.単価 * 販売明細表.個数) >= 10000
AND COUNT(*) >= 5

エ

GROUP BY 販売表.会員番号
HAVING 販売表.販売日 >= '2016-01-01' AND 販売表.販売日 <= '2016-12-31'
AND 商品表.分類 = 'コーヒー'
AND SUM(商品表.単価 * 販売明細表.個数) >= 10000
AND COUNT(*) >= 5

設問1 解説

SQLの問題です。サブクエリ内のWHERE句の穴埋めです。

サブクエリのSQLは下記の通りです。

SELECT
    販売表.会員番号
FROM 販売表, 販売明細表, 商品表
WHERE 販売表.伝票番号 = 販売明細表.伝票番号
AND   商品表.商品番号 = 販売明細表.商品番号 a

このSQLは何を表現しているのでしょうか？

設問1より下記が条件となります。

会員ごとに（会員番号ごとに）

・2016年の1月1日から12月31日までの1年間

・分類がコーヒー

・5回以上購入（1回の購入は販売明細表の1行に該当）

・購入額の合計が10,000円以上

ここで、注意するのは「単なる条件」と、「計算結果が条件」となるものがあることです。

単なる条件はWHERE句に書けますが、計算結果が条件はHAVING句に記載します。

●単なる条件

・2016年の1月1日から12月31日までの1年間

・分類がコーヒー

単なる条件は、WHERE句にそのまま書けます。

・2016年の1月1日から12月31日までの1年間

→　販売表.販売日 >= '2016-01-01' AND 販売表.販売日 <= '2016-12-31'

・分類がコーヒー

→　商品表.分類 = 'コーヒー'

●計算結果が条件

・5回以上購入（1回の購入は販売明細表の1行に該当）

・購入額の合計が10,000円以上

計算結果が条件はHAVING句に記載します。

・5回以上購入（1回の購入は販売明細表の1行に該当）

→　COUNT(*) >= 5 # 販売明細表のレコード数を計算

・購入額の合計が10,000円以上

→　SUM(商品表.単価 * 販売明細表.個数) >= 10000

最後に、「会員ごと」なので会員番号でGROUP BYします。

これをしないと、SUM()やCOUNT()の計算ができません。

これらを合わせると「イ」となります！

AND 販売表.販売日 >= '2016-01-01' AND 販売表.販売日 <= '2016-12-31'
AND 商品表.分類 = 'コーヒー'
GROUP BY 販売表.会員番号
HAVING SUM(商品表.単価 * 販売明細表.個数) >= 10000
AND COUNT(*) >= 5

設問2

商品表の単価を何回でも変更できるようにする。併せて、販売時点の単価が分かるように、販売明細表の項目として販売時点の単価を追加することにした。変更した販売明細表の表構成を、図2に示す。商品表の単価の変更は、当日の受付時間前に行う。販売時点の単価の追加によって得ることができる情報として最も適切な答えを、解答群の中から選べ。

販売明細表

図2 変更した販売明細表の表構成

解答群

• ア：ある時、ある商品をある会員が購入した単価と、その直後に変更された単価との価格差
• イ：実際に購入された商品の、販売時点の単価の変遷
• ウ：全ての商品の、単価の変遷
• エ：全ての商品の、直近の単価変更日の前日における単価

設問2 解説

販売明細表をみると、「販売時単価」が追加されています。

販売明細表は実際に購入された時に追加されます。販売時単価はその名の通り、販売された時の単価です。

上記より、販売時点での単価がわかります。これらのレコードを集計すると単価の変遷がわかります。

従って、「イ：実際に購入された商品の、販売時点の単価の変遷」が正解です！

設問3

商品表の単価を変更できるようにした後の販売状況を把握するために、2017年の1月1日から6月30日までの半年間を対象に、商品表の分類別の販売額の合計(合計販売額)を会員の年齢ごとに求めて、出力したい。年齢は2017から生年を引いた値とする。次のSQL文の　に入れる正しい答えを、解答群の中から選べ。ここで、b1～ b3に入れる答えは、bに関する解答群の中から組合せとして正しいものを選ぶものとする。

SELECT
      年齢
    , 分類
    , b1 AS 合計販売額
FROM 
(SELECT
       2017 - 会員表.生年 AS 年齢
     , 商品表.分類
     , b2
 FROM 会員表, 販売表, 販売明細表, 商品表
 WHERE 会員表.会員番号    = 販売表.会員番号
 AND   販売表.伝票番号    = 販売明細表.伝票番号
 AND   販売明細表.商品番号 = 商品表.商品番号
 AND   販売表.販売日     >= '2017-01-01'
 AND   販売表.販売日     <= '2017-06-30'
) FACTTB
GROUP BY b3
ORDER BY 年齢 ASC, 合計販売額 DESC

b に関する解答群

設問3 解説

ポイント

・商品表の単価を変更できるようにした後の販売状況

→　販売明細表から販売時単価と個数が必要

→　サブクエリで必要

→　b2 販売明細表.販売時単価, 販売明細表.個数

→　販売明細表に販売時単価があり、計算に利用する

→　SUM(販売時単価 * 個数) が販売額の合計になる

→　b1 SUM(販売時単価 * 個数)

・商品表の分類別の販売額の合計(合計販売額)を会員の年齢ごとに求めて、出力したい

→　年齢と分類ごと

→　GROUP BY 年齢, 分類

→　b3 年齢, 分類

【参考】既にSQLにある条件

・2017年の1月1日から6月30日までの半年間。

→　AND 販売表.販売日 >= '2017-01-01' AND 販売表.販売日 <= '2017-06-30'

・年齢は2017から生年を引いた値とする

→　2017 - 会員表.生年 AS 年齢

設問4

入荷情報を管理するシステム(以下、入荷管理システムという)を販売管理システムと同時に運用開始している。入荷管理システムで利用するデータベースの表構成を図3に示す。

ビュー入荷集計表は運用開始から現在までの入荷数の総数を表示する。さらに販売総数を把握するためにビュー販売集計表を、最新の在庫数を把握するためにビュー在庫表を作成する。ビュー在庫表は一度でも入荷した商品は在庫数ゼロでも表示する仕様である。データベースに追加する表の構成を、図4に示す。

次の記述中の　に入れる正しい答えを、解答群の中から選べ。ここで、c1とc2に入れる答えは、cに関する解答群の中から組合せとして正しいものを選ぶものとする。

図1～図3の表を用いて、図4のビュー販売集計表を作成するための必要最小限の表の数はc1である。図4のビュー在庫表は、ビュー販売集計表を用いて作成する。このとき、ビュー在庫表を作成するための必要最小限の表の数は、ビュー販売集計表も含めてc2である。

入荷表

ビュー入荷集計表

図3 入荷管理システムで利用するデータベースの表構成

ビュー販売集計表

ビュー在庫表

図4 入荷管理システムのデータベースに追加する表の構成

c に関する解答群

設問4 解説

ビュー入荷集計表は、入荷表のレコードを集計すれば求めることができます。

従って、ビュー集計表作成に必要な必要最低限の表は「c1 1」です！

ビュー販売集計表の在庫数は、ビュー入荷集計表の入荷総数 - ビュー販売集計表の販売総数で求めることができます。

従って、ビュー販売集計表作成に必要最低限の表はビュー入荷集計表とビュー販売集計表の「c2 2」つです！