この記事は前回の続きです。
第1回：https://machi11038004.hatenablog.com/entry/2020/10/19/182604
第2回（前回）：https://machi11038004.hatenablog.com/entry/2020/10/19/182807

前回までは、基本情報処理技術者試験の問題を解くことを最終目的とした上で、
クエリの基本をおさえる試みでした。

前回までで必要な武装はできたはずなので今回は問題に挑みます。
内容は以下の通り。

第一回---------------------------------
1.準備運動
2.とりあえず問題を見てみる
3.クエリの基本的な文
4.アスタリスク(*)
第二回---------------------------------
5.関数、式、述語
6.集合演算、結合
7.その他キーワード
第三回（本記事）---------------------------------
8.問題に挑む
　8.1 問題を見る
　8.2 問題を解く
　8.3 ほんとにそうか？
9.おわりに

8.問題を解く

8.1 問題を見る

以下に問題を貼り付けます。
令和元年秋期のデータベース問題の設問1です。

ちなみに、「ちょっと覗きに来ただけ」という方は読み込まなくてもOKです。
後ほど全体感で話をします。

問題ここから-------------
書籍及び貸出情報を管理する関係データベースの設計及び運用に関する次の記述を読んで，設問1～3に答えよ。

　D社の部署である資料室は，業務に関連する書籍を所蔵しており，従業員への貸出しを2015年4月から実施している。
　所蔵する書籍を管理するデータベースは，書籍の情報を管理する書籍情報表と貸出状況を管理する貸出表とで構成されている。データベース構成を，図1に示す。下線付きの項目は主キーを表し，下破線付きの項目は外部キーを表す。各書籍は1冊しか所蔵していない。
f:id:machi11038004:20201014230148p:plain

〔貸出表に関する説明〕
（１）：従業員に書籍を貸し出す際は，一意の貸出番号，貸し出す書籍のISBNコード，従業員番号，貸出日及び返却予定日を設定し，返却日にはNULLを設定したレコードを追加する。
（２）：書籍が返却されたら，対象のレコードの返却日に返却された日付を設定する。

設問1

次のSQL文は，ISBNコードが ISBN978-4-905318-63-7 の書籍の貸出し状態を表示するSQL文である。ISBNコードで貸出表を検索し，最も新しい貸出日のレコードの返却日にNULLが設定されている場合は，"貸出中"が表示される。また，最も新しい貸出日のレコードの返却日に日付が設定されている場合，及び貸出実績のない書籍の場合は，"貸出可"が表示される。　□に入れる正しい答えを，解答群の中から選べ。ここで，検索に使用するISBNコードの書籍は必ず所蔵されているものとする。また，返却された書籍はその日のうちに再び貸し出されることはない。 f:id:machi11038004:20201014230720p:plain 問題ここまで-------------

図書館のような、書籍の貸出のシステムに関する問題です。

目的の本（=ISBNコードが ISBN978-4-905318-63-7 の書籍）が貸出中なら"貸出中"、
そうでないなら"貸出可"と出力するSQL文を完成させなさいと言ってるわけですね。

登場するテーブルは2つで、書籍の情報がまとまっている「書籍情報表」と、
貸出記録が記録されている「貸出表」です。

8.2 問題を解く

まず、おや？と思うのがUNIONの存在です。
これにより、大きく2つのSELECT文が現れて、その二つが足されていることが分かります。
f:id:machi11038004:20201019121646p:plain
とはいえ、それぞれが何をしようとしているのかよくわかりません。
下のSELECT文2は全貌が見えているので、こいつの仕事を明確にできれば、
上のSELECT文１に必要な記述が見えてきそうです。

というわけで、下のSELECT文2に注目します。
f:id:machi11038004:20201019133717p:plain
一行目のSELECT句に注目すると、「SELECT DISTINCT 書籍情報表.ISBNコード, '貸出可' AS 書籍状態
FROM 書籍情報表」
より、貸出可のものを表示しようとしていることがわかります。

さらに、WHERE句以降を見ると、
NOT EXISTS ( SELECT 貸出表.ISBNコード FROM 貸出表
　　　　　　　　WHERE 貸出表.ISBNコード = 'ISBN978-4-905318-63-7')
という記述があります。これは、
「貸出表にISBN'978-4-905318-63-7'の本が存在しなければ（つまり貸出実積がなければ）」
という意味の一文です。

そのため、SELECT文２の仕事は、
「ISBNコードで貸出表を検索し，最も新しい貸出日のレコードの返却日にNULLが設定されている場合は，"貸出中"が表示される。また，最も新しい貸出日のレコードの返却日に日付が設定されている場合，及び貸出実績のない書籍の場合は，"貸出可"が表示される。　」
この部分を担っている文ということがわかります。

ということは、SELECT文１の仕事はこれ以外の部分です。
f:id:machi11038004:20201019133823p:plain
つまり、「ISBNコードで貸出表を検索し，最も新しい貸出日のレコードの返却日にNULLが設定されている場合は，"貸出中"が表示される。また，最も新しい貸出日のレコードの返却日に日付が設定されている場合，及び貸出実績のない書籍の場合は，"貸出可"が表示される。　」
この部分ということになります。

この部分は
「最も新しい貸出日のレコードの返却日に○○が設定されている場合」という文言が２回も出てきます。
その結果次第で、処理を分けろということです。

ここまで来たら後は気合です。
SELECT文を０から構築する必要はありません。問題と選択肢を見ながら想像力を働かせます。
①貸出表からISBN978-4-905318-63-7の"最も新しい貸出日”のレコードを抽出して、
②場合分けで表示を"貸出中"か"貸出可"に変えて出力すればよさそうです。

①について、貸出表から"ISBN978-4-905318-63-7の最も新しい貸出日”を返すのは、WHERE句の
(SELECT [b] FROM 貸出表 WHERE 貸出表.ISBNコード = 'ISBN978-4-905318-63-7')
の部分です。
[b]には、MAX(貸出表.貸出日)を入れれば、
ISBN978-4-905318-63-7書籍の最も新しい貸出日のデータが一行得られます。
ISBNコードを「かつ」で指定して、MAX関数に日付を入れれば、
該当する書籍の最も新しい日付が返ってくるからです。

②の場合分けについては、問題文に
「（中略）返却日にNULLが設定されている場合は，"貸出中"が表示される。
また，（中略）日付が設定されている場合，（中略）"貸出可"が表示される。」
とはっきり書かれていますので、
「貸出表.返却日 IS NULL THEN '貸出中'
　　WHEN 貸出表.返却日 IS NOT NULL THEN '貸出可'」
を記述すれば意図通りの場合分けになります。
返却日は、NULLでなければ必ず返却された日が入っているからです。

以上より、「エ、イ」が解答です。

8.3 ほんとにそうか？

正解はこれで間違いないのですが、せっかくなのでSQL文を使って確かめてみます。

問題文からそれぞれのテーブルを再現しました。中身は私が適当に作ったものです。

書籍情報表

      isbnコード       |      書籍名      |    著者名     |  出版社名  | 出版年
-----------------------+------------------+---------------+------------+--------
 ISBN978-4-905318-63-7 | ドラゴソボール   | 島山 明       | 集国社     | 1999
 ISBN978-4-905319-64-8 | 名探偵ドイル     | 赤山 剛昌     | 中学館     | 2000
 ISBN978-4-905320-65-9 | 風景水彩画集     | 絵 うま太郎   | 絵乃会社   | 2001
 ISBN978-4-905321-66-0 | 静物油絵集       | 絵 うま太郎   | 絵乃会社   | 2002
 ISBN978-4-905322-67-1 | 国内絶景写真集   | 写真 撮太郎   | 絵乃会社   | 2003
 ISBN978-4-905323-68-2 | 孤独に学ぶRuby   | ﾙﾋﾞｰ･ﾀﾞｲｽｷｰ   | 技術大好社 | 2005
 ISBN978-4-905324-69-3 | 孤独に学ぶPython | ﾊﾟｲｿﾝ・ﾏｼﾞｽｷｰ | 技術大好社 | 2007
(7 行)

貸出表

 貸出番号 |      isbnコード       | 従業員番号 |   貸出日   | 返却予定日 |   返却日
----------+-----------------------+------------+------------+------------+------------
        1 | ISBN978-4-905319-64-8 |       1111 | 2000-01-01 | 2000-01-07 | 2000-01-07
        2 | ISBN978-4-905321-66-0 |       2222 | 2000-01-02 | 2000-01-08 | 2000-01-08
        3 | ISBN978-4-905320-65-9 |       1111 | 2000-01-03 | 2000-01-09 | 2000-01-09
        4 | ISBN978-4-905322-67-1 |       3333 | 2000-01-04 | 2000-01-10 | 2000-01-10
        5 | ISBN978-4-905323-68-2 |       3333 | 2000-01-05 | 2000-01-11 |
        6 | ISBN978-4-905319-64-8 |       4444 | 2000-01-06 | 2000-01-12 |
ｺｺを変更→7 | ISBN978-4-905318-63-7 |       1111 | 2000-01-01 | 2000-01-07 |
(7 行)

コマンド

SELECT 貸出表.ISBNコード,
       CASE WHEN 貸出表.返却日 IS NULL THEN '貸出中'
       WHEN 貸出表.返却日 IS NOT NULL THEN '貸出可'
       END AS 書籍状態
FROM 貸出表
WHERE 貸出表.ISBNコード = 'ISBN978-4-905318-63-7'
      AND 貸出表.貸出日 = (SELECT MAX(貸出表.貸出日) FROM 貸出表
                           WHERE 貸出表.ISBNコード = 'ISBN978-4-905318-63-7')
UNION ALL
SELECT DISTINCT 書籍情報表.ISBNコード, '貸出可' AS 書籍状態
FROM 書籍情報表
WHERE 書籍情報表.ISBNコード = 'ISBN978-4-905318-63-7'
      AND NOT EXISTS (SELECT 貸出表.ISBNコード FROM 貸出表
                      WHERE 貸出表.ISBNコード = 'ISBN978-4-905318-63-7');

・ISBN978-4-905318-63-7の書籍が貸出表にあって返却日が入っている
結果

      isbnコード       | 書籍状態
-----------------------+----------
 ISBN978-4-905318-63-7 | 貸出可
(1 行)

・ISBN978-4-905318-63-7の書籍が貸出表にあって返却日がNULL
結果

      isbnコード       | 書籍状態
-----------------------+----------
 ISBN978-4-905318-63-7 | 貸出中
(1 行)

・ISBN978-4-905318-63-7の書籍が貸出表にない
結果

      isbnコード       | 書籍状態
-----------------------+----------
 ISBN978-4-905318-63-7 | 貸出可
(1 行)

なるほど確かに貸出可か貸出中かを判断するクエリです。
UNION ALLがあるのに出力が1行なのは、上のSELECT文か下のSELECT文のどちらかが当てはまると、
もう片方のSELECT文は返すレコードが無いという貸出システム上の仕組みだからです。

おしまい

9.おわりに

お疲れさまでした。

これは数ある問題の中のたった一問にすぎませんし、
奥深～いデータベースの世界の氷山の一角だと思いますが、
アレルギーを払拭することが大事だと思うので、こういった記事を書いてみました。

個人的には勉強になり中々楽しかったです。

ご意見ご指摘あればご連絡ください。
その時もし返信が遅くなりましたらすみません。

参考ウェブページ
https://www.fe-siken.com/

machi11038004’s diary

私はFEデータベースを克服したい(3/3)

8.問題を解く

8.1 問題を見る

8.2 問題を解く

8.3 ほんとにそうか？

9.おわりに