総視聴再生時間43万分以上(2017年5月13日現在)の動画で基本情報技術者試験の過去問&キーワード解説!スキマ時間に動画!〜これじょIT〜

PR広告

平成29年度秋 基本情報技術者試験午後 問3 データベース|合格率アップ!動画解説!

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

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

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

会員表

会員番号 氏名 生年 電話番号 郵便番号 住所
K20001 山田太郎 1960 03-3811-XXXX 112-0003 東京都文京区...
K20002 中村二郎 1953 03-3235-YYYY 160-0022 東京都新宿区
... ... ... ... ... ...

販売表

伝票番号 販売日 会員番号 販売額
D0000001 2016-10-02 K20002 4200
D0000002 2016-10-02 K40027 2000
D0000003 2016-10-02 K20004 12700
... ... ... ...

販売明細表

伝票番号 商品番号 個数
D0000001 S1000001 30
D0000001 S1000002 10
D0000002 S3000001 1
... ... ...

商品表

商品番号 分類 商品名 単価
S1000001 コーヒー ブラックコーヒー200mL 100
S1000002 コーヒー エスプレッソ200mL 120
S3000001 ジュース リンゴジュース1L 12本 2000
... ... ... ...

設問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 解説

平成29年度秋基本情報技術者試験午後過去問3 データベース

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 解説

平成29年度秋基本情報技術者試験午後過去問3 データベース

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

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

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

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

設問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 に関する解答群

b1 b2 b3
SUM(単価 * 個数) 商品表.単価, 販売明細表.個数 年齢, 分類
SUM(単価 * 個数) 商品表.単価, 販売明細表.個数 年齢, 分類, 合計販売額
SUM(販売額) 販売表.販売額 年齢, 分類
SUM(販売額) 販売表.販売額 年齢, 分類, 合計販売額
SUM(販売時単価 * 個数) 販売明細表.販売時単価, 販売明細表.個数 年齢, 分類
SUM(販売時単価 * 個数) 販売明細表.販売時単価, 販売明細表.個数 年齢, 分類, 合計販売額

設問3 解説

平成29年度秋基本情報技術者試験午後過去問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 に関する解答群

c1 c2
1 2
1 3
2 2
2 3

設問4 解説

平成29年度秋基本情報技術者試験午後過去問3 データベース

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

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

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

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

PR広告

フェイスブックコメント

平成28年度秋 基本情報技術者試験 午後 テキスト・動画解説

平成28年度秋 基本情報技術者試験 午前 テキスト・動画解説

平成28年度春 基本情報技術者試験 午後 テキスト・動画解説

平成28年度春 基本情報技術者試験 午前 テキスト・動画解説

平成27年度秋 基本情報技術者試験 午後 テキスト・動画解説

平成27年度春 基本情報技術者試験 午後 テキスト・動画解説

平成27年度春 基本情報技術者試験 午前 テキスト・動画解説

平成26年度秋 基本情報技術者試験 午前 テキスト・動画解説

平成26年度春 基本情報技術者試験 午前 テキスト・動画解説