初級シスアド 平成12年度春期試験 問題(午後問1)
問1 販売管理及び顧客管理に関する次の記述を読んで，設問1，2に答えよ。
音楽CDを販売しているI店では，顧客サービス向上のために，会員カードを発行して1ポイント1円の値引きを行っている。店長のEさんは，パソコンのデータベースソフトを利用して，販売管理及び顧客管理を行うシステムを運用している。
このシステムのためのデータベースの構造は，図に示すとおりである。
売上表
| 売上番号 | 顧客番号 | 日付 | 時刻 | 売上総額 |
明細表
| 明細番号 | 売上番号 | 商品番号 | 数量 |
商品マスタ表
| 商品番号 | 商品分類 | 名称 | 単価 |
顧客マスタ表
| 顧客番号 | 氏名 | 住所 | 電話番号 | 生年月日 | 積立ポイント |
このシステムは，売上時に明細表と売上表にデータを書き込み，売上総額は，商品番号と数量から商品マスタ表の単価を参照して計算する。
新規の顧客については，購入時に会員カードの作成を勧め，会員となった顧客のデータを顧客マスタ表に書き込む。会員だけを値引きサービスの対象とする。
非会員が購入した場合，売上表の顧客番号には全けた“9”をセットし，売上集計の対象とする。なお，顧客マスタ表には顧客番号が全けた“9”，氏名に“非会員”とセットされているデータが登録されている。
会員に対してのポイントの積立て及び利用の処理手順を次に示す。
| (1) | 会員カードから顧客番号を読み取る。 |
| (2) | 顧客マスタ表から，積立ポイントを読み込む。 |
| (3) | 会員からポイント利用の要望があれば，積立ポイントの中から利用ポイント数を決めてもらう。ただし，売上総額を超えてのポイント利用はできない。利用ポイント相当額を売上総額から差し引き，新しい売上総額とする。利用したポイント数を積立ポイントから差し引く。 |
| (4) | 売上総額の5パーセントをポイントとして積み立てる。 |
| 設問1 | 顧客に人気CDを知ってもらうため，表のようなI店の売上数量ベスト20（2000年4月度）を掲示したい。売上数量ベスト20を作成するためのデータを抽出するSQL文の［ ］に入れる適切な字句を，解答群の中から選べ。 |
SELECT 商品マスタ表.商品番号,商品マスタ表.名称,商品マスタ表.商品分類,
［ a ］ AS 数量
FROM 売上表,明細表,商品マスタ表
WHERE 売上表.売上番号 = 明細表.売上番号
AND 明細表.商品番号 = 商品マスタ表.商品番号
AND 売上表.日付 ［ b ］ '2000-04-01' ［ c ］ '2000-04-30'
［ d ］ 商品マスタ表.商品番号,商品マスタ表.名称,商品マスタ表.商品分類
［ e ］ ［ a ］ DESC
aに関する解答群
| ア COUNT(明細表.数量) | イ SUM(売上表.売上総額) |
| ウ SUM(明細表.数量) |
b，cに関する解答群
| ア AND | イ BETWEEN | ウ FROM |
| エ IN | オ TO |
d，eに関する解答群
| ア GROUP BY | イ ORDER BY | ウ SORT BY |
設問1の正解例と解説へ
| 設問2 |
さらに顧客サービスを向上させるため，顧客マスタ表にボーナス区分を追加して，会員にボーナスポイントのプレゼントを月単位で実施することにした。対象者には商品購入時のポイントを通常の2倍（売上総額の10パーセント）にする。ただし，ボーナスポイントのプレゼントは1回／月限りとする。 対象者は次の条件のいずれか又は両方に該当する会員とし，非会員は含まない。 ・前月（1日〜月末）に1万円以上購入のあった会員 ・前月までの過去1年間に購入履歴があり，今月誕生日を迎える会員 |
月初の対象者抽出処理，売上時のポイント処理に関する次の記述中の［ ］に入れる適切な字句を，解答群の中から選べ。
（月初の対象者抽出処理）
| (1) | 顧客マスタ表のボーナス区分を，初期値“1”に更新する。 |
| (2) | ［ f ］を照会し，前月1日から月末を条件に顧客番号ごとの［ g ］を合計する。［ g ］の合計が1万円以上の会員の顧客番号を抽出する。抽出した顧客番号に一致する顧客マスタ表のボーナス区分を“2”に更新する。 |
| (3) | ［ h ］を照会し，生年月日の月と今月が一致し，かつ［ f ]を照会して前月までの過去1年間に購入履歴があった顧客番号を抽出する。抽出した顧客番号に一致する顧客マスタ表のボーナス区分を“2”に更新する。 |
（売上時のポイント処理）
ポイントの処理は，次の決定表に従って行う。
| 会員からポイント利用の要望あり | N | N | Y | Y |
| 顧客マスタ表のボーナス区分が“2”である | Y | N | Y | N |
| ［ i ］ | - | - | X | X |
| ［ j ］ | X | - | X | - |
| ［ k ］ | - | X | - | X |
| 顧客マスタ表のボーナス区分を“1”に更新する | X | - | X | - |
f，hに関する解答群
| ア 売上表 | イ 顧客マスタ表 | ウ 商品マスタ表 | エ 明細表 |
gに関する解答群
| ア 売上総額 | イ 数量 | ウ 単価 |
i〜kに関する解答群
| ア | 売上総額からポイント相当額を値引きし，新たな売上総額とする |
| イ | 売上総額の10％をポイントとして積み立てる |
| ウ | 売上総額の5％をポイントとして積み立てる |
設問2の正解例と解説へ
メニューへ戻る