販売管理

　X 社の営業会議において，取扱い商品の見直しや単価改定などを行うことが決まり，上司の指示に基づき，Y 君は，次回開催される営業会議で必要な資料を，販売管理データベースを活用して作成することになった。

　商品在庫表の販売終了日には，その商品の販売をやめた日付けが設定されているが，販売中の商品については，NULL 値が設定されている。

　また，商品分類には，類似している商品をひとくくりに把握するためのコードが設定されている。

　Y 君は，次の作業を行うよう，上司から指示を受けた。

作業1:

　1999 年 4 月以降販売を開始し，継続して販売している商品については，商品ごとに販売金額，販売数量，利益率を求める。
　利益率を求める計算式は，“利益率 = 利益金額 / 販売金額”とする。

作業2:

　1997 年 10 月以前に販売を開始し，継続して販売している商品については，商品分類ごとにランク付けを行い，その推移状況を求める。

 　手順は次のとおりである。

(1)　1997年10月 〜 1998 年 9 月 〜 1998 年 10 月 〜 1999 年 9 月に分けて，それぞれ商品分類ごとの販売金額合計を基に，商品分類ごとにランク付けを行う。ランク付けは，作業2 と同様にする。

(2)　1997 年 10 月 〜 1998 年 9 月のランク付けした結果と 1998 年 10 月 〜1999 年 9 月のランク付けした結果を基に，図2 で示すランク推移表を作る。

(3)　ランク推移表を基に，検討資料を作成するためのデータを抽出する。

　なお，ランク推移表の販売金額合計 1 とランク 1 には，1997 年 10 月 〜 1998年 9 月の販売金額合計とランクを設定し，販売金額合計 2 とランク 2 には, 1998 年 10月 〜 1999 年 9 月の販売金額合計とランクを設定する。

　また，ランク推移状況には，状況ステータスとして，ランクが上がったものに‘↑’，ランクが変わらないものに‘→’，ランクが下がったものに‘↓’を設定する。

設問2　作業 2 を行う上で全商品をランク付けの対象にするためには，販売実績がない商品があった場合のことを考慮しておく必要がある。Y 君は，あらかじめ，販売実績がない商品を抽出する SQL 文を考えておくことにした。

　次に示す SQL 文は，販売実績がない商品を抽出するためのものである。

　SQL 文の［　　　］に入れる適切な字句を，解答群の中から選べ。

　SELECT 商品コード, 商品名，0 AS 販売金額 FROM 商品在庫表
　WHERE ［　ｄ　］ AND 販売終了日 IS NULL
　AND　商品コード［　ｅ　］（SELECT DISTINCT 商品コード
      FROM 販売表, 販売明細表
　　　WHERE 販売表.販売番号＝販売明細表.販売番号 AND [ f ])

d, f に関する解答群

　ア　販売開始日 BETWEEN‘1998-10-01' AND‘1999-09-30'
　イ　販売開始日 < '1998-10-01'
　ウ　販売開始日 < ‘1999-09-30'
　エ　販売開始日 > ‘1998-10-01'
　オ　販売開始日 > ‘1999-09-30'
　カ　販売日 BETWEEN ‘1998-10-01' AND ‘1999-09-30'
　キ　販売日 < ‘1998-10-01'
　ク　販売日 < ‘1999-09-30'
　ケ　販売日 > ‘1998-10-01'
　コ　販売日 > ‘1999-09-30'

e に関する解答群
　ア IN
　イ NOT IN

設問3　作業 3−(2)のランク推移表を作成する作業として，ランク推移状況に状況ステータスを設定する。

 　次に示す SQL 文は, ランク推移表のランク推移状況に状況ステータスを設定するためのものである。表は, SQL文中の下線の状況ステータスと条件式の一覧である。

　表中の［　　　］に入れる適切な字句を，解答群の中から選ベ。

  UPDATE ランク推移表 SET ランク推移状況 = 状況ステータス WHERE 条件式

g に関する解答群
　ア　ランク1 = 'C' AND ランク2 = 'A'
　イ　ランク1 = 'C' AND ランク2 = 'B'
　ウ　ランク1 = 'C' AND ランク2 = 'C'
　エ　ランク1 = 'C' AND ランク2 <> 'A'
　オ　ランク1 = 'C' AND ランク2 <> 'B'
　カ　ランク1 = 'C' AND ランク2 <> 'C'

h, i に関する解答群
　ア　ランク1 = 'B' AND ランク2 = 'A'
　イ　ランク1 = 'B' AND ランク2 = 'B'
　ウ　ランク1 = 'B' AND ランク2 = 'C'
　エ　ランク1 = 'B' AND ランク2 <> 'A'
　オ　ランク1 = 'B' AND ランク2 <> 'B'
　カ　ランク1 = 'B' AND ランク2 <> 'C'

j に関する解答群
　ア　ランク1 = 'A' AND　ランク2 = 'A'
　イ　ランク1 = 'A' AND　ランク2 = 'B'
　ウ　ランク1 = 'A' AND　ランク2 = 'C'
　エ　ランク1 = 'A' AND　ランク2 <> 'A'
　オ　ランク1 = 'A' AND　ランク2 <> 'B'
　カ　ランク1 = 'A' AND　ランク2 <> 'C'

設問4　次に示す SQL 文は，作業 3-(3)の作業として，ランク別商品分類一覧表を作成するためのデータを抽出するものである。

　SQL 文を読んで，抽出結果の順序に関する記述の［　　　］に入れる適切な字句を，解答群の中から選べ。解答は重複して選んでもよい。

　なお，ランク推移状況に入力した状況ステータスを昇順に整列させると，‘↑’，‘→’，‘↓’の順となる。

　SELECT　ランク2.商品分類，ランク推移状況，ランク1，販売金額合計2
　FROM ランク推移表
　ORDER BY ランク2　ASC，ランク推移状況　ASC，ランク1　DESC

抽出結果の順序に関する記述

(1)抽出結果の順序は，1998 年 10 月〜1999 年 9 月のランクが[ k ]〜[ l ]の順となっている。

(2)同一ランクの場合は，ランクが［ m ］商品を先に抽出し，次にランクが表わらない商品，そしてランクが[ n ]商品の順に抽出している。

(3)さらに，ランクが上がった商品について, [ o ］からランクＡに上がった商品は，[ p ]からランクＡに上がった商品より先に抽出している。

k, l, o, p に関する解答群
　ア　ランク A
　イ　ランク B
　ウ　ランク C

m, n に関する解答群
　ア　上がった
　イ　変わらない
　ウ　下がった

すー：本文は，いい加減にしか読んでません。

ジャン：テストの場合は，それで，設問に入りましょう。

すー：これって，GROUP BY で，グループ化してます。

ジャン：合計とかとるときに，このように指示をしますね。

すー：あと，最大値とか最小値なども，グループ化を指示しますよね。

ジャン：グループ化した場合，SELECT のところに書けるのは，GROUP BY で上げた項目か，グループに対する指定をするもの，合計とか最大値などです。

すー：これは，販売明細を，商品コードごとに，販売金額，販売数量，利益率を求めているSQL文ですね。

ジャン：すーちゃん，このSQLを分析したのですか？

すー：いや，たぶん，そんな感じかなと思って。

ジャン：商品コードごとの，販売金額，販売数量，利益率ですので，合計を利用して，SUM(販売金額), SUM(販売数量)で，利益率は，式になりますので，(SUM(販売金額) - 仕入単価 * SUM(販売数量))/SUM(販売金額)ですね。

すー：仕入単価は，変化ないのですか。

ジャン：図１をみると，変化が無いという前提ですよ。

すー：簡単でしたね。設問２にいきましょうか。

ジャン：お願いします。

すー：「販売実績がない商品」ということですので，e は，NOT IN ですね。後ろで求めた中に商品コードを求めるということでしょう。

ジャン：すーちゃんいい勘してますね。

すー：d, f は，日にちが入るようですね。

ジャン：どれが入ると思う？

すー：先生，分かりませんよ。本文でチェックしなくては。

ジャン：まず，予想してみましょう。私は，たぶん，アとエと思いますよ。

すー：どっちがどっちですか。

ジャン：d がアで，f が，カでしょう。

すー：なんでそう予想できるのですか。

ジャン：まず，営業会議用資料なんで，日付は範囲指定でしょう。

すー：だから，BETWEEN 日付 AND 日付 だろうということですね。じゃ，d がアなんですか。

ジャン：先に解答群にあるじゃないですか。

すー：それだけの理由ですか。

ジャン：いやもう少し，ヒントはあります。d のところは，商品在庫表の項目ですよね。販売日というのは，販売表かもしくは，販売明細表のデータですので，カが，f で，アが，d でしょう。販売開始日というのは，商品在庫表にあってもおかしい項目ではありません。

すー：先生，図１を見ないで考えているのですか。

ジャン：まだ，しっかりとみていません。解いた後に見ます。

すー：先生，販売開始って，意味から考えると，範囲で指定するということは，特別な意味がありますよ。普通なら，販売中かいなかの判断だけではないでしょうか。

ジャン：そうですね。アは，違ってますね。さすが，すーちゃん，賢い。

すー：作業２を見ますね。

ジャン：作業２を見れば，一発で分かりましたね。

すー：先生予想するだけ無駄ということはないですか。

ジャン：そんなことはないですよ。予想したからこそ，一発に目に入って来るもんですよ。

すー：解答は，d: イ，f: カですね。

ジャン：次にいきましょうか。

すー：先生，このSEQ の中に，DESTINCT とありますが，少し解説お願いします。

ジャン：すーちゃん，英語の本来の意味？

すー：別々のとか，はっきり区別できるといった形容詞です。

ジャン：SQL では，重複カットですよ。同じ商品コードが登場した場合，いくつも出さずに一つだけにします。

すー：では，設問３に行きますね。

ジャン：この SQL，UPDATE が使ってありますね。

すー：更新ですか。

ジャン：条件に合致した行のある項目の値を更新ですね。この場合，→などの矢印を入れるということです。

すー：ランクが上がったら，↑，下がったら，↓，変化しないときは，→ですね。

ジャン：ランクは，C < B < A ですので，g には，「ランク1 = 'C' AND ランク2 <> 'C'」ですね。「ランク1 = 'C' AND ランク2 = 'A'」「ランク1 = 'C' AND ランク2 = 'B'」でもいいのですが，空欄が一つなので，「ランク1 = 'C' AND ランク2 <> 'C'」ですね。

すー：ちょっと，技巧派ですね。プログラムの鉄則から言ったら，たまたま，A, B, Cの３つから選択なので，このテクニックが使えますが，４パターンになったら，使えませんよ。

ジャン：４パターンになったら，クラス１ = 'D' AND ランク2 <> 'D' ってやるんですよ。そうすれば，使えますよね。

すー：確かにそうですけど。（；；）

ジャン：h; は？

すー：アの「ランク1 = 'B' AND ランク2 = 'A'」ですね。

ジャン：i も　j も同様にできますね。

すー：i は，ウの「ランク1 = 'B' AND ランク2 = 'C'」。j は，エの「ランク1 = 'A' AND　ランク2 <> 'A'」ですね。

ジャン：では，設問４に行きましょうか。

すー：はい。

ジャン：並び替えの問題ですね。「　ORDER BY ランク2　ASC，ランク推移状況　ASC，ランク1　DESC」を見れば，分かりますね。

すー：先ず，ランク２で昇順で並べて，同一グループ内をランク推移状況を昇順，さらにそのグループをランク１で降順ですね。

ジャン：ランク２の中は，具体的には，何が入っていますか？

すー：A, B, C です。

ジャン：昇順に並べるということは，A, B, Cの順ですね。k;ランク A，l:ランク C の順となります。

すー：(2)では，ランク推移状況で昇順です。

ジャン：ランク推移状況の中身は？

すー：↑，→，↓です。

ジャン：これって昇順に並べるとどうなりますか？

すー：知りません。こんなの知っている人いますか。先生，設問４に「なお，ランク推移状況に入力した状況ステータスを昇順に整列させると，‘↑’，‘→’，‘↓’の順となる。」とありました。

ジャン：良かったですね。昇順ですので，m:ア 上がった，n:ウ 下がった　ですね。

すー：最後は，ランク１で降順です。o:ウ ランク C，p: ランク B ですね。

ジャン：これは，いわゆるABC分析の話でしたね。

すー：作業１のここのところですね。

ジャン：販売金額で，商品の ABC 分析をしています。このバックある理論は，総販売金額の８割の金額は，２割の商品種別で占められているいうパレートの法則です。パレートの法則の応用として，販売金額の７割を占める商品群をランク A，最後の１割にあたる商品群をランク C としてますね。

すー：パレート図のこの理論の応用ですね。

ジャン：すーちゃんのお小遣いの使い道は？

すー：なにか身近な例のABC分析してみるといいですよね。

ジャン：私の今日の時間でやってみますね。４時半に起床，30分ほどメール等チェック，５時から７時まで宿題メール作成，７時１５分まで，朝の準備，移動を開始して，８時３０分に学校，１７時半から２０時まで移動，この中に１時間，モスバーガーでコーヒーを飲みながら読書。夕食３０分，すーちゃんと勤マガ作成１１時まで，たぶん，１１時半までにお風呂等で寝ます。

すー：表にしてますね。

ジャン：これを降順に並べるのですね。

すー：はい。問題の基準で，ABC分析してみますね。

ジャン：こう思うと，勤務時間の占める時間が大きいですね。あと，読書時間は，移動中も１時間ほど読んでましたので，本日の読書時間は２時間でした。

すー：先生，移動時間を朝，夕方合わせて，一つの枠にすると，順が変わりますね。そうなると，このABC分析はまた違ってきますよね。

ジャン：商品の場合は，分け方がはっきりしているのでそういう問題はないですが，分け方は，あんまり厳密に考えなくてもいいですよ。傾向が見れればいいと割り切りましょう。

すー：先生，これを見て，どうですか。

ジャン：勤務時間と移動時間を短縮したいですよね。明日は，大学の非常勤で１日，岐阜大学です。そうすると，勤務時間が，８時間で，移動時間が，朝，夕方合わせて３時間ほどになります。２時間は自宅での仕事（宿題メール，勤マガ）にかけられそうですね。

すー：じゃ，明日も１号はでそうですね。私も夕食ごろまた，おじゃます。

ジャン：よろしくお願いします。

すー：さて，宿題ですが，いよいよ表計算ですね。楽しみです。

ジャン：みなさん，ぜひ，やって下さいね。

宿題：初級システムアドミニストレータ午後平成１１年秋問６

(c)斎藤末広， 2000
この資料は2000年10月15日まで，許可した方のみ閲覧可能です。もし，なんらかの手段でこの資料を読まれて，有効であると判断された場合は，ご入金をお願いします。許可無く再利用，公開を禁止します。なお，情報処理技術者試験問題，サンプルプログラムはその限りではありません。

spage@yscon.co.jp
末広ページへ　このコーナーの目次へ