SQLパズル 21 飛行機と飛行士の解法その２ メモ

概要

SQLパズル　21 　飛行機と飛行士の解法その２について

Having句を使った関係除算の理解が必要です。

要件

待機中の飛行機すべてを操縦できるパイロットをすべて選択したい。

関係除算

関係演算R÷Sは、関係Rのうち関係Sの全ての項目を含む行の集合を返す演算です。

SQL

DDL

drop table if EXISTS PilotSkills;
drop table if EXISTS Hangar;

CREATE TABLE PilotSkills
(pilot CHAR(15) NOT NULL,
 plane CHAR(15) NOT NULL,
    PRIMARY KEY (pilot, plane));

CREATE TABLE Hangar
(plane CHAR(15) PRIMARY KEY);

INSERT INTO PilotSkills VALUES ('Celko', 'Piper Cub');
INSERT INTO PilotSkills VALUES ('Higgins', 'B-52 Bomber');
INSERT INTO PilotSkills VALUES ('Higgins', 'F-14 Fighter');
INSERT INTO PilotSkills VALUES ('Higgins', 'Piper Cub');
INSERT INTO PilotSkills VALUES ('Jones', 'B-52 Bomber');
INSERT INTO PilotSkills VALUES ('Jones', 'F-14 Bomber');
INSERT INTO PilotSkills VALUES ('Smith', 'B-1 Bomber');
INSERT INTO PilotSkills VALUES ('Smith', 'B-52 Bomber');
INSERT INTO PilotSkills VALUES ('Smith', 'F-14 Fighter');
INSERT INTO PilotSkills VALUES ('Wilson', 'B-1 Bomber');
INSERT INTO PilotSkills VALUES ('Wilson', 'B-52 Bomber');
INSERT INTO PilotSkills VALUES ('Wilson', 'F-14 Fighter');
INSERT INTO PilotSkills VALUES ('Wilson', 'F-17 Fighter');
INSERT INTO PilotSkills VALUES ('KIRIKO', 'F-17 Fighter');

INSERT INTO Hangar VALUES ('B-1 Bomber');
INSERT INTO Hangar VALUES ('B-52 Bomber');
INSERT INTO Hangar VALUES ('F-14 Fighter');

答えのSQL

SELECT Pilot
  FROM PilotSkills AS PS1, Hangar AS H1
 WHERE PS1.plane = H1.plane
 GROUP BY PS1.pilot
HAVING COUNT(PS1.plane) = (SELECT COUNT(*) FROM Hangar)	
;

図

分解して考える

イ

PilotSkillsとHangerのすべての組み合わせを求めた後、
PilotSkillsとHangerのplaneが合致する条件のみを取得する。

この時点で、Celkoは以下理由のため弾かれる
HangerにあるPlaneには乗れないため

sql

-- 1 PilotSKillsとHangerのすべての組み合わせを求める
SELECT PS1.*,H1.*
FROM PilotSkills AS PS1, Hangar AS H1
;

-- 2 
SELECT PS1.*,H1.*
  FROM PilotSkills AS PS1, Hangar AS H1
 WHERE PS1.plane = H1.plane
;

図

ロ

イで求めたレコードをGroup byとHaving句を使って条件指定する。
Hangerの件数と、pilotのplaneの件数を合致しているパイロットを取得する。

HigginsとJonesとKIRIKOは以下理由で弾かれる。
Hangerの総件数(=3)と同じ値にならないため。

結果、SmithとWillsonのみが残る。

sql

-- 2
SELECT PS1.*,H1.*
  FROM PilotSkills AS PS1, Hangar AS H1
 WHERE PS1.plane = H1.plane
;

-- 3
SELECT Pilot
  FROM PilotSkills AS PS1, Hangar AS H1
 WHERE PS1.plane = H1.plane
 GROUP BY PS1.pilot
HAVING COUNT(PS1.plane) = (SELECT COUNT(*) FROM Hangar)	
;

図

一言

数の除算は数を割るだけで一発で処理できる分、
関係除算（というか集合の除算？？）は計算するのに数ステップ要るからややこしい。

参考

SQLパズル　p91

SQLパズル　サポートページ
https://mickindex.sakura.ne.jp/database/db_support_sqlpuzzle.html#LocalLink-p21

達人に学ぶSQL徹底指南書第２版 p133 column　関係除算
関係除算の定義が複数ある

達人に学ぶSQL徹底指南書第２版 p127 バスケット解析

github コミット分
https://github.com/RYA234/SQL_Puzzle_Learning/commit/9a3690b4e91057cb23ed96ef856c4cfa02baa3dc