AP過去問 令和7年度春期 午後 問6 データベース
AP 過去問題 午後に戻る。
AP過去問_令和7年度春期_午後_問5_ネットワークの同じ回の前の問題へ移動。
AP過去問_令和7年度春期_午後_問7_組込みシステム開発の同じ回の次の問題へ移動。
AP過去問_令和6年度秋期_午後_問6_データベースの前の回の同じカテゴリの問題へ移動。
令和7年度春期 午後 問6 データベース(AIプロンプト向け)
■販売管理システムの構築に関する次の記述を読んで、設問に答えよ。
H社は、全国の店舗及びインターネットで釣り具の販売を行っている。これまで販売促進のためにキャンペーンを定期的に実施してきたが、売上に全く結びつかないものもあった。そこで、キャンペーンの機能を強化し、さらにその効率を上げるために、既存の販売管理システムを改修した新しい販売管理システム(以下、新システムという)を構築することになった。
〔キャンペーンの概要〕
キャンペーンとは、2週間~3か月間の定められた期間、幾つかの商品に安い売価を設定することで、新規会員の獲得や、他の商品もー緒に購入してもらうことによる売上の向上を目指す活動である。新システムで提供するキャンペーンを広報する手段は4種類あり、種別と呼ばれる英字1字で分類される。新システムで提供するキャンペーン種別について、表1に示す。
<div><div class="table-container"><div class="table-header"><span class="table-title">表1 新システムで提供するキャンペーン種別</span><span class="table-unit"></span></div>
<table border="0" width="100%" style="border-collapse: collapse;border-style: solid">
<tr>
<td align="center" style="border: 2px solid; width: 5em;">種別</td>
<td align="center" style="border: 2px solid;">種別名</td>
<td align="center" style="border: 2px solid;">概要</td>
</tr>
<tr>
<td align="center" style="border: 2px solid;">H</td>
<td align="center" style="border: 2px solid;">葉書</td>
<td align="center" style="border: 2px solid;">キャンペーンについて、特定の会員に葉書で案内する。全国の店舗及びインターネットでの販売が対象の場合、直近1年以内に購入した実績がある会員に案内する。特定の店舗が対象の場合、店舗ごとに決められた範囲(以下、店舗エリアという)に会員に案内する。店舗エリアは、その店舗に来客が見込まれる会員の居住範囲を複数の郵便番号で定めた範囲として定義する。店舗エリアの範囲は、最初に対象店舗の5km以内の地域に該当する郵便番号を登録し、定期的に見直す。</td>
</tr>
<tr>
<td align="center" style="border: 2px solid;">E</td>
<td align="center" style="border: 2px solid;">電子メール</td>
<td align="center" style="border: 2px solid;">キャンペーンについて、全ての会員に電子メールで案内する。案内の内容は、会員の住所や購入実績に応じてカスタマイズする。</td>
</tr>
<tr>
<td align="center" style="border: 2px solid;">B</td>
<td align="center" style="border: 2px solid;">バナー広告</td>
<td align="center" style="border: 2px solid;">釣り情報のWebサイトやソーシャルメディアにバナー広告を表示して、キャンペーンをアピールする。</td>
</tr>
<tr>
<td align="center" style="border: 2px solid;">L</td>
<td align="center" style="border: 2px solid;">リスティング広告(検索連動型広告)</td>
<td align="center" style="border: 2px solid;">Webサイトの検索エンジンにおいて、釣り関連のキーワードを入力した際、検索結果にURLリンク付きのテキストを表示して、キャンペーンをアピールする。</td>
</tr>
</table>
</div>
</div>
各キャンペーンには、目標となる複数の指標と予算を設定し、その実施を複数回に分けて行うことがある。その予算を各種別に配賦する割合を変えることによって目標達成率を上げる工夫が求められる。
バナー広告とリスティング広告のキャンペーン(以下、Webキャンペーンという)は、キャンペーン単位で広告内容と予算金額を設定してから、Webマーケディングを運営するT社に実施の詳細を委託する。T社からは、広告内容として設定した広告表示キーワードごとに、その広告費用や表示回数、クリック数などの実績を週次で集計したレポートが送付される。
新システムのE-R図(抜粋)を図1に示す。なお、新システムは、E-R図のエンティティ名を表名にし、属性名を列名にして、適切なデータ型で表定義した関係データベースによって、データを管理する。
図1 新システムのE-R図(抜粋)ここから
注記 属性名の実線の下線<u> </u>は主キー、破線の下線<span style="text-decoration: underline dashed;"> </span>は外部キーを示す。主キーの実線が付いている属性名には、外部キーの破線を付けない。
テーブル名=店舗エリア詳細
店舗エリア詳細テーブルの列名=<u>店舗エリア番号</u>
店舗エリア詳細テーブルの列名=[ a ]
店舗エリア詳細テーブルと店舗エリアテーブルの関係は多対1
テーブル名=店舗エリア
店舗エリアテーブルの列名=<u>店舗エリア番号</u>
店舗エリアテーブルの列名=店舗エリア名
店舗エリアテーブルと店舗テーブルの関係は1対1
テーブル名=店舗
店舗テーブルの列名=<u>店舗番号</u>
店舗テーブルの列名=<span style="text-decoration: underline dashed;">店舗エリア番号</span>
店舗テーブルの列名=店舗郵便番号
店舗テーブルの列名=店舗住所
テーブル名=会員
会員テーブルの列名=<u>会員番号</u>
会員テーブルの列名=会員郵便番号
会員テーブルの列名=会員住所
会員テーブルの列名=会員メールアドレス
会員テーブルと売上テーブルの関係は1対多
テーブル名=売上
売上テーブルの列名=<u>売上番号</u>
売上テーブルの列名=<span style="text-decoration: underline dashed;">店舗番号</span>
売上テーブルの列名=売上年月日
売上テーブルの列名=<span style="text-decoration: underline dashed;">会員番号</span>
売上テーブルの列名=<span style="text-decoration: underline dashed;">キャンペーン番号</span>
売上テーブルの列名=キャンペーン種別
店舗テーブルと売上テーブルの関係は1対多
売上テーブルと売上明細テーブルの関係は1対多
テーブル名=売上明細
売上明細テーブルの列名=<u>売上番号</u>
売上明細テーブルの列名=<u>売上明細番号</u>
売上明細テーブルの列名=<span style="text-decoration: underline dashed;">商品番号</span>
売上明細テーブルの列名=商品個数
売上明細テーブルの列名=商品単価
キャンペーンテーブルと売上テーブルの関係は1対多
テーブル名=キャンペーン
キャンペーンテーブルの列名=<u>キャンペーン番号</u>
キャンペーンテーブルの列名=キャンペーン名
キャンペーンテーブルの列名=目標会員獲得数
キャンペーンテーブルの列名=目標売上向上金額
キャンペーンテーブルの列名=目標利益向上金額
キャンペーンテーブルの列名=予算金額
キャンペーンテーブルの列名=開始年月日
キャンペーンテーブルの列名=終了年月日
テーブル名=商品
商品テーブルの列名=<u>商品番号</u>
商品テーブルの列名=商品名
商品テーブルの列名=商品種別
商品テーブルの列名=商品標準売価
商品テーブルの列名=商品原価
商品テーブルと売上明細テーブルの関係は1対多
キャンペーンテーブルとキャンペーン実施テーブルの関係は1対多
テーブル名=キャンペーン実施
キャンペーン実施テーブルの列名=<u>キャンペーン番号</u>
キャンペーン実施テーブルの列名=<u>キャンペーン実施番号</u>
キャンペーン実施テーブルの列名=キャンペーン種別
キャンペーン実施テーブルの列名=キャンペーン実施金額
キャンペーン実施テーブルの列名=開始年月日
キャンペーン実施テーブルの列名=終了年月日
キャンペーンテーブルとキャンペーン商品テーブルの関係は1対多
テーブル名=キャンペーン商品
キャンペーン商品テーブルの列名=<u>キャンペーン番号</u>
キャンペーン商品テーブルの列名=<u>キャンペーン商品番号</u>
キャンペーン商品テーブルの列名=キャンペーン商品売価
商品テーブルとキャンペーン商品テーブルの関係は[ b ]
図1 新システムのE-R図(抜粋)ここまで
〔キャンペーン種別ごとの実績の集計〕
新しいキャンペーンを計画するために、指定したキャンペーンについて、キャンペーン種別ごとの実績として、売上、原価、粗利、実施金額を集計するSQL文を図2に示す。ここで、❝:キャンペーン番号❞は指定したキャンペーン番号を表す埋込み変数である。なお、関数COALESCE(A, B)は、AがNULLでないときはAを、AがNULLのときはBを返す。
図2 キャンペーン種別ごとの実績を集計するSQL文 ここから
SELECT CK.キャンペーン種別, COALESCE(UK.売上計, 0) AS 売上計,
COALESCE(UK.原価計, 0) AS 原価計,
COALESCE(UK.売上計, 0) - COALESCE(UK.原価計, 0) AS 粗利計,
CK.キャンペーン実施金額計
[ c ]
(SELECT キャンペーン種別, SUM(キャンペーン実施金額) AS キャンペーン実施金額計
FROM キャンペーン実施
WHERE キャンペーン番号 = :キャンペーン番号
GROUP BY キャンペーン種別) CK
[ d ]
(SELECT U.キャンペーン種別, SUM(M.商品個数 * M.商品単価) AS 売上計,
[ e ] AS 原価計
FROM 売上 U
INNER JOIN 売上明細 M ON U.売上番号 = M.売上番号
INNER JOIN 商品 S ON M.商品番号 = S.商品番号
WHERE U.キャンペーン番号 = :キャンペーン番号
GROUP BY U.キャンペーン種別) UK
ON CK.キャンペーン種別 = UK.キャンペーン種別
図2 キャンペーン種別ごとの実績を集計するSQL文 ここまで
〔葉書を送る店舗エリアの見直し〕
葉書を送る対象となる店舗エリアを、葉書キャンペーンによる会員の購入実績を基に見直すことを考える。対象の店舗における過去1年間に葉書キャンペーンで購入した会員の郵便番号ごとの売上実績数を、売上実績数の降順に出力するSQL文を図3に示す。ここで、❝:店舗番号❞は指定した店舗番号を、❝:一年前年月日❞は現在から1年前の年月日を表す埋込み変数である。
図3 売上実績数を降順に出力するSQL文 ここから
SELECT K.会員郵便番号, COUNT(*) AS 売上実績数
FROM 売上 U INNER JOIN 会員 K ON U.会員番号 = K.会員番号
WHERE U.店舗番号 = :店舗番号
AND U.売上年月日 >= :一年前年月日
[ f ]
GROUP BY K.会員郵便番号
[ g ]
図3 売上実績数を降順に出力するSQL文 ここまで
[Webキャンペーン内容の定期的な見直しの検討〕
Webキャンペーンの効果をより高めるために、T社から送付されるレポートを基に、キャンペーン期間中に広告内容を適宜変更する依頼をT社に出すことを考える。Webキャンペーンの実績を記録するために追加した表の構造を図4に示す。
図4 Webキャンペーンの実績を記録するために追加した表の構造 ここから
Webキャンペーン実績(<u>キャンペーン番号</u>, <u>キャンペーン実施番号</u>,
キャンペーン種別, <u>広告番号</u>, <u>実績年月日</u>, <u>広告表示位置</u>,
<u>キーワード</u>, 広告費用, 表示回数, クリック数, 会員登録数)
注記 下線は主キーを示す。
図4 Webキャンペーンの実績を記録するために追加した表の構造 ここまで
レビューを実施したところ、Web キャンペーン実績表は日次で実績を集計する想定になってしまっているので見直す必要がある、との指摘を受けた。
その後、<u>①受けた指摘に対応する</u>ことで、目的の機能を実装することができた。
設問1 図1中の[ a ][ b ]に入れる適切な属性名およびエンティティ間の関連を答え、E-R図を完成させよ。なお、属性名及びエンティティ間の関連の表記は、図1の凡例に倣うこと。
設問2 図2中の[ c ]~[ e ]に入れる適切な字句を答えよ。
設問3 図3中の[ f ]、[ g ]に入れる適切な字句を答えよ。
設問4 本文中の下線①について、対応した内容を35字以内で答えよ。
令和7年度春期 午後 問6 データベース(問題原文)
■販売管理システムの構築に関する次の記述を読んで、設問に答えよ。
H社は、全国の店舗及びインターネットで釣り具の販売を行っている。これまで販売促進のためにキャンペーンを定期的に実施してきたが、売上に全く結びつかないものもあった。そこで、キャンペーンの機能を強化し、さらにその効率を上げるために、既存の販売管理システムを改修した新しい販売管理システム(以下、新システムという)を構築することになった。
〔キャンペーンの概要〕
キャンペーンとは、2週間~3か月間の定められた期間、幾つかの商品に安い売価を設定することで、新規会員の獲得や、他の商品もー緒に購入してもらうことによる売上の向上を目指す活動である。新システムで提供するキャンペーンを広報する手段は4種類あり、種別と呼ばれる英字1字で分類される。新システムで提供するキャンペーン種別について、表1に示す。
種別 | 種別名 | 概要 |
H | 葉書 | キャンペーンについて、特定の会員に葉書で案内する。全国の店舗及びインターネットでの販売が対象の場合、直近1年以内に購入した実績がある会員に案内する。特定の店舗が対象の場合、店舗ごとに決められた範囲(以下、店舗エリアという)に会員に案内する。店舗エリアは、その店舗に来客が見込まれる会員の居住範囲を複数の郵便番号で定めた範囲として定義する。店舗エリアの範囲は、最初に対象店舗の5km以内の地域に該当する郵便番号を登録し、定期的に見直す。 |
E | 電子メール | キャンペーンについて、全ての会員に電子メールで案内する。案内の内容は、会員の住所や購入実績に応じてカスタマイズする。 |
B | バナー広告 | 釣り情報のWebサイトやソーシャルメディアにバナー広告を表示して、キャンペーンをアピールする。 |
L | リスティング広告(検索連動型広告) | Webサイトの検索エンジンにおいて、釣り関連のキーワードを入力した際、検索結果にURLリンク付きのテキストを表示して、キャンペーンをアピールする。 |
各キャンペーンには、目標となる複数の指標と予算を設定し、その実施を複数回に分けて行うことがある。その予算を各種別に配賦する割合を変えることによって目標達成率を上げる工夫が求められる。
バナー広告とリスティング広告のキャンペーン(以下、Webキャンペーンという)は、キャンペーン単位で広告内容と予算金額を設定してから、Webマーケディングを運営するT社に実施の詳細を委託する。T社からは、広告内容として設定した広告表示キーワードごとに、その広告費用や表示回数、クリック数などの実績を週次で集計したレポートが送付される。
新システムのE-R図(抜粋)を図1に示す。なお、新システムは、E-R図のエンティティ名を表名にし、属性名を列名にして、適切なデータ型で表定義した関係データベースによって、データを管理する。
図1 新システムのE-R図(抜粋)
〔キャンペーン種別ごとの実績の集計〕
新しいキャンペーンを計画するために、指定したキャンペーンについて、キャンペーン種別ごとの実績として、売上、原価、粗利、実施金額を集計するSQL文を図2に示す。ここで、❝:キャンペーン番号❞は指定したキャンペーン番号を表す埋込み変数である。なお、関数COALESCE(A, B)は、AがNULLでないときはAを、AがNULLのときはBを返す。
SELECT CK.キャンペーン種別, COALESCE(UK.売上計, 0) AS 売上計,
COALESCE(UK.原価計, 0) AS 原価計,
COALESCE(UK.売上計, 0) - COALESCE(UK.原価計, 0) AS 粗利計,
CK.キャンペーン実施金額計
c
(SELECT キャンペーン種別, SUM(キャンペーン実施金額) AS キャンペーン実施金額計
FROM キャンペーン実施
WHERE キャンペーン番号 = :キャンペーン番号
GROUP BY キャンペーン種別) CK
d
(SELECT U.キャンペーン種別, SUM(M.商品個数 * M.商品単価) AS 売上計,
e AS 原価計
FROM 売上 U
INNER JOIN 売上明細 M ON U.売上番号 = M.売上番号
INNER JOIN 商品 S ON M.商品番号 = S.商品番号
WHERE U.キャンペーン番号 = :キャンペーン番号
GROUP BY U.キャンペーン種別) UK
ON CK.キャンペーン種別 = UK.キャンペーン種別
図2 キャンペーン種別ごとの実績を集計するSQL文
〔葉書を送る店舗エリアの見直し〕
葉書を送る対象となる店舗エリアを、葉書キャンペーンによる会員の購入実績を基に見直すことを考える。対象の店舗における過去1年間に葉書キャンペーンで購入した会員の郵便番号ごとの売上実績数を、売上実績数の降順に出力するSQL文を図3に示す。ここで、❝:店舗番号❞は指定した店舗番号を、❝:一年前年月日❞は現在から1年前の年月日を表す埋込み変数である。
SELECT K.会員郵便番号, COUNT(*) AS 売上実績数
FROM 売上 U INNER JOIN 会員 K ON U.会員番号 = K.会員番号
WHERE U.店舗番号 = :店舗番号
AND U.売上年月日 >= :一年前年月日
f
GROUP BY K.会員郵便番号
g
図3 売上実績数を降順に出力するSQL文
[Webキャンペーン内容の定期的な見直しの検討〕
Webキャンペーンの効果をより高めるために、T社から送付されるレポートを基に、キャンペーン期間中に広告内容を適宜変更する依頼をT社に出すことを考える。Webキャンペーンの実績を記録するために追加した表の構造を図4に示す。
Webキャンペーン実績(キャンペーン番号, キャンペーン実施番号,
キャンペーン種別, 広告番号, 実績年月日, 広告表示位置,
キーワード, 広告費用, 表示回数, クリック数, 会員登録数)
注記 下線は主キーを示す。
図4 Webキャンペーンの実績を記録するために追加した表の構造
レビューを実施したところ、Web キャンペーン実績表は日次で実績を集計する想定になってしまっているので見直す必要がある、との指摘を受けた。
その後、①受けた指摘に対応することで、目的の機能を実装することができた。
設問1 図1中の a 、b に入れる適切な属性名およびエンティティ間の関連を答え、E-R図を完成させよ。なお、属性名及びエンティティ間の関連の表記は、図1の凡例に倣うこと。
設問2 図2中の c ~ e に入れる適切な字句を答えよ。
設問3 図3中の f 、g に入れる適切な字句を答えよ。
設問4 本文中の下線①について、対応した内容を35字以内で答えよ。
回答・解説
設問1
問題文が短めなのは好印象の問題ですね。最初に問題を読まなくても、設問に取り掛かれるレベル。おなじみのE-R図の穴埋めですね。いつも4問くらいあるのに2問しかないのは配点比重大きそう。間違えられない感はある。
でも、簡単な穴埋めだったね。
aは店舗エリア詳細テーブルの店舗エリア番号キーと合わせて管理したいような列名を探す旅です。
この謎は葉書キャンペーンの概要に詰め込まれています。店舗エリアは、その店舗に来客が見込まれる会員の居住範囲を複数の郵便番号で定めた範囲として定義する。店舗エリアの範囲は、最初に対象店舗の5km以内の地域に該当する郵便番号を登録し、定期的に見直す。という店舗に関する情報がこのあたりに書かれています。なるほど店舗ごとに葉書を送付するエリアを管理するんだ。と読み取ることができます。が、図の方をみてみると各店舗のテーブルにはその送付するエリアを管理できる仕組みがどこにもない。これは店舗エリア詳細で、その店舗が葉書を送付するエリアの情報を持たないといけないことがわかります。店舗テーブルに紛らわしく、店舗郵便番号というのがありますが、これでは顧客のエリアを把握できません。店舗自身の郵便番号は葉書の送付に役立ちません。そうすると、店舗エリア詳細では会員テーブルにある会員郵便番号と同じ意味の属性名、列名を保持していればいいということになります。
したがって
a 郵便番号
が答えです。
会員テーブルに倣って、会員郵便番号と書いても満点だと思います。飛躍して、配送範囲郵便番号とか好き勝手に適切な修飾があっても満点だと思います。ま、余計な事せずに郵便番号でいいでしょう。主キーや外部キーではないので、下線や破線アンダーラインにする必要はないです。これがあると減点されちゃうでしょうね。文字列の下になんらかの線を引いてると減点でしょう。あー、でもTACさんの回答だと、下線引いてるわ。主キーにすべきなのか。そうなんだ。なら減点はないか。むしろ下線がないと減点もありえるのか。う~ん。なんで主キーにしなきゃいけないんだろう。保留ですね。まぁ、運用に差し支えないし、問題文でそのあたりの制約には触れられてないので、どっちでもいいんだろうね。GeminiもGPTも、いろいろ話し合った結果、結局どっちでもいいって言ってました。ただIPAがどういう狙いをもって作ったかは謎のままになるでしょうと言ってました。ふふふ。
bは商品テーブルとキャンペーン商品テーブルの関係について、1対1、多対1、1対多、多対多なのか考える問題です。1つ商品が時期を変えて、複数のキャンペーンで参照されることはありそうですね。一つのキャンペーンが複数の商品を対象にすることもありえそうです。多対多が答えでもおかしくないですね。TACさんはなぜか、1対多だっていってますね。とりあえず、管理人の見解としては、多対多にしときますね。ちなみに管理人は1対多だと思ってしまいました。うっかりしたな。多対多だな。Geminiは多対多って言ってました。GPTは最初、多対1とか言ってましたが、ありゃ1対多か?いやいや多対多だなみたいな感じ。
したがって
←→
が答えです。
まぁ多対多だろうな。
設問2
SQL句は以下のような構造になっています。再度確認しましょう。
SELECT
CK.キャンペーン種別,
COALESCE(UK.売上計, 0) AS 売上計,
COALESCE(UK.原価計, 0) AS 原価計,
COALESCE(UK.売上計, 0) - COALESCE(UK.原価計, 0) AS 粗利計,
CK.キャンペーン実施金額計
[ c ]
(
SELECT
キャンペーン種別,
SUM(キャンペーン実施金額) AS キャンペーン実施金額計
FROM
キャンペーン実施
WHERE
キャンペーン番号 = :キャンペーン番号
GROUP BY
キャンペーン種別
) CK
[ d ]
(
SELECT
U.キャンペーン種別,
SUM(M.商品個数 * M.商品単価) AS 売上計,
[ e ] AS 原価計
FROM
売上 U
INNER JOIN
売上明細 M ON U.売上番号 = M.売上番号
INNER JOIN
商品 S ON M.商品番号 = S.商品番号
WHERE
U.キャンペーン番号 = :キャンペーン番号
GROUP BY
U.キャンペーン種別
) UK
ON
CK.キャンペーン種別 = UK.キャンペーン種別
こんな感じ。cとdは副問合せ、外部結合、内部結合ですね。
cの部分はON句がないので、外部結合でも内部結合でもないので、副問合せです。なので、FROMですね。
したがって
c FROM
が答えです。SQLの構造を分割して眺めれる能力がないと答えられませんが、簡単な問題でした。ON句がないからFROMといいましたが、まだ一つも表を参照するFROMがこれより前にないので、外部結合や内部結合のやりようがないので、最初の表参照が副問合せになっているFROMというだけでしたね。ちなみにデータベース素人の管理人は、長すぎる。なんだこれってなっちゃって答えられませんでした。cとd、死にました。試験が終わった今、冷静に読み解くと上記のように分割できることに気づきますね。でも、eは答えれたね。ピーンと来ましたから。
dは最後にON句による結合規則が設定されているので、内部結合か外部結合がなされていますね。
実際に動かしたイメージで結合を考えてみましょう。本当は、頭の中だけで以下のような表があるとイメージできないと駄目なんですけどね。コツコツ、それぞれのサンプル表を作ってみましょうか。
キャンペーン番号 | キャンペーン実施番号 | キャンペーン種別 | キャンペーン実施金額 | 開始年月日 | 終了年月日 |
---|---|---|---|---|---|
CAM001 | 1 | H | 10000 | 2025-06-01 | 2025-06-15 |
CAM001 | 2 | E | 15000 | 2025-06-01 | 2025-06-15 |
CAM001 | 3 | B | 20000 | 2025-06-01 | 2025-06-15 |
CAM002 | 1 | L | 12000 | 2025-07-01 | 2025-07-15 |
売上番号 | 店舗番号 | 売上年月日 | 会員番号 | キャンペーン番号 | キャンペーン種別 |
---|---|---|---|---|---|
UR001 | SHOP01 | 2025-06-05 | MEM001 | CAM001 | H |
UR002 | NET01 | 2025-06-08 | MEM002 | CAM001 | E |
UR003 | SHOP02 | 2025-06-12 | MEM003 | CAM001 | H |
UR004 | NET01 | 2025-07-03 | MEM004 | CAM002 | L |
UR005 | SHOP01 | 2025-07-07 | MEM005 | CAM002 | B |
売上番号 | 売上明細番号 | 商品番号 | 商品個数 | 商品単価 |
---|---|---|---|---|
UR001 | 1 | PROD01 | 1 | 1500 |
UR002 | 1 | PROD02 | 2 | 800 |
UR003 | 1 | PROD01 | 1 | 1600 |
UR004 | 1 | PROD03 | 1 | 2500 |
UR005 | 1 | PROD04 | 1 | 1000 |
商品番号 | 商品名 | 商品種別 | 商品標準売価 | 商品原価 |
---|---|---|---|---|
PROD01 | 竿A | 釣竿 | 2000 | 1200 |
PROD02 | リールB | リール | 1000 | 600 |
PROD03 | ルアーC | ルアー | 3000 | 1800 |
PROD04 | 仕掛けD | 小物 | 1200 | 700 |
このように定義したサンプルテーブルの中でCKテーブルは以下のようになります。埋め込み変数 :キャンペーン番号は、仮に作ったIDですが、ここではCAM001を指定したと想定しています。
キャンペーン種別 | キャンペーン実施金額計 |
---|---|
H | 10000 |
E | 15000 |
B | 20000 |
ここではサンプルが少ないテーブルなので各キャンペーン種別が一個ずつしかないので、合計した感じまでは出せませんでしたが、キャンペーン実施テーブルから、キャンペーン種別、SUM(キャンペーン実施金額)をキャンペーン実施金額計という名前で列を作り、キャンペーン種別ごとにグループ化した内容が抽出されるのがCKテーブルです。
さて、次にUKテーブルがどんなものか描きたいところですが、先にeの穴埋めを考えないとUKテーブルを出力するのは無理です。eの後ろにはAS 原価計とありますので、この列にはキャンペーン種別ごとの原価の合計を表示したいようです。原価は商品テーブルに格納されているので、商品テーブルの商品原価という列と売上げた商品個数を売上明細との積をとり、合計するとよいです。
都合がいいことに商品テーブルはSテーブルとして、売上明細はMテーブルとして外部結合されているので、これを利用するとよいです。
したがって
e SUM(M.商品個数 * S.商品原価)
が答えです。
都合がいいことにと書きましたが、穴埋めしやすいようになっているので、当然といえば当然なわけです。表が読み込まれているのは当たり前なんですね。
するとUKテーブルは以下のようなものが出力されることが想像できます。
キャンペーン種別 | 売上計 | 原価計 |
---|---|---|
H | 3100 | 2400 |
E | 1600 | 1200 |
売上テーブルの売上番号と売上明細の売上番号が一致しているものと 売上明細テーブルの商品番号と商品テーブルの商品番号が一致しているものがそろった以下のようなテーブルができあがって
U.売上番号 | U.店舗番号 | U.売上年月日 | U.会員番号 | U.キャンペーン番号 | U.キャンペーン種別 | M.売上明細番号 | M.商品番号 | M.商品個数 | M.商品単価 | S.商品番号 | S.商品名 | S.商品種別 | S.商品標準売価 | S.商品原価 |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
UR001 | SHOP01 | 2025-06-05 | MEM001 | CAM001 | H | 1 | PROD01 | 1 | 1500 | PROD01 | 竿A | 釣竿 | 2000 | 1200 |
UR002 | NET01 | 2025-06-08 | MEM002 | CAM001 | E | 1 | PROD02 | 2 | 800 | PROD02 | リールB | リール | 1000 | 600 |
UR003 | SHOP02 | 2025-06-12 | MEM003 | CAM001 | H | 1 | PROD01 | 1 | 1600 | PROD01 | 竿A | 釣竿 | 2000 | 1200 |
UR004 | NET01 | 2025-07-03 | MEM004 | CAM002 | L | 1 | PROD03 | 1 | 2500 | PROD03 | ルアーC | ルアー | 3000 | 1800 |
UR005 | SHOP01 | 2025-07-07 | MEM005 | CAM002 | B | 1 | PROD04 | 1 | 1000 | PROD04 | 仕掛けD | 小物 | 1200 | 700 |
上記のテーブルのキャンペーン番号CAM001と一致するものをキャンペーン種別ごとに商品個数*売上と商品個数*原価を合計したものがUKテーブルです。このサンプルのミソはキャンペーン実施テーブルにある全てのキャンペーン種別が売り上げに存在するとは限らないよという、うまい例になっていて、ここではキャンペーンBによる売上がないので、HとEだけになってるんですね。
問題では実施したキャンペーンの種別は全部表示したいのでHとEとBのそれぞれの売上合計と原価合計と売上と原価の差の合計とキャンペーン実施金額を表示したいと言っています。CKとUKでCKにだけあってUKにない場合もNULLを0として集計する準備もできています。
このことを実現できるdの結合方法はLEFT OUTER JOINです。CK LEFT OUTER JOIN UKで左が全て残る。外部結合だからと覚えましょう。ON CK.キャンペーン種別 = UK.キャンペーン種別でもON UK.キャンペーン種別 = CK.キャンペーン種別となっていてもCKに関するテーブル情報が先に書かれているので左はCKになります。ON の条件の書き順と左右は関係ないです。あとRIGHT JOINってあんまり出てこないのは、今回のように主となる情報のクエリを先に書くからLEFT JOINが多くなります。INNER JOINの方が多くて、その次にLEFT JOINって感じだね。RIGHT JOINはたまにそうなっちゃうくらいの感じだね。試験では理解できてるか試してくる可能性もあるので一応覚えておこう。
したがって
d LEFT OUTER JOIN
が答えです。
LEFT OUTER JOINはLEFT JOINと書いても同じことなので、こちらでも満点です。
ちなみにサンプルのデータだと以下のような結果になります。
キャンペーン種別 | 売上計 | 原価計 | 粗利計 | キャンペーン実施金額計 |
---|---|---|---|---|
H | 3100 | 2400 | 700 | 10000 |
E | 1600 | 1200 | 400 | 15000 |
B | 0 | 0 | 0 | 20000 |
試験の中でここまで想像するのは難しいです。感覚的に言うと、今回の問題のように列の抽出でNULL対策で0に置き換えるようなウィンドウ関数と呼ばれる関数での処理がある場合は外部結合が関わってきているなという匂いはありますよね。そこからどこで外部結合をしようとしているのか、ON句のキャンペーン種別の多い、少ないが発生しないかを確認してLEFT JOINに行き着く感じになるでしょうか。まぁでも完全に読み切るには時間がかかるっすね。
設問3
設問2が難しかったからといって解けないとは限らないのがSQL。よく考えれば、この問題だけでも解ける。しぶとく食らいついていきましょう。切り替えが大事です。葉書を送るエリアの売上実績数を降順に出力SQLの問題です。ってこのように言ってくれると、すぐ解けるんですけどね。そういう問題だと気づくのに5分くらいかかる場合もある。
ま、簡単な問題の穴埋めではありますが、回りくどく実際のサンプルを用いて、描画してみましょう!
売上番号 | 店舗番号 | 売上年月日 | 会員番号 | キャンペーン番号 | キャンペーン種別 |
---|---|---|---|---|---|
UR001 | SHOP01 | 2025-06-05 | MEM001 | CAM001 | H |
UR002 | NET01 | 2025-06-08 | MEM002 | CAM001 | E |
UR003 | SHOP01 | 2025-06-12 | MEM001 | CAM001 | H |
UR004 | SHOP01 | 2025-07-03 | MEM001 | CAM002 | H |
UR005 | SHOP01 | 2025-07-07 | MEM005 | CAM002 | B |
UR006 | SHOP01 | 2024-05-15 | MEM001 | CAM999 | H |
UR007 | SHOP01 | 2025-08-01 | MEM001 | CAM003 | H |
会員番号 | 会員郵便番号 | 会員住所 | 会員メールアドレス |
---|---|---|---|
MEM001 | 123-4567 | 東京都〇〇区 | mem001@example.com |
MEM002 | 876-5432 | 大阪府△△市 | mem002@example.com |
MEM003 | 987-6543 | 東京都□□町 | mem003@example.com |
MEM004 | 456-7890 | 神奈川県☆☆市 | mem004@example.com |
MEM005 | 567-8901 | 大阪府××町 | mem005@example.com |
という具合にこんな表があったとしたら、会員番号で内部結合した表は次のようになります。
U.売上番号 | U.店舗番号 | U.売上年月日 | U.会員番号 | U.キャンペーン番号 | U.キャンペーン種別 | K.会員番号 | K.会員郵便番号 | K.会員住所 | K.会員メールアドレス |
---|---|---|---|---|---|---|---|---|---|
UR001 | SHOP01 | 2025-06-05 | MEM001 | CAM001 | H | MEM001 | 123-4567 | 東京都〇〇区 | mem001@example.com |
UR002 | NET01 | 2025-06-08 | MEM002 | CAM001 | E | MEM002 | 876-5432 | 大阪府△△市 | mem002@example.com |
UR003 | SHOP01 | 2025-06-12 | MEM001 | CAM001 | H | MEM001 | 123-4567 | 東京都〇〇区 | mem001@example.com |
UR004 | SHOP01 | 2025-07-03 | MEM001 | CAM002 | H | MEM001 | 123-4567 | 東京都〇〇区 | mem001@example.com |
UR005 | SHOP01 | 2025-07-07 | MEM005 | CAM002 | B | MEM005 | 567-8901 | 大阪府××町 | mem005@example.com |
UR006 | SHOP01 | 2024-05-15 | MEM001 | CAM999 | H | MEM001 | 123-4567 | 東京都〇〇区 | mem001@example.com |
UR007 | SHOP01 | 2025-08-01 | MEM001 | CAM003 | H | MEM001 | 123-4567 | 東京都〇〇区 | mem001@example.com |
対象の店舗における過去1年間に葉書キャンペーンで購入した会員の郵便番号ごとの売上実績数を、売上実績数の降順に出力するSQLを作成しなさいと言っているので、葉書キャンペーンでの売上に絞る必要もありますが、問題のSQLでは、条件として記述されていないので、この条件を記述することが穴埋めのfの問題で降順に出力する指定もないので、それがgの問題だということです。
上記サンプルの表において、埋め込み変数 :店舗番号が 'SHOP01'、埋め込み変数 :一年前年月日が '2025-05-12' だとして、穴埋めのSQLが無い状態で実行すると以下のようなSQLと結果が得られます。
SELECT K.会員郵便番号, COUNT(*) AS 売上実績数
FROM 売上 U INNER JOIN 会員 K ON U.会員番号 = K.会員番号
WHERE U.店舗番号 = :店舗番号
AND U.売上年月日 >= :一年前年月日
GROUP BY K.会員郵便番号
会員郵便番号 | 売上実績数 |
---|---|
567-8901 | 1 |
123-4567 | 3 |
Hの葉書キャンペーンではないBのキャンペーンで2025-05-12以降の売上の売上番号UR005のデータが1件が検索対象になってしまい。売上実績数の列も昇順になってしまう可能性があります。今回の場合は無理やり昇順にしてる感じになってしまいましたが…
キャンペーン種別がHである条件をfに設定する必要があります。そしてORDER BY句を使って、売上実績数を降順に設定するものをgに設定する必要があります。
したがって
f AND U.キャンペーン種別 = 'H'
g ORDER BY 売上実績数 DESC
が答えです。
条件設定する方法やORDER BY句の使い方、降順はDESCと設定しなければいけないことを知らない場合は答えられない問題だったでしょう。e、f、gはやや簡単な問題だったような気がしますね。SQL素人の自分が簡単だと思うので間違いないかなと思う感覚です。気のせいかな。ちなみに正しいSQLなら以下のような結果になります。あんまりおもしろいサンプルではないですけど。手動で書いてる表だからね。
会員郵便番号 | 売上実績数 |
---|---|
123-4567 | 3 |
設問4
次の問題はやや何をいいたいのかわかりにくい設問でした。Web キャンペーン実績表は日次で実績を集計する想定になってしまっているので見直す必要がある。問題のSQLでは実績年月日で集計しているので、まさに指摘のとおりです。じゃあ、どういう感じ期間表示するべきかがまったく書かれていない。キャンペーン期間中の任意の期間の実績でいいのか、週次にすべきなのか?とかね。二つくらいかな、考えられる期間設定って。これを35字で説明します。
したがって、
1 5 10 11 20
実績年月日ではなく実 績週のような属性を追
加し週次集計ができる ようにする(35文字)
21 30 31 35
とか
集計の開始日と終了日 についての属性を追加し
集計できるようにする (30文字)
21 30 31 35
が答えです。
TACさんは週次集計を推してますね。まぁどっちでも正解なんじゃないのかなと思います。意味わかんねぇ問題だなとは思いました。週次のほうが思いつきやすい。特定の期間?どっち。どっちでもいい。そんな問題なのかなと思います。特定の期間の集計ならすんげぇありがたいけど、誰が集計プログラムをつくってデータをぶちこむのかな?とかそんな心配は無用なのかな。週次はよくある集計だけど、それが欲しいかどうかもよくわからないし。まぁなんでもありだな。特定の期間に設定できた方がうれしいけど、大変さはある。週次とかそんなやわな集計ならいらないわってなることもある。週次くらいでこの会社には十分だろって、安売りのソフトウェア会社や開発者は考える人が多いんだろうな。苦労したくないもんね。特定の期間で集計しようとした猛者の方を管理人は褒めたいね。ちなみに管理人は既にある属性でなんとかしろってことなのかなと思って、キャンペーン期間の開始と終了で集計するとかいうアホ集計を提案しましたので、0点だろうな。なんの役にもたたない集計。キャンペーン期間中に広告の効果を見たいっていってるのにね。ふふふ。でもキャンペーン開始日からの集計結果は見れるからそれでもいいじゃんね。よく考えたらそれでも部分点か満点でもよさそうだな。無理か。いいように考えすぎだな。つうか、午後の採点されてないかもしれないっつうね。午後の問題の回答解説の各所で自分の採点はどうなってるだろうとか、ほざいてますけど、午前で死んでる可能性もあります。自己採点しないことにしたので。つらい現実を受け止めるのは先延ばしにしました。いろいろ不安になるのも嫌だし。どっちのみち継続して応用情報の勉強はつづける覚悟だし。
AP過去問_令和7年度春期_午後_問5_ネットワークの同じ回の前の問題へ移動。
AP過去問_令和7年度春期_午後_問7_組込みシステム開発の同じ回の次の問題へ移動。
AP過去問_令和6年度秋期_午後_問6_データベースの前の回の同じカテゴリの問題へ移動。
AP 過去問題 午後に戻る。