9-64 厳密な関係除算

SQLパズル

工場テーブル    操縦士テーブル
飛行機名        操縦士ID   操縦可能機
--------        --------  ----------
ジャビィ               1    ジャビィ
ハンター               2    ファルコ
ファルコ               3    ハンター
                       4    ジャビィ
                       4    ファルコ
                       5    ジャビィ
                       5    ハンター
                       5    ファルコ
                       5    ペリカン
                       6    ハンター
                       6    ペリカン
                       7    ジャビィ
                       7    ファルコ
                       7    ペリカン
                       8    ジャビィ
                       8    ハンター
                       8    ファルコ

工場テーブルにある全ての飛行機のみを操縦できる操縦士IDと、操縦可能機を出力する。

出力結果
操縦士ID   飛行機名
--------  ----------
       8    ジャビィ
       8    ハンター
       8    ファルコ

9-9 差集合が空集合かチェックのアレンジ問題です

データ作成スクリプト

create table 工場(飛行機名 char(8));

insert into 工場 values('ジャビィ');
insert into 工場 values('ハンター');
insert into 工場 values('ファルコ');

create table 操縦士(
操縦士ID   number(1),
操縦可能機 char(8));

insert into 操縦士 values(1,'ジャビィ');
insert into 操縦士 values(2,'ファルコ');
insert into 操縦士 values(3,'ハンター');
insert into 操縦士 values(4,'ジャビィ');
insert into 操縦士 values(4,'ファルコ');
insert into 操縦士 values(5,'ジャビィ');
insert into 操縦士 values(5,'ハンター');
insert into 操縦士 values(5,'ファルコ');
insert into 操縦士 values(5,'ペリカン');
insert into 操縦士 values(6,'ハンター');
insert into 操縦士 values(6,'ペリカン');
insert into 操縦士 values(7,'ジャビィ');
insert into 操縦士 values(7,'ファルコ');
insert into 操縦士 values(7,'ペリカン');
insert into 操縦士 values(8,'ジャビィ');
insert into 操縦士 values(8,'ハンター');
insert into 操縦士 values(8,'ファルコ');
commit;

SQL

--■■■not existsを使う方法■■■
select a.操縦士ID,a.操縦可能機
  from 操縦士 a
 where not exists(select b.操縦可能機,count(*) over()
                    from 操縦士 b
                   where b.操縦士ID = a.操縦士ID
                   minus
                  select c.飛行機名,count(*) over()
                    from 工場 c)
order by a.操縦可能機;

--■■■内部結合を使う方法■■■
select 操縦士ID,操縦可能機
from (select b.操縦士ID,b.操縦可能機,BeforeCount1,BeforeCount2,
      count(*) over(partition by 操縦士ID) as AfterCount
        from (select 飛行機名,count(*) over() as BeforeCount1
                from 工場) a,
              (select 操縦士ID,操縦可能機,
               count(*) over(partition by 操縦士ID) as BeforeCount2
                 from 操縦士) b
       where a.飛行機名 = b.操縦可能機)
where AfterCount = all(BeforeCount1,BeforeCount2)
order by 操縦可能機;

解説

内部結合を使う方法が、面白いと思います。

---