今回も、令和7年度春期応用情報技術者試験の午後問6「データベース」 について解説します。
前回の続きとなっておりますので、まだご覧になられていない場合はまずこちらからどうぞ。
【応用情報】令和７年度春期（午後：問６ データベース）第１回 - ER図
この記事は、下記の方々を対象としております。
- 応用情報技術者試験を受験する、且つ午後にデータベースを選択する。
- データベーススペシャリスト試験を受験するが、応用情報レベルから復習したい。
- 開発エンジニアでデータモデリングやDB関連の設計を担当する、もしくは目指している。
試験問題の構成や難易度、SQLや設計のポイントを整理しました。
下記の内容を全４回に分けてご紹介しています。
- 第１回：設問１（ER図 - 複合主キー）
- 第２回：設問２（今回）（SQL - 結合と集計）
- 第３回：設問３（SQL - ソートと条件考察）
- 第４回：設問４（テーブル定義の変更）
設問2：結合と集計
※出典：令和7年度 春期 応用情報技術者試験 午後 問題冊子
構文の構成を完成させる（空欄：c,d）
前半のSQLは、サブクエリが2つ（CKとUK）存在し、それらから項目を取得していることが分かります。
構文をいくつかのブロックに分け、SQLのルールに則り下記のように考えられます。
空欄cに『FROM』が入るのは、SELECT文でテーブルから項目を取得する基本中の基本です。
空欄dは、画像でも記した通り候補がいくつかあります。
内部結合か？外部結合か？を判断する必要がありますが、今回はSQL文だけで判断できます。
まず、外部結合であることが分かります。
なぜなら、SELECT文でCOALESCE関数を使用しており、今回は内部結合にするとCOALESCE関数は必ず第１引数（下記画像ではA）しか取得しないので、COALESCE関数を使用する意味がないからです。
また、COALESCEの仕様により、CKが主体であるのが分かります。
SQL文の順番では、CKがUKより先に記載されています。
先に記載された方を主体にするのは、左外部結合（LEFT）なので、空欄dは『LEFT OUTER JOIN』 です。
原価の合計算出（空欄：e）
原価計を出すための構文を考えますが、すぐそばに売上計があるので、同じ理屈で考えられます。
売上計は商品個数×単価したものを合計します。
ということは、原価計は商品個数×原価したものを合計すれば良いのでは？
と、流用する思考ができれば簡単に解けます。
では、原価はどこから取得するのか？
上記画像の青で囲ったテーブルのいずれかに存在しますが、そもそもER図に原価は１つしかありませんので、すぐ見つかります。
商品テーブルの商品原価であることが分かったので、あとは売上計のマネをするだけです。
空欄eは『SUM(M.商品個数 * S.商品原価)』 です。
SQLを動かしてみよう
解説はしたものの、簡単に試せるのなら試した方が良いです！
PostgreSQLで書きますので、よろしければ試してみてください。
※簡単に試す方法はこちらの記事を参考にして下さい。
データサンプル
問題文には具体的なデータが無いので、私が考えたオリジナルをご紹介します。
まず、テーブル定義とデータです。
-- キャンペーン実施テーブル
CREATE TABLE キャンペーン実施 (
キャンペーン番号 CHAR(4)
, キャンペーン実施番号 INTEGER
, キャンペーン種別 CHAR(1)
, キャンペーン実施金額 NUMERIC
, 開始年月日 DATE
, 終了年月日 DATE
, PRIMARY KEY (キャンペーン番号 ,キャンペーン実施番号)
);
INSERT INTO キャンペーン実施 VALUES
('CP01',1,'L',30000,'2025/1/1','2025/12/31')
,('CP01',2,'H',70000,'2025/1/1','2025/12/31')
,('CP02',1,'H',50000,'2025/1/1','2025/12/31')
,('CP02',2,'L',40000,'2025/1/1','2025/12/31')
,('CP02',3,'H',20000,'2025/1/1','2025/12/31')
,('CP03',1,'H',15000,'2025/1/1','2025/12/31')
;
-- 商品テーブル（設問に必要な項目に限定）
CREATE TABLE 商品 (
商品番号 CHAR(4) PRIMARY KEY
, 商品原価 NUMERIC
);
INSERT INTO 商品 VALUES
('S001',30)
,('S002',50)
,('S003',100)
;
-- 売上テーブル
CREATE TABLE 売上 (
売上番号 CHAR(4) PRIMARY KEY
, 店舗番号 CHAR(4)
, 売上年月日 DATE
, 会員番号 CHAR(4)
, キャンペーン番号 CHAR(4)
, キャンペーン種別 CHAR(1)
);
INSERT INTO 売上 VALUES
('U101','T111','2025/07/01','KA01','CP01','H')
,('U102','T111','2025/07/01','KA01','CP01','L')
,('U103','T222','2025/07/01','KA02','CP02','H')
,('U104','T222','2025/07/01','KA02','CP02','H')
;
-- 売上明細テーブル（設問に必要な項目に限定）
CREATE TABLE 売上明細 (
売上番号 CHAR(4)
, 売上明細番号 INTEGER
, 商品番号 CHAR(4) REFERENCES 商品(商品番号)
, 商品個数 NUMERIC
, 商品単価 NUMERIC
, PRIMARY KEY (売上番号 ,売上明細番号)
);
INSERT INTO 売上明細 VALUES
('U101',1,'S001',5,90)
,('U101',2,'S002',8,100)
,('U101',3,'S003',3,200)
,('U102',1,'S001',7,90)
,('U102',2,'S002',2,100)
,('U102',3,'S003',11,200)
,('U103',1,'S002',1,100)
,('U103',2,'S002',3,105)
,('U103',3,'S002',2,90)
,('U104',1,'S001',6,92)
,('U104',2,'S001',12,89)
,('U104',3,'S003',9,210)
;
問題文のSQL完成版
パラメータで指定するキャンペーン番号は、変えて試してみてください。
SELECT
CK.キャンペーン種別
,COALESCE(UK.売上計,0) AS 売上計
,COALESCE(UK.原価計,0) AS 原価計
,COALESCE(UK.売上計,0) - COALESCE(UK.原価計,0) AS 粗利計
,CK.キャンペーン実施金額計
FROM
(
SELECT
キャンペーン種別
,SUM(キャンペーン実施金額) AS キャンペーン実施金額計
FROM キャンペーン実施
WHERE キャンペーン番号 = 'CP01' -- パラメータで指定する項目
GROUP BY キャンペーン種別
) CK
LEFT OUTER JOIN
(
SELECT
U.キャンペーン種別
,SUM(M.商品個数 * M.商品単価) AS 売上計
,SUM(M.商品個数 * S.商品原価) AS 原価計
FROM 売上 U
INNER JOIN 売上明細 M ON U.売上番号 = M.売上番号
INNER JOIN 商品 S ON M.商品番号 = S.商品番号
WHERE U.キャンペーン番号 = 'CP01' -- パラメータで指定する項目
GROUP BY U.キャンペーン種別
) UK
ON CK.キャンペーン種別 = UK.キャンペーン種別
;
さいごに
これで、設問２は終わりです。
前回と同じことですが、問題文の全体ではなくポイントだけ紹介しているので、分かり辛いところもあるかもしれません。
下記のコンテンツを合わせてご利用いただくことで、更に理解が深まると思います。
- IPAの過去問を見ながら、本記事を読む。
- 私の個人YouTubeの過去問解説をご視聴いただく（※毎回プレビュー載せるのも何なので、今回はリンクのみ）
- 本記事でご紹介したSQLを動かしてみる。
そして、次回もSQLが続きます。
ここまでお付き合い頂き、ありがとうございましたm(_ _)m
Discussion