IPA H31 DB午後②問１ 感想戦（CASE式中にINを書いてもいいのか）

目次

こんちには。リングフィットアドベンチャーのプレイ時間が「10時間」を超えました。
やはりやったことが自動的に記録に残って視覚化されるのは励みになりますね。
はい。令和2年。IPAデータベーススペシャリスト試験に申し込みしました。頑張ります。
ので、とりあえず昨年の午後２問１を解いてみたので、感想がてらに振り返ります。

先に表題の回答

CASE式中にINで指定できるのか。→できた。

想定読者

僕と同じようなレベルの人でしょうか。
近々データベースを受けようとしている人の励みにでもなればよいかな。と思います。
（自分以外の誰かもやっているとちょっとモチベあがりますよね）
勉強にはならないかもしれないです。息抜きとしてご覧いただければと思います。

解いた問題

www.jitec.ipa.go.jp
ここから拝借。解説はないものの、過去数年分の過去問と回答があるのはとても助かります。
私が説いたのは問１。データベースの設計・実装における全般的な問題。といったところでしょうか。

穴埋めSQL

文章にからSQLを構築する。
構築といっても、ひな形があるのでそれになぞって埋めるだけではあります。
が、人のプログラムというのは意図をつかまないと苦しいですね。

こちらのSQLを完成させよ。となります。
gyazo.com
対応する文章は
gyazo.com
となります。
問われているSQL部分はSELECT区のCASE式。なので、文中の「'1'」だか「'2’」だかの権限の辺りであることは早々に掴むことができました。
悩ましいのは「'（シングルクコーテーション）」の取り扱いでした。文中に名に書かれているので、おそらくこれは数値のカラムではなく、文字のカラムだろうと判断しました。
となると

CASE WHEN 権限レベル  = '2’ OR 権限レベル '1' THEN 所属店番 ELSE NULL END AS 検索店番 -- ’3以外。が示したかったが。このケースの方向だとこうなる'
CASE WHEN 権限レベル  = '1' THEN 行員番号 ELSE NULL END AS 検索行員番号  --'1'だと行員の所属で制限が

となってしまい、どうにも問題文中に埋める回答としては違和感があったので「'」を忘れることにしました。

CASE WHEN 権限レベル  < 3 OR 権限レベル '1' THEN 所属店番 ELSE NULL END AS 検索店番
CASE WHEN 権限レベル  = 1 THEN 行員番号 ELSE NULL END AS 検索行員番号

まぁ、間違いなわけですが。回答は以下の通りです。

CASE WHEN 権限レベル  IN('1','2') OR 権限レベル '1' THEN 所属店番 ELSE NULL END AS 検索店番
CASE WHEN 権限レベル  = 1 THEN 行員番号 ELSE NULL END AS 検索行員番号

IN使えるの知りませんでした。ほんまかいな。ということでSQLServerにサンプルデータ放り込んでみます。

CASE式でのINの活用検証

頑張ってFKつけてSQLServerに読ましてみたけど、思いのほかわかりにくい。
gyazo.com
ということでDDLべたっと。

create table 支店(	
	店番 int,
	店名 varchar(10),
	所在地 varchar(10),
	PRIMARY KEY(店番)
);	
	
create table 端末種別(	
	端末種別コード int,
	端末種別名 varchar(10),
	PRIMARY KEY (端末種別コード)
);	
	
create table 窓口端末(	
	店番 int,
	機番 int,
	端末種別コード int,
	設置場所 varchar(10),
	PRIMARY KEY(店番, 機番),
	FOREIGN KEY(端末種別コード) REFERENCES dbo.端末種別(端末種別コード) 
);	
	
create table 取引種別(	
	取引種別コード int,
	取引種別名 varchar(10),
	PRIMARY KEY(取引種別コード)
);	
	
create table 取引(	
	取引番号 int,
	取引種別コード int,
	取引名 varchar(10),
	PRIMARY KEY(取引番号),
	FOREIGN KEY(取引種別コード) REFERENCES dbo.取引種別(取引種別コード)
);	
	
create table 画面(	
	画面番号 int,
	タイトル varchar(10),
	PRIMARY KEY(画面番号)
);	
	
create table 行員(	
	行員番号 int,
	行員氏名 varchar(10), --氏名は分けておいたほうがいいと思うけど。
	PRIMARY KEY(行員番号)
);	
	
create table 行員所属(	
	行員番号 int,
	適用開始日 datetime2(0),
	所属店番 int,
	権限レベル varchar(2),
	適用終了日 datetime2(0),
	PRIMARY KEY (行員番号, 適用開始日),
	FOREIGN KEY (行員番号) REFERENCES dbo.行員(行員番号),
	FOREIGN KEY (所属店番) REFERENCES dbo.支店(店番)
);	
	
-- ログのとこ少しざつ。((ここに脚注を書きます))
create table ログ収集(	
	店番 int,
	機番 int,
	TS datetime2(7),
	ログテキスト nvarchar(max),
	PRIMARY KEY(店番,機番,TS),
	FOREIGN KEY(店番) REFERENCES dbo.支店(店番),
	FOREIGN KEY(店番,機番) REFERENCES dbo.窓口端末(店番,機番)
);	
	
create table ログ基本(	
	ログID int,
	店番 int,
	機番 int,
	TS datetime2(7),
	行員番号 int,
	取引番号 int,
	画面番号 int,
	年月 datetime2(0),
	PRIMARY KEy(ログID),
	FOREIGN KEY(店番,機番,TS) REFERENCES ログ収集(店番,機番,TS),
	FOREIGN KEY(店番) REFERENCES dbo.支店(店番),
	FOREIGN KEY(店番,機番) REFERENCES dbo.窓口端末(店番,機番),
	FOREIGN KEY(行員番号) REFERENCES dbo.行員(行員番号),
	FOREIGN KEY(画面番号) REFERENCES dbo.画面(画面番号)
);	

ながいですね。SQLServerようにかきなおして実行するとうまくいきました。

declare @hv1 int = 1;
declare @hv2 datetime2(0) = '2020/01/01 00:00:00';

WITH TEMP AS (SELECT
		CASE WHEN 権限レベル IN('1','2') THEN 所属店番 ELSE NULL END AS 検索店番,
		CASE WHEN 権限レベル = '1'		 THEN 行員番号 ELSE NULL END AS 検索行員番号
	FROM dbo.行員所属
	WHERE 行員番号 = @hv1 AND GETDATE() >= 適用開始日
	AND (適用終了日 IS NULL OR GETDATE() < 適用終了日))
SELECT A.店番, B.店名, A.機番, A.TS, A.行員番号, C.行員氏名, A.取引番号, D.取引種別コード
FROM dbo.ログ基本 A, dbo.支店 B, dbo.行員 C, 取引 D, TEMP E
WHERE A.年月 = @hv2
	AND A.店番 = B.店番
	AND A.行員番号 = C.行員番号
	AND A.取引番号 = D.取引番号
	AND (E.検索店番 IS NULL OR E.検索店番 = A.店番)
	AND (E.検索行員番号 IS NULL OR E.検索行員番号 = A.行員番号)

しらなかったなぁ・・・

他

設問２．ツリー構造のデータの持ち方

ついこの間ミック氏の本で読んだのでちょっと感動した。
先に情報があたまにあると、問題のイメージとか問われていることが掴みやすいので得した気分になります。
https://www.amazon.co.jp/%E9%81%94%E4%BA%BA%E3%81%AB%E5%AD%A6%E3%81%B6DB%E8%A8%AD%E8%A8%88-%E5%BE%B9%E5%BA%95%E6%8C%87%E5%8D%97%E6%9B%B8-%E3%83%9F%E3%83%83%E3%82%AF-ebook/dp/B00EE1XPAI/ref=sr_1_1?adgrpid=51694709245&gclid=Cj0KCQiAv8PyBRDMARIsAFo4wK32GQytpRCH1IFwBdqXOESpYUY-hgOqk5aOi1W2jcW35tItRSt39vEaAr2QEALw_wcB&hvadid=338576532191&hvdev=c&hvlocphy=1009630&hvnetw=g&hvqmt=e&hvrand=17291860833842853418&hvtargid=kwd-333952501323&hydadcr=15819_11177362&jp-ad-ap=0&keywords=%E9%81%94%E4%BA%BA%E3%81%AB%E5%AD%A6%E3%81%B6db%E8%A8%AD%E8%A8%88&qid=1582433045&sr=8-1

設問３．パーティションに関する問題

本業のほうでやたらとパーティション問題を抱えまくっててタイムリーですこし悲しい気持ちになった。
基本的に、業務だとINSERT、UPDATEの性能って気にしなくなってしまっているがDB設計という視点から見るともちろん考慮が必要でなるほど。という問題だった。
もうちょっとこの辺は深く理解しないと歯が立たないと感じた。パーティションに深く言及しているような資料があるといいけど。リファレンスでも読めばましになるだろうか。

後半、SQLかいてSQLServerの設定にてこづってさらっと流してしまった。
日本語でテーブルつくるとSSMSがエラー吐きまくって異様にデバッグしにくかったのも原因（全然テーブル認識してくれない。実行したら走るのに）。
gyazo.com
赤線まみれなのがお分かりいただけるだろうか。テーブル認識してないからカラムも全部NG扱いになっている様子。

ということで1問おわり。SQL系の問題ならそこまで苦しまずに解けそう。絡んだ設計思想だったりが理解できてないと途端に苦しくなりそうではある。
また時間をみつけて別の過去問を解くことにする。